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

SET DATEFORMAT DMY


-----------------
/*
DECLARE @d1 DATETIME =  '04-12-2018 00:00:00.001'
DECLARE @d2 DATETIME =  '04-12-2018 23:59:59.990'
DECLARE @t1 DATETIME =  '01-01-1900 11:30:00.001'
DECLARE @t2 DATETIME =  '01-01-1900 18:40:00.001'
DECLARE @interval INT = 15
*/


--------ПОДГОТОВКА---------
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 @Accepted TABLE (IdChain UNIQUEIDENTIFIER, GroupId UNIQUEIDENTIFIER, GroupName NVARCHAR(250), anumberdialed NVARCHAR(250))

INSERT INTO @Accepted (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
        --AND IdChain in ('C0E4403C-BE90-4F68-8F96-6F52D02A254A','7B6A6E4F-A1F6-4786-A2BB-7535DD0FF009')
GROUP BY [IdChain],[A_Groups].ID,[A_Groups].Name
ORDER BY [IdChain]



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

INSERT INTO @Missed (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
LEFT JOIN  [oktell].[dbo].[A_Stat_Connections_1x1] AS CON2
ON (CON2.IdChain = MissedCalls.IdChain AND ConnectionType = 4 AND IdPrev IS NULL)
/*Ситуация:Если в отделе А кто-то пропустил звонок, а потом другой пользователь из отедла А ответил на звонок. Необходимо считать такой звонок как принятый этим отедлом и не засчитывать ему пропущенный.*/
FULL OUTER JOIN @Accepted AS Accepted
ON ([MissedCalls].[IdChain] = Accepted.IdChain AND [A_Groups].ID = Accepted.GroupId)
/**********************************************************************************************************************************************************************************************************/
WHERE [MissedCalls].[TimeStart] BETWEEN @dt1 AND @dt2
        AND [A_Groups].ID IS NOT NULL
        AND Accepted.IdChain IS NULL /*Убираем принятые из пропущенных*/
GROUP BY [MissedCalls].[IdChain], [A_Groups].ID, [A_Groups].Name
-------------------------------


INSERT INTO @Missed (IdChain, GroupId, GroupName, anumberdialed)

SELECT [IdChain],[OLP_GroupNumbers].GroupId, [OLP_GroupNumbers].[GroupName]
,(SELECT top 1 ANumberDialed FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS sc1 WITH (NOLOCK) WHERE sc1.IdChain=CON3.IdChain ORDER BY TimeStart)
FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS CON3
LEFT JOIN [oktell].[dbo].[OLP_GroupNumbers] AS [OLP_GroupNumbers]
ON CON3.BOutNumber LIKE '%'+ [OLP_GroupNumbers].Phone
WHERE [ConnectionType] = 4
    AND [TimeStart] BETWEEN @dt1 AND @dt2
    AND [IdPrev] IS NULL
    AND [IdChain] NOT IN (SELECT DISTINCT [IdChain]
                        FROM [oktell].[dbo].[A_Stat_Connections_1x1]
                        WHERE [ConnectionType] IN (5,6)
                                AND [TimeStart] BETWEEN @dt1 AND @dt2
                        )
    AND [OLP_GroupNumbers].[GroupName] IS NOT NULL
    AND [IdChain] NOT IN (SELECT [IdChain] FROM @Missed)


------------------------------------------------------------------------------------------------------------------------------------
---------Разбиение "Справочно-информационная служба" на 2 записи (+"НККДЦ") по номерам 3001 и 3002. Обращение  AI-14517.------------
------------------------------------------------------------------------------------------------------------------------------------
UPDATE @Accepted
SET GroupName = 'НККДЦ' WHERE anumberdialed = '3002'
--select * from @Accepted where anumberdialed = '3002'

UPDATE @Missed
SET GroupName = 'НККДЦ' WHERE anumberdialed = '3002'
--select * from @Missed where anumberdialed = '3002'
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------


DECLARE @RESULT TABLE (IdChain UNIQUEIDENTIFIER, GroupId UNIQUEIDENTIFIER, GroupName NVARCHAR(250), FlagAccepting INT, FlagMissed INT)

INSERT INTO @RESULT (IdChain, GroupId, GroupName, FlagAccepting, FlagMissed)
SELECT IdChain, GroupId, GroupName, '1', '0'
FROM @Accepted

INSERT INTO @RESULT (IdChain, GroupId, GroupName, FlagAccepting, FlagMissed)
SELECT IdChain, GroupId, GroupName, '0', '1'
FROM @Missed



SELECT
CASE
    WHEN GroupName='Колл-центр' THEN 'Операторы ПКК 974-96-44'
    ELSE GroupName END  AS [Группа]
        ,COUNT (*) AS [Всего звонков]
        ,SUM(FlagAccepting) AS [Принято]
        ,SUM(FlagMissed) AS [Пропущенно]
        ,ISNULL(ROUND(CAST(SUM(FlagAccepting) AS FLOAT) * 100 / NULLIF((CAST(COUNT (*) AS FLOAT)),0),1),0) AS [Принято, %]
        ,ISNULL(ROUND(CAST(SUM(FlagMissed) 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]