Отчет по времени звонков

/****** Скрипт для команды SelectTopNRows из среды SSMS  ******/
--Переменные для фильтра
DECLARE @OPERATOR uniqueidentifier
DECLARE @TASK uniqueidentifier
DECLARE @DATE datetime
DECLARE @TIME_START datetime
DECLARE @TIME_END datetime

--SET @OPERATOR = 'D1F85F26-C9DF-4A32-8287-8A34EA3FC02B'
--SET @TASK = '2257D551-2245-47B0-8D3B-BDB1919A84BD'
--SET @DATE = '2019-01-01 00:00:00.000'

SET @OPERATOR = '23BDA663-D080-4D57-ADAD-9CD894068E89'
SET @TASK = 'E49E7F5C-2ACD-4869-90B2-6CE4E9291DE9'
SET @DATE = '2019-06-22 00:00:00.000'

SET @TIME_START = '1900-01-01 01:00:00.000'
SET @TIME_END = '1900-01-01 20:00:00.000'

DECLARE @FilteredTable TABLE(IdTask uniqueidentifier, IdOperator uniqueidentifier, [State] int, DateStart datetime, TimeStart datetime, DateTimeStop datetime)
DECLARE @FilteredTable3 TABLE(IdTask uniqueidentifier, IdOperator uniqueidentifier, [State] int, DateStart datetime, TimeStart datetime, DateTimeStop datetime)
DECLARE @FilteredTable2 TABLE(IdTask uniqueidentifier, IdOperator uniqueidentifier, [State] int, DateStart datetime, TimeStart datetime, DateTimeStop datetime, Number int)
DECLARE @SecondTable TABLE(IdTask uniqueidentifier, IdOperator uniqueidentifier, [State] int, DateStart datetime, TimeStart datetime, DateTimeStop datetime, Interval nvarchar(20), Number int)
DECLARE @SecondTable2 TABLE(IdTask uniqueidentifier, IdOperator uniqueidentifier, [State] int, DateStart datetime, TimeStart datetime, DateTimeStop datetime, Interval nvarchar(20), Number int)
DECLARE @Intervals_And_Length TABLE(IdTask uniqueidentifier, IdOperator uniqueidentifier, [State] int, DateStart datetime, TimeStart datetime, DateTimeStop datetime, Interval nvarchar(20), LenTime float)

DECLARE @StartTable TABLE(IdOperator uniqueidentifier, DateStart datetime, TimeStart datetime, Task nvarchar(200), Operator nvarchar(200), [State] int, Date datetime, Interval nvarchar(20), LenTime float)

DECLARE @TimeIntervals TABLE(Interval nvarchar(20), TimeStart time, TimeEnd time)

DECLARE @TestTable TABLE(Interval nvarchar(20))

--отфильтровать таблицу
INSERT INTO @FilteredTable
SELECT IdTask
    ,IdOperator
    ,[State]
    ,DateStart
    ,TimeStart
    ,DateTimeStop
FROM [oktell_cc_temp].[dbo].[A_Cube_CC_OperatorStates] with (nolock)
WHERE IdOperator = @OPERATOR AND DateStart = @DATE AND CAST(DateTimeStop as date) = @DATE
    AND TimeStart >= @TIME_START AND CAST(DateTimeStop as time) <= CAST(@TIME_END as time)

INSERT INTO @FilteredTable3
SELECT *
FROM @FilteredTable
WHERE IdTask = @TASK OR [State] = 10 OR [State] = 9

--пронумеровать колонки
INSERT INTO @FilteredTable2
SELECT *
    , row_number() over(ORDER BY [@FilteredTable3].DateStart) as Number
FROM @FilteredTable3

DECLARE @Count int
SELECT @Count = COUNT(*) from @FilteredTable2

DECLARE @Iterator int
DECLARE @IdTask uniqueidentifier
DECLARE @IdOperator uniqueidentifier
DECLARE @State int
DECLARE @DateStart datetime
DECLARE @TimeStart datetime
DECLARE @DateTimeStop datetime

SET @Iterator = 1

