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

DECLARE @TASKS TABLE(Id uniqueidentifier)

INSERT INTO @TASKS
SELECT id FROM oktell.dbo.A_TaskManager_Tasks

DECLARE @DATE_START datetime = '07-18-2019 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 #Connections
FROM [dbo].[A_Stat_Connections_1x1] as SC
WHERE CAST(TimeStart AS TIME) BETWEEN CAST(@TIME_START AS TIME) AND CAST(@TIME_END AS TIME)
    AND TimeStart BETWEEN @DATE_START AND @DATE_END

--ФИЛЬТРУЕМ ПО ТАСКАМ
SELECT *
    , (
        SELECT TOP 1 ConnectionChainId FROM [oktell].[dbo].[A_TaskManager_Task_IncomingCalls] as TI
        WHERE ConnectionChainId = IdChain AND EXISTS(SELECT * FROM @TASKS as T WHERE T.Id = TaskId)
    ) as IdCh2
INTO #Connections2
FROM #Connections

DELETE FROM #Connections2 WHERE IdCh2 IS NULL

------ИЗВЛЕКАЕМ ПО ОПЕРАТОРАМ
SELECT * INTO #Filtered
FROM #Connections2
WHERE ConnectionType = 5

------ИЗВЛЕКАЕМ ПО IVR
SELECT * INTO #Filtered2
FROM #Connections
WHERE ConnectionType = 4

DROP TABLE #Connections2
DROP TABLE #Connections

----Считаем длительнсти звонков
DECLARE @Calls TABLE (Id uniqueidentifier, IvrTime INT, OperTime INT, TalkTime INT, StartTime datetime)

INSERT INTO @Calls
SELECT F1.IdChain
    , DATEDIFF(SECOND, (SELECT DISTINCT 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
-----------------------------------------------------------------------------------------------------------

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