Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*DECLARE @DateStart datetime;
- DECLARE @DateEnd datetime;
- DECLARE @TimeStart datetime;
- DECLARE @TimeEnd datetime;
- SET @DateStart = '20190709 00:00:00.000'
- SET @DateEnd = '20190709 23:59:00.000'
- SET @TimeStart = '19000101 00:00:00.000'
- SET @TimeEnd = '19000101 23:59:00.000'
- DECLARE @tasks TABLE (id uniqueidentifier)
- DECLARE @opers TABLE ( id uniqueidentifier)
- INSERT INTO @opers
- SELECT DISTINCT id FROM oktell.dbo.A_Users
- INSERT INTO @tasks
- SELECT id FROM oktell.dbo.A_TaskManager_Tasks WHERE name ='B2D not in ride'
- DECLARE @AbonentNumber nvarchar(250);
- SET @AbonentNumber = ''
- DECLARE @type int = 0*/
- --------------------------------------------------------------------------------------------------------------------------
- SELECT od.IdChain,datewrite, callerID,od.idtask,Operator,Casy_type,Sub_type,Sub_3,[result]
- INTO #dialogtabletemp
- FROM oktell..Olp_dialog od
- LEFT JOIN oktell.dbo.A_Users au
- ON au.name=od.Operator --Старый джойн, возможно пригодится, заменен на eff
- LEFT JOIN oktell_cc_temp.dbo.A_Cube_CC_EffortConnections eff with (nolock)
- ON eff.IdChain=od.IdChain
- WHERE od.DateWrite BETWEEN @DateStart AND @DateEnd
- AND CAST(cast(od.DateWrite as time)AS datetime) BETWEEN @TimeStart AND @TimeEnd
- AND od.idtask IN (SELECT id FROM @tasks)
- AND au.ID IN (select id from @opers)
- AND eff.IdOperator IN (SELECT id FROM @opers)
- AND CallResult NOT IN (6, 7, 13) -- строка добавлена по AI-11600 14.11.2018, раньше было без нее
- SELECT DISTINCT D1.IdChain
- , D2.DateWrite
- , D1.CallerID
- , D2.idtask
- , D1.Operator
- , D1.Casy_type
- , D1.Sub_type
- , D1.Sub_3
- , D1.result
- INTO #olp_dialog_report
- FROM #dialogtabletemp D1
- LEFT JOIN #dialogtabletemp as D2
- ON D1.IdChain = D2.IdChain
- DROP TABLE #dialogtabletemp
- DECLARE @final TABLE
- (
- idchain uniqueidentifier,datewrite datetime ,callerid nvarchar(50)
- ,idtask uniqueidentifier,operator nvarchar(100),casy_type nvarchar(max)
- ,sub_type nvarchar(max),sub_3 nvarchar(max),mark int,post int, result int
- )
- INSERT INTO @final
- SELECT DISTINCT od1.idchain
- , OD2.DateWrite
- , OD2.CallerID
- , OD2.idtask
- , OD2.Operator
- , OD2.Casy_type
- , OD2.Sub_type
- , OD2.Sub_3
- , TM.MARK
- , NULL
- , OD2.result
- FROM #olp_dialog_report as OD1
- LEFT JOIN oktell.dbo.A_Users au
- ON au.name=OD1.Operator
- LEFT JOIN #olp_dialog_report as OD2
- ON OD1.IdChain = OD2.IdChain
- LEFT JOIN oktell.dbo.tbl_MARK as TM
- ON TM.IDHCAIN = OD1.IdChain
- LEFT JOIN oktell_cc_temp.dbo.A_Cube_CC_EffortConnections eff with (nolock)
- ON eff.IdChain=OD1.IdChain
- WHERE OD1.DateWrite BETWEEN @DateStart AND @DateEnd
- AND CAST(cast(OD1.DateWrite as time)AS datetime) BETWEEN @TimeStart AND @TimeEnd
- AND OD1.idtask IN (SELECT id FROM @tasks)
- AND eff.IdOperator IN (select id from @opers)
- AND au.ID IN (SELECT id FROM @opers)
- DROP TABLE #olp_dialog_report
- UPDATE @final
- SET post = (
- case
- when
- (
- select sum(lentime)
- from oktell_cc_temp.[dbo].[A_Cube_CC_OperatorStates]
- where state=7 and idoperator=au.ID
- and ideffort in
- (
- SELECT DISTINCT ideffort FROM oktell_cc_temp.dbo.A_Cube_CC_EffortConnections WHERE idchain=t.idchain
- )
- )>41
- then
- (
- select datediff(SECOND,(select top 1 datetimestop from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with (nolock) where IdOperator = au.ID and idchain=t.idchain order by datetimestop desc),(select max(DateTimeStop) from oktell_cc_temp.[dbo].[A_Cube_CC_OperatorStates] where state=7 and idoperator=au.ID and ideffort in(select distinct ideffort from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections where idchain=t.idchain)))
- )
- else
- (
- select sum(lentime)
- from oktell_cc_temp.[dbo].[A_Cube_CC_OperatorStates]
- where state=7 and idoperator=au.ID
- and ideffort in
- (
- select distinct ideffort from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections where idchain=t.idchain
- )
- )
- end
- )
- FROM @final t
- LEFT JOIN oktell.dbo.a_users au
- ON au.Name = t.operator
- SELECT datewrite [Дата, Время]
- , callerid[Номер абонента]
- , tsk.Name[Задача]
- , operator[Оператор]
- , casy_type
- , sub_type
- , sub_3
- , mark[Оценка]
- , CAST(DATEADD(ss,post,'00:00') as time(0)) post
- , IIF(result != -1, result, null) [Оценка Оператором]
- ,'http://gett.oktell.olp.site:4055/download/byscript?name=Test_voice&startparam1='+CAST(idchain as nvarchar(50))+'&async=0&timeout=10' [Ссылка на запись]
- INTO #result_temp
- FROM @final as od
- LEFT JOIN [oktell].dbo.[A_TaskManager_Tasks] as tsk
- ON tsk.Id = od.idtask
- DELETE @final
- IF(@type=1 AND (@AbonentNumber='' OR @AbonentNumber IS NULL))
- BEGIN
- SELECT * FROM #result_temp
- ORDER BY [Оператор]
- END
- IF((@type=0 or @type is null) and ( @AbonentNumber='' OR @AbonentNumber IS NULL))
- BEGIN
- SELECT * FROM #result_temp
- WHERE casy_type IS NULL OR casy_type=''
- ORDER BY [Дата, Время]
- END
- IF((@type=0 OR @type IS NULL) AND ( @AbonentNumber<>'' OR @AbonentNumber IS NOT NULL))
- BEGIN
- SELECT * FROM #result_temp
- WHERE (casy_type IS NULL OR casy_type='' ) AND [Номер абонента] LIKE '%'+@AbonentNumber+'%'
- ORDER BY [Дата, Время]
- END
- IF(@type=1 AND ( @AbonentNumber<>'' OR @AbonentNumber IS NOT NULL))
- begin
- SELECT * FROM #result_temp
- where [Номер абонента] LIKE '%'+@AbonentNumber+'%'
- end
- DROP TABLE #result_temp
Add Comment
Please, Sign In to add comment