--применяем функцию для каждой строки таблицы, которая вставляет интервалы
WHILE @Iterator <= @Count
    BEGIN
        SELECT
            @IdTask = IdTask
            ,@IdOperator = IdOperator
            ,@State = [State]
            ,@DateStart = DateStart
            ,@TimeStart = TimeStart
            ,@DateTimeStop = DateTimeStop
        FROM @FilteredTable2
        WHERE Number = @Iterator

        INSERT INTO @SecondTable SELECT *,0 FROM [oktell_cc_temp].[dbo].[AddTimeIntervals] (
            @IdTask
            ,@IdOperator
            ,@State
            ,@DateStart
            ,@TimeStart
            ,@DateTimeStop
        )

        SET @Iterator = @Iterator + 1
    END;

INSERT INTO @SecondTable2
SELECT
    IdTask
    ,IdOperator
    ,[State]
    ,DateStart
    ,TimeStart
    ,DateTimeStop
    ,Interval
    ,row_number() over(ORDER BY [@SecondTable].DateStart) as Number
FROM @SecondTable

SET @Iterator = 1
DECLARE @Interval nvarchar(20)

WHILE @Iterator <= @Count
    BEGIN
        SELECT
            @IdTask = IdTask
            ,@IdOperator = IdOperator
            ,@State = [State]
            ,@DateStart = DateStart
            ,@TimeStart = TimeStart
            ,@DateTimeStop = DateTimeStop
            ,@Interval = Interval
        FROM @SecondTable2
        WHERE Number = @Iterator

        INSERT INTO @Intervals_And_Length SELECT * FROM [oktell_cc_temp].[dbo].[SetLenForTimeIntervals] (
            @IdTask
            ,@IdOperator
            ,@State
            ,@DateStart
            ,@TimeStart
            ,@DateTimeStop
            ,@Interval
        )

        SET @Iterator = @Iterator + 1
    END;

--заполняем StartTable нужными значениями и интервалами и работаем уже с ней
INSERT INTO @StartTable
SELECT IdOperator, DateStart, TimeStart
    ,[oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task].[Name] AS Task
    ,[oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo].[Name] AS Operator
    ,[State]
    ,DateStart
    ,Interval
    ,LenTime
FROM @Intervals_And_Length
LEFT JOIN
[oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task]
    ON [@Intervals_And_Length].IdTask = [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task].Id
LEFT JOIN
[oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo]
    ON [@Intervals_And_Length].IdOperator = [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo].Id

DECLARE @TimeTable TABLE
    (
        IdOperator uniqueidentifier, DateStart datetime, TimeStart datetime,
        Task nvarchar(200), Operator nvarchar(200), [State] int, Date datetime, Interval nvarchar(20), LenTime float,
        Time_PostProcessing float, Time_Talking float, Time_Ready float, Time_Break float
    )

INSERT INTO @TimeTable
SELECT *
    ,IIF([State] = 7, LenTime / 60, 0)
    ,IIF([State] = 4 OR [State] = 5 OR [State] = 6 OR [State] = 8, LenTime / 60, 0)
    ,IIF([State] = 10, LenTime / 60, 0)
    ,IIF([State] = 9, LenTime / 60, 0)
FROM @StartTable

DECLARE @TimeTableICode TABLE
    (
        IdOperator uniqueidentifier, DateStart datetime, TimeStart datetime,
        Task nvarchar(200), Operator nvarchar(200), [State] int, Date datetime, Interval nvarchar(20), LenTime float,
        Time_PostProcessing float, Time_Talking float, Time_Ready float, Time_Break float, ICode int
    )

DECLARE @ICodes TABLE(OperatorId uniqueidentifier, TimeChange time, ICode int)

INSERT INTO @ICodes
SELECT UserId
    ,TimeChange
    ,ICode
FROM [oktell].[dbo].[A_UserStateHistory]
WHERE UserId = @OPERATOR AND CAST(TimeChange as date) = @DATE AND ICode != -1
        AND CAST(TimeChange as time) >= CAST(@TIME_START as time) AND CAST(TimeChange as time) <= CAST(@TIME_END as time)

--select * from @ICodes

