Индивидуальная статистика V3

DECLARE @DATE_START datetime = '20190729 23:59'
DECLARE @DATE_END   datetime = '20190730 23:59'

DECLARE @TIME_START datetime = '19000101 00:00'
DECLARE @TIME_END   datetime = '19000101 23:59'

DECLARE @TASKS TABLE (Id uniqueidentifier)

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

DECLARE @OPERATORS TABLE (Id uniqueidentifier)

INSERT INTO @OPERATORS
SELECT Id
FROM [oktell_settings].[dbo].[A_Users]

DECLARE @TYPE_TIME bit = 'False'

DECLARE @TYPE_REPORT int = 1
    --select 1, 'к каждой дате'
    --union all
    --select 2, 'к дате начала и конца'
----------------------------------------------------------------------------------

----ФИЛЬТРУЕМ ВХОДЯЩИЕ ЗВОНКИ---------------------------------------------------------
DECLARE @FilteredEffort TABLE(OperId uniqueidentifier, LenTime int, CallResult int)

IF(@TYPE_REPORT = 1)
BEGIN
    INSERT INTO @FilteredEffort
    SELECT IdOperator
        , CAST(LenTime as int)
        , CallResult
    FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
    WHERE DateTimeStart BETWEEN @DATE_START AND @DATE_END
        AND IdTask IN (SELECT * FROM @TASKS)
        AND IdOperator IN (SELECT * FROM @OPERATORS)
END

IF(@TYPE_REPORT = 2)
BEGIN
    INSERT INTO @FilteredEffort
    SELECT IdOperator
        , CAST(LenTime as int)
        , CallResult
    FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
    WHERE (CAST(DateTimeStart as date) = CAST(@DATE_START as date) OR CAST(DateTimeStart as date) =  CAST(@DATE_END as date))
        AND IdTask IN (SELECT * FROM @TASKS)
        AND IdOperator IN (SELECT * FROM @OPERATORS)
END

---------ИЗВЛЕКАЕМ УСПЕШНЫЕ ВХОДЯЩИЕ ЗВОНКИ------------------------------------------------------
DECLARE @SuccessIncom TABLE(OperId uniqueidentifier, [Count] int, AvgTime int)

INSERT INTO @SuccessIncom
SELECT OperId
    , COUNT(OperId)
    , CAST(AVG(LenTime) as int)
FROM @FilteredEffort
WHERE CallResult = 5
GROUP BY OperId
-----------------------------------------------------------------------------------------

------ИЗВЛЕКАЕМ ПРОПУЩЕННЫЕ ВХОДЯЩИЕ ЗВОНКИ-------------------------------------------------------
DECLARE @FailedIncom TABLE(OperId uniqueidentifier, [Count] int)

INSERT INTO @FailedIncom
SELECT OperId
    , COUNT(OperId)
FROM @FilteredEffort
WHERE CallResult != 5
GROUP BY OperId
-----------------------------------------------------------------------------------------

---------ИЗВЛЕКАЕМ ВСЕ ВХОДЯЩИЕ ЗВОНКИ------------------------------------------------------
DECLARE @AllIncom TABLE(OperId uniqueidentifier, [Count] int)

INSERT INTO @AllIncom
SELECT OperId
    , COUNT(OperId)
FROM @FilteredEffort
GROUP BY OperId
-----------------------------------------------------------------------------------------
------ВЫВОДИМ РЕЗУЛЬТАТ------------------------------------------------------------------
DECLARE @Result TABLE([Name] nvarchar(50), Incoming int, Outgoing int, Failed int, AvgTime nvarchar(20), AvgTime1 int)

INSERT INTO @Result
SELECT OI.[Name]
    , ISNULL(AI.[Count], 0)
    , ISNULL(SI.[Count], 0)
    , ISNULL(FI.[Count], 0)
    , (SELECT [dbo].[GetTimeFromSecond](ISNULL(SI.AvgTime, 0)))
    , ISNULL(SI.AvgTime, 0)
FROM @AllIncom as AI
LEFT JOIN [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo] AS OI
    ON AI.OperId = OI.Id
LEFT JOIN @SuccessIncom AS SI
    ON AI.OperId = SI.OperId
LEFT JOIN @FailedIncom AS FI
    ON AI.OperId = FI.OperId

IF(@TYPE_TIME = 'True')
BEGIN
    SELECT [Name] as 'Менеджер'
        , Incoming as 'Итого Поступило'
        , Outgoing as 'Итого Принято'
        , Failed as 'Итого Пропущено'
        , AvgTime as 'Среднее время ответа'
    FROM @Result
    ORDER BY AvgTime1 DESC
END

IF(@TYPE_TIME = 'False')
BEGIN
    SELECT [Name] as 'Менеджер'
        , Incoming as 'Итого Поступило'
        , Outgoing as 'Итого Принято'
        , Failed as 'Итого Пропущено'
        , AvgTime as 'Среднее время ответа'
    FROM @Result
    ORDER BY AvgTime1
END