temp

declare @dt1 datetime = '20190729 00:00'
declare @dt2 datetime = '20190730 23:59'

declare @tm1 datetime = '19000101 00:00'
declare @tm2 datetime = '19000101 23:00'

declare @task TABLE (Id uniqueidentifier)
INSERT INTO @task
SELECT id
FROM [oktell_settings].[dbo].[A_TaskManager_Tasks]

declare @operators TABLE (Id uniqueidentifier)
INSERT INTO @operators
SELECT id
FROM [oktell_settings].[dbo].[A_Users]

declare @tmp table(idoperator uniqueidentifier, summary int, success int, failed int, lentime float,
v1 int, v2 int, v3 int, v4 int, v5 int, v6 int, v7 int, v8 int, v9 int, v10 int, v11 int, wd int)

declare @temp table (ideffort uniqueidentifier,idchain uniqueidentifier,idoperator uniqueidentifier, [CallResult] int, UserResult int, datetimestart datetime, lenTime float)

if ((select top 1 [type] from [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task]
    where id in (select * from @task)) = 10)
    begin
        /*--------------------------------------------------*/
        /*--------------------- IVR ------------------------*/
        /*--------------------------------------------------*/

        if (not exists (select * from [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo]
                        where id = 'AB000000-0000-0000-0000-000000000000'))
                insert into [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo]
                values ('AB000000-0000-0000-0000-000000000000','IVR',0,0,0,0,0,0,null,null,null,null,null,null,getdate())

        insert into @temp
        select Ideffort, idchain, idoperator, [CallResult], UserResult, Datetimestart, lenTime
            from    [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections] ef1
            where   ef1.Datetimestart between @dt1 and @dt2
                    and ef1.TimeStart  between @tm1 and @tm2
                    and IDTask in (select * from @task)
                    and ((IdOperator is null and (callresult in (13,6,7, 19, 23,24,26,27,30))) or IdOperator in (select id from @operators))

    end
else
begin

