temp

DECLARE @DATE_START DATETIME = '20190805 00:00'
DECLARE @DATE_END DATETIME = '20190811 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 VALUES ('6c739bd3-4348-4f96-9702-1cb638385d6a')

--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
   ,IdEffort UNIQUEIDENTIFIER
)

IF (@TYPE_REPORT = 1)
BEGIN
    INSERT INTO @FilteredEffort
        SELECT
            IdOperator
           ,LenTime
           ,CallResult
           ,IdEffort
        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 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
           ,IdEffort
        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 TimeStart BETWEEN @TIME_START AND @TIME_END
        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(DISTINCT IdEffort)
       ,CAST(AVG(LenTime) AS INT)
    FROM @FilteredEffort
    WHERE CallResult IN (5, 18)
    GROUP BY OperId
-----------------------------------------------------------------------------------------

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

INSERT INTO @FailedIncom
    SELECT
        OperId
       ,COUNT(DISTINCT IdEffort)
    FROM @FilteredEffort
    WHERE CallResult NOT IN (5, 18)
    GROUP BY OperId
-----------------------------------------------------------------------------------------

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

INSERT INTO @AllIncom
    SELECT
        OperId
       ,COUNT(DISTINCT IdEffort)
    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