B2B по отделам V2

SET DATEFORMAT DMY

-----------------
DECLARE @d1 DATETIME =  '18-07-2019 00:00:00.001'
DECLARE @d2 DATETIME =  '19-07-2019 23:59:59.990'
DECLARE @t1 DATETIME =  '01-01-1900 00:00:00.001'
DECLARE @t2 DATETIME =  '01-01-1900 23:59:59.001'
DECLARE @interval INT = 15
DECLARE @gr TABLE(ID uniqueidentifier)

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


--------ПОДГОТОВКА---------
DECLARE @dt01 NVARCHAR(250) =   (CAST(CAST(@d1 AS DATE) AS NVARCHAR(25)) + ' ' + CAST(CAST(@t1 AS TIME(0)) AS NVARCHAR(25)) )
DECLARE @dt02 NVARCHAR(250) =   (CAST(CAST(@d2 AS DATE) AS NVARCHAR(25)) + ' ' + CAST(CAST(@t2 AS TIME(0)) AS NVARCHAR(25)) )
DECLARE @dt1 DATETIME = CONVERT (DATETIME,@dt01,121)
DECLARE @dt2 DATETIME = CONVERT (DATETIME,@dt02,121)
--SELECT @dt1,@dt2
DECLARE @dt1_WHILE DATETIME = @dt1


---------ПРИНЯТО------------
----------------------------
DECLARE @Accepted0 TABLE (IdChain UNIQUEIDENTIFIER, GroupId UNIQUEIDENTIFIER, GroupName NVARCHAR(250), anumberdialed NVARCHAR(250))

INSERT INTO @Accepted0 (IdChain, GroupId, GroupName, anumberdialed)
SELECT [IdChain],
        --BUserId,
        [A_Groups].ID AS GroupId,
        [A_Groups].Name
        ,(select top 1 ANumberDialed FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS sc1 WITH (NOLOCK) where sc1.IdChain=con1.IdChain order by TimeStart)
FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS CON1 WITH (NOLOCK)
LEFT JOIN [oktell_settings].[dbo].[A_Users] AS [A_Users]
ON CON1.BUserId = [A_Users].ID
LEFT JOIN [oktell].[dbo].[A_Groups] AS [A_Groups]
ON [A_Users].ParentGroupID = [A_Groups].ID
WHERE [TimeStart] BETWEEN @dt1 AND @dt2
        AND ConnectionType = 5
        AND [A_Groups].ID IS NOT NULL
GROUP BY [IdChain],[A_Groups].ID,[A_Groups].Name
ORDER BY [IdChain]
------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @Accepted1 TABLE (IdChain UNIQUEIDENTIFIER, GroupId UNIQUEIDENTIFIER, GroupName NVARCHAR(250), anumberdialed NVARCHAR(250))

INSERT INTO @Accepted1 (IdChain, GroupId, GroupName, anumberdialed)
SELECT [IdChain],
        --BUserId,
        [A_Groups].ID AS GroupId,
        [A_Groups].Name
        ,(select top 1 ANumberDialed FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS sc1 WITH (NOLOCK) where sc1.IdChain=con1.IdChain order by TimeStart)
FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS CON1 WITH (NOLOCK)
LEFT JOIN [oktell_settings].[dbo].[A_Users] AS [A_Users]
ON CON1.BUserId = [A_Users].ID
LEFT JOIN [oktell].[dbo].[A_Groups] AS [A_Groups]
ON [A_Users].ParentGroupID = [A_Groups].ID
WHERE [TimeStart] BETWEEN @dt1 AND @dt2
        AND ConnectionType = 3
        AND [A_Groups].ID IS NOT NULL
GROUP BY [IdChain],[A_Groups].ID,[A_Groups].Name
ORDER BY [IdChain]



---------ПРОПУЩЕННО------------
-------------------------------
DECLARE @Missed0 TABLE (IdChain UNIQUEIDENTIFIER, GroupId UNIQUEIDENTIFIER, GroupName NVARCHAR(250), anumberdialed NVARCHAR(250))

INSERT INTO @Missed0 (IdChain, GroupId, GroupName, anumberdialed)
SELECT [MissedCalls].[IdChain]
            ,[A_Groups].ID
            ,[A_Groups].Name
            ,(select top 1 ANumberDialed FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS sc1 WITH (NOLOCK) where sc1.IdChain=[MissedCalls].IdChain order by TimeStart)
FROM [oktell].[dbo].[A_Stat_MissedCalls] AS [MissedCalls]
LEFT JOIN [oktell_settings].[dbo].[A_Users] AS [A_Users]
    ON [MissedCalls].BUserId = [A_Users].ID
LEFT JOIN [oktell].[dbo].[A_Groups] AS [A_Groups]
    ON [A_Users].ParentGroupID = [A_Groups].ID
/*Ситуация:Если в отделе А кто-то пропустил звонок, а потом другой пользователь из отедла А ответил на звонок. Необходимо считать такой звонок как принятый этим отедлом и не засчитывать ему пропущенный.*/
FULL OUTER JOIN @Accepted0 AS Accepted
    ON ([MissedCalls].[IdChain] = Accepted.IdChain AND [A_Groups].ID = Accepted.GroupId)
