СИС Звонков по операм

DECLARE @DATE_START  DATETIME = '07-18-2019 00:00:00.000'
DECLARE @DATE_FINISH DATETIME = '07-19-2019 00:00:00.000'
DECLARE @TIME_START  DATETIME = '01-01-1900 01:00:00.001'
DECLARE @TIME_FINISH DATETIME = '01-01-1900 23:00:00.001'

DECLARE @OPERATORS TABLE(Id uniqueidentifier)
INSERT @OPERATORS VALUES('230FCDC7-5DCD-4126-9374-E1EEB4F1F165')
INSERT @OPERATORS VALUES('95B1BDD5-AF34-40C4-9368-CB80E99A2818')
INSERT @OPERATORS VALUES('61CB2A8E-7BC8-4AAB-8915-89804CD7EC61')

--------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @HoursCount int = DATEDIFF(HOUR, @TIME_START, @TIME_FINISH)

DECLARE @FilteredConnections0 TABLE(TimeStart datetime, TimeStop datetime, OperId uniqueidentifier, IdChain nvarchar(50), [Name] nvarchar(50), ConnectionType int)
DECLARE @FilteredConnections TABLE(TimeStart datetime, TimeStop datetime, OperId uniqueidentifier, IdChain nvarchar(50), [Name] nvarchar(50))

INSERT INTO @FilteredConnections0
SELECT
    TimeStart
    ,TimeStop
    ,BUserID
    ,IdChain
    ,Bstr
    ,ConnectionType
FROM [oktell].[dbo].[A_Stat_Connections_1x1] as ascs WITH (NOLOCK)
RIGHT JOIN @OPERATORS as o
ON o.Id = ascs.BUserId
WHERE  CAST(TimeStart AS date) BETWEEN CAST(@DATE_START AS date) AND CAST(@DATE_FINISH AS date)
    AND CAST(TimeStart AS time) BETWEEN CAST(@TIME_START AS time) AND CAST(@TIME_FINISH AS time)


--ФИЛЬТРУЕМ ТАБЛИЦУ И СОХРАНЯЕМ В ПЕРЕМЕННОЙ
INSERT INTO @FilteredConnections
SELECT
    TimeStart
    ,TimeStop
    ,OperId
    ,IdChain
    ,[Name]
FROM @FilteredConnections0
WHERE ConnectionType = 5

--DELETE @FilteredConnections0

--select * from @FilteredConnections
-----------------------------------------------------------

--ЗАДАЕМ ТАБЛИЦУ СВЯЗЕЙ ID - NAME У ОПЕРАТОРОВ
DECLARE @Id_Names TABLE(Id uniqueidentifier, [Name] nvarchar(50))

INSERT INTO @Id_Names
SELECT OperId, [Name]
FROM @FilteredConnections
GROUP BY OperId, [Name]

--select * from @Id_Names
--------------------------------------------------------

--СЧИТАЕМ ОБРАБОТАННЫЕ ЗВОНКИ И ВРЕМЯ РАЗГОВОРА
DECLARE @LenTalking TABLE(LenTalking int, OperId uniqueidentifier)
DECLARE @LenTalking2 TABLE(LenTalking int, OperId uniqueidentifier, [Count] int)

INSERT INTO @LenTalking
SELECT DATEDIFF(SECOND,TimeStart, TimeStop), OperId
FROM @FilteredConnections

INSERT INTO @LenTalking2
SELECT SUM(LenTalking), OperId, COUNT(OperId)
FROM @LenTalking
GROUP BY OperId

DELETE @LenTalking
--select * from @LenTalking2
----------------------------------------------------------

--СЧИТАЕМ ПРОПУЩЕННЫЕ ЗВОНКИ
DECLARE @Missed TABLE(OperId uniqueidentifier)
DECLARE @Missed2 TABLE([Count] int, OperId uniqueidentifier)

INSERT INTO @Missed
SELECT fc.OperId
FROM @FilteredConnections0 as fc
JOIN [oktell].[dbo].[A_Stat_MissedCalls] AS sm
ON fc.IdChain = sm.IdChain

INSERT INTO @Missed2
SELECT COUNT(OperId), OperId
FROM @Missed
GROUP BY OperId

DELETE @Missed
--select * from @Missed2
--------------------------------------------------------------

