Update Dash DAy Calls

DECLARE @TASK UNIQUEIDENTIFIER = '157633C7-DC49-41BF-BE3F-CE3C97F4AAF5'

DECLARE @OPERATORS TABLE (
    Id UNIQUEIDENTIFIER
)

INSERT INTO @OPERATORS
    SELECT
        OperatorId
    FROM [oktell_settings].[dbo].[A_TaskManager_Operators] WITH (NOLOCK)
    WHERE TaskId = @TASK

DECLARE @CurrentDate DATETIME = CAST(CAST(GETDATE() AS DATE) AS DATETIME)

SELECT
    * INTO #EffortConnections
FROM oktell_cc_temp.dbo.A_Cube_CC_EffortConnections WITH (NOLOCK)
WHERE DateStart = @CurrentDate
AND IdOperator IN (SELECT
        *
    FROM @OPERATORS)

SELECT
    * INTO #StatConnections
FROM oktell.dbo.A_Stat_Connections_1x1 WITH (NOLOCK)
WHERE CAST(CAST(TimeStart AS DATE) AS DATETIME) = @CurrentDate

DECLARE @IvrCalls TABLE (
    Id UNIQUEIDENTIFIER
   ,TimeStart TIME
)

INSERT INTO @IvrCalls
    SELECT
        sc.Id
       ,CAST(sc.TimeStart AS TIME)
    FROM #StatConnections sc
    LEFT JOIN #EffortConnections ec
        ON sc.IdChain = ec.IdChain
    WHERE sc.Bstr = 'IVR'
    AND sc.ReasonStart IN (1, 2)
    AND ec.IdTask = @TASK


UPDATE [severstal_utils].[dbo].[Dashbord_DayCalls_VIP]
SET Incoming = (SELECT
            COUNT(DISTINCT IdChain)
        FROM #EffortConnections
        WHERE CAST(TimeStart AS TIME) BETWEEN tbl.TimeStart AND tbl.TimeEnd
        AND IsOutput = 0)
   ,Outcoming = (SELECT
            COUNT(DISTINCT Id)
        FROM #StatConnections
        WHERE CAST(TimeStart AS TIME) BETWEEN tbl.TimeStart AND tbl.TimeEnd
        AND ConnectionType IN (1, 2, 3)
        AND BUserId IN (SELECT
                *
            FROM @OPERATORS))
   ,Lost = (SELECT
            COUNT(IdChain)
        FROM (SELECT DISTINCT
                IdChain
               ,AbonentNumber
               ,MIN(TimeStart) TimeStart
            FROM #EffortConnections
            WHERE CAST(TimeStart AS TIME) BETWEEN tbl.TimeStart AND tbl.TimeEnd
            AND CallResult NOT IN (5, 18, 1)
            AND IdConn IS NOT NULL
            GROUP BY IdChain
                    ,AbonentNumber) t2
        WHERE (SELECT
                COUNT(*)
            FROM #EffortConnections
            WHERE IdChain = t2.IdChain
            AND CallResult IN (5, 18))
        < 1)
   ,IVR = (SELECT
            COUNT(DISTINCT id)
        FROM @IvrCalls
        WHERE TimeStart BETWEEN tbl.TimeStart AND tbl.TimeEnd)
FROM [severstal_utils].[dbo].[Dashbord_DayCalls_VIP] tbl

DROP TABLE #EffortConnections
DROP TABLE #StatConnections