Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @DATE_START datetime = '20190813 00:00'
- DECLARE @DATE_END datetime = '20190813 23:59'
- DECLARE @TIME_START DATETIME = '19000101 00:00'
- DECLARE @TIME_END DATETIME = '19000101 23:59'
- DECLARE @TASKS TABLE (Id uniqueidentifier)
- INSERT INTO @tasks
- SELECT id FROM oktell.dbo.A_TaskManager_Tasks
- DECLARE @OPERATORS TABLE (Id uniqueidentifier)
- INSERT INTO @operators
- SELECT Id
- FROM [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo]
- WHERE [Name] LIKE '%kelly_B2D_makovetskii%'
- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- --------ОТФИЛЬТРОВЫВАЕМ И СОЕДИНЯЕМ ТАБЛИЦЫ-----------------------------------------------------------
- DECLARE @OperStates TABLE
- (
- IdTask uniqueidentifier
- , IdOperator uniqueidentifier
- , TimeStart datetime
- , [State] int
- , ICode int
- , LenTime int
- , UserState int
- );
- WITH FilteredTable AS
- (
- SELECT IdTask
- , IdOperator
- , DateTimeStart
- , [State]
- , ICode
- , LenTime
- FROM [oktell_cc_temp].[dbo].[A_Cube_CC_OperatorStates] WITH (NOLOCK)
- WHERE DateTimeStart BETWEEN @DATE_START AND @DATE_END
- AND TimeStart BETWEEN @TIME_START AND @TIME_END
- AND IdOperator IN (SELECT * FROM @OPERATORS)
- )
- INSERT INTO @OperStates
- SELECT IdTask
- , IdOperator
- , DateTimeStart
- , OS.[State]
- , OS.ICode
- , CAST(LenTime as int)
- , US.[State]
- FROM FilteredTable as OS
- LEFT JOIN [oktell].[dbo].[A_UserStateHistory] as US
- ON US.UserId = OS.IdOperator AND OS.DateTimeStart = US.TimeChange
- -------------------------------------------------------------------------------------------------
- ---------СЧИТАЕМ ВХОДЯЩИЕ--------------------------------------------------------------
- DECLARE @FilteredEffort TABLE(OperId uniqueidentifier, [Count] int)
- INSERT INTO @FilteredEffort
- SELECT IdOperator, COUNT(IdOperator)
- FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
- WHERE DateTimeStart BETWEEN @DATE_START AND @DATE_END
- AND TimeStart BETWEEN @TIME_START AND @TIME_END
- AND BLineNum <> 'IVR' AND IsOutput = 0
- AND IdTask IN (SELECT * FROM @TASKS)
- AND IdOperator IN (SELECT * FROM @OPERATORS)
- and IsRecorded = 1
- GROUP BY IdOperator
- ----------------------------------------------------------------------------------------
- ---------СЧИТАЕМ ИСХОДЯЩИЕ--------------------------------------------------------------
- DECLARE @FilteredStat TABLE(OperId uniqueidentifier, [Count] int)
- INSERT INTO @FilteredStat
- SELECT AUserId, COUNT(AUserId)
- FROM [oktell].[dbo].[A_Stat_Connections_1x1]
- WHERE TimeStart BETWEEN @DATE_START AND @DATE_END
- AND CAST(TimeStart as time) BETWEEN CAST(@TIME_START as time) AND CAST(@TIME_END as time)
- AND ConnectionType = 1
- AND AUserId IN (SELECT * FROM @OPERATORS)
- GROUP BY AUserId;
- -----------------------------------------------------------------------------------------
- ------ПОДСЧИТЫВАЕМ ВСЕ ЗНАЧЕНИЯ----------------------------------------------------------
- WITH FinalTable AS
- (
- SELECT IdOperator
- , SUM(LenTime) as SumTotal
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] IN (1, 2, 3) AND OS.IdOperator = IdOperator AND IdTask IN (SELECT * FROM @TASKS)), 0) as BeforeTask
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 6 AND OS.IdOperator = IdOperator AND IdTask IN (SELECT * FROM @TASKS)), 0) as Tasks
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] IN (1, 2, 3, 4, 6, 7) AND OS.IdOperator = IdOperator AND IdTask NOT IN (SELECT * FROM @TASKS)), 0) as OtherTasks
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 10 AND OS.IdOperator = IdOperator), 0) as Ready
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] IN (5, 8) AND OS.IdOperator = IdOperator), 0) as Talk
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ([State] = 9 AND UserState IS NULL OR UserState = 3) AND OS.IdOperator = IdOperator), 0) as [Break]
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 1 AND OS.IdOperator = IdOperator), 0) as Dispatchs
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 2 AND OS.IdOperator = IdOperator), 0) as Tickets
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 3 AND OS.IdOperator = IdOperator), 0) as Chat
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 4 AND OS.IdOperator = IdOperator), 0) as Other
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 6 AND OS.IdOperator = IdOperator), 0) as Refunds
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 7 AND OS.IdOperator = IdOperator), 0) as Training
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 9 AND ICode = 8 AND OS.IdOperator = IdOperator), 0) as Tech
- , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 7 AND OS.IdOperator = IdOperator), 0) as Post
- , ISNULL((SELECT AVG(LenTime) FROM @OperStates WHERE [State] = 7 AND OS.IdOperator = IdOperator), 0) as AvgPost
- , ISNULL((SELECT TOP 1 [Count] FROM @FilteredEffort WHERE OperId = OS.IdOperator), 0) as Incoming
- , ISNULL((SELECT TOP 1 [Count] FROM @FilteredStat WHERE OperId = OS.IdOperator), 0) as Outgoing
- FROM @OperStates AS OS
- GROUP BY (IdOperator)
- )
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
- SELECT OI.Name
- , (SELECT [dbo].[IntToTimeStr](Tasks)) as 'Разговоры по задаче'
- , (SELECT [dbo].[IntToTimeStr](BeforeTask)) as 'Время до ответа'
- , (SELECT [dbo].[IntToTimeStr](Post)) as 'ACW'
- , (SELECT [dbo].[IntToTimeStr](OtherTasks)) as 'Разговоры по др. задачам'
- , (SELECT [dbo].[IntToTimeStr](Talk)) as 'Разговоры не по задаче'
- , (SELECT [dbo].[IntToTimeStr](Ready)) as 'Готов'
- , (SELECT [dbo].[IntToTimeStr]([Break])) as 'Перерыв'
- , (SELECT [dbo].[IntToTimeStr](Dispatchs)) as 'Отправка'
- , (SELECT [dbo].[IntToTimeStr](Tickets)) as 'Тикеты'
- , (SELECT [dbo].[IntToTimeStr](Chat)) as 'Чат'
- , (SELECT [dbo].[IntToTimeStr](Other)) as 'Другое'
- , (SELECT [dbo].[IntToTimeStr](Refunds)) as 'Возврат'
- , (SELECT [dbo].[IntToTimeStr](Training)) as 'Обучение'
- , (SELECT [dbo].[IntToTimeStr](Tech)) as 'Технический вопрос'
- , (SELECT [dbo].[IntToTimeStr](SumTotal)) as 'Всего'
- , Incoming as 'Входящие'
- , Outgoing as 'Исходящие'
- , (SELECT [dbo].[IntToTimeStr](AvgPost)) as 'Среднее время постобработки'
- FROM FinalTable as F
- LEFT JOIN [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo] as OI
- ON OI.Id = F.IdOperator
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement