WorkAkkaunt

Северсталь По звонкам

Jul 22nd, 2019
71
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --По звонкам(фильтры: задачи, период, в разрезе дни/недели/месяцы)
  2. DECLARE @TASKS TABLE(Id uniqueidentifier)
  3.  
  4. INSERT INTO @TASKS
  5. SELECT id FROM oktell.dbo.A_TaskManager_Tasks
  6.  
  7. DECLARE @DATE_START datetime = '07-10-2018 00:00:00.001'
  8. DECLARE @DATE_END   datetime = '07-19-2019 23:59:59.000'
  9. DECLARE @TIME_START datetime = '01-01-1900 00:00:00.001'
  10. DECLARE @TIME_END   datetime = '01-01-1900 23:59:00.001'
  11.  
  12. DECLARE @INTERVAL INT = 3
  13.     /*select 1, 'По дням'
  14.     union all
  15.     select 2, 'По неделям'
  16.     union all
  17.     select 3, 'По месяцам'*/
  18. --------------------------------------------------------------------------------------------------------------------------
  19. DECLARE @Months TABLE(NUMBER INT, [Name] nvarchar(20))
  20. INSERT INTO @Months VALUES(1, 'Январь')
  21. INSERT INTO @Months VALUES(2, 'Февраль')
  22. INSERT INTO @Months VALUES(3, 'Март')
  23. INSERT INTO @Months VALUES(4, 'Апрель')
  24. INSERT INTO @Months VALUES(5, 'Май')
  25. INSERT INTO @Months VALUES(6, 'Июнь')
  26. INSERT INTO @Months VALUES(7, 'Июль')
  27. INSERT INTO @Months VALUES(8, 'Август')
  28. INSERT INTO @Months VALUES(9, 'Сентябрь')
  29. INSERT INTO @Months VALUES(10, 'Октябрь')
  30. INSERT INTO @Months VALUES(11, 'Ноябрь')
  31. INSERT INTO @Months VALUES(12, 'Декабрь')
  32.  
  33.  
  34. ----ИЗВЛЕКАЕМ ПО ОПЕРАТОРАМ
  35. SELECT * INTO #Filtered
  36. FROM [dbo].[A_Stat_Connections_1x1] AS SC
  37. WHERE TimeStart BETWEEN @DATE_START AND @DATE_END
  38.     AND CAST(TimeStart AS TIME) BETWEEN CAST(@TIME_START AS TIME) AND CAST(@TIME_END AS TIME)
  39.     AND ConnectionType = 5
  40.  
  41. ----ИЗВЛЕКАЕМ ПО IVR
  42. SELECT * INTO #Filtered2
  43. FROM [dbo].[A_Stat_Connections_1x1] AS SC
  44. WHERE TimeStart BETWEEN @DATE_START AND @DATE_END
  45.     AND CAST(TimeStart AS TIME) BETWEEN CAST(@TIME_START AS TIME) AND CAST(@TIME_END AS TIME)
  46.     AND ConnectionType = 4
  47.  
  48. ----Считаем длительнсти звонков
  49. DECLARE @Calls TABLE (Id uniqueidentifier, IvrTime INT, OperTime INT, TalkTime INT, StartTime datetime)
  50.  
  51. INSERT INTO @Calls
  52. SELECT F1.IdChain
  53.     , DATEDIFF(SECOND, (SELECT TOP 1 TimeStart FROM #Filtered2 AS F2 WHERE F1.IdChain = F2.IdChain), F1.TimeAnswer)
  54.     , DATEDIFF(SECOND, F1.TimeStart, F1.TimeAnswer)
  55.     , DATEDIFF(SECOND, F1.TimeStart, F1.TimeStop)
  56.     , F1.TimeStart
  57. FROM #Filtered AS F1
  58.  
  59. DROP TABLE #Filtered
  60. DROP TABLE #Filtered2
  61. ---------------------------------------------------------
  62. --select * from @Calls
  63.  
  64. -----Извлекаем пропущенные звонки
  65. DECLARE @MissedCalls TABLE(Id uniqueidentifier, DateStart datetime)
  66.  
  67. INSERT INTO @MissedCalls
  68. SELECT IdChain, TimeStart
  69. FROM [dbo].[A_Stat_MissedCalls]
  70. WHERE TimeStart BETWEEN @DATE_START AND @DATE_END
  71.     AND DATEDIFF(SECOND, TimeStart, TimeStop) <= 10
  72.  
  73. -----разбиваем пропущенные по дням
  74. DECLARE @MissedByDay TABLE([COUNT] INT, [DAY] NCHAR(20))
  75.  
  76. INSERT INTO @MissedByDay
  77. SELECT COUNT(Id), CAST(DateStart AS DATE)
  78. FROM @MissedCalls
  79. --WHERE COUNT(Id) >= 10
  80. GROUP BY CAST(DateStart AS DATE)
  81. ----------------------------------------------
  82.  
  83.  
  84. ------РАЗБИВАЕМ ЗВОНКИ ПО ДНЯМ
  85. DECLARE @CallsByDay0 TABLE
  86.     (
  87.         [DAY] NCHAR(20), [COUNT] INT, MiddleCall FLOAT
  88.         , OperTimeMin INT, OperTimeMax INT, OperTimeMiddle FLOAT
  89.         , IvrTimeMiddle FLOAT, IvrTimeMax INT, IvrTimeMin INT
  90.         , SLA FLOAT
  91.     )
  92.  
  93. INSERT INTO @CallsByDay0
  94. SELECT CAST(StartTime AS DATE)
  95.     , COUNT(C.Id)
  96.     , AVG(TalkTime)
  97.     , MIN(OperTime)
  98.     , MAX(OperTime)
  99.     , IIF(SUM(IIF(OperTime - 10 > 0, 1, 0) ) != 0, SUM( IIF(OperTime - 10 > 0, OperTime - 10, 0)) / SUM(IIF(OperTime - 10 > 0, 1, 0) ), 0)
  100.     , AVG(IvrTime)
  101.     , MAX(IvrTime)
  102.     , MIN(IvrTime)
  103.     , ROUND((COUNT(C.Id) -  SUM( IIF(OperTime - 10 > 0, OperTime - 10, 0))) / CAST(COUNT(C.Id) AS FLOAT), 2)
  104. FROM @Calls AS C
  105. LEFT JOIN @MissedByDay AS MC
  106. ON CAST(C.StartTime AS DATE) = MC.[DAY]
  107. GROUP BY CAST(StartTime AS DATE)
  108.  
  109.  DECLARE @CallsByDay TABLE
  110.     (
  111.         [DAY] NCHAR(20), [COUNT] INT, MiddleCall FLOAT
  112.         , OperTimeMin INT, OperTimeMax INT, OperTimeMiddle FLOAT
  113.         , IvrTimeMiddle FLOAT, IvrTimeMax INT, IvrTimeMin INT
  114.         , SLA FLOAT, Missed INT
  115.     )
  116.  
  117. INSERT INTO @CallsByDay
  118. SELECT CD.[DAY]
  119.     , CD.[COUNT]
  120.     , MiddleCall
  121.     , OperTimeMin
  122.     , OperTimeMax
  123.     , OperTimeMiddle
  124.     , IvrTimeMiddle
  125.     , IvrTimeMax
  126.     , IvrTimeMin
  127.     , SLA
  128.     , ISNULL(MD.[COUNT], 0)
  129. FROM @CallsByDay0 AS CD
  130. LEFT JOIN @MissedByDay AS MD
  131. ON CD.[DAY] = MD.[DAY]
  132.  
  133. DELETE @CallsByDay0
  134. DELETE @MissedCalls
  135.  
  136. --SELECT * FROM @CallsByDay
  137. -----------------------------------------------------------------------------------------------------------
  138.  
  139. --SELECT * FROM @MissedCalls
  140.  
  141. --Жесткая жесть для разбития по неделям
  142. --SELECT COUNT(Id)
  143. --  , CONCAT
  144. --      (  
  145. --          CONCAT
  146. --          (
  147. --              DATEPART(DAY, CAST(DATEADD(day, (DATEPART(WEEK, DateStart) - 1) * 7, '01-01-1900 00:00:00.001') as DATE))
  148. --              , '-'
  149. --              , DATEPART(MONTH, CAST(DATEADD(day, (DATEPART(WEEK, DateStart) - 1) * 7, '01-01-1900 00:00:00.001') as DATE))
  150. --          )
  151. --          , ':::'
  152. --          ,CONCAT
  153. --          (
  154. --              DATEPART(DAY, CAST(DATEADD(day, (DATEPART(WEEK, DateStart)) * 7, '01-01-1900 00:00:00.001') as DATE))
  155. --              , '-'
  156. --              , DATEPART(MONTH, CAST(DATEADD(day, (DATEPART(WEEK, DateStart)) * 7, '01-01-1900 00:00:00.001') as DATE))
  157. --          )
  158. --      )
  159. --FROM @MissedCalls
  160. --GROUP BY DATEPART(WEEK, DateStart)
  161.  
  162. IF (@INTERVAL = 1)
  163. BEGIN
  164.    SELECT [DAY] AS 'День'
  165.         , [COUNT] AS 'Всего звонков'
  166.         , MiddleCall AS 'Средняя длительность звонка'
  167.         , OperTimeMin AS 'Время до ответа оператора min'
  168.         , OperTimeMax AS 'Время до ответа оператора max'
  169.         , OperTimeMiddle AS 'Среднее время до ответа оператора'
  170.         , IvrTimeMiddle AS 'Время в IVR среднее'
  171.         , IvrTimeMax AS 'Время в IVR MAX'
  172.         , IvrTimeMin AS 'Время в IVR MIN'
  173.         , CAST(SLA * 100 AS nvarchar) + '%' AS 'SLA'
  174.         , Missed AS 'Пропущено'
  175.    FROM @CallsByDay
  176. END
  177.  
  178. ---Разбитие по неделям------------------------------------------------------------------------------
  179. IF (@INTERVAL = 2)
  180. BEGIN
  181.     DECLARE @MissedByWeek TABLE([COUNT] INT, [Week] NCHAR(20))
  182.     INSERT INTO @MissedByWeek
  183.     SELECT SUM([COUNT])
  184.         , CAST(DATEPART(DAY, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY]) - 1) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
  185.             + 'd'
  186.             + '-'
  187.             + CAST(DATEPART(MONTH, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY]) - 1) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
  188.             + 'm'
  189.             + ':::'
  190.             + CAST(DATEPART(DAY, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY])) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
  191.             + 'd'
  192.             + '-'
  193.             + CAST(DATEPART(MONTH, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY])) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
  194.             + 'm'
  195.     FROM @MissedByDay
  196.     GROUP BY DATEPART(WEEK, [DAY])
  197.  
  198.     DECLARE @CallsByWeek TABLE
  199.         (
  200.             [Week] NCHAR(20), [COUNT] INT, MiddleCall FLOAT
  201.             , OperTimeMin INT, OperTimeMax INT, OperTimeMiddle FLOAT
  202.             , IvrTimeMiddle FLOAT, IvrTimeMax INT, IvrTimeMin INT
  203.             , SLA FLOAT
  204.         )
  205.    
  206.     INSERT INTO @CallsByWeek
  207.     SELECT CAST(DATEPART(DAY, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY]) - 1) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
  208.             + 'd'
  209.             + '-'
  210.             + CAST(DATEPART(MONTH, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY]) - 1) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
  211.             + 'm'
  212.             + ':::'
  213.             + CAST(DATEPART(DAY, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY])) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
  214.             + 'd'
  215.             + '-'
  216.             + CAST(DATEPART(MONTH, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY])) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
  217.             + 'm'
  218.         , SUM([COUNT])
  219.         , ROUND(AVG(MiddleCall), 2)
  220.         , MIN(OperTimeMin)
  221.         , MAX(OperTimeMax)
  222.         , ROUND(AVG(OperTimeMiddle), 2)
  223.         , ROUND(AVG(IvrTimeMiddle), 2)
  224.         , MAX(IvrTimeMax)
  225.         , MIN(IvrTimeMin)
  226.         , ROUND(AVG(SLA), 2)
  227.     FROM @CallsByDay
  228.     GROUP BY DATEPART(WEEK, [DAY])
  229.  
  230.     SELECT CW.[Week] AS 'Неделя'
  231.         , CW.[COUNT] AS 'Всего звонков'
  232.         , MiddleCall AS 'Средняя длительность звонка'
  233.         , OperTimeMin AS 'Время до ответа оператора min'
  234.         , OperTimeMax AS 'Время до ответа оператора max'
  235.         , OperTimeMiddle AS 'Среднее время до ответа оператора'
  236.         , IvrTimeMiddle AS 'Время в IVR среднее'
  237.         , IvrTimeMax AS 'Время в IVR MAX'
  238.         , IvrTimeMin AS 'Время в IVR MIN'
  239.         , CAST(SLA * 100 AS nvarchar) + '%' AS 'SLA'
  240.         , ISNULL(MW.[COUNT], 0) AS 'Пропущено'
  241.     FROM @CallsByWeek   AS CW
  242.     LEFT JOIN @MissedByWeek  AS MW
  243.     ON CW.[Week] = MW.[Week]
  244. END
  245.  
  246. ----Разбитие по месяцам-----------------------------------------------------------------
  247. IF(@INTERVAL = 3)
  248. BEGIN
  249.     DECLARE @MissedByMonth TABLE([COUNT] INT, [MONTH] NCHAR(20))
  250.     INSERT INTO @MissedByMonth
  251.     SELECT SUM([COUNT])
  252.         , DATEPART(MONTH, [DAY])
  253.     FROM @MissedByDay
  254.     GROUP BY DATEPART(MONTH, [DAY])
  255.  
  256.     DECLARE @CallsByMonth TABLE
  257.         (
  258.             [MONTH] NCHAR(20), [COUNT] INT, MiddleCall FLOAT
  259.             , OperTimeMin INT, OperTimeMax INT, OperTimeMiddle FLOAT
  260.             , IvrTimeMiddle FLOAT, IvrTimeMax INT, IvrTimeMin INT
  261.             , SLA FLOAT
  262.         )
  263.    
  264.     INSERT INTO @CallsByMonth
  265.     SELECT DATEPART(MONTH, [DAY])
  266.         , SUM([COUNT])
  267.         , ROUND(AVG(MiddleCall), 2)
  268.         , MIN(OperTimeMin)
  269.         , MAX(OperTimeMax)
  270.         , ROUND(AVG(OperTimeMiddle), 2)
  271.         , ROUND(AVG(IvrTimeMiddle), 2)
  272.         , MAX(IvrTimeMax)
  273.         , MIN(IvrTimeMin)
  274.         , ROUND(AVG(SLA), 2)
  275.     FROM @CallsByDay
  276.     GROUP BY DATEPART(MONTH, [DAY])
  277.  
  278.     SELECT M.[Name] AS 'Месяц'
  279.         , CM.[COUNT] AS 'Всего звонков'
  280.         , MiddleCall AS 'Средняя длительность звонка'
  281.         , OperTimeMin AS 'Время до ответа оператора min'
  282.         , OperTimeMax AS 'Время до ответа оператора max'
  283.         , OperTimeMiddle AS 'Среднее время до ответа оператора'
  284.         , IvrTimeMiddle AS 'Время в IVR среднее'
  285.         , IvrTimeMax AS 'Время в IVR MAX'
  286.         , IvrTimeMin AS 'Время в IVR MIN'
  287.         , CAST(SLA * 100 AS nvarchar) + '%' AS 'SLA'
  288.         , ISNULL(MM.[COUNT], 0) AS 'Пропущено'
  289.     FROM @CallsByMonth AS CM
  290.     LEFT JOIN @MissedByMonth AS MM
  291.     ON CM.[MONTH] = MM.[MONTH]
  292.     LEFT JOIN @Months AS M
  293.     ON CM.[MONTH] = M.[NUMBER]
  294. END
RAW Paste Data