КЦ ФЛ по операторам

DECLARE @DT date = GETDATE()
DECLARE @TASKS  TABLE(Id uniqueidentifier)

INSERT INTO @TASKS
SELECT '9A92A716-D5DB-48F6-8418-DCFF8392018A' --Физ лица перезвоны
INSERT INTO @TASKS
SELECT 'E984DCD7-A62D-4D2D-8A86-416D3FD52C9B' --Физ лица входящая
INSERT INTO @TASKS
SELECT 'CE73BD66-76D4-4999-BEF2-EEFE8A04961E' --Физ лица исходящая


DECLARE @OPERATORS TABLE (id uniqueidentifier)
INSERT INTO @OPERATORS
SELECT DISTINCT Id
FROM oktell.dbo.A_users WITH(NOLOCK)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @temp TABLE (ideffort uniqueidentifier,idchain uniqueidentifier,idoperator uniqueidentifier, suc int, unsuc int, datetimestart datetime, lenTime float)

DECLARE @EffortConnections TABLE (IdEffort uniqueidentifier, IdOperator uniqueidentifier, CallResult int)

INSERT INTO @EffortConnections
SELECT IdEffort, IdOperator, CallResult
FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
WHERE CAST(DateTimeStart as date) = @DT
    AND IdTask IN (SELECT * FROM @TASKS)
    AND IdOperator IN (SELECT * FROM @OPERATORS)
    AND IdChain IS NOT NULL
    AND IsOutput = 0

DECLARE @Accepted TABLE (IdOperator uniqueidentifier, [Count] int)

INSERT INTO @Accepted
SELECT IdOperator
    , COUNT(DISTINCT IdEffort)
FROM @EffortConnections
WHERE CallResult = 5
GROUP BY IdOperator

DECLARE @Losted TABLE (IdOperator uniqueidentifier, [Count] int)

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

-------ПОИСК СТАТУСОВ-----------------------------------------------------------------------------
DECLARE @Statuses TABLE (IdOperator uniqueidentifier, [Status] int, TimeChange datetime)

INSERT INTO @Statuses
SELECT UserId
    , [State]
    , TimeChange
FROM [oktell].[dbo].[A_UserStateHistory]
WHERE CAST(TimeChange as date) = @DT
    AND UserId IN (SELECT * FROM @OPERATORS)
GROUP BY UserId, [State], TimeChange, ICode

DECLARE @Stat1 TABLE (IdOperator uniqueidentifier, [Status] int, TimeChange datetime)

INSERT INTO @Stat1
SELECT IdOperator
    , [Status]
    , TimeChange
FROM @Statuses
ORDER BY IdOperator, TimeChange DESC

DECLARE @OpersStatuses TABLE (IdOper uniqueidentifier, [State] nvarchar(20));

WITH Stat2 AS
(
    SELECT IdOperator
        , MAX(TimeChange) as TimeChange
    FROM @Statuses
    GROUP BY IdOperator
)

INSERT INTO @OpersStatuses
SELECT S2.IdOperator
    , CASE
        WHEN S1.[Status] = 0 THEN 'Отключен'
        WHEN S1.[Status] = 1 THEN 'Готов'
        WHEN S1.[Status] = 2 THEN 'Перерыв'
        WHEN S1.[Status] = 3 THEN 'Нет на месте'
        WHEN S1.[Status] = 5 THEN 'Занят'
        WHEN S1.[Status] = 6 THEN 'Зарезервирован'
        WHEN S1.[Status] = 7 THEN 'Без Телефона'
        ELSE 'Прочее'
    END
FROM Stat2 as S2
LEFT JOIN @Stat1 as S1
    ON S2.TimeChange = S1.TimeChange
----------------------------------------------------------------------------------------
DECLARE @Result TABLE([Name] nvarchar(50), [State] nvarchar(20), Accepted int, Losted int)

INSERT INTO @Result
SELECT U.Name
    , OS.[State]
    , ISNULL(A.[Count], 0) as Accepted
    , ISNULL(L.[Count], 0) as Losted
FROM @OpersStatuses as OS
LEFT JOIN oktell.dbo.A_users as U
    ON OS.IdOper = U.ID
LEFT JOIN @Accepted as A
    ON OS.IdOper = A.IdOperator
LEFT JOIN @Losted as L
    ON OS.IdOper = L.IdOperator

DECLARE @acceptedAll int = (SELECT SUM(Accepted) FROM @Result)
DECLARE @lostedAll int = (SELECT SUM(Losted) FROM @Result)

INSERT INTO @Result VALUES(' Итого', NULL, @acceptedAll, @lostedAll)

SELECT
    [Name] as 'Оператор'
    , [State] as 'Состояние'
    , Accepted as 'Успешных'
    , Losted as 'Пропущенных'
FROM @Result
WHERE Accepted != 0 OR Losted != 0
ORDER BY [Name]