Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @DATE_START DATETIME = '07-10-2018 00:00:00.000'
- DECLARE @DATE_FINISH DATETIME = '08-10-2018 00:00:00.000'
- DECLARE @TIME_START DATETIME = '01-01-1900 01:00:00.001'
- DECLARE @TIME_FINISH DATETIME = '01-01-1900 23:00:00.001'
- DECLARE @OPERATORS TABLE(Id uniqueidentifier)
- INSERT @OPERATORS VALUES('230FCDC7-5DCD-4126-9374-E1EEB4F1F165')
- INSERT @OPERATORS VALUES('95B1BDD5-AF34-40C4-9368-CB80E99A2818')
- DECLARE @HoursCount int = DATEDIFF(HOUR, @TIME_START, @TIME_FINISH)
- DECLARE @FilteredConnections0 TABLE(TimeStart datetime, TimeStop datetime, OperId uniqueidentifier, IdChain nvarchar(50), [Name] nvarchar(50), ConnectionType int)
- DECLARE @FilteredConnections TABLE(TimeStart datetime, TimeStop datetime, OperId uniqueidentifier, IdChain nvarchar(50), [Name] nvarchar(50))
- INSERT INTO @FilteredConnections0
- SELECT
- TimeStart
- ,TimeStop
- ,BUserID
- ,IdChain
- ,Bstr
- ,ConnectionType
- FROM [oktell].[dbo].[A_Stat_Connections_1x1] as ascs WITH (NOLOCK)
- RIGHT JOIN @OPERATORS as o
- ON o.Id = ascs.BUserId
- --ФИЛЬТРУЕМ ТАБЛИЦУ И СОХРАНЯЕМ В ПЕРЕМЕННОЙ
- INSERT INTO @FilteredConnections
- SELECT
- TimeStart
- ,TimeStop
- ,OperId
- ,IdChain
- ,[Name]
- FROM @FilteredConnections0
- WHERE CAST(TimeStart AS date) BETWEEN CAST(@DATE_START AS date) AND CAST(@DATE_FINISH AS date)
- AND CAST(TimeStart AS time) BETWEEN CAST(@TIME_START AS time) AND CAST(@TIME_FINISH AS time)
- AND (ConnectionType = 5 OR ConnectionType = 3 OR ConnectionType = 8)
- DELETE @FilteredConnections0
- --select * from @FilteredConnections
- -----------------------------------------------------------
- --ЗАДАЕМ ТАБЛИЦУ СВЯЗЕЙ ID - NAME У ОПЕРАТОРОВ
- DECLARE @Id_Names TABLE(Id uniqueidentifier, [Name] nvarchar(50))
- INSERT INTO @Id_Names
- SELECT OperId, [Name]
- FROM @FilteredConnections
- GROUP BY OperId, [Name]
- --select * from @Id_Names
- --------------------------------------------------------
- --СЧИТАЕМ ОБРАБОТАННЫЕ ЗВОНКИ И ВРЕМЯ РАЗГОВОРА
- DECLARE @LenTalking TABLE(LenTalking int, OperId uniqueidentifier)
- DECLARE @LenTalking2 TABLE(LenTalking int, OperId uniqueidentifier, [Count] int)
- INSERT INTO @LenTalking
- SELECT DATEDIFF(SECOND,TimeStart, TimeStop), OperId
- FROM @FilteredConnections
- INSERT INTO @LenTalking2
- SELECT SUM(LenTalking), OperId, COUNT(OperId)
- FROM @LenTalking
- GROUP BY OperId
- DELETE @LenTalking
- --select * from @LenTalking2
- ----------------------------------------------------------
- --СЧИТАЕМ ПРОПУЩЕННЫЕ ЗВОНКИ
- DECLARE @Missed TABLE(OperId uniqueidentifier)
- DECLARE @Missed2 TABLE([Count] int, OperId uniqueidentifier)
- INSERT INTO @Missed
- SELECT fc.OperId
- FROM @FilteredConnections as fc
- JOIN [oktell].[dbo].[A_Stat_MissedCalls] AS sm
- ON fc.IdChain = sm.IdChain
- INSERT INTO @Missed2
- SELECT COUNT(OperId), OperId
- FROM @Missed
- GROUP BY OperId
- DELETE @Missed
- --select * from @Missed2
- --------------------------------------------------------------
- --СЧИТАЕМ ПОТЕРЯННЫЕ ЗВОНКИ
- DECLARE @Failed TABLE(OperId uniqueidentifier)
- DECLARE @Failed2 TABLE([Count] int, OperId uniqueidentifier)
- INSERT INTO @Failed
- SELECT fc.OperId
- FROM @FilteredConnections as fc
- JOIN [oktell].[dbo].[A_Stat_FailedCalls] AS sm
- ON fc.IdChain = sm.IdChain
- DELETE @FilteredConnections
- INSERT INTO @Failed2
- SELECT COUNT(OperId), OperId
- FROM @Failed
- GROUP BY OperId
- DELETE @Failed
- --select * from @Failed2
- --------------------------------------------------------------
- --СЧИТАЕМ ВСЕ ЗВОНКИ
- DECLARE @Final TABLE(OperId uniqueidentifier, [Count] int)
- DECLARE @Final2 TABLE(OperId uniqueidentifier, [Count] int)
- INSERT INTO @Final
- SELECT OperId, [Count]
- FROM @LenTalking2
- UNION
- SELECT OperId, [Count]
- FROM @Failed2
- UNION
- SELECT OperId, [Count]
- FROM @Missed2
- INSERT INTO @Final2
- SELECT OperId, SUM(Count)
- FROM @Final
- GROUP BY OperId
- DELETE @Final
- DELETE @Failed2
- --select * from @Final2
- ---------------------------------------------------------------
- DECLARE @FinalTable0 TABLE([Name] nvarchar(50), [All] int, Checked int, Missed int, TimeTalking int, MiddleCount float)
- DECLARE @FinalTable TABLE([Name] nvarchar(50), [All] int, Checked int, Missed int, TimeTalking int, MiddleCount float)
- INSERT INTO @FinalTable0
- SELECT
- ins.[Name]
- ,fin.[Count]
- ,lt.[Count]
- ,m.[Count]
- ,lt.LenTalking
- ,lt.[Count]
- FROM @LenTalking2 as lt
- LEFT JOIN @Missed2 as m
- ON m.OperId = lt.OperId
- LEFT JOIN @Final2 as fin
- ON fin.OperId = lt.OperId
- LEFT JOIN @Id_Names as ins
- ON ins.Id = lt.OperId
- --select * from @FinalTable
- DELETE @Final2
- DELETE @LenTalking2
- DELETE @Missed2
- DELETE @Id_Names
- INSERT INTO @FinalTable
- SELECT [Name]
- ,SUM([All])
- ,SUM(Checked)
- ,SUM(Missed)
- ,SUM(TimeTalking) / SUM([All])
- ,ROUND(SUM(Checked) / CAST(@HoursCount as float), 2)
- FROM @FinalTable0
- GROUP BY [Name]
- DELETE @FinalTable0
- SELECT [Name] as 'Оператор'
- , ISNULL([All], 0) as 'Итого'
- , ISNULL(Checked, 0) as 'Обработано'
- , CONCAT(IIF([All] != 0 AND [ALL] IS NOT NULL, ROUND((ISNULL(Checked, 0) / CAST([All] as float)), 4) * 100, 0), '%') as 'Обработано %'
- , ISNULL(Missed, 0) as 'Пропущено'
- , CONCAT(IIF([All] != 0 AND [ALL] IS NOT NULL, ROUND((ISNULL(Missed, 0) / CAST([All] as float)), 4) * 100, 0), '%') as 'Пропущено %'
- , CONCAT(TimeTalking / 3600, '.', TimeTalking / 60 - (TimeTalking / 3600) * 60, '.', TimeTalking - (TimeTalking / 60) * 60) as 'Среднее время разговора'
- , MiddleCount as 'Среднее количество принятых звонков за час'
- FROM @FinalTable
- ORDER BY [Name]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement