ОДФ 3

/*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