Звонков В2В по отделам

-----------------
DECLARE @d1 DATETIME =  '20190731 00:00'
DECLARE @d2 DATETIME =  '20190731 23:59'
DECLARE @t1 DATETIME =  '19000101 00:00'
DECLARE @t2 DATETIME =  '19000101 23:59'

DECLARE @gr TABLE(ID uniqueidentifier)

INSERT INTO @gr
SELECT ID FROM [oktell].[dbo].[A_Groups]
-----------------------------------------------------------------------------------------------------------------------------

-------ФИЛЬТРУЕМ [oktell].[dbo].[A_Stat_Connections_1x1]---------------------------------------------------------------------
DECLARE @StatConnections TABLE(IdChain uniqueidentifier, GroupId uniqueidentifier, ConnectionType int)

INSERT INTO @StatConnections
SELECT IdChain, AG.ID, ConnectionType
FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS SC WITH (NOLOCK)
LEFT JOIN [oktell_settings].[dbo].[A_Users] AS AU
    ON SC.BUserId = AU.ID
LEFT JOIN [oktell].[dbo].[A_Groups] AS AG
    ON AU.ParentGroupID = AG.ID
WHERE TimeStart BETWEEN @d1 AND @d2
    AND CAST(TimeStart as time) BETWEEN CAST(@t1 as time) AND CAST (@t2 as time)
    AND AG.ID IN (SELECT * FROM @gr)
------------------------------------------------------------------------------------------------------------------------------

------СЧИТАЕМ ВНЕШНИЕ ПРИНЯТЫЕ ЗВОНКИ-----------------------------------------------------------------------------------------
DECLARE @ExternalAccepted TABLE(GroupId uniqueidentifier, [Count] int)

INSERT INTO @ExternalAccepted
SELECT GroupId, COUNT(DISTINCT IdChain)
FROM @StatConnections
WHERE ConnectionType = 5
GROUP BY GroupId

--SELECT * FROM @ExternalAccepted
------------------------------------------------------------------------------------------------------------------------------

------СЧИТАЕМ ВНУТРЕННИЕ ПРИНЯТЫЕ ЗВОНКИ--------------------------------------------------------------------------------------
DECLARE @InternalAccepted TABLE(GroupId uniqueidentifier, [Count] int)

INSERT INTO @InternalAccepted
SELECT GroupId, COUNT(DISTINCT IdChain)
FROM @StatConnections
WHERE ConnectionType = 3
GROUP BY GroupId

--SELECT * FROM @InternalAccepted
-------------------------------------------------------------------------------------------------------------------------------

-------ФИЛЬТРУЕМ ПРОПУЩЕННЫЕ ЗВОНКИ--------------------------------------------------------------------------------------------
DECLARE @Missed TABLE (IdChain uniqueidentifier, GroupId uniqueidentifier, Number nvarchar(200))

INSERT INTO @Missed
SELECT MC.IdChain
            , AG.ID
            , MC.AOutNumber
FROM [oktell].[dbo].[A_Stat_MissedCalls] AS MC
LEFT JOIN [oktell_settings].[dbo].[A_Users] AS AU
    ON MC.BUserId = AU.ID
LEFT JOIN [oktell].[dbo].[A_Groups] AS AG
    ON AU.ParentGroupID = AG.ID
/*Ситуация:Если в отделе А кто-то пропустил звонок, а потом другой пользователь из отедла А ответил на звонок. Необходимо считать такой звонок как принятый этим отедлом и не засчитывать ему пропущенный.*/
FULL OUTER JOIN @StatConnections AS Accepted
    ON (MC.[IdChain] = Accepted.IdChain AND AG.ID = Accepted.GroupId)
--/**********************************************************************************************************************************************************************************************************/
WHERE MC.TimeStart BETWEEN @d1 AND @d2
        AND CAST(MC.TimeStart as time) BETWEEN CAST(@t1 as time) AND CAST (@t2 as time)
        AND AG.ID IN (SELECT * FROM @gr)
        AND Accepted.IdChain IS NULL /*Убираем принятые из пропущенных*/
------------------------------------------------------------------------------------------------------------------------------

------СЧИТАЕМ ВНЕШНИЕ ПРОПУЩЕННЫЕ ЗВОНКИ-----------------------------------------------------------------------------------------
DECLARE @ExternalMissed TABLE(GroupId uniqueidentifier, [Count] int)

INSERT INTO @ExternalMissed
SELECT GroupId, COUNT(DISTINCT IdChain)
FROM @Missed
WHERE LEN(Number) >= 10
GROUP BY GroupId

--SELECT * FROM @ExternalMissed
------------------------------------------------------------------------------------------------------------------------------

------СЧИТАЕМ ВНУТРЕННИЕ ПРОПУЩЕННЫЕ ЗВОНКИ--------------------------------------------------------------------------------------
DECLARE @InternalMissed TABLE(GroupId uniqueidentifier, [Count] int)

INSERT INTO @InternalMissed
SELECT GroupId, COUNT(DISTINCT IdChain)
FROM @Missed
WHERE LEN(Number) < 10
GROUP BY GroupId

--SELECT * FROM @InternalMissed
-------------------------------------------------------------------------------------------------------------------------------

------СЧИТАЕМ ВСЕ ЗВОНКИ-------------------------------------------------------------------------------------------------------
DECLARE @AllCallS TABLE(GroupId uniqueidentifier, [Count] int)

INSERT INTO @AllCalls
SELECT G.ID
    , ISNULL(ISNULL(EA.[Count], 0) + ISNULL(IA.[Count], 0) + ISNULL(EM.[Count], 0) + ISNULL(IM.[Count], 0), 0)
FROM @gr as G
LEFT JOIN @InternalAccepted AS IA
    ON G.ID = IA.GroupId
LEFT JOIN @ExternalAccepted AS EA
    ON G.ID = EA.GroupId
LEFT JOIN @InternalMissed AS IM
    ON G.ID = IM.GroupId
LEFT JOIN @ExternalMissed AS EM
    ON G.ID = EM.GroupId
-------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------
SELECT AG.Name AS 'Группа'
    , AC.[Count] AS 'Всего звонков'
    , ISNULL(EA.[Count], 0) AS 'Принято внешние'
    , ISNULL(IA.[Count], 0) AS 'Принято внутренние'
    , ISNULL(EM.[Count], 0) AS 'Пропущено внешние'
    , ISNULL(IM.[Count], 0) AS 'Пропущенные внутренние'--[dbo].[GetPersent]
    , ISNULL((SELECT [dbo].[GetPersent](EA.[Count], AC.[Count])), '0%') AS 'Принято внешние, %'
    , ISNULL((SELECT [dbo].[GetPersent](IA.[Count], AC.[Count])), '0%') AS 'Принято внутренние, %'
    , ISNULL((SELECT [dbo].[GetPersent](EM.[Count], AC.[Count])), '0%') AS 'Пропущено внешние, %'
    , ISNULL((SELECT [dbo].[GetPersent](IM.[Count], AC.[Count])), '0%') AS 'Пропущенные внутренние, %'
FROM @AllCallS AS AC
LEFT JOIN [oktell].[dbo].[A_Groups] AS AG
    ON AC.GroupId = AG.ID
LEFT JOIN @InternalAccepted AS IA
    ON AC.GroupId = IA.GroupId
LEFT JOIN @ExternalAccepted AS EA
    ON AC.GroupId = EA.GroupId
LEFT JOIN @InternalMissed AS IM
    ON AC.GroupId = IM.GroupId
LEFT JOIN @ExternalMissed AS EM
    ON AC.GroupId = EM.GroupId