Северсталь Рабочее время оперов В1

select * into #operators
      from @operators
if ( @@rowcount = 0 )
    insert into #operators
    SELECT id
    FROM [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo]

select * into #tasks
      from @tasks
if ( @@rowcount = 0 )
    insert into #tasks
        Select id
  FROM [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task]


create table #temp(IdOperator uniqueidentifier, IdTask uniqueidentifier, State int, LenTime float)

insert into #temp
select IdOperator, IdTask, State, LenTime
from(
    select IdOperator, IdTask, State,
        case when DateStart = DateStop then cast(TimeStop-TimeStart as float)
        else (DateDiff(day, DateStart, DateStop)-1)*cast((@tm2-@tm1) as float)+cast((@tm2-TimeStart)+(TimeStop-@tm1) as float) end LenTime
    from(
        select IdOperator, IdTask, State, DateStart,
            case when DateTimeStart-DateStart<@tm1 then @tm1 else DateTimeStart-DateStart end TimeStart,
            DateStop,
            case when DateTimeStop-DateStop>@tm2 then @tm2 else DateTimeStop-DateStop end TimeStop, DateTimeStart d1, datetimestop d2
        from(
            select IdOperator, IdTask, State, DateTimeStart,
                case when DateStart<@dt1 then @dt1 else DateStart end DateStart,
                DateTimeStop,
                case when dateadd(day, datediff(day, 0, DateTimeStop),0)>@dt2 then @dt2 else dateadd(day, datediff(day, 0, DateTimeStop),0) end DateStop
            from
            [oktell_cc_temp].[dbo].[A_Cube_CC_OperatorStates] os
            where DateTimeStart<=dateadd(day, datediff(day, 0, @dt2),0)+@tm2 and DateTimeStop>=@dt1+@tm1
            and IdOperator in (select id from #operators)
        )t)t)t
where LenTime>0


select oi.Name,
    round(task*86400, 1) task,
    cast(floor(task*24) as nvarchar(10)) + stuff(convert(nvarchar(10), cast(task as datetime), 108), 1, 2, '') taskLabel,
    round(othertask*86400, 1) othertask,
    cast(floor(othertask*24) as nvarchar(10)) + stuff(convert(nvarchar(10), cast(othertask as datetime), 108), 1, 2, '') othertaskLabel,
    round(talk*86400, 1) talk,
    cast(floor(talk*24) as nvarchar(10)) + stuff(convert(nvarchar(10), cast(talk as datetime), 108), 1, 2, '') talkLabel,
    round(rest*86400, 1) rest,
    cast(floor(rest*24) as nvarchar(10)) + stuff(convert(nvarchar(10), cast(rest as datetime), 108), 1, 2, '') restLabel,
    round(ready*86400, 1) ready,
    cast(floor(ready*24) as nvarchar(10)) + stuff(convert(nvarchar(10), cast(ready as datetime), 108), 1, 2, '') readyLabel
from(
    select a.IdOperator,
        sum(case when State in (1,2,3,4,6,7,13,14) and t.id is not null then LenTime else 0 end) task,
        sum(case when State in (1,2,3,4,6,7,13,14) and t.id is null then LenTime else 0 end) othertask,
        sum(case when State in (9) then LenTime else 0 end) rest,
        sum(case when State in (10,12) then LenTime else 0 end) ready,
        sum(case when State in (5,8) then LenTime else 0 end) talk
    from #temp a
    left join #tasks t
    on a.IdTask=t.id
    group by a.IdOperator)t
inner join
    oktell_cc_temp..A_Cube_CC_Cat_OperatorInfo oi
on oi.Id=t.IdOperator
order by oi.Name

drop table #operators
drop table #temp
drop table #tasks