OneTwoTrip по локали мое

declare @d1 datetime = '2016-11-01 00:00:00'
declare @d2 datetime = '2016-11-24 00:00:00'

declare @task table (id uniqueidentifier)
insert into @task
--values( '8B199244-CA64-400E-9734-40AE42B63261'),('0C9B090C-3663-4E8A-92A7-82714D6CB161')
select ID    FROM [oktell_settings].[dbo].[A_TaskManager_Tasks] where ID not in ('9C16C13C-E089-4225-AB3F-78F1D53E5FD7','8B199244-CA64-400E-9734-40AE42B63261')
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------ФИЛЬТРУЕМ EffortConnections----------------------------------------------------------------
DECLARE @EffortConnections TABLE (IdChain uniqueidentifier, DateStart date, IdEffort uniqueidentifier)

INSERT INTO @EffortConnections
SELECT IdChain
    , DateStart
    , IdEffort
FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
WHERE DateStart BETWEEN @d1 AND @d2
    AND IdTask IN (SELECT * FROM @task)
ORDER BY DateStart
-----------------------------------------------------------------------------------------------------

---------ФИЛЬТРУЕМ StatConnections-------------------------------------------------------------------
DECLARE @StatConnections TABLE
    (
        IdChain uniqueidentifier, DateStart date, ConnectionType int
        , IdPrev uniqueidentifier, IdNext uniqueidentifier
        , TimeStart datetime, TimeAnswer datetime, TimeStop datetime
        , ReasonStop int
    )

INSERT INTO @StatConnections
SELECT SC.IdChain
    , CAST(TimeStart as date)
    , ConnectionType
    , IdPrev
    , IdNext
    , TimeStart
    , TimeAnswer
    , TimeStop
    , ReasonStop
FROM [oktell].[dbo].[A_Stat_Connections_1x1] as SC
JOIN @EffortConnections as EC
    ON SC.IdChain = EC.IdChain
WHERE TimeStart BETWEEN @d1 AND @d2
ORDER BY TimeStart
------------------------------------------------------------------------------------------------------

-------СЧИТАЕМ ВХОДЯЩИЕ ЗВОНКИ------------------------------------------------------------------------
DECLARE @InputCalls TABLE (TimeStart date, [Count] int)

INSERT INTO @InputCalls
SELECT DateStart
    , COUNT(DISTINCT IdChain)
FROM @EffortConnections
GROUP BY DateStart

--SELECT * FROM @InputCalls
------------------------------------------------------------------------------------------------------

-------СЧИТАЕМ ПРИНЯТЫЕ ЗВОНКИ------------------------------------------------------------------------
DECLARE @AcceptedCalls TABLE (TimeStart date, [Count] int)

INSERT INTO @AcceptedCalls
SELECT DateStart
    , COUNT(DISTINCT IdChain)
FROM @StatConnections
WHERE ConnectionType = 4
    AND IdPrev IS NULL
    AND IdNext IS NOT NULL
GROUP BY DateStart

--SELECT * FROM @AcceptedCalls
-------------------------------------------------------------------------------------------------------

-------СЧИТАЕМ ПОТЕРЯНЫЕ ЗВОНКИ------------------------------------------------------------------------
DECLARE @LostCalls TABLE (TimeStart date, [Count] int)

INSERT INTO @LostCalls
SELECT DateStart
    , COUNT(DISTINCT IdChain)
FROM @StatConnections
WHERE ConnectionType = 4
    AND IdPrev IS NULL
    AND IdNext IS NULL
GROUP BY DateStart

--SELECT * FROM @LostCalls
-------------------------------------------------------------------------------------------------------

------СРЕДНЕЕ ВРЕМЯ ASA-----------------------------------------------------------------------------
DECLARE @AvgIvr TABLE (TimeStart date, LenTime int)

INSERT INTO @AvgIvr
SELECT DateStart
    , AVG(DATEDIFF(SECOND, TimeStart, TimeStop))
FROM @StatConnections
WHERE ConnectionType = 4
GROUP BY DateStart

--SELECT * FROM @AvgIvr
-------------------------------------------------------------------------------------------------------

------СРЕДНЕЕ ВРЕМЯ AHT-----------------------------------------------------------------------------
DECLARE @AvgTalk TABLE (TimeStart date, LenTime int)

INSERT INTO @AvgTalk
SELECT DateStart
    , AVG(DATEDIFF(SECOND, TimeStart, TimeStop))
FROM @StatConnections
WHERE ConnectionType = 5
GROUP BY DateStart

--SELECT * FROM @AvgTalk
-------------------------------------------------------------------------------------------------------

------СРЕДНЕЕ ВРЕМЯ ATT--------------------------------------------------------------------------------
DECLARE @AvgAnswer TABLE (TimeStart date, LenTime int)

