Геттаксе Рабочее время оперов V4

DECLARE @DATE_END datetime = '20190727 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_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo]
    WHERE [Name] LIKE '%kelly%'*/

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------ОТФИЛЬТРОВЫВАЕМ И СОЕДИНЯЕМ ТАБЛИЦЫ-----------------------------------------------------------
DECLARE @OperStates TABLE
    (
        IdTask uniqueidentifier
        , IdOperator uniqueidentifier
        , TimeStart datetime
        , [State] int
        , ICode int
        , LenTime int
        , UserState int
    );

WITH FilteredTable AS
(
    SELECT IdTask
        , IdOperator
        , DateTimeStart
        , [State]
        , ICode
        , LenTime
    FROM [oktell_cc_temp].[dbo].[A_Cube_CC_OperatorStates] WITH (NOLOCK)
    WHERE DateTimeStart BETWEEN @DATE_START AND @DATE_END
        AND TimeStart BETWEEN @TIME_START AND @TIME_END
        AND IdOperator IN (SELECT * FROM @OPERATORS)
)

INSERT INTO @OperStates
SELECT IdTask
    , IdOperator
    , DateTimeStart
    , OS.[State]
    , OS.ICode
    , CAST(LenTime as int)
    , US.[State]
FROM FilteredTable as OS
LEFT JOIN [oktell].[dbo].[A_UserStateHistory] as US
    ON US.UserId = OS.IdOperator AND OS.DateTimeStart = US.TimeChange

-------------------------------------------------------------------------------------------------
---------СЧИТАЕМ ВХОДЯЩИЕ--------------------------------------------------------------
DECLARE @FilteredEffort TABLE(OperId uniqueidentifier, [Count] int)

INSERT INTO @FilteredEffort
SELECT IdOperator, COUNT(IdOperator)
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 BLineNum <> 'IVR' AND IsOutput = 0
    AND IdTask IN (SELECT * FROM @TASKS)
    AND IdOperator IN (SELECT * FROM @OPERATORS)
    and IsRecorded = 1
GROUP BY IdOperator
----------------------------------------------------------------------------------------

---------СЧИТАЕМ ИСХОДЯЩИЕ--------------------------------------------------------------
DECLARE @FilteredStat TABLE(OperId uniqueidentifier, [Count] int)

INSERT INTO @FilteredStat
SELECT AUserId, COUNT(AUserId)
FROM [oktell].[dbo].[A_Stat_Connections_1x1]
WHERE TimeStart BETWEEN @DATE_START AND @DATE_END
    AND CAST(TimeStart as time) BETWEEN CAST(@TIME_START as time) AND CAST(@TIME_END as time)
    AND ConnectionType = 1
    AND AUserId IN (SELECT * FROM @OPERATORS)
GROUP BY AUserId;
-----------------------------------------------------------------------------------------

------ПОДСЧИТЫВАЕМ ВСЕ ЗНАЧЕНИЯ----------------------------------------------------------
WITH FinalTable AS
(
    SELECT IdOperator
        , SUM(LenTime) as SumTotal
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] IN (1, 2, 3)                                   AND OS.IdOperator = IdOperator AND IdTask IN     (SELECT * FROM @TASKS)), 0) as BeforeTask
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 6                                            AND OS.IdOperator = IdOperator AND IdTask IN     (SELECT * FROM @TASKS)), 0) as Tasks
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] IN (1, 2, 3, 4, 6, 7)                          AND OS.IdOperator = IdOperator AND IdTask NOT IN (SELECT * FROM @TASKS)), 0) as OtherTasks
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 10                                           AND OS.IdOperator = IdOperator), 0) as Ready
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] IN (5, 8)                                      AND OS.IdOperator = IdOperator), 0) as Talk
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ([State] = 9 AND UserState IS NULL OR UserState = 3)   AND OS.IdOperator = IdOperator), 0) as [Break]
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 1                              AND OS.IdOperator = IdOperator), 0) as Dispatchs
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 2                              AND OS.IdOperator = IdOperator), 0) as Tickets
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 3                              AND OS.IdOperator = IdOperator), 0) as Chat
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 4                              AND OS.IdOperator = IdOperator), 0) as Other
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 6                              AND OS.IdOperator = IdOperator), 0) as Refunds
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 7                              AND OS.IdOperator = IdOperator), 0) as Training
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 8                              AND OS.IdOperator = IdOperator), 0) as Tech
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 7                                            AND OS.IdOperator = IdOperator), 0) as Post
        , ISNULL((SELECT AVG(LenTime) FROM @OperStates WHERE [State] = 7                                            AND OS.IdOperator = IdOperator), 0) as AvgPost
        , ISNULL((SELECT TOP 1 [Count] FROM @FilteredEffort WHERE OperId = OS.IdOperator), 0) as Incoming
        , ISNULL((SELECT TOP 1 [Count] FROM @FilteredStat WHERE OperId = OS.IdOperator), 0) as Outgoing
    FROM @OperStates AS OS
    GROUP BY (IdOperator)
)
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT OI.Name
    , (SELECT [dbo].[IntToTimeStr](Tasks)) as 'Разговоры по задаче'
    , (SELECT [dbo].[IntToTimeStr](BeforeTask)) as 'Время до ответа'
    , (SELECT [dbo].[IntToTimeStr](Post)) as 'ACW'
    , (SELECT [dbo].[IntToTimeStr](OtherTasks)) as 'Разговоры по др. задачам'
    , (SELECT [dbo].[IntToTimeStr](Talk)) as 'Разговоры не по задаче'
    , (SELECT [dbo].[IntToTimeStr](Ready)) as 'Готов'
    , (SELECT [dbo].[IntToTimeStr]([Break])) as 'Перерыв'
    , (SELECT [dbo].[IntToTimeStr](Dispatchs)) as 'Отправка'
    , (SELECT [dbo].[IntToTimeStr](Tickets)) as 'Тикеты'
    , (SELECT [dbo].[IntToTimeStr](Chat)) as 'Чат'
    , (SELECT [dbo].[IntToTimeStr](Other)) as 'Другое'
    , (SELECT [dbo].[IntToTimeStr](Refunds)) as 'Возврат'
    , (SELECT [dbo].[IntToTimeStr](Training)) as 'Обучение'
    , (SELECT [dbo].[IntToTimeStr](Tech)) as 'Технический вопрос'
    , (SELECT [dbo].[IntToTimeStr](SumTotal)) as 'Всего'
    , Incoming as 'Входящие'
    , Outgoing as 'Исходящие'
    , (SELECT [dbo].[IntToTimeStr](AvgPost)) as 'Среднее время постобработки'
FROM FinalTable as F
LEFT JOIN [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo] as OI
    ON OI.Id = F.IdOperator