if ((select top 1 isoutput from [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task]
    where id in (select * from @task)) = 1)
    begin
        /*--------------------------------------------------*/
        /*------------------- ИСХОДЯЩИЕ --------------------*/
        /*--------------------------------------------------*/
        insert into @temp
        select tb1.[Ideffort], idchain, tb1.idoperator, [CallResult], UserResult, Datetimestart, lenTime
        from (
                select  ef1.[Ideffort], min(datetimestart) dt, ef1.IdOperator
                from    [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections] ef1
                where isoutput = 0
                        and ef1.Datetimestart between @dt1 and @dt2
                        and ef1.TimeStart  between @tm1 and @tm2
                        and IDTask in (select * from @task)
                        and ((IdOperator is null and (callresult in (13,7)))
                            or IdOperator in (select id from @operators))
                Group By ef1.IdEffort, IdOperator

            ) tb1
        inner join
            (select ideffort, idchain, idoperator, [CallResult],  UserResult, datetimestart,lenTime
            from [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
            where idchain is not null or callresult in (2,3,7,13,6)
            ) ef3
        on (tb1.ideffort = ef3.ideffort) and (tb1.dt = ef3.Datetimestart) and ((tb1.idoperator = ef3.idoperator) or (( tb1.idoperator is NULL ) and ( ef3.idoperator is NULL )))

    end
else
    begin
        /*--------------------------------------------------*/
        /*------------------- ВХОДЯЩИЕ ---------------------*/
        /*--------------------------------------------------*/
        insert into @temp
        select tb1.[Ideffort], idchain, tb1.idoperator, [CallResult], UserResult, Datetimestart, lenTime
        from (
                select  ef1.[Ideffort], min(datetimestart) dt, ef1.IdOperator
                from    [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections] ef1
                where isoutput = 0
                        and ef1.Datetimestart between @dt1 and @dt2
                        and ef1.TimeStart  between @tm1 and @tm2
                        and IDTask in (select * from @task)
                        and ((IdOperator is null and (callresult in (13,6, 19, 23,24,26,27,30)))
                            or IdOperator in (select id from @operators))
                Group By ef1.IdEffort, IdOperator
            ) tb1
        inner join
            (   select ideffort, idchain, idoperator, [CallResult], UserResult,  datetimestart, lentime
                from [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
                where idchain is not null or callresult in (19,13,6)
            ) ef3
        on (tb1.ideffort = ef3.ideffort) and (tb1.dt = ef3.Datetimestart) and ((tb1.idoperator = ef3.idoperator) or (( tb1.idoperator is NULL ) and ( ef3.idoperator is NULL )))


    end
end

--select * from @temp
--order by datetimestart

insert into @tmp
select  IdOperator,
        count (cast (ideffort as nvarchar(50))),
        sum(p0) v0,
        sum(p00) v00,
        avg(lenTime),
        sum(p1) v1,
        sum(p2) v2,
        sum(p3) v3,
        sum(p4) v4,
        sum(p5) v5,
        sum(p6) v6,
        sum(p7) v7,
        sum(p8) v8,
        sum(p9) v9,
        sum(p10) v10,
        sum(p11) v11,
        0
from
(
select ideffort , IdOperator, DateTimeStart, lenTime,
case when CallResult=5 or CallResult=18 then 1 else 0 end as p0,
case when CallResult != 5 and CallResult != 18 then 1 else 0 end as p00,
case when UserResult=1 then 1 else 0 end as p1,
case when UserResult=2 then 1 else 0 end as p2,
case when UserResult=3 then 1 else 0 end as p3,
case when UserResult=4 then 1 else 0 end as p4,
case when UserResult=5 then 1 else 0 end as p5,
case when UserResult=6 then 1 else 0 end as p6,
case when UserResult=7 then 1 else 0 end as p7,
case when UserResult=8 then 1 else 0 end as p8,
case when UserResult=9 then 1 else 0 end as p9,
case when UserResult=10 then 1 else 0 end as p10,
case when UserResult=11 then 1 else 0 end as p11
from @temp
) t
group by IdOperator

update @tmp
set wd = daycnt
from
(
select IdOperator id, count(distinct DateStart) daycnt
from oktell_cc_temp..A_Cube_CC_OperatorStates os
where DateStart between @dt1 and @dt2
and cast(
    cast ( cast('1900-01-01' as datetime) as float) +
    cast(TimeStart as float) - floor(cast(TimeStart as float))
                        as datetime) between @tm1 and @tm2
group by idoperator) t
where idoperator=t.Id

--select * from @tmp

declare @sc int
declare @fl int
declare @sm int

select @sc = count (distinct (cast(idchain as nvarchar(40))) )
from @temp
where CallResult = 5 or CallResult = 18

select @fl = count (distinct (cast(idchain as nvarchar(40))) )
from @temp
where CallResult != 5 and CallResult != 18
and idchain not in (select idchain
from @temp
where CallResult = 5 or CallResult = 18 )

select @sm = @fl + @sc


    select  case when ( t.Idoperator = 'AB000000-0000-0000-0000-000000000000' ) then 'IVR' else isnull(oi.Name,'Потеряно по задаче') end Name,
            t.summary,
            t.success,
            t.failed,
            case when t.failed + t.success = 0 then 100 else round(t.success*100/(t.success+t.failed), 0) end prc,
            case when t.failed + t.success = 0 then 0 else round(t.failed*100/(t.success+t.failed), 0) end prcfailed,
            case
            when oi.Name is null then null
            else (cast(floor(t.lentime/3600) as nvarchar (10)))
                    + ':' +
                    case
                    when floor(t.lentime/60) < 10 then ('0'+ cast(floor(t.lentime/60) as nvarchar (10)))
                    when floor(t.lentime/60) >= 10 then (cast(floor(t.lentime/60) as nvarchar (10)))
                    end     + ':' +
                    case
                    when floor(t.lentime) - floor(t.lentime/60)*60 < 10 then ('0'+cast(floor(t.lentime) - floor(t.lentime/60)*60 as nvarchar (10)))
                    when floor(t.lentime) - floor(t.lentime/60)*60 >= 10 then (cast(floor(t.lentime) - floor(t.lentime/60)*60 as nvarchar (10)))
                    end
            end lentime,
            case when oi.Name is null then null
            else t.v1 end as [v1],
            case when oi.Name is null then null
            else t.v2 end as [v2],
            case when oi.Name is null then null
            else t.v3 end as [v3],
            case when oi.Name is null then null
            else t.v4 end as [v4],
            case when oi.Name is null then null
            else t.v5 end as [v5],
            case when oi.Name is null then null
            else t.v6 end as [v6],
            case when oi.Name is null then null
            else t.v7 end as [v7],
            case when oi.Name is null then null
            else t.v8 end as [v8],
            case when oi.Name is null then null
            else t.v9 end as [v9],
            case when oi.Name is null then null
            else t.v10 end as [v10],
            case when oi.Name is null then null
            else t.v11 end as [v11],
            case when oi.Name is null then null
            else t.wd end as [wd]
    from  @tmp t
    left join oktell_cc_temp..A_Cube_CC_Cat_OperatorInfo oi
    on oi.Id=t.idoperator
    union all
    Select 'Всего', @sm, @sc, @fl,
    case when @sm = 0 then 100 else round(@sc*100/@sm,0) end,
    case when @sm = 0 then 0 else 100-round(@sc*100/@sm,0) end,
    null,
     sum(v1), sum(v2), sum(v3), sum(v4), sum(v5), sum(v6), sum(v7), sum(v8), sum(v9), sum(v10), sum(v11),null
    from @tmp