INSERT INTO @TimeTableICode
SELECT *
    ,(
        SELECT ICode FROM @ICodes
        WHERE (ROUND(CAST(TimeStart as float), 2) = ROUND(CAST(CAST(CAST(TimeChange as time) as datetime) as float), 2))
    ) as ICode
FROM @TimeTable

DECLARE @TimeTableBreaks TABLE
    (
        IdOperator uniqueidentifier, DateStart datetime, TimeStart datetime,
        Task nvarchar(200), Operator nvarchar(200), [State] int, Date datetime, Interval nvarchar(20), LenTime float,
        Time_PostProcessing float, Time_Talking float, Time_Ready float, Time_Break float, ICode int,
        Time_Break_CallBack float, Time_Break_Activation float, Time_Break_Training float, Time_Break_Plan float
    )

INSERT INTO @TimeTableBreaks
SELECT *
    , IIF(ICode = 7778, LenTime / 60, 0)
    , IIF(ICode = 7775, LenTime / 60, 0)
    , IIF(ICode = 7776, LenTime / 60, 0)
    , IIF(ICode = 7774, LenTime / 60, 0)
FROM @TimeTableICode

DECLARE @GroupedTable TABLE
    (
        Interval nvarchar(20),
        Time_PostProcessing float, Time_Talking float, Time_Ready float,
        Time_Break_CallBack float, Time_Break_Activation float, Time_Break_Training float, Time_Break_Plan float
    )

INSERT INTO @GroupedTable
SELECT Interval
    ,ROUND(SUM(Time_PostProcessing), 2)
    ,ROUND(SUM(Time_Talking), 2)
    ,ROUND(SUM(Time_Ready), 2)
    ,ROUND(SUM(Time_Break_CallBack), 2)
    ,ROUND(SUM(Time_Break_Activation), 2)
    ,ROUND(SUM(Time_Break_Training), 2)
    ,ROUND(SUM(Time_Break_Plan), 2)
FROM @TimeTableBreaks
GROUP BY Interval

DECLARE @FinalTable TABLE
    (
        Interval nvarchar(20),
        Time_PostProcessing float, Time_Talking float, Time_Ready float,
        Time_Break_CallBack float, Time_Break_Activation float, Time_Break_Training float, Time_Break_Plan float,
        Occupancy nchar(10), UtilizationStandart nchar(10), Utilization nchar(10)
    )

INSERT INTO @FinalTable
SELECT Interval
    ,ROUND(Time_PostProcessing, 2)
    ,ROUND(Time_Talking, 2)
    ,ROUND(Time_Ready, 2)
    ,ROUND(Time_Break_CallBack, 2)
    ,ROUND(Time_Break_Activation, 2)
    ,ROUND(Time_Break_Training, 2)
    ,ROUND(Time_Break_Plan, 2)
    ,IIF
    (
        Time_PostProcessing + Time_Talking + Time_Ready + Time_Break_CallBack + Time_Break_Activation != 0
        ,CONCAT(ROUND(((Time_PostProcessing + Time_Talking + Time_Break_CallBack + Time_Break_Activation)
            / (Time_PostProcessing + Time_Talking + Time_Ready + Time_Break_CallBack + Time_Break_Activation)) * 100, 2), '%')
        ,'0'
    )
    ,IIF
    (
        Time_PostProcessing + Time_Talking + Time_Ready + Time_Break_CallBack + Time_Break_Activation + Time_Break_Training + Time_Break_Plan != 0
        ,CONCAT(ROUND(((Time_PostProcessing + Time_Talking + Time_Ready + Time_Break_CallBack + Time_Break_Activation + Time_Break_Training)
            / (Time_PostProcessing + Time_Talking + Time_Ready + Time_Break_CallBack + Time_Break_Activation + Time_Break_Training + Time_Break_Plan)) * 100, 2), '%')
        ,'0'
    )
    ,IIF
    (
        Time_PostProcessing + Time_Talking + Time_Ready + Time_Break_CallBack + Time_Break_Activation + Time_Break_Training + Time_Break_Plan != 0
        ,CONCAT(ROUND(((Time_PostProcessing + Time_Talking + Time_Ready + Time_Break_CallBack + Time_Break_Activation)
            / (Time_PostProcessing + Time_Talking + Time_Ready + Time_Break_CallBack + Time_Break_Activation + Time_Break_Training + Time_Break_Plan)) * 100, 2), '%')
        ,'0'
    )
