API tools faq
paste
Advertisement
WorkAkkaunt

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

WorkAkkaunt
Jul 29th, 2019
202
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 26.43 KB | None | 0 0
raw download clone embed print report
  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
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!