Advertisement
WorkAkkaunt

Гетт такси Рабочее время операторов V3 - Backup

Jul 29th, 2019
202
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 26.43 KB | None | 0 0
  1. /*DECLARE @dt1 DATETIME = '20190703 00:00'
  2. DECLARE @dt2 DATETIME ='20190704 23:59'
  3. DECLARE @tm1 DATETIME = '19000101 00:00'
  4. DECLARE @tm2 DATETIME = '19000101 23:59'
  5. DECLARE @autoparam_05_operators TABLE
  6.   (
  7.      id UNIQUEIDENTIFIER
  8.   )
  9.  
  10.   DECLARE @tasks TABLE
  11.   (
  12.      id UNIQUEIDENTIFIER
  13.   )
  14.   insert into @tasks
  15.   select distinct id from oktell.dbo.A_TaskManager_Tasks*/
  16.  
  17. --маковетцки - 9EE34782-68B6-4C6C-819F-CD352D670BCF
  18.  
  19. /*
  20.   Дата: 05.09.2018
  21.   Изменил:Казанцев В.С.
  22.   Изменения: 28.08.2018 - статус перерыв разбит на составляющие, добавлены исходящие, входящие, сумма. Добавлено время постобработки и ее среднее.
  23.              05.09.2018 - Поправлены входящие звонки (+условие без учета IVR), исправлены все исходящие. В разговоре разбит на state = 6 и время перед самим звонком с оператором,
  24.              поле beforeTask.
  25.              20.11.2018 - Разговоры не по задаче - добавлена длительность всех исходящих звонков, совершенных оператором, когда он находился в статусе перерыв.
  26.              13.12.2018 - beforetask1 и 23 изменены исходные статусы.  
  27.  
  28. */
  29. DECLARE @FilteredTable [dbo].[TableType]
  30.  
  31. declare @optemp table(  [id] uniqueidentifier )
  32.  
  33. --insert into @optemp
  34. --select * from @autoparam_05_operators
  35. --if ( @@rowcount = 0 )
  36. --    insert into @autoparam_05_operators
  37. --SELECT '5C614C90-8F16-4C4A-B299-F701EE05C713'  -- id  FROM [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo]
  38.  
  39. insert into @optemp
  40. select * from @autoparam_05_operators
  41. if ( @@rowcount = 0 )
  42.     insert into @autoparam_05_operators
  43. SELECT '9EE34782-68B6-4C6C-819F-CD352D670BCF'
  44.  
  45. select * into #taskstmp
  46.       from @tasks
  47. if ( @@rowcount = 0 )
  48.     insert into #taskstmp
  49.         Select id
  50.   FROM [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task]
  51.  
  52. declare @temp_talk  table (idoperator uniqueidentifier, timetalk int)
  53. --------------------------------------------ИСХОДЯЩИЕ ЗВОНКИ, СОВЕРШЕННЫЕ В ПЕРЕРЫВАХ, ИДУТ ДОПОМ К РАЗГОВОРАМ НЕ ПО ЗАДАЧЕ------------------------------
  54. insert into @temp_talk
  55. select AUserId op,sum(datediff(second,TimeAnswer,TimeStop)) tm from oktell.dbo.A_Stat_Connections_1x1 sc with (nolock)
  56. inner join [oktell_cc_temp].[dbo].[A_Cube_CC_OperatorStates] os with (nolock) on  os.IdOperator in (select * from @autoparam_05_operators) and sc.AUserId = os.IdOperator and sc.timestart between os.DateTimeStart and os.DateTimeStop
  57. and os.state = 9  --and cast(os.DateTimeStart as date) between @dt1 and @dt2 and cast(cast(os.DateTimeStart as time) as datetime) between @tm1 and @tm2
  58. where ConnectionType = 1
  59. and ReasonStart = 3
  60. and cast(sc.TimeStart as date) between @dt1 and @dt2 and cast(cast(sc.TimeStart as time) as datetime) between @tm1 and @tm2
  61. group by AUserId
  62.  
  63. --select * from @temp_talk
  64.  
  65. SELECT [idtask]
  66.       ,[IdOperator]
  67.       ,[State]
  68.       ,icode
  69.       ,[DateTimeStart] dtbegin
  70.       ,[TimeStart]
  71.       ,[DateTimeStop] dtend
  72.       ,[LenTime]
  73. into #temp
  74. FROM [oktell_cc_temp].[dbo].[A_Cube_CC_OperatorStates]
  75. where datetimestart between @dt1 and @dt2
  76. and idoperator in (select * from @autoparam_05_operators)
  77.  
  78.  
  79. --select * from #temp  
  80. ----------------------------------------------------------------
  81. ------- разбиваем промежутки охватывающие несколько дней -------
  82. ----------------------------------------------------------------
  83.  
  84. insert into #temp
  85. select idtask
  86.         ,[IdOperator]
  87.       ,[State]
  88.       ,icode
  89.       ,dtbegin
  90.       ,[TimeStart],
  91.         cast (floor(cast(dtbegin as float)) + cast(@tm2 as float)
  92.         as datetime),
  93.         (cast(@tm2 as float ) - cast([TimeStart] as float) )*86400
  94. from #temp  
  95. where datepart(dd,dtbegin)!= datepart(dd,dtend )
  96.  
  97. --select * from #temp where [State] = 9
  98.  
  99. insert into #temp
  100. select idtask
  101.         ,[IdOperator]
  102.       ,[State] ,icode,
  103.         cast (floor(cast(dtEnd as float)) + cast(@tm1 as float)
  104.         as datetime) ,
  105.         @tm1,
  106.         dtEnd,
  107.         (cast(dtEnd as float) - floor(cast(dtEnd as float)) - cast(@tm1 as float)) *86400
  108. from #temp  
  109. where datepart(dd,dtbegin)!= datepart(dd,dtend )
  110.  
  111. --select * from #temp  
  112.  
  113. ----------------------------------------------------------------
  114. ------- разбиваем промежутки пересекающиеся с tm1 и tm2 --------
  115. ----------------------------------------------------------------
  116.  
  117. insert into #temp
  118. select idtask
  119.         ,[IdOperator]
  120.       ,[State] ,icode,
  121.         cast (floor(cast(dtbegin as float)) + cast(@tm1 as float)
  122.         as datetime) ,
  123.         @tm1,
  124.         dtEnd,
  125.         (cast(dtEnd as float) - floor(cast(dtbegin as float)) - cast(@tm1 as float)) *86400
  126. from #temp  
  127. where  
  128.     [TimeStart]
  129.     <  
  130.     cast(@tm1 as float)
  131.     and
  132.     cast(dtend as float) - floor(cast(dtend as float)) - cast(@tm1 as float)  
  133.     >  
  134.     0.00001
  135.    
  136. insert into #temp  
  137. select idtask
  138.         ,[IdOperator]
  139.         ,[State],icode ,
  140.         dtBegin,
  141.         [TimeStart],
  142.         cast (floor(cast(dtbegin as float)) + cast(@tm2 as float)  
  143.         as datetime),    
  144.         (floor(cast(dtbegin as float)) + cast(@tm2 as float) - cast(dtBegin as float)) *86400
  145. from #temp  
  146. where  
  147.     cast(dtend as float) - floor(cast(dtend as float))- cast(@tm2 as float)
  148.     >  
  149.     0.00001
  150.     and
  151.     [TimeStart]
  152.     <  
  153.     cast(@tm2 as float)
  154. order by dtbegin
  155.  
  156. --select * from #temp
  157. --order by dtbegin
  158.  
  159. ----------------------------------------------------------------
  160. -----------Удаляем промежутки которые тока что разбили----------
  161. ----------------------------------------------------------------
  162.  
  163. delete from #temp  
  164. where datepart(dd,dtbegin)!= datepart(dd,dtend )          
  165.         or  
  166.         cast(TimeStart as float)
  167.         <  
  168.         cast(@tm1 as float)
  169.         or
  170.         cast(dtend as float) - floor(cast(dtend as float)) - cast(@tm2 as float)
  171.         >  
  172.         0.00001
  173.         or
  174.         [LenTime] < 0
  175.         or [LenTime] is null
  176.            
  177. --select * from #temp
  178. --order by dtbegin
  179.  
  180. INSERT INTO @FilteredTable
  181. SELECT UserId, [State], TimeChange, ICode
  182.   FROM [oktell].[dbo].[A_UserStateHistory]
  183. where TimeChange between @dt1 and @dt2 and UserId in (select * from @autoparam_05_operators) and CAST(TimeChange as time) between CAST(@tm1 as time) and CAST(@tm2 as time)
  184. ORDER BY TimeChange
  185.  
  186. DECLARE @OperatorSum TABLE (UserId uniqueidentifier, LenTime float)
  187.  
  188. INSERT INTO @OperatorSum
  189. SELECT UserId
  190.     ,[dbo].[CalculateLenBreaksForUserStates](@FilteredTable, UserId)
  191. FROM @FilteredTable
  192. GROUP BY UserId
  193.  
  194. select  oi.name,
  195.         (cast(floor(task/3600) as nvarchar (10)))          
  196.                     + ':' +
  197.                     case
  198.                     when floor(task/60 - floor(task/3600)*60) < 10 then ('0'+ cast(floor(task/60 - floor(task/3600)*60) as nvarchar (10)))
  199.                     when floor(task/60 - floor(task/3600)*60) >= 10 then (cast(floor(task/60 - floor(task/3600)*60) as nvarchar (10)))
  200.                     end     + ':' +            
  201.                     case
  202.                     when floor(task) - floor(task/60)*60 < 10 then ('0'+cast(floor(task) - floor(task/60)*60 as nvarchar (10)))
  203.                     when floor(task) - floor(task/60)*60 >= 10 then (cast(floor(task) - floor(task/60)*60 as nvarchar (10)))
  204.                     end  taskLabel,
  205.         round(task, 1) task,
  206.                     (cast(floor(beforetask/3600) as nvarchar (10)))        
  207.                     + ':' +
  208.                     case
  209.                     when floor(beforetask/60 - floor(beforetask/3600)*60) < 10 then ('0'+ cast(floor(beforetask/60 - floor(beforetask/3600)*60) as nvarchar (10)))
  210.                     when floor(beforetask/60 - floor(beforetask/3600)*60) >= 10 then (cast(floor(beforetask/60 - floor(beforetask/3600)*60) as nvarchar (10)))
  211.                     end     + ':' +            
  212.                     case
  213.                     when floor(beforetask) - floor(beforetask/60)*60 < 10 then ('0'+cast(floor(beforetask) - floor(beforetask/60)*60 as nvarchar (10)))
  214.                     when floor(beforetask) - floor(beforetask/60)*60 >= 10 then (cast(floor(beforetask) - floor(beforetask/60)*60 as nvarchar (10)))
  215.                     end  beforetaskLabel,
  216.         round(beforetask, 1) beforetask,
  217.                     (cast(floor(post_len/3600) as nvarchar (10)))          
  218.                     + ':' +
  219.                     case
  220.                     when floor(post_len/60 - floor(post_len/3600)*60) < 10 then ('0'+ cast(floor(post_len/60 - floor(post_len/3600)*60) as nvarchar (10)))
  221.                     when floor(post_len/60 - floor(post_len/3600)*60) >= 10 then (cast(floor(post_len/60 - floor(post_len/3600)*60) as nvarchar (10)))
  222.                     end     + ':' +            
  223.                     case
  224.                     when floor(post_len) - floor(post_len/60)*60 < 10 then ('0'+cast(floor(post_len) - floor(post_len/60)*60 as nvarchar (10)))
  225.                     when floor(post_len) - floor(post_len/60)*60 >= 10 then (cast(floor(post_len) - floor(post_len/60)*60 as nvarchar (10)))
  226.                     end  post_lenLabel,
  227.         round([post_len], 1)[post_len],                
  228.                     (cast(floor(othtask/3600) as nvarchar (10)))          
  229.                     + ':' +
  230.                     case
  231.                     when floor(othtask/60 - floor(othtask/3600)*60) < 10 then ('0'+ cast(floor(othtask/60 - floor(othtask/3600)*60) as nvarchar (10)))
  232.                     when floor(othtask/60 - floor(othtask/3600)*60) >= 10 then (cast(floor(othtask/60 - floor(othtask/3600)*60) as nvarchar (10)))
  233.                     end     + ':' +            
  234.                     case
  235.                     when floor(othtask) - floor(othtask/60)*60 < 10 then ('0'+cast(floor(othtask) - floor(othtask/60)*60 as nvarchar (10)))
  236.                     when floor(othtask) - floor(othtask/60)*60 >= 10 then (cast(floor(othtask) - floor(othtask/60)*60 as nvarchar (10)))
  237.                     end  othtaskLabel,
  238.         round(othtask, 1)othtask,  
  239.                     (cast(floor(talk/3600) as nvarchar (10)))          
  240.                     + ':' +
  241.                     case
  242.                     when floor(talk/60 - floor(talk/3600)*60) < 10 then ('0'+ cast(floor(talk/60 - floor(talk/3600)*60) as nvarchar (10)))
  243.                     when floor(talk/60 - floor(talk/3600)*60) >= 10 then (cast(floor(talk/60 - floor(talk/3600)*60) as nvarchar (10)))
  244.                     end     + ':' +            
  245.                     case
  246.                     when floor(talk) - floor(talk/60)*60 < 10 then ('0'+cast(floor(talk) - floor(talk/60)*60 as nvarchar (10)))
  247.                     when floor(talk) - floor(talk/60)*60 >= 10 then (cast(floor(talk) - floor(talk/60)*60 as nvarchar (10)))
  248.                     end  talkLabel,        
  249.         round(talk, 1)talk,
  250.                     (cast(floor(ready/3600) as nvarchar (10)))        
  251.                     + ':' +
  252.                     case
  253.                     when floor(ready/60 - floor(ready/3600)*60) < 10 then ('0'+ cast(floor(ready/60 - floor(ready/3600)*60) as nvarchar (10)))
  254.                     when floor(ready/60 - floor(ready/3600)*60) >= 10 then (cast(floor(ready/60 - floor(ready/3600)*60) as nvarchar (10)))
  255.                     end     + ':' +            
  256.                     case
  257.                     when floor(ready) - floor(ready/60)*60 < 10 then ('0'+cast(floor(ready) - floor(ready/60)*60 as nvarchar (10)))
  258.                     when floor(ready) - floor(ready/60)*60 >= 10 then (cast(floor(ready) - floor(ready/60)*60 as nvarchar (10)))
  259.                     end  readyLabel,          
  260.         round([ready], 1)[ready],
  261.                     (cast(floor([break]/3600) as nvarchar (10)))          
  262.                     + ':' +
  263.                     case
  264.                     when floor([break]/60 - floor([break]/3600)*60) < 10 then ('0'+ cast(floor([break]/60 - floor([break]/3600)*60) as nvarchar (10)))
  265.                     when floor([break]/60 - floor([break]/3600)*60) >= 10 then (cast(floor([break]/60 - floor([break]/3600)*60) as nvarchar (10)))
  266.                     end     + ':' +            
  267.                     case
  268.                     when floor([break]) - floor([break]/60)*60 < 10 then ('0'+cast(floor([break]) - floor([break]/60)*60 as nvarchar (10)))
  269.                     when floor([break]) - floor([break]/60)*60 >= 10 then (cast(floor([break]) - floor([break]/60)*60 as nvarchar (10)))
  270.                     end  [breakLabel],
  271.         round([break], 1) [break],
  272.                     (cast(floor([dispatch]/3600) as nvarchar (10)))        
  273.                     + ':' +
  274.                     case
  275.                     when floor([dispatch]/60 - floor([dispatch]/3600)*60) < 10 then ('0'+ cast(floor([dispatch]/60 - floor([dispatch]/3600)*60) as nvarchar (10)))
  276.                     when floor([dispatch]/60 - floor([dispatch]/3600)*60) >= 10 then (cast(floor([dispatch]/60 - floor([dispatch]/3600)*60) as nvarchar (10)))
  277.                     end     + ':' +            
  278.                     case
  279.                     when floor([dispatch]) - floor([dispatch]/60)*60 < 10 then ('0'+cast(floor([dispatch]) - floor([dispatch]/60)*60 as nvarchar (10)))
  280.                     when floor([dispatch]) - floor([dispatch]/60)*60 >= 10 then (cast(floor([dispatch]) - floor([dispatch]/60)*60 as nvarchar (10)))
  281.                     end  [dispatchLabel],  
  282.         round([dispatch], 1)[dispatch],
  283.                     (cast(floor([tickets]/3600) as nvarchar (10)))        
  284.                     + ':' +
  285.                     case
  286.                     when floor([tickets]/60 - floor([tickets]/3600)*60) < 10 then ('0'+ cast(floor([tickets]/60 - floor([tickets]/3600)*60) as nvarchar (10)))
  287.                     when floor([tickets]/60 - floor([tickets]/3600)*60) >= 10 then (cast(floor([tickets]/60 - floor([tickets]/3600)*60) as nvarchar (10)))
  288.                     end     + ':' +            
  289.                     case
  290.                     when floor([tickets]) - floor([tickets]/60)*60 < 10 then ('0'+cast(floor([tickets]) - floor([tickets]/60)*60 as nvarchar (10)))
  291.                     when floor([tickets]) - floor([tickets]/60)*60 >= 10 then (cast(floor([tickets]) - floor([tickets]/60)*60 as nvarchar (10)))
  292.                     end  [ticketsLabel],  
  293.         round([tickets], 1)[tickets],
  294.                     (cast(floor(chat/3600) as nvarchar (10)))          
  295.                     + ':' +
  296.                     case
  297.                     when floor(chat/60 - floor(chat/3600)*60) < 10 then ('0'+ cast(floor(chat/60 - floor(chat/3600)*60) as nvarchar (10)))
  298.                     when floor(chat/60 - floor(chat/3600)*60) >= 10 then (cast(floor(chat/60 - floor(chat/3600)*60) as nvarchar (10)))
  299.                     end     + ':' +            
  300.                     case
  301.                     when floor(chat) - floor(chat/60)*60 < 10 then ('0'+cast(floor(chat) - floor(chat/60)*60 as nvarchar (10)))
  302.                     when floor(chat) - floor(chat/60)*60 >= 10 then (cast(floor(chat) - floor(chat/60)*60 as nvarchar (10)))
  303.                     end  chatLabel,
  304.         round([chat], 1)[chat],
  305.                     (cast(floor([other]/3600) as nvarchar (10)))          
  306.                     + ':' +
  307.                     case
  308.                     when floor([other]/60 - floor([other]/3600)*60) < 10 then ('0'+ cast(floor([other]/60 - floor([other]/3600)*60) as nvarchar (10)))
  309.                     when floor([other]/60 - floor([other]/3600)*60) >= 10 then (cast(floor([other]/60 - floor([other]/3600)*60) as nvarchar (10)))
  310.                     end     + ':' +            
  311.                     case
  312.                     when floor([other]) - floor([other]/60)*60 < 10 then ('0'+cast(floor([other]) - floor([other]/60)*60 as nvarchar (10)))
  313.                     when floor([other]) - floor([other]/60)*60 >= 10 then (cast(floor([other]) - floor([other]/60)*60 as nvarchar (10)))
  314.                     end  [otherLabel],
  315.         round([other], 1) [other],
  316.                     (cast(floor([refunds]/3600) as nvarchar (10)))        
  317.                     + ':' +
  318.                     case
  319.                     when floor([refunds]/60 - floor([refunds]/3600)*60) < 10 then ('0'+ cast(floor([refunds]/60 - floor([refunds]/3600)*60) as nvarchar (10)))
  320.                     when floor([refunds]/60 - floor([refunds]/3600)*60) >= 10 then (cast(floor([refunds]/60 - floor([refunds]/3600)*60) as nvarchar (10)))
  321.                     end     + ':' +            
  322.                     case
  323.                     when floor([refunds]) - floor([refunds]/60)*60 < 10 then ('0'+cast(floor([refunds]) - floor([refunds]/60)*60 as nvarchar (10)))
  324.                     when floor([refunds]) - floor([refunds]/60)*60 >= 10 then (cast(floor([refunds]) - floor([refunds]/60)*60 as nvarchar (10)))
  325.                     end  [refundsLabel],  
  326.         round([refunds], 1) [refunds],
  327.                     (cast(floor([training]/3600) as nvarchar (10)))        
  328.                     + ':' +
  329.                     case
  330.                     when floor([training]/60 - floor([training]/3600)*60) < 10 then ('0'+ cast(floor([training]/60 - floor([training]/3600)*60) as nvarchar (10)))
  331.                     when floor([training]/60 - floor([training]/3600)*60) >= 10 then (cast(floor([training]/60 - floor([training]/3600)*60) as nvarchar (10)))
  332.                     end     + ':' +            
  333.                     case
  334.                     when floor([training]) - floor([training]/60)*60 < 10 then ('0'+cast(floor([training]) - floor([training]/60)*60 as nvarchar (10)))
  335.                     when floor([training]) - floor([training]/60)*60 >= 10 then (cast(floor([training]) - floor([training]/60)*60 as nvarchar (10)))
  336.                     end  [trainingLabel],  
  337.         round([training], 1) [training],
  338.                     (cast(floor([tech]/3600) as nvarchar (10)))        
  339.                     + ':' +
  340.                     case
  341.                     when floor([tech]/60 - floor([tech]/3600)*60) < 10 then ('0'+ cast(floor([tech]/60 - floor([tech]/3600)*60) as nvarchar (10)))
  342.                     when floor([tech]/60 - floor([tech]/3600)*60) >= 10 then (cast(floor([tech]/60 - floor([tech]/3600)*60) as nvarchar (10)))
  343.                     end     + ':' +            
  344.                     case
  345.                     when floor([tech]) - floor([tech]/60)*60 < 10 then ('0'+cast(floor([tech]) - floor([tech]/60)*60 as nvarchar (10)))
  346.                     when floor([tech]) - floor([tech]/60)*60 >= 10 then (cast(floor([tech]) - floor([tech]/60)*60 as nvarchar (10)))
  347.                     end  [techLabel],  
  348.         round([tech], 1)[tech],
  349.         round(task+beforetask+[post_len]+othtask+talk+[ready]+[break]+[dispatch]+[tickets]+[chat]+[other]+[refunds]+[training]+[tech]-isnull((select timetalk from @temp_talk where idoperator = tt.IdOperator),0),1) [sum_status],  --за вычетом времени исходящих звонков, совершенных в перерыве
  350.         isnull(oktell.dbo.GetTimeFromSecond(task+beforetask+[post_len]+othtask+talk+[ready]+[break]+[dispatch]+[tickets]+[chat]+[other]+[refunds]+[training]+[tech]-isnull((select timetalk from @temp_talk where idoperator = tt.IdOperator),0)),0) [sum_statLabel], --за вычетом времени исходящих звонков, совершенных в перерыве
  351. incoming,
  352. outcomingcalls,
  353. incoming+outcomingcalls as sum,
  354. (cast(floor(avgpost/3600) as nvarchar (10)))          
  355.                     + ':' +
  356.                     case
  357.                     when floor(avgpost/60 - floor(avgpost/3600)*60) < 10 then ('0'+ cast(floor(avgpost/60 - floor(avgpost/3600)*60) as nvarchar (10)))
  358.                     when floor(avgpost/60 - floor(avgpost/3600)*60) >= 10 then (cast(floor(avgpost/60 - floor(avgpost/3600)*60) as nvarchar (10)))
  359.                     end     + ':' +            
  360.                     case
  361.                     when floor(avgpost) - floor(avgpost/60)*60 < 10 then ('0'+cast(floor(avgpost) - floor(avgpost/60)*60 as nvarchar (10)))
  362.                     when floor(avgpost) - floor(avgpost/60)*60 >= 10 then (cast(floor(avgpost) - floor(avgpost/60)*60 as nvarchar (10)))
  363.                     end  avgpostLabel,
  364. round([avgpost], 1)[avgpost]              
  365.        
  366. from (
  367.     SELECT os.[IdOperator],
  368.             [cc] = sum(lentime),
  369.             [beforetask] = isnull(
  370.             (SELECT sum(lentime)
  371.             FROM #temp
  372.             WHERE State in (1,2,3)
  373.             and idtask in (select * from #taskstmp )
  374.             and [IdOperator] = os.[IdOperator])
  375.             , 0),
  376.             [task] = isnull(
  377.             (select sum(lentime)
  378.             from #temp
  379.             where State in (6)
  380.             and idtask in (select * from #taskstmp )
  381.             and [IdOperator] = os.[IdOperator])
  382.             , 0),
  383.             [othtask] = isnull(
  384.             (select sum(lentime)
  385.             from #temp
  386.             where State in (1,2,3,4,6,7 )
  387.             and idtask not in (select * from #taskstmp )
  388.             and [IdOperator] = os.[IdOperator])
  389.             , 0),              
  390.             [ready] = isnull(
  391.             (select sum(lentime)
  392.             from #temp
  393.             where State = 10
  394.             and [IdOperator] = os.[IdOperator])
  395.             , 0),
  396.             [talk] = isnull(
  397.             (select sum(lentime)
  398.             from #temp
  399.             where State in (5,8)
  400.             and [IdOperator] = os.[IdOperator])
  401.             --, 0)+isnull((select timetalk from @temp_talk where idoperator = os.IdOperator),0),
  402.             --[break] = isnull(------------------------------------------------
  403.             --(select sum(lentime)
  404.             --from #temp
  405.             --where State = 9 and icode = 0
  406.             --and [IdOperator] = os.[IdOperator])
  407.             --, 0),
  408.             , 0)+isnull((select timetalk from @temp_talk where idoperator = os.IdOperator),0),
  409.             [break] = isnull(------------------------------------------------
  410.             (select LenTime from @OperatorSum where UserId = os.[IdOperator])
  411.             , 0),
  412.             [dispatch] = isnull(
  413.             (select sum(lentime)
  414.             from #temp
  415.             where State = 9 and icode = 1
  416.             and [IdOperator] = os.[IdOperator])
  417.             , 0)            ,
  418.             [tickets] = isnull(
  419.             (select sum(lentime)
  420.             from #temp
  421.             where State = 9 and icode = 2
  422.             and [IdOperator] = os.[IdOperator])
  423.             , 0)            ,
  424.             [chat] = isnull(
  425.             (select sum(lentime)
  426.             from #temp
  427.             where State = 9 and icode = 3
  428.             and [IdOperator] = os.[IdOperator])
  429.             , 0)            ,
  430.             [other] = isnull(
  431.             (select sum(lentime)
  432.             from #temp
  433.             where State = 9 and icode = 4
  434.             and [IdOperator] = os.[IdOperator])
  435.             , 0),
  436.             [refunds] = isnull(
  437.             (select sum(lentime)
  438.             from #temp
  439.             where State = 9 and icode = 6
  440.             and [IdOperator] = os.[IdOperator])
  441.             , 0),
  442.             [training] = isnull(
  443.             (select sum(lentime)
  444.             from #temp
  445.             where State = 9 and icode = 7
  446.             and [IdOperator] = os.[IdOperator])
  447.             , 0),
  448.             [tech] = isnull(
  449.             (select sum(lentime)
  450.             from #temp
  451.             where State = 9 and icode = 8
  452.             and [IdOperator] = os.[IdOperator])
  453.             , 0),
  454.             post_len = isnull(
  455.             (select sum(lentime)
  456.             from #temp
  457.             where State = 7
  458.             and [IdOperator] = os.[IdOperator])
  459.             , 0),
  460. cast(isnull((select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock)
  461.                     where datetimestart between @dt1 and @dt2 and timestart between @tm1 and @tm2 and IdOperator=os.IdOperator and idtask in (select id from @tasks) and  blinenum<>'IVR' and IsOutput=0),0) as nvarchar(200)) incoming,
  462.  
  463.  
  464.  
  465.                     isnull((select count (distinct idchain) from oktell.dbo.A_Stat_Connections_1x1 sc where
  466.                     sc.TimeStart between @dt1 and @dt2
  467.                     and cast(CAST(sc.TimeStart as time(0))as datetime) between @tm1 and @tm2
  468.                     and IsRecorded = 1
  469.                     and ConnectionType in (1)
  470.                     and sc.AUserId=os.IdOperator),0) outcomingcalls,  
  471.                     /*cast(isnull((select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(NOlock)
  472.                     where datetimestart between @dt1 and @dt2 and timestart between @tm1 and @tm2 and IdOperator=os.IdOperator/* and idtask in (select id from @tasks) and  blinenum<>'IVR'*/ and IsOutput=1),0) as nvarchar(200)) outcomingcalls,
  473. */
  474.  
  475. /*
  476.                     cast(isnull((select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(NOlock)
  477.                     where datetimestart between @dt1 and @dt2 and timestart between @tm1 and @tm2 and IdOperator=os.IdOperator and idtask in (select id from @tasks) and  blinenum<>'IVR' ),0) as nvarchar(200)) sumallcalls,*/
  478.                 case when isnull(
  479.             (select sum(lentime)
  480.             from #temp
  481.             where State = 7
  482.             and [IdOperator] = os.[IdOperator])
  483.             , 0) = 0  or (isnull((select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(NOlock)
  484.                     where datetimestart between @dt1 and @dt2 and timestart between @tm1 and @tm2 and IdOperator=os.IdOperator and  blinenum<>'IVR' and idtask in (select id from @tasks) and IsOutput=0),0)) = 0 then 0
  485.                     else isnull(
  486.             (select sum(lentime)
  487.             from #temp
  488.             where State = 7
  489.             and [IdOperator] = os.[IdOperator])
  490.             , 0)/(isnull((select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(NOlock)
  491.                     where datetimestart between @dt1 and @dt2 and timestart between @tm1 and @tm2 and IdOperator=os.IdOperator and  blinenum<>'IVR' and idtask in (select id from @tasks) and IsOutput=0),0) ) end avgpost
  492.     FROM #temp os
  493.     group by os.[IdOperator]
  494. )tt
  495. inner join
  496. oktell_cc_temp.dbo.A_Cube_CC_Cat_OperatorInfo oi
  497. on oi.id = tt.[IdOperator]
  498.  
  499. drop table #temp
  500. drop table #taskstmp
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement