Потерянные звонк и GETT

DECLARE @DATE_START DATETIME = '20190727 00:00'
DECLARE @DATE_END DATETIME = '20190730 23:59'

DECLARE @TIME_START DATETIME = '19000101 00:00'
DECLARE @TIME_END DATETIME = '19000101 23:00'

DECLARE @TASKS TABLE (Id uniqueidentifier)
INSERT INTO @TASKS
SELECT id
FROM [oktell_settings].[dbo].[A_TaskManager_Tasks]

DECLARE @OPERATORS TABLE (Id uniqueidentifier)
INSERT INTO @OPERATORS
SELECT TOP 200 id
FROM [oktell_settings].[dbo].[A_Users]
-------------------------------------------------------------------------------------------------------------------------

----ФИЛЬТРУЕМ СТАРТОВУЮ ТАБЛИЦУ---------------------------------------------------------
DECLARE @Filtered TABLE
    (
        IdEffort uniqueidentifier, IdChain uniqueidentifier, IdOperator uniqueidentifier
        , CallResult int , UserResult int
        , TimeStart datetime, LenTime int
    )

INSERT INTO @Filtered
SELECT IdEffort
    , IdChain
    , IdOperator
    , CallResult
    , UserResult
    , DateTimeStart
    , LenTime
FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
WHERE DateTimeStart BETWEEN @DATE_START AND @DATE_END
    AND TimeStart BETWEEN @TIME_START AND @TIME_END
    AND IdTask IN (SELECT * FROM @TASKS)
    AND IdOperator IS NOT NULL
    AND IdOperator != 'AB000000-0000-0000-0000-000000000000'

DELETE @TASKS
---------------------------------------------------------------------------------------------------

----ОТФИЛЬТРОВАННЫЕ ПО ОПЕРАТОРАМ------------------------------------------------------------------
DECLARE @FilteredWithOpers TABLE
    (
        IdEffort uniqueidentifier, IdChain uniqueidentifier, IdOperator uniqueidentifier
        , CallResult int , UserResult int
        , TimeStart datetime, LenTime int
    )

INSERT INTO @FilteredWithOpers
SELECT *
FROM @Filtered
WHERE IdOperator IN (SELECT * FROM @OPERATORS)

DELETE @OPERATORS
-------------------------------------------------------------------------------------------------------

-----СЧИТАЕМ ЗВОНКИ ПО ЗАДАЧАМ БЕЗ ОПЕРАТОРОВ----------------------------------------------------------
DECLARE @Temp TABLE(IdOperator uniqueidentifier, IdEffort uniqueidentifier, CallResult int)

INSERT INTO @Temp
SELECT F.IdOperator, F.IdEffort, F.CallResult
FROM @FilteredWithOpers AS FO
RIGHT JOIN @Filtered AS F
    ON FO.IdOperator = F.IdOperator
WHERE FO.IdOperator IS NULL

DECLARE @FailedByTask TABLE (Id uniqueidentifier, IdEffort uniqueidentifier)

INSERT INTO @FailedByTask
SELECT IdOperator, IdEffort
FROM @Temp
WHERE CallResult != 5
GROUP BY IdOperator, IdEffort


DECLARE @failed_by_task int =
(
    SELECT COUNT(*)
    FROM @FailedByTask
)

DECLARE @SuccessByTask TABLE (Id uniqueidentifier, IdEffort uniqueidentifier)

INSERT INTO @SuccessByTask
SELECT IdOperator, IdEffort
FROM @Temp
WHERE CallResult = 5
GROUP BY IdOperator, IdEffort


DECLARE @success_by_task int =
(
    SELECT COUNT(*)
    FROM @SuccessByTask
)

DELETE @Temp
DELETE @FailedByTask
DELETE @SuccessByTask
DELETE @Filtered

--PRINT @failed_by_task
--PRINT @success_by_task
------------------------------------------------------------------------------------------------------------------

-----СЧИТАЕМ ЗВОНКИ ПО ОПЕРАТОРАМ------------------------------------------------------------------
DECLARE @AllCalls TABLE (Id uniqueidentifier, [Count] int)

