Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --По звонкам(фильтры: задачи, период, в разрезе дни/недели/месяцы)
- DECLARE @TASKS TABLE(Id uniqueidentifier)
- INSERT INTO @TASKS
- SELECT id FROM oktell.dbo.A_TaskManager_Tasks
- DECLARE @DATE_START datetime = '07-10-2018 00:00:00.001'
- DECLARE @DATE_END datetime = '07-19-2019 23:59:59.000'
- DECLARE @TIME_START datetime = '01-01-1900 00:00:00.001'
- DECLARE @TIME_END datetime = '01-01-1900 23:59:00.001'
- DECLARE @INTERVAL INT = 3
- /*select 1, 'По дням'
- union all
- select 2, 'По неделям'
- union all
- select 3, 'По месяцам'*/
- --------------------------------------------------------------------------------------------------------------------------
- DECLARE @Months TABLE(NUMBER INT, [Name] nvarchar(20))
- INSERT INTO @Months VALUES(1, 'Январь')
- INSERT INTO @Months VALUES(2, 'Февраль')
- INSERT INTO @Months VALUES(3, 'Март')
- INSERT INTO @Months VALUES(4, 'Апрель')
- INSERT INTO @Months VALUES(5, 'Май')
- INSERT INTO @Months VALUES(6, 'Июнь')
- INSERT INTO @Months VALUES(7, 'Июль')
- INSERT INTO @Months VALUES(8, 'Август')
- INSERT INTO @Months VALUES(9, 'Сентябрь')
- INSERT INTO @Months VALUES(10, 'Октябрь')
- INSERT INTO @Months VALUES(11, 'Ноябрь')
- INSERT INTO @Months VALUES(12, 'Декабрь')
- ----ИЗВЛЕКАЕМ ПО ОПЕРАТОРАМ
- SELECT * INTO #Filtered
- FROM [dbo].[A_Stat_Connections_1x1] AS SC
- WHERE TimeStart BETWEEN @DATE_START AND @DATE_END
- AND CAST(TimeStart AS TIME) BETWEEN CAST(@TIME_START AS TIME) AND CAST(@TIME_END AS TIME)
- AND ConnectionType = 5
- ----ИЗВЛЕКАЕМ ПО IVR
- SELECT * INTO #Filtered2
- FROM [dbo].[A_Stat_Connections_1x1] AS SC
- WHERE TimeStart BETWEEN @DATE_START AND @DATE_END
- AND CAST(TimeStart AS TIME) BETWEEN CAST(@TIME_START AS TIME) AND CAST(@TIME_END AS TIME)
- AND ConnectionType = 4
- ----Считаем длительнсти звонков
- DECLARE @Calls TABLE (Id uniqueidentifier, IvrTime INT, OperTime INT, TalkTime INT, StartTime datetime)
- INSERT INTO @Calls
- SELECT F1.IdChain
- , DATEDIFF(SECOND, (SELECT TOP 1 TimeStart FROM #Filtered2 AS F2 WHERE F1.IdChain = F2.IdChain), F1.TimeAnswer)
- , DATEDIFF(SECOND, F1.TimeStart, F1.TimeAnswer)
- , DATEDIFF(SECOND, F1.TimeStart, F1.TimeStop)
- , F1.TimeStart
- FROM #Filtered AS F1
- DROP TABLE #Filtered
- DROP TABLE #Filtered2
- ---------------------------------------------------------
- --select * from @Calls
- -----Извлекаем пропущенные звонки
- DECLARE @MissedCalls TABLE(Id uniqueidentifier, DateStart datetime)
- INSERT INTO @MissedCalls
- SELECT IdChain, TimeStart
- FROM [dbo].[A_Stat_MissedCalls]
- WHERE TimeStart BETWEEN @DATE_START AND @DATE_END
- AND DATEDIFF(SECOND, TimeStart, TimeStop) <= 10
- -----разбиваем пропущенные по дням
- DECLARE @MissedByDay TABLE([COUNT] INT, [DAY] NCHAR(20))
- INSERT INTO @MissedByDay
- SELECT COUNT(Id), CAST(DateStart AS DATE)
- FROM @MissedCalls
- --WHERE COUNT(Id) >= 10
- GROUP BY CAST(DateStart AS DATE)
- ----------------------------------------------
- ------РАЗБИВАЕМ ЗВОНКИ ПО ДНЯМ
- DECLARE @CallsByDay0 TABLE
- (
- [DAY] NCHAR(20), [COUNT] INT, MiddleCall FLOAT
- , OperTimeMin INT, OperTimeMax INT, OperTimeMiddle FLOAT
- , IvrTimeMiddle FLOAT, IvrTimeMax INT, IvrTimeMin INT
- , SLA FLOAT
- )
- INSERT INTO @CallsByDay0
- SELECT CAST(StartTime AS DATE)
- , COUNT(C.Id)
- , AVG(TalkTime)
- , MIN(OperTime)
- , MAX(OperTime)
- , 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)
- , AVG(IvrTime)
- , MAX(IvrTime)
- , MIN(IvrTime)
- , ROUND((COUNT(C.Id) - SUM( IIF(OperTime - 10 > 0, OperTime - 10, 0))) / CAST(COUNT(C.Id) AS FLOAT), 2)
- FROM @Calls AS C
- LEFT JOIN @MissedByDay AS MC
- ON CAST(C.StartTime AS DATE) = MC.[DAY]
- GROUP BY CAST(StartTime AS DATE)
- DECLARE @CallsByDay TABLE
- (
- [DAY] NCHAR(20), [COUNT] INT, MiddleCall FLOAT
- , OperTimeMin INT, OperTimeMax INT, OperTimeMiddle FLOAT
- , IvrTimeMiddle FLOAT, IvrTimeMax INT, IvrTimeMin INT
- , SLA FLOAT, Missed INT
- )
- INSERT INTO @CallsByDay
- SELECT CD.[DAY]
- , CD.[COUNT]
- , MiddleCall
- , OperTimeMin
- , OperTimeMax
- , OperTimeMiddle
- , IvrTimeMiddle
- , IvrTimeMax
- , IvrTimeMin
- , SLA
- , ISNULL(MD.[COUNT], 0)
- FROM @CallsByDay0 AS CD
- LEFT JOIN @MissedByDay AS MD
- ON CD.[DAY] = MD.[DAY]
- DELETE @CallsByDay0
- DELETE @MissedCalls
- --SELECT * FROM @CallsByDay
- -----------------------------------------------------------------------------------------------------------
- --SELECT * FROM @MissedCalls
- --Жесткая жесть для разбития по неделям
- --SELECT COUNT(Id)
- -- , CONCAT
- -- (
- -- CONCAT
- -- (
- -- DATEPART(DAY, CAST(DATEADD(day, (DATEPART(WEEK, DateStart) - 1) * 7, '01-01-1900 00:00:00.001') as DATE))
- -- , '-'
- -- , DATEPART(MONTH, CAST(DATEADD(day, (DATEPART(WEEK, DateStart) - 1) * 7, '01-01-1900 00:00:00.001') as DATE))
- -- )
- -- , ':::'
- -- ,CONCAT
- -- (
- -- DATEPART(DAY, CAST(DATEADD(day, (DATEPART(WEEK, DateStart)) * 7, '01-01-1900 00:00:00.001') as DATE))
- -- , '-'
- -- , DATEPART(MONTH, CAST(DATEADD(day, (DATEPART(WEEK, DateStart)) * 7, '01-01-1900 00:00:00.001') as DATE))
- -- )
- -- )
- --FROM @MissedCalls
- --GROUP BY DATEPART(WEEK, DateStart)
- IF (@INTERVAL = 1)
- BEGIN
- SELECT [DAY] AS 'День'
- , [COUNT] AS 'Всего звонков'
- , MiddleCall AS 'Средняя длительность звонка'
- , OperTimeMin AS 'Время до ответа оператора min'
- , OperTimeMax AS 'Время до ответа оператора max'
- , OperTimeMiddle AS 'Среднее время до ответа оператора'
- , IvrTimeMiddle AS 'Время в IVR среднее'
- , IvrTimeMax AS 'Время в IVR MAX'
- , IvrTimeMin AS 'Время в IVR MIN'
- , CAST(SLA * 100 AS nvarchar) + '%' AS 'SLA'
- , Missed AS 'Пропущено'
- FROM @CallsByDay
- END
- ---Разбитие по неделям------------------------------------------------------------------------------
- IF (@INTERVAL = 2)
- BEGIN
- DECLARE @MissedByWeek TABLE([COUNT] INT, [Week] NCHAR(20))
- INSERT INTO @MissedByWeek
- SELECT SUM([COUNT])
- , CAST(DATEPART(DAY, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY]) - 1) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
- + 'd'
- + '-'
- + CAST(DATEPART(MONTH, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY]) - 1) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
- + 'm'
- + ':::'
- + CAST(DATEPART(DAY, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY])) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
- + 'd'
- + '-'
- + CAST(DATEPART(MONTH, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY])) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
- + 'm'
- FROM @MissedByDay
- GROUP BY DATEPART(WEEK, [DAY])
- DECLARE @CallsByWeek TABLE
- (
- [Week] NCHAR(20), [COUNT] INT, MiddleCall FLOAT
- , OperTimeMin INT, OperTimeMax INT, OperTimeMiddle FLOAT
- , IvrTimeMiddle FLOAT, IvrTimeMax INT, IvrTimeMin INT
- , SLA FLOAT
- )
- INSERT INTO @CallsByWeek
- SELECT CAST(DATEPART(DAY, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY]) - 1) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
- + 'd'
- + '-'
- + CAST(DATEPART(MONTH, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY]) - 1) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
- + 'm'
- + ':::'
- + CAST(DATEPART(DAY, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY])) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
- + 'd'
- + '-'
- + CAST(DATEPART(MONTH, CAST(DATEADD(DAY, (DATEPART(WEEK, [DAY])) * 7, '01-01-1900 00:00:00.001') AS DATE)) AS nvarchar)
- + 'm'
- , SUM([COUNT])
- , ROUND(AVG(MiddleCall), 2)
- , MIN(OperTimeMin)
- , MAX(OperTimeMax)
- , ROUND(AVG(OperTimeMiddle), 2)
- , ROUND(AVG(IvrTimeMiddle), 2)
- , MAX(IvrTimeMax)
- , MIN(IvrTimeMin)
- , ROUND(AVG(SLA), 2)
- FROM @CallsByDay
- GROUP BY DATEPART(WEEK, [DAY])
- SELECT CW.[Week] AS 'Неделя'
- , CW.[COUNT] AS 'Всего звонков'
- , MiddleCall AS 'Средняя длительность звонка'
- , OperTimeMin AS 'Время до ответа оператора min'
- , OperTimeMax AS 'Время до ответа оператора max'
- , OperTimeMiddle AS 'Среднее время до ответа оператора'
- , IvrTimeMiddle AS 'Время в IVR среднее'
- , IvrTimeMax AS 'Время в IVR MAX'
- , IvrTimeMin AS 'Время в IVR MIN'
- , CAST(SLA * 100 AS nvarchar) + '%' AS 'SLA'
- , ISNULL(MW.[COUNT], 0) AS 'Пропущено'
- FROM @CallsByWeek AS CW
- LEFT JOIN @MissedByWeek AS MW
- ON CW.[Week] = MW.[Week]
- END
- ----Разбитие по месяцам-----------------------------------------------------------------
- IF(@INTERVAL = 3)
- BEGIN
- DECLARE @MissedByMonth TABLE([COUNT] INT, [MONTH] NCHAR(20))
- INSERT INTO @MissedByMonth
- SELECT SUM([COUNT])
- , DATEPART(MONTH, [DAY])
- FROM @MissedByDay
- GROUP BY DATEPART(MONTH, [DAY])
- DECLARE @CallsByMonth TABLE
- (
- [MONTH] NCHAR(20), [COUNT] INT, MiddleCall FLOAT
- , OperTimeMin INT, OperTimeMax INT, OperTimeMiddle FLOAT
- , IvrTimeMiddle FLOAT, IvrTimeMax INT, IvrTimeMin INT
- , SLA FLOAT
- )
- INSERT INTO @CallsByMonth
- SELECT DATEPART(MONTH, [DAY])
- , SUM([COUNT])
- , ROUND(AVG(MiddleCall), 2)
- , MIN(OperTimeMin)
- , MAX(OperTimeMax)
- , ROUND(AVG(OperTimeMiddle), 2)
- , ROUND(AVG(IvrTimeMiddle), 2)
- , MAX(IvrTimeMax)
- , MIN(IvrTimeMin)
- , ROUND(AVG(SLA), 2)
- FROM @CallsByDay
- GROUP BY DATEPART(MONTH, [DAY])
- SELECT M.[Name] AS 'Месяц'
- , CM.[COUNT] AS 'Всего звонков'
- , MiddleCall AS 'Средняя длительность звонка'
- , OperTimeMin AS 'Время до ответа оператора min'
- , OperTimeMax AS 'Время до ответа оператора max'
- , OperTimeMiddle AS 'Среднее время до ответа оператора'
- , IvrTimeMiddle AS 'Время в IVR среднее'
- , IvrTimeMax AS 'Время в IVR MAX'
- , IvrTimeMin AS 'Время в IVR MIN'
- , CAST(SLA * 100 AS nvarchar) + '%' AS 'SLA'
- , ISNULL(MM.[COUNT], 0) AS 'Пропущено'
- FROM @CallsByMonth AS CM
- LEFT JOIN @MissedByMonth AS MM
- ON CM.[MONTH] = MM.[MONTH]
- LEFT JOIN @Months AS M
- ON CM.[MONTH] = M.[NUMBER]
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement