Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement