Северсталь отчет 11

DECLARE @DATE_START datetime = '20190626 00:00'
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_cc_temp].[dbo].[A_Cube_CC_Cat_Task]

DECLARE @OPERATORS TABLE (Id uniqueidentifier)

INSERT INTO @operators
SELECT Id
FROM [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo]

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------ОТФИЛЬТРОВЫВАЕМ И СОЕДИНЯЕМ ТАБЛИЦЫ-----------------------------------------------------------
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, SUM(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 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, SUM(DATEDIFF(SECOND, TimeStart, TimeStop))
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] = 10                                           AND OS.IdOperator = IdOperator), 0) as Ready
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 101                                            AND OS.IdOperator = IdOperator), 0) as CallDirector
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 102                                            AND OS.IdOperator = IdOperator), 0) as Consult
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 103                                            AND OS.IdOperator = IdOperator), 0) as Training
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 104                                            AND OS.IdOperator = IdOperator), 0) as Lunch
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 105                                            AND OS.IdOperator = IdOperator), 0) as Break5Minutes
        , 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 ICode = 106                                            AND OS.IdOperator = IdOperator), 0) as Solution
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 107                                            AND OS.IdOperator = IdOperator), 0) as Status1
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 108                                            AND OS.IdOperator = IdOperator), 0) as Status2
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 109                                            AND OS.IdOperator = IdOperator), 0) as Missing
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 110                                            AND OS.IdOperator = IdOperator), 0) as Post
        , 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](SumTotal)) as 'Всего в работе'
    , (SELECT [dbo].[IntToTimeStr](Ready)) as 'Готов'
    , (SELECT [dbo].[IntToTimeStr](CallDirector)) as 'Вызов ку руководителю'
    , (SELECT [dbo].[IntToTimeStr](Consult)) as 'Консультация'
    , (SELECT [dbo].[IntToTimeStr](Training)) as 'Обучение'
    , (SELECT [dbo].[IntToTimeStr](Lunch)) as 'Обед'
    , (SELECT [dbo].[IntToTimeStr](Break5Minutes)) as 'Перерыв 5 минут'
    , (SELECT [dbo].[IntToTimeStr]([Break])) as 'Перерыв'
    , (SELECT [dbo].[IntToTimeStr](Solution)) as 'Решенеие обращения'
    , (SELECT [dbo].[IntToTimeStr](Status1)) as 'Статус1'
    , (SELECT [dbo].[IntToTimeStr](Status2)) as 'Статус2'
    , (SELECT [dbo].[IntToTimeStr](Missing)) as 'Отсутствует'
    , (SELECT [dbo].[IntToTimeStr](Post)) as 'Постобработка'
    , Incoming as 'Входящие'
    , Outgoing as 'Исходящие'
FROM FinalTable as F
LEFT JOIN [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo] as OI
    ON OI.Id = F.IdOperator