ТЕЛЕТАЙ - Отчет по интервалам

DECLARE @DATE_START DATETIME = '20190830 00:00'
DECLARE @DATE_END DATETIME = '20190830 23:59'
DECLARE @TIME_START DATETIME = '19000101 00:00'
DECLARE @TIME_END DATETIME = '19000101 20:00'

DECLARE @OPERATORS TABLE (Id UNIQUEIDENTIFIER PRIMARY KEY)

INSERT INTO @OPERATORS
SELECT Id
FROM [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo] WITH (NOLOCK)
--------------------------------------------------------------------------------------------------------------------------------

SELECT * INTO #OlpCalls
FROM [oktell].[dbo].[OLP_NextCall_V2] WITH (NOLOCK)
WHERE [date_insert] BETWEEN @DATE_START AND @DATE_END
AND CAST(CAST([date_insert] AS TIME) AS DATETIME) BETWEEN @TIME_START AND @TIME_END
AND OperatorId IN (SELECT Id FROM @OPERATORS)

SELECT * INTO #ClientCalls
FROM #OlpCalls
WHERE client_type = 0

SELECT CAST(date_insert AS DATE) [Day]
    ,DATEPART(HOUR, CAST(date_insert AS TIME)) [Hour]
    ,COUNT(*) [Count] INTO #All
FROM #ClientCalls
GROUP BY CAST(date_insert AS DATE), DATEPART(HOUR, CAST(date_insert AS TIME))

SELECT CAST(date_insert AS DATE) [Day]
    ,DATEPART(HOUR, CAST(date_insert AS TIME)) [Hour]
    ,COUNT(*) [Count] INTO #Dialing
FROM #ClientCalls
WHERE CallResult IN (5,18,29)
GROUP BY CAST(date_insert AS DATE), DATEPART(HOUR, CAST(date_insert AS TIME))

--Не дозвон = Все - Дозвон

SELECT CAST(date_insert AS DATE) [Day]
    ,DATEPART(HOUR, CAST(date_insert AS TIME)) [Hour]
    ,COUNT(*) [Count] INTO #Missing
FROM #ClientCalls
WHERE CallResult IN (1,6,7,20)
GROUP BY CAST(date_insert AS DATE), DATEPART(HOUR, CAST(date_insert AS TIME))

SELECT CAST(date_insert AS DATE) [Day]
    ,DATEPART(HOUR, CAST(date_insert AS TIME)) [Hour]
    ,COUNT(*) [Count] INTO #Tickets
FROM #ClientCalls
WHERE id_result = 31 AND messenger_id IN (1,3,4,5)
GROUP BY CAST(date_insert AS DATE), DATEPART(HOUR, CAST(date_insert AS TIME))

SELECT CAST(date_insert AS DATE) [Day]
    ,DATEPART(HOUR, CAST(date_insert AS TIME)) [Hour]
    ,COUNT(*) [Count] INTO #Refuse
FROM #ClientCalls
WHERE id_result = 32
GROUP BY CAST(date_insert AS DATE), DATEPART(HOUR, CAST(date_insert AS TIME))

DROP TABLE #OlpCalls

DROP TABLE #ClientCalls

DROP TABLE #All
DROP TABLE #Dialing
DROP TABLE #Missing
DROP TABLE #Tickets
DROP TABLE #Refuse