SHARE
TWEET

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

WorkAkkaunt Jul 22nd, 2019 (edited) 61 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
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
 
Top