СИС. Общее число звонков.

DECLARE @DATE_START  DATETIME = '07-10-2019 00:00:00.000'
DECLARE @DATE_FINISH DATETIME = '07-10-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 @NUMBERS TABLE(Number nvarchar(50))
INSERT @NUMBERS VALUES('84957772515')
INSERT @NUMBERS VALUES('84956821244')

DECLARE @FilteredConnections0 TABLE(TimeStart datetime, TimeStop datetime, OperId nvarchar(50), IdChain nvarchar(50), Number nvarchar(50), ConnectionType int)

INSERT INTO @FilteredConnections0
SELECT
    TimeStart
    ,TimeStop
    ,BLineID
    ,IdChain
    ,AOutNumber
    ,ConnectionType
FROM [oktell].[dbo].[A_Stat_Connections_1x1] as ascs WITH (NOLOCK)
RIGHT JOIN @NUMBERS as n
ON n.Number = ascs.AOutNumber

--select * from @FilteredConnections0

DECLARE @FilteredConnections TABLE(TimeStart datetime, TimeStop datetime, OperId nvarchar(50), IdChain nvarchar(50), Number nvarchar(50))

--ФИЛЬТРУЕМ ТАБЛИЦУ И СОХРАНЯЕМ В ПЕРЕМЕННОЙ
INSERT INTO @FilteredConnections
SELECT
    TimeStart
    ,TimeStop
    ,OperId
    ,IdChain
    ,Number
FROM @FilteredConnections0
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)
    AND (ConnectionType = 5 OR ConnectionType = 6)
----------------------------------------------------

DELETE @FilteredConnections0

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

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

INSERT INTO @LenTalking2
SELECT SUM(LenTalking), Number, COUNT(Number)
FROM @LenTalking
GROUP BY NUMBER
---------------------------------------------------------
DELETE @LenTalking
--select * from @LenTalking2

--СЧИТАЕМ ВРЕМЯ ОЖИДАНИЯ
DECLARE @RingTime TABLE(RingLen float, Number nvarchar(50))
DECLARE @RingTime2 TABLE(RingLen float, Number nvarchar(50))

INSERT INTO @RingTime
SELECT RingMs / 1000, Number
FROM @FilteredConnections as sc
JOIN [oktell].[dbo].[A_Stat_RingTime] as rt
ON sc.TimeStart = rt.StartDT

INSERT INTO @RingTime2
SELECT SUM(RingLen), Number
FROM @RingTime
GROUP BY Number
----------------------------------------------------------
DELETE @RingTime
--select * from @RingTime2

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

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

INSERT INTO @Missed2
SELECT COUNT(NUMBER), Number
FROM @Missed
GROUP BY Number
--------------------------------------------------------------
DELETE @Missed
--select * from @Missed2

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

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

DELETE @FilteredConnections

INSERT INTO @Failed2
SELECT COUNT(NUMBER), Number
FROM @Failed
GROUP BY Number
--------------------------------------------------------------
DELETE @Failed
--select * from @Failed2

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

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

INSERT INTO @Final2
SELECT Number, SUM(Count)
FROM @Final
GROUP BY Number
---------------------------------------------------------------
DELETE @Final
--select * from @Final2
DECLARE @FinalTable TABLE(Number nvarchar(50), [All] int, Checked int, Failed int, Missed int, TimeWaiting int, TimeTalking int)

INSERT INTO @FinalTable
SELECT
    lt.Number
    ,fin.[Count]
    ,lt.[Count]
    ,f.[Count]
    ,m.[Count]
    ,r.RingLen / lt.[Count]
    ,lt.LenTalking / fin.[Count]
FROM @LenTalking2 as lt
LEFT JOIN @Missed2 as m
    ON m.Number = lt.Number
LEFT JOIN @Final2 as fin
    ON fin.Number = lt.Number
LEFT JOIN @Failed2 as f
    ON f.Number = lt.Number
LEFT JOIN @RingTime2 as r
    ON r.Number = lt.Number

DELETE @LenTalking2
DELETE @Missed2
DELETE @Final2
DELETE @Failed2
DELETE @RingTime2

select Number as 'Номер'
    ,ISNULL([All], 0) as 'Итого'
    ,ISNULL(Checked, 0) as 'Обработано'
    ,ISNULL(Failed, 0) as 'Потеряно'
    ,ISNULL(Missed, 0) as 'Пропущено'
    ,CONCAT(TimeWaiting / 3600, '.', TimeWaiting / 60 - (TimeWaiting / 3600) * 3600, '.', TimeWaiting - (TimeWaiting / 60) * 60) as 'Среднее время ожидания'
    ,CONCAT(TimeTalking / 3600, '.', TimeTalking / 60 - (TimeTalking / 3600) * 60, '.', TimeTalking - (TimeTalking / 60) * 60) as 'Среднее время разговора'
from @FinalTable
ORDER BY Number