API tools faq
paste
Advertisement
WorkAkkaunt

GEtt виталя

WorkAkkaunt
Jul 9th, 2019
144
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 26.19 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.  
  195. select * from @OperatorSum
  196.  
  197. select  oi.name,
  198.         (cast(floor(task/3600) as nvarchar (10)))          
  199.                     + ':' +
  200.                     case
  201.                     when floor(task/60 - floor(task/3600)*60) < 10 then ('0'+ cast(floor(task/60 - floor(task/3600)*60) as nvarchar (10)))
  202.                     when floor(task/60 - floor(task/3600)*60) >= 10 then (cast(floor(task/60 - floor(task/3600)*60) as nvarchar (10)))
  203.                     end     + ':' +            
  204.                     case
  205.                     when floor(task) - floor(task/60)*60 < 10 then ('0'+cast(floor(task) - floor(task/60)*60 as nvarchar (10)))
  206.                     when floor(task) - floor(task/60)*60 >= 10 then (cast(floor(task) - floor(task/60)*60 as nvarchar (10)))
  207.                     end  taskLabel,
  208.         round(task, 1) task,
  209.                     (cast(floor(beforetask/3600) as nvarchar (10)))        
  210.                     + ':' +
  211.                     case
  212.                     when floor(beforetask/60 - floor(beforetask/3600)*60) < 10 then ('0'+ cast(floor(beforetask/60 - floor(beforetask/3600)*60) as nvarchar (10)))
  213.                     when floor(beforetask/60 - floor(beforetask/3600)*60) >= 10 then (cast(floor(beforetask/60 - floor(beforetask/3600)*60) as nvarchar (10)))
  214.                     end     + ':' +            
  215.                     case
  216.                     when floor(beforetask) - floor(beforetask/60)*60 < 10 then ('0'+cast(floor(beforetask) - floor(beforetask/60)*60 as nvarchar (10)))
  217.                     when floor(beforetask) - floor(beforetask/60)*60 >= 10 then (cast(floor(beforetask) - floor(beforetask/60)*60 as nvarchar (10)))
  218.                     end  beforetaskLabel,
  219.         round(beforetask, 1) beforetask,
  220.                     (cast(floor(post_len/3600) as nvarchar (10)))          
  221.                     + ':' +
  222.                     case
  223.                     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)))
  224.                     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)))
  225.                     end     + ':' +            
  226.                     case
  227.                     when floor(post_len) - floor(post_len/60)*60 < 10 then ('0'+cast(floor(post_len) - floor(post_len/60)*60 as nvarchar (10)))
  228.                     when floor(post_len) - floor(post_len/60)*60 >= 10 then (cast(floor(post_len) - floor(post_len/60)*60 as nvarchar (10)))
  229.                     end  post_lenLabel,
  230.         round([post_len], 1)[post_len],                
  231.                     (cast(floor(othtask/3600) as nvarchar (10)))          
  232.                     + ':' +
  233.                     case
  234.                     when floor(othtask/60 - floor(othtask/3600)*60) < 10 then ('0'+ cast(floor(othtask/60 - floor(othtask/3600)*60) as nvarchar (10)))
  235.                     when floor(othtask/60 - floor(othtask/3600)*60) >= 10 then (cast(floor(othtask/60 - floor(othtask/3600)*60) as nvarchar (10)))
  236.                     end     + ':' +            
  237.                     case
  238.                     when floor(othtask) - floor(othtask/60)*60 < 10 then ('0'+cast(floor(othtask) - floor(othtask/60)*60 as nvarchar (10)))
  239.                     when floor(othtask) - floor(othtask/60)*60 >= 10 then (cast(floor(othtask) - floor(othtask/60)*60 as nvarchar (10)))
  240.                     end  othtaskLabel,
  241.         round(othtask, 1)othtask,  
  242.                     (cast(floor(talk/3600) as nvarchar (10)))          
  243.                     + ':' +
  244.                     case
  245.                     when floor(talk/60 - floor(talk/3600)*60) < 10 then ('0'+ cast(floor(talk/60 - floor(talk/3600)*60) as nvarchar (10)))
  246.                     when floor(talk/60 - floor(talk/3600)*60) >= 10 then (cast(floor(talk/60 - floor(talk/3600)*60) as nvarchar (10)))
  247.                     end     + ':' +            
  248.                     case
  249.                     when floor(talk) - floor(talk/60)*60 < 10 then ('0'+cast(floor(talk) - floor(talk/60)*60 as nvarchar (10)))
  250.                     when floor(talk) - floor(talk/60)*60 >= 10 then (cast(floor(talk) - floor(talk/60)*60 as nvarchar (10)))
  251.                     end  talkLabel,        
  252.         round(talk, 1)talk,
  253.                     (cast(floor(ready/3600) as nvarchar (10)))        
  254.                     + ':' +
  255.                     case
  256.                     when floor(ready/60 - floor(ready/3600)*60) < 10 then ('0'+ cast(floor(ready/60 - floor(ready/3600)*60) as nvarchar (10)))
  257.                     when floor(ready/60 - floor(ready/3600)*60) >= 10 then (cast(floor(ready/60 - floor(ready/3600)*60) as nvarchar (10)))
  258.                     end     + ':' +            
  259.                     case
  260.                     when floor(ready) - floor(ready/60)*60 < 10 then ('0'+cast(floor(ready) - floor(ready/60)*60 as nvarchar (10)))
  261.                     when floor(ready) - floor(ready/60)*60 >= 10 then (cast(floor(ready) - floor(ready/60)*60 as nvarchar (10)))
  262.                     end  readyLabel,          
  263.         round([ready], 1)[ready],
  264.                     (cast(floor([break]/3600) as nvarchar (10)))          
  265.                     + ':' +
  266.                     case
  267.                     when floor([break]/60 - floor([break]/3600)*60) < 10 then ('0'+ cast(floor([break]/60 - floor([break]/3600)*60) as nvarchar (10)))
  268.                     when floor([break]/60 - floor([break]/3600)*60) >= 10 then (cast(floor([break]/60 - floor([break]/3600)*60) as nvarchar (10)))
  269.                     end     + ':' +            
  270.                     case
  271.                     when floor([break]) - floor([break]/60)*60 < 10 then ('0'+cast(floor([break]) - floor([break]/60)*60 as nvarchar (10)))
  272.                     when floor([break]) - floor([break]/60)*60 >= 10 then (cast(floor([break]) - floor([break]/60)*60 as nvarchar (10)))
  273.                     end  [breakLabel],
  274.         round([break], 1) [break],
  275.                     (cast(floor([dispatch]/3600) as nvarchar (10)))        
  276.                     + ':' +
  277.                     case
  278.                     when floor([dispatch]/60 - floor([dispatch]/3600)*60) < 10 then ('0'+ cast(floor([dispatch]/60 - floor([dispatch]/3600)*60) as nvarchar (10)))
  279.                     when floor([dispatch]/60 - floor([dispatch]/3600)*60) >= 10 then (cast(floor([dispatch]/60 - floor([dispatch]/3600)*60) as nvarchar (10)))
  280.                     end     + ':' +            
  281.                     case
  282.                     when floor([dispatch]) - floor([dispatch]/60)*60 < 10 then ('0'+cast(floor([dispatch]) - floor([dispatch]/60)*60 as nvarchar (10)))
  283.                     when floor([dispatch]) - floor([dispatch]/60)*60 >= 10 then (cast(floor([dispatch]) - floor([dispatch]/60)*60 as nvarchar (10)))
  284.                     end  [dispatchLabel],  
  285.         round([dispatch], 1)[dispatch],
  286.                     (cast(floor([tickets]/3600) as nvarchar (10)))        
  287.                     + ':' +
  288.                     case
  289.                     when floor([tickets]/60 - floor([tickets]/3600)*60) < 10 then ('0'+ cast(floor([tickets]/60 - floor([tickets]/3600)*60) as nvarchar (10)))
  290.                     when floor([tickets]/60 - floor([tickets]/3600)*60) >= 10 then (cast(floor([tickets]/60 - floor([tickets]/3600)*60) as nvarchar (10)))
  291.                     end     + ':' +            
  292.                     case
  293.                     when floor([tickets]) - floor([tickets]/60)*60 < 10 then ('0'+cast(floor([tickets]) - floor([tickets]/60)*60 as nvarchar (10)))
  294.                     when floor([tickets]) - floor([tickets]/60)*60 >= 10 then (cast(floor([tickets]) - floor([tickets]/60)*60 as nvarchar (10)))
  295.                     end  [ticketsLabel],  
  296.         round([tickets], 1)[tickets],
  297.                     (cast(floor(chat/3600) as nvarchar (10)))          
  298.                     + ':' +
  299.                     case
  300.                     when floor(chat/60 - floor(chat/3600)*60) < 10 then ('0'+ cast(floor(chat/60 - floor(chat/3600)*60) as nvarchar (10)))
  301.                     when floor(chat/60 - floor(chat/3600)*60) >= 10 then (cast(floor(chat/60 - floor(chat/3600)*60) as nvarchar (10)))
  302.                     end     + ':' +            
  303.                     case
  304.                     when floor(chat) - floor(chat/60)*60 < 10 then ('0'+cast(floor(chat) - floor(chat/60)*60 as nvarchar (10)))
  305.                     when floor(chat) - floor(chat/60)*60 >= 10 then (cast(floor(chat) - floor(chat/60)*60 as nvarchar (10)))
  306.                     end  chatLabel,
  307.         round([chat], 1)[chat],
  308.                     (cast(floor([other]/3600) as nvarchar (10)))          
  309.                     + ':' +
  310.                     case
  311.                     when floor([other]/60 - floor([other]/3600)*60) < 10 then ('0'+ cast(floor([other]/60 - floor([other]/3600)*60) as nvarchar (10)))
  312.                     when floor([other]/60 - floor([other]/3600)*60) >= 10 then (cast(floor([other]/60 - floor([other]/3600)*60) as nvarchar (10)))
  313.                     end     + ':' +            
  314.                     case
  315.                     when floor([other]) - floor([other]/60)*60 < 10 then ('0'+cast(floor([other]) - floor([other]/60)*60 as nvarchar (10)))
  316.                     when floor([other]) - floor([other]/60)*60 >= 10 then (cast(floor([other]) - floor([other]/60)*60 as nvarchar (10)))
  317.                     end  [otherLabel],
  318.         round([other], 1) [other],
  319.                     (cast(floor([refunds]/3600) as nvarchar (10)))        
  320.                     + ':' +
  321.                     case
  322.                     when floor([refunds]/60 - floor([refunds]/3600)*60) < 10 then ('0'+ cast(floor([refunds]/60 - floor([refunds]/3600)*60) as nvarchar (10)))
  323.                     when floor([refunds]/60 - floor([refunds]/3600)*60) >= 10 then (cast(floor([refunds]/60 - floor([refunds]/3600)*60) as nvarchar (10)))
  324.                     end     + ':' +            
  325.                     case
  326.                     when floor([refunds]) - floor([refunds]/60)*60 < 10 then ('0'+cast(floor([refunds]) - floor([refunds]/60)*60 as nvarchar (10)))
  327.                     when floor([refunds]) - floor([refunds]/60)*60 >= 10 then (cast(floor([refunds]) - floor([refunds]/60)*60 as nvarchar (10)))
  328.                     end  [refundsLabel],  
  329.         round([refunds], 1) [refunds],
  330.                     (cast(floor([training]/3600) as nvarchar (10)))        
  331.                     + ':' +
  332.                     case
  333.                     when floor([training]/60 - floor([training]/3600)*60) < 10 then ('0'+ cast(floor([training]/60 - floor([training]/3600)*60) as nvarchar (10)))
  334.                     when floor([training]/60 - floor([training]/3600)*60) >= 10 then (cast(floor([training]/60 - floor([training]/3600)*60) as nvarchar (10)))
  335.                     end     + ':' +            
  336.                     case
  337.                     when floor([training]) - floor([training]/60)*60 < 10 then ('0'+cast(floor([training]) - floor([training]/60)*60 as nvarchar (10)))
  338.                     when floor([training]) - floor([training]/60)*60 >= 10 then (cast(floor([training]) - floor([training]/60)*60 as nvarchar (10)))
  339.                     end  [trainingLabel],  
  340.         round([training], 1) [training],
  341.                     (cast(floor([tech]/3600) as nvarchar (10)))        
  342.                     + ':' +
  343.                     case
  344.                     when floor([tech]/60 - floor([tech]/3600)*60) < 10 then ('0'+ cast(floor([tech]/60 - floor([tech]/3600)*60) as nvarchar (10)))
  345.                     when floor([tech]/60 - floor([tech]/3600)*60) >= 10 then (cast(floor([tech]/60 - floor([tech]/3600)*60) as nvarchar (10)))
  346.                     end     + ':' +            
  347.                     case
  348.                     when floor([tech]) - floor([tech]/60)*60 < 10 then ('0'+cast(floor([tech]) - floor([tech]/60)*60 as nvarchar (10)))
  349.                     when floor([tech]) - floor([tech]/60)*60 >= 10 then (cast(floor([tech]) - floor([tech]/60)*60 as nvarchar (10)))
  350.                     end  [techLabel],  
  351.         round([tech], 1)[tech],
  352.         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],  --за вычетом времени исходящих звонков, совершенных в перерыве
  353.         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], --за вычетом времени исходящих звонков, совершенных в перерыве
  354. incoming,
  355. outcomingcalls,
  356. incoming+outcomingcalls as sum,
  357. (cast(floor(avgpost/3600) as nvarchar (10)))          
  358.                     + ':' +
  359.                     case
  360.                     when floor(avgpost/60 - floor(avgpost/3600)*60) < 10 then ('0'+ cast(floor(avgpost/60 - floor(avgpost/3600)*60) as nvarchar (10)))
  361.                     when floor(avgpost/60 - floor(avgpost/3600)*60) >= 10 then (cast(floor(avgpost/60 - floor(avgpost/3600)*60) as nvarchar (10)))
  362.                     end     + ':' +            
  363.                     case
  364.                     when floor(avgpost) - floor(avgpost/60)*60 < 10 then ('0'+cast(floor(avgpost) - floor(avgpost/60)*60 as nvarchar (10)))
  365.                     when floor(avgpost) - floor(avgpost/60)*60 >= 10 then (cast(floor(avgpost) - floor(avgpost/60)*60 as nvarchar (10)))
  366.                     end  avgpostLabel,
  367. round([avgpost], 1)[avgpost]              
  368.        
  369. from (
  370.     SELECT os.[IdOperator],
  371.             [cc] = sum(lentime),
  372.             [beforetask] = isnull(
  373.             (SELECT sum(lentime)
  374.             FROM #temp
  375.             WHERE State in (1,2,3)
  376.             and idtask in (select * from #taskstmp )
  377.             and [IdOperator] = os.[IdOperator])
  378.             , 0),
  379.             [task] = isnull(
  380.             (select sum(lentime)
  381.             from #temp
  382.             where State in (6)
  383.             and idtask in (select * from #taskstmp )
  384.             and [IdOperator] = os.[IdOperator])
  385.             , 0),
  386.             [othtask] = isnull(
  387.             (select sum(lentime)
  388.             from #temp
  389.             where State in (1,2,3,4,6,7 )
  390.             and idtask not in (select * from #taskstmp )
  391.             and [IdOperator] = os.[IdOperator])
  392.             , 0),              
  393.             [ready] = isnull(
  394.             (select sum(lentime)
  395.             from #temp
  396.             where State = 10
  397.             and [IdOperator] = os.[IdOperator])
  398.             , 0),
  399.             [talk] = isnull(
  400.             (select sum(lentime)
  401.             from #temp
  402.             where State in (5,8)
  403.             and [IdOperator] = os.[IdOperator])
  404.             , 0)+isnull((select timetalk from @temp_talk where idoperator = os.IdOperator),0),
  405.             [break] = isnull(------------------------------------------------
  406.             (select sum(lentime)
  407.             from #temp
  408.             where State = 9 and icode = 0
  409.             and [IdOperator] = os.[IdOperator])
  410.             , 0),
  411.             [dispatch] = isnull(
  412.             (select sum(lentime)
  413.             from #temp
  414.             where State = 9 and icode = 1
  415.             and [IdOperator] = os.[IdOperator])
  416.             , 0)            ,
  417.             [tickets] = isnull(
  418.             (select sum(lentime)
  419.             from #temp
  420.             where State = 9 and icode = 2
  421.             and [IdOperator] = os.[IdOperator])
  422.             , 0)            ,
  423.             [chat] = isnull(
  424.             (select sum(lentime)
  425.             from #temp
  426.             where State = 9 and icode = 3
  427.             and [IdOperator] = os.[IdOperator])
  428.             , 0)            ,
  429.             [other] = isnull(
  430.             (select sum(lentime)
  431.             from #temp
  432.             where State = 9 and icode = 4
  433.             and [IdOperator] = os.[IdOperator])
  434.             , 0),
  435.             [refunds] = isnull(
  436.             (select sum(lentime)
  437.             from #temp
  438.             where State = 9 and icode = 6
  439.             and [IdOperator] = os.[IdOperator])
  440.             , 0),
  441.             [training] = isnull(
  442.             (select sum(lentime)
  443.             from #temp
  444.             where State = 9 and icode = 7
  445.             and [IdOperator] = os.[IdOperator])
  446.             , 0),
  447.             [tech] = isnull(
  448.             (select sum(lentime)
  449.             from #temp
  450.             where State = 9 and icode = 8
  451.             and [IdOperator] = os.[IdOperator])
  452.             , 0),
  453.             post_len = isnull(
  454.             (select sum(lentime)
  455.             from #temp
  456.             where State = 7
  457.             and [IdOperator] = os.[IdOperator])
  458.             , 0),
  459. cast(isnull((select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock)
  460.                     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,
  461.  
  462.  
  463.  
  464.                     isnull((select count (distinct idchain) from oktell.dbo.A_Stat_Connections_1x1 sc where
  465.                     sc.TimeStart between @dt1 and @dt2
  466.                     and cast(CAST(sc.TimeStart as time(0))as datetime) between @tm1 and @tm2
  467.                     and IsRecorded = 1
  468.                     and ConnectionType in (1)
  469.                     and sc.AUserId=os.IdOperator),0) outcomingcalls,  
  470.                     /*cast(isnull((select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(NOlock)
  471.                     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,
  472. */
  473.  
  474. /*
  475.                     cast(isnull((select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(NOlock)
  476.                     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,*/
  477.                 case when isnull(
  478.             (select sum(lentime)
  479.             from #temp
  480.             where State = 7
  481.             and [IdOperator] = os.[IdOperator])
  482.             , 0) = 0  or (isnull((select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(NOlock)
  483.                     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
  484.                     else isnull(
  485.             (select sum(lentime)
  486.             from #temp
  487.             where State = 7
  488.             and [IdOperator] = os.[IdOperator])
  489.             , 0)/(isnull((select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(NOlock)
  490.                     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
  491.     FROM #temp os
  492.     group by os.[IdOperator]
  493. )tt
  494. inner join
  495. oktell_cc_temp.dbo.A_Cube_CC_Cat_OperatorInfo oi
  496. on oi.id = tt.[IdOperator]
  497.  
  498. drop table #temp
  499. 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!