API tools faq
paste
Advertisement
WorkAkkaunt

Gett taxi Рабочее время оперов

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