INSERT INTO @AllCalls
SELECT IdOperator
    , COUNT(DISTINCT IdEffort)
FROM @FilteredWithOpers
GROUP BY IdOperator

DECLARE @SuccessCalls TABLE (Id uniqueidentifier, [Count] int, LenTime int)

INSERT INTO @SuccessCalls
SELECT IdOperator
    , COUNT(DISTINCT IdEffort)
    , AVG(LenTime)
FROM @FilteredWithOpers
WHERE CallResult = 5
GROUP BY IdOperator

DECLARE @FailedCalls TABLE (Id uniqueidentifier, [Count] int)

INSERT INTO @FailedCalls
SELECT IdOperator
    , COUNT(DISTINCT IdEffort)
FROM @FilteredWithOpers
WHERE CallResult != 5
GROUP BY IdOperator

DELETE @FilteredWithOpers
-------------------------------------------------------------------------------------------------------------
--select * from @AllCalls
--select * from @SuccessCalls
--select * from @FailedCalls

DECLARE @Result TABLE ([Name] nvarchar(50), [All] int, Success int, Failed int, AvgTime nvarchar(20))

INSERT @Result VALUES('А По задачам', @success_by_task + @failed_by_task, @success_by_task, @failed_by_task, NULL)

INSERT INTO @Result
SELECT OI.[Name]
    , ISNULL(AC.[Count], 0)
    , ISNULL(SC.[Count], 0)
    , ISNULL(FC.[Count], 0)
    , (SELECT [dbo].[GetTimeFromSecond](SC.LenTime))
FROM @AllCalls as AC
LEFT JOIN [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo] as OI
    ON AC.Id = OI.Id
LEFT JOIN @SuccessCalls as SC
    ON AC.Id = SC.Id
LEFT JOIN @FailedCalls as FC
    ON AC.Id = FC.Id

DECLARE @Total TABLE ([Name] nvarchar(50), [All] int, Success int, Failed int, AvgTime nvarchar(20))

INSERT INTO @Total
SELECT 'Всего'
    , SUM([All])
    , SUM(Success)
    , SUM(Failed)
    , NULL
FROM @Result

SELECT * FROM @Total
UNION
SELECT * FROM @Result
ORDER BY [Name] DESC

Информацию извлекал из таблиц - EffortConnections

Удачные звонки от неудачных я отделял по таблице EffortConnections по полю CallResult

Примерный Расчет сложности выполнения запроса:

Размер таблицы EffortConnections = EC; OperatorInfo = OI
Размеры фильтров - Дата = d; Таски = t; Операторы = x

Fa - сложность функции подсчета среднего
Fs - сложность функции подсчета суммы
Fsec - сложность функции получения секунд от времени
Fcast, F/

Join - коэффицент сложности джойна => 0 <= Join <= 1
    Самый низкий, если джойн по int.
    Средний если join по uniqueidentifier
    Высокий если join по nvarchar

Знак равно задает приблизительное соответсвие

1.) Filtered
    Сложность построения = O(EC * t)
    Длина = O(d)

2.) FilteredWithOpers
    Сложность построения = O(d * x)
    Длина = O(d - OI) = O(d - 636) = O(d)

3.) Temp
    Сложность построения = F * FO * J = O[(d^2) * J]
    Длина = F - FO = O(d)

4.) failed_by_task = success_by_task
    Сложность построения = 2 * T = O(d)
    Длина = O(d / 2) = O(d)

5.) AllCalls = SuccessCalls = FailedCalls
    Сложность построения = FO * x^2 = O(dx^2)
    Длина = O(d / x)

6.) Result
    Сложность построения = (AC * SC * FC) * J^2 * (3Fnull + Fsec) = O[(d^3 / x^3) * J^2]
    Длина = O(d / x) = AC

7.) Total
    Сложность построения = AC = O(d / x)
    Длина = 1

8.) Сложность построения = AC + 1 = O(d / x)

Сложность построения суммарная =
O [
    EC * t + (3d^3 / x^3) * (2 + J^2) + Jd^2 + d(3x^2 + x + 2)
  ]