--СЧИТАЕМ ПОТЕРЯННЫЕ ЗВОНКИ
DECLARE @Failed TABLE(OperId uniqueidentifier)
DECLARE @Failed2 TABLE([Count] int, OperId uniqueidentifier)

INSERT INTO @Failed
SELECT fc.OperId
FROM @FilteredConnections as fc
JOIN [oktell].[dbo].[A_Stat_FailedCalls] AS sm
ON fc.IdChain = sm.IdChain


DELETE @FilteredConnections

INSERT INTO @Failed2
SELECT COUNT(OperId), OperId
FROM @Failed
GROUP BY OperId

DELETE @Failed
--select * from @Failed2
--------------------------------------------------------------

--СЧИТАЕМ ВСЕ ЗВОНКИ
DECLARE @Final TABLE(OperId uniqueidentifier, [Count] int)
DECLARE @Final2 TABLE(OperId uniqueidentifier, [Count] int)

INSERT INTO @Final
SELECT OperId, [Count]
FROM @LenTalking2
UNION
SELECT OperId, [Count]
FROM @Failed2
UNION
SELECT OperId, [Count]
FROM @Missed2

INSERT INTO @Final2
SELECT OperId, SUM(Count)
FROM @Final
GROUP BY OperId

DELETE @Final
DELETE @Failed2
--select * from @Final2
---------------------------------------------------------------

DECLARE @FinalTable0 TABLE([Name] nvarchar(50), [All] int, Checked int, Missed int, TimeTalking int, MiddleCount float)
DECLARE @FinalTable TABLE([Name] nvarchar(50), [All] int, Checked int, Missed int, TimeTalking int, MiddleCount float)

INSERT INTO @FinalTable0
SELECT
    ins.[Name]
    ,fin.[Count]
    ,lt.[Count]
    ,m.[Count]
    ,lt.LenTalking
    ,lt.[Count]
FROM @LenTalking2 as lt
LEFT JOIN @Missed2 as m
    ON m.OperId = lt.OperId
LEFT JOIN @Final2 as fin
    ON fin.OperId = lt.OperId
LEFT JOIN @Id_Names as ins
    ON ins.Id = lt.OperId

 --select * from @FinalTable
 DELETE @Final2
 DELETE @LenTalking2
 DELETE @Missed2
 DELETE @Id_Names

 INSERT INTO @FinalTable
 SELECT [Name]
    ,SUM([All])
    ,SUM(Checked)
    ,SUM(Missed)
    ,SUM(TimeTalking) / SUM([All])
    ,ROUND(SUM(Checked) / CAST(@HoursCount as float), 2)
 FROM @FinalTable0
 GROUP BY [Name]

 DELETE @FinalTable0

 SELECT [Name] as 'Оператор'
    , ISNULL([All], 0) as 'Итого'
    , ISNULL(Checked, 0) as 'Обработано'
    , CONCAT(IIF([All] != 0 AND [ALL] IS NOT NULL, ROUND((ISNULL(Checked, 0) / CAST([All] as float)), 4) * 100, 0), '%')    as 'Обработано %'
    , ISNULL(Missed, 0) as 'Пропущено'
    , CONCAT(IIF([All] != 0 AND [ALL] IS NOT NULL, ROUND((ISNULL(Missed, 0) / CAST([All] as float)), 4) * 100, 0), '%') as 'Пропущено %'
    , IIF(LEN(CAST(TimeTalking / 3600 as nvarchar)) != 1, CAST(TimeTalking / 3600 as nvarchar), '0' + CAST(TimeTalking / 3600 as nvarchar))
        + ':'
        + IIF(LEN(CAST(TimeTalking / 60 - (TimeTalking / 3600) * 60 as nvarchar)) != 1, CAST(TimeTalking / 60 - (TimeTalking / 3600) * 60 as nvarchar), '0' + CAST(TimeTalking / 60 - (TimeTalking / 3600) * 60 as nvarchar))
        + ':'
        + IIF(LEN(CAST(TimeTalking - (TimeTalking / 60) * 60 as nvarchar)) != 1, CAST(TimeTalking - (TimeTalking / 60) * 60 as nvarchar), '0' + CAST(TimeTalking - (TimeTalking / 60) * 60 as nvarchar))
    , MiddleCount as 'Среднее количество принятых звонков за час'
 FROM @FinalTable
 ORDER BY [Name]