INSERT INTO @AvgAnswer
SELECT DateStart
    , AVG(DATEDIFF(SECOND, TimeStart, TimeAnswer))
FROM @StatConnections
WHERE ConnectionType = 5
GROUP BY DateStart

--SELECT * FROM @AvgAnswer
-------------------------------------------------------------------------------------------------------

------СРЕДНЕЕ ВРЕМЯ ПОСТВЫЗОВА-------------------------------------------------------------------------
DECLARE @AvgPost TABLE (TimeStart date, LenTime int)

INSERT INTO @AvgPost
SELECT OS.DateStart
     , AVG(LenTime)
FROM [oktell_cc_temp].[dbo].[A_Cube_CC_OperatorStates] as OS
JOIN @EffortConnections as EC
    ON OS.IdEffort = EC.IdEffort
JOIN @StatConnections as SC
    ON EC.IdChain = SC.IdChain
WHERE OS.DateStart BETWEEN @d1 AND @d2
    AND IdTask IN (SELECT * FROM @task)
    AND [State] = 7
GROUP BY OS.DateStart
ORDER BY OS.DateStart

--SELECT * FROM @AvgPost
-------------------------------------------------------------------------------------------------------

------СРЕДНЕЕ ВРЕМЯ УДЕРЖАНИЯ-------------------------------------------------------------------------
DECLARE @AvgHold TABLE (TimeStart date, LenTime int)

INSERT INTO @AvgHold
SELECT DateStart
    , AVG(DATEDIFF(SECOND, TimeStart, TimeStop))
FROM @StatConnections
WHERE ReasonStop = 6
GROUP BY DateStart

--SELECT * FROM @AvgHold
-------------------------------------------------------------------------------------------------------

------SLA----------------------------------------------------------------------------------------------
DECLARE @SLA TABLE (TimeStart date, [Count] int)

INSERT INTO @SLA
SELECT DateStart
    , COUNT(DateStart)
FROM @StatConnections
WHERE ConnectionType = 5
    AND DATEDIFF(SECOND, TimeStart, TimeAnswer) > 20
GROUP BY DateStart

--SELECT * FROM @SLA
-------------------------------------------------------------------------------------------------------

SELECT IC.TimeStart AS 'Дата'
    , ISNULL(IC.[Count], 0) AS 'Входящие вызовы'
    , ISNULL(AC.[Count], 0) AS 'Принятые вызовы'
    , ISNULL(LC.[Count], 0) AS 'Потеряные вызовы'
    , ISNULL((SELECT [dbo].[GetPersent](AC.[Count], IC.[Count])), '0%') AS '% Принятых вызовов'
    , ISNULL((SELECT [dbo].[GetPersent](LC.[Count], IC.[Count])), '0%') AS '% Потеряных вызовов'
    , ISNULL(SLA.[Count], 0) AS 'Больше 20'
    , ISNULL((SELECT [dbo].[GetPersent](ISNULL(IC.[Count], 0) - ISNULL(SLA.[Count], 0), IC.[Count])), '0%') AS 'SLA 80/20'
    , (SELECT [dbo].[GetTimeFromSecond](ISNULL(AI.LenTime, 0))) AS 'Среднее время ответа ASA'
    , (SELECT [dbo].[GetTimeFromSecond](ISNULL(AT.LenTime, 0))) AS 'ССреднее время ответа AHT'
    , (SELECT [dbo].[GetTimeFromSecond](ISNULL(AA.LenTime, 0))) AS 'Среднее время ATT'
    , (SELECT [dbo].[GetTimeFromSecond](ISNULL(AP.LenTime, 0))) AS 'Среднее время поствызова'
    , (SELECT [dbo].[GetTimeFromSecond](ISNULL(AH.LenTime, 0))) AS 'Среднее время удержания'
FROM @InputCalls AS IC
LEFT JOIN @AcceptedCalls AS AC
    ON IC.TimeStart = AC.TimeStart
LEFT JOIN @LostCalls AS LC
    ON IC.TimeStart = LC.TimeStart
LEFT JOIN @SLA AS SLA
    ON IC.TimeStart = SLA.TimeStart
LEFT JOIN @AvgIvr AS AI
    ON IC.TimeStart = AI.TimeStart
LEFT JOIN @AvgTalk AS AT
    ON IC.TimeStart = AT.TimeStart
LEFT JOIN @AvgAnswer AS AA
    ON IC.TimeStart = AA.TimeStart
LEFT JOIN @AvgPost AS AP
    ON IC.TimeStart = AP.TimeStart
LEFT JOIN @AvgHold AS AH
    ON IC.TimeStart = AH.TimeStart