/**********************************************************************************************************************************************************************************************************/
WHERE [MissedCalls].[TimeStart] BETWEEN @dt1 AND @dt2
        AND AOutNumber = AStr
        AND [A_Groups].ID IS NOT NULL
        AND Accepted.IdChain IS NULL /*Убираем принятые из пропущенных*/
GROUP BY [MissedCalls].[IdChain], [A_Groups].ID, [A_Groups].Name
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @Missed1 TABLE (IdChain UNIQUEIDENTIFIER, GroupId UNIQUEIDENTIFIER, GroupName NVARCHAR(250), anumberdialed NVARCHAR(250))

INSERT INTO @Missed1 (IdChain, GroupId, GroupName, anumberdialed)
SELECT [MissedCalls].[IdChain]
            ,[A_Groups].ID
            ,[A_Groups].Name
            ,(select top 1 ANumberDialed FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS sc1 WITH (NOLOCK) where sc1.IdChain=[MissedCalls].IdChain order by TimeStart)
FROM [oktell].[dbo].[A_Stat_MissedCalls] AS [MissedCalls]
LEFT JOIN [oktell_settings].[dbo].[A_Users] AS [A_Users]
    ON [MissedCalls].BUserId = [A_Users].ID
LEFT JOIN [oktell].[dbo].[A_Groups] AS [A_Groups]
    ON [A_Users].ParentGroupID = [A_Groups].ID
/*Ситуация:Если в отделе А кто-то пропустил звонок, а потом другой пользователь из отедла А ответил на звонок. Необходимо считать такой звонок как принятый этим отедлом и не засчитывать ему пропущенный.*/
FULL OUTER JOIN @Accepted1 AS Accepted
    ON ([MissedCalls].[IdChain] = Accepted.IdChain AND [A_Groups].ID = Accepted.GroupId)
/**********************************************************************************************************************************************************************************************************/
WHERE [MissedCalls].[TimeStart] BETWEEN @dt1 AND @dt2
        AND AOutNumber != AStr
        AND [A_Groups].ID IS NOT NULL
        AND Accepted.IdChain IS NULL /*Убираем принятые из пропущенных*/
GROUP BY [MissedCalls].[IdChain], [A_Groups].ID, [A_Groups].Name
------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------
DECLARE @Result TABLE (IdChain UNIQUEIDENTIFIER, GroupId UNIQUEIDENTIFIER, GroupName NVARCHAR(250), FlagAccepting0 INT, FlagMissed0 INT, FlagAccepting1 INT, FlagMissed1 INT)

INSERT INTO @Result (IdChain, GroupId, GroupName, FlagAccepting0, FlagMissed0, FlagAccepting1, FlagMissed1)
SELECT IdChain, GroupId, GroupName, '1', '0', '0', '0'
FROM @Accepted0

INSERT INTO @Result (IdChain, GroupId, GroupName, FlagAccepting0, FlagMissed0, FlagAccepting1, FlagMissed1)
SELECT IdChain, GroupId, GroupName, '0', '0', '1', '0'
FROM @Accepted1

INSERT INTO @Result (IdChain, GroupId, GroupName, FlagAccepting0, FlagMissed0, FlagAccepting1, FlagMissed1)
SELECT IdChain, GroupId, GroupName, '0', '1', '0', '0'
FROM @Missed0

INSERT INTO @Result (IdChain, GroupId, GroupName, FlagAccepting0, FlagMissed0, FlagAccepting1, FlagMissed1)
SELECT IdChain, GroupId, GroupName, '0', '0', '0', '1'
FROM @Missed1



SELECT
case
    when GroupName='Колл-центр' then 'Операторы ПКК 974-96-44'
    else GroupName end  AS [Группа]
        , COUNT (*) AS [Всего звонков]
        , SUM(FlagAccepting0) AS [Принято внешние]
        , SUM(FlagAccepting1) AS [Принято внутренние]
        , SUM(FlagMissed0) AS [Пропущенно внешние]
        , SUM(FlagMissed1) AS [Пропущенно внутренние]
        ,ISNULL(ROUND(CAST(SUM(FlagAccepting0) AS float) * 100 / NULLIF((CAST(COUNT (*) AS float)),0),1),0) AS [Принято внешние, %]
        ,ISNULL(ROUND(CAST(SUM(FlagAccepting1) AS float) * 100 / NULLIF((CAST(COUNT (*) AS float)),0),1),0) AS [Принято внутренние, %]
        ,ISNULL(ROUND(CAST(SUM(FlagMissed0) AS float) * 100 / NULLIF((CAST(COUNT (*) AS float)),0),1),0) AS [Пропущенно внешние, %]
        ,ISNULL(ROUND(CAST(SUM(FlagMissed1) AS float) * 100 / NULLIF((CAST(COUNT (*) AS float)),0),1),0) AS [Пропущенно внутренние, %]
FROM @Result
WHERE GroupName<>'ТПП ИАС'
AND GroupId IN (SELECT ID FROM @gr)
GROUP BY [GroupName]