FROM @GroupedTable

DECLARE  @sum_Time_PostProcessing float
DECLARE  @sum_Time_Talking float
DECLARE  @sum_Time_Ready float
DECLARE  @sum_Time_Break_CallBack float
DECLARE  @sum_Time_Break_Activation float
DECLARE  @sum_Time_Break_Training float
DECLARE  @sum_Time_Break_Plan float

DECLARE @Occupation float
DECLARE @UtilizationStandart float
DECLARE @Utilization float

SET @sum_Time_PostProcessing = (SELECT SUM(Time_PostProcessing) FROM @FinalTable)
SET @sum_Time_Talking = (SELECT SUM(Time_Talking) FROM @FinalTable)
SET @sum_Time_Ready = (SELECT SUM(Time_Ready) FROM @FinalTable)
SET @sum_Time_Break_CallBack = (SELECT SUM(Time_Break_CallBack) FROM @FinalTable)
SET @sum_Time_Break_Activation = (SELECT SUM(Time_Break_Activation) FROM @FinalTable)
SET @sum_Time_Break_Training = (SELECT SUM(Time_Break_Training) FROM @FinalTable)
SET @sum_Time_Break_Plan = (SELECT SUM(Time_Break_Plan) FROM @FinalTable)

SET @Occupation = IIF
    (
        @sum_Time_PostProcessing + @sum_Time_Talking + @sum_Time_Ready + @sum_Time_Break_CallBack + @sum_Time_Break_Activation != 0
        ,(@sum_Time_PostProcessing + @sum_Time_Talking + @sum_Time_Break_CallBack + @sum_Time_Break_Activation)
            / (@sum_Time_PostProcessing + @sum_Time_Talking + @sum_Time_Ready + @sum_Time_Break_CallBack + @sum_Time_Break_Activation) * 100
        ,0
    )
SET @UtilizationStandart = IIF
    (
        @sum_Time_PostProcessing + @sum_Time_Talking + @sum_Time_Ready + @sum_Time_Break_CallBack + @sum_Time_Break_Activation + @sum_Time_Break_Training + @sum_Time_Break_Plan != 0
        ,(@sum_Time_PostProcessing + @sum_Time_Talking + @sum_Time_Ready + @sum_Time_Break_CallBack + @sum_Time_Break_Activation + @sum_Time_Break_Training)
            / (@sum_Time_PostProcessing + @sum_Time_Talking + @sum_Time_Ready + @sum_Time_Break_CallBack + @sum_Time_Break_Activation + @sum_Time_Break_Training + @sum_Time_Break_Plan) * 100
        ,0
    )
SET @Utilization = IIF
    (
        @sum_Time_PostProcessing + @sum_Time_Talking + @sum_Time_Ready + @sum_Time_Break_CallBack + @sum_Time_Break_Activation + @sum_Time_Break_Training + @sum_Time_Break_Plan != 0
        ,((@sum_Time_PostProcessing + @sum_Time_Talking + @sum_Time_Ready + @sum_Time_Break_CallBack + @sum_Time_Break_Activation)
            / (@sum_Time_PostProcessing + @sum_Time_Talking + @sum_Time_Ready + @sum_Time_Break_CallBack + @sum_Time_Break_Activation + @sum_Time_Break_Training + @sum_Time_Break_Plan)) * 100
        ,0
    )

INSERT INTO @FinalTable
VALUES
(
  'Итого'
  ,@sum_Time_PostProcessing
  ,@sum_Time_Talking
  ,@sum_Time_Ready
  ,@sum_Time_Break_CallBack
  ,@sum_Time_Break_Activation
  ,@sum_Time_Break_Training
  ,@sum_Time_Break_Plan
  ,CONCAT(ROUND(@Occupation, 2), '%')
  ,CONCAT(ROUND(@UtilizationStandart, 2), '%')
  ,CONCAT(ROUND(@Utilization, 2), '%')
)

select * from @FinalTable