确定差距记录，填写并返回结果

以下工作代码针对 SQL-Server 2012，我已将实际问题提炼为可管理的场景，下面显示的代码示例也在SQL Fiddle.

我寻求有关使用 CTE 和临时表的最佳实践的反馈，并且想知道是否可以简化以下内容。

（请注意，这也在代码审查（插入差距记录报告流程）中被问到，到目前为止没有任何回应。）

前提

存在两个表，其中包含代理和管理的时间戳事件。将在代理表上运行报告。但在代理表中，如果事件间隔超过 5 小时，则必须从事件管理表中为该间隔填写生成的报告。

代理人

CREATE TABLE AgentInteractions
(
   [Event]      VARCHAR(12) NOT NULL, 
   [Timestamp] [DateTime] NOT NULL
);


INSERT INTO dbo.AgentInteractions( Event, TimeStamp  )
VALUES ( 'Alpha', '24-Jan-2018 3:04:00 PM' ),
       ( 'Beta', '24-Jan-2018 10:04:00 PM' ),  -- Gap 7 hours
       ( 'Omega', '25-Jan-2018 2:04:00 AM' );  -- No Gap

管理

CREATE TABLE ManagementInteractions
(
  [Event]      VARCHAR(12) NOT NULL, 
  [Timestamp] [DateTime] NOT NULL
);


INSERT INTO dbo.ManagementInteractions( Event, TimeStamp  )
VALUES ( '5pm',      '24-Jan-2018 5:00:00 PM' ),  -- Gap Filler #1
       ( '8pm',      '24-Jan-2018 8:00:00 PM' ),  -- Gap Filler #2
       ( 'Midnight', '25-Jan-2018 12:00:00 AM' ); -- Not used

初步报告（或第一步）

使用以下将报告放入临时表的 sql，它计算行之间的时间差并设置原始数据中不存在的序列。

CTE 到临时表

IF OBJECT_ID('tempdb..#Actions') IS NOT NULL DROP TABLE #Actions;

WITH AgentActions AS 
( SELECT  ROW_NUMBER() OVER ( ORDER BY [Timestamp] ) AS [Sequence], -- Create an index number ordered by time.
          Event ,
          Timestamp
  FROM  AgentInteractions
)
SELECT  CAST('Agent' AS VARCHAR(20)) AS [Origin] ,
            AgentActions.Sequence ,
            AgentActions.Event ,
            ( SELECT    Other.Timestamp
              FROM      AgentActions Other
              WHERE     Other.Sequence = AgentActions.Sequence - 1
            ) AS Previous ,
            AgentActions.Timestamp ,
            ISNULL(DATEDIFF(HOUR,
                            ( SELECT    Other.Timestamp
                              FROM      AgentActions Other
                              WHERE     Other.Sequence = AgentActions.Sequence - 1), 
                            AgentActions.Timestamp), 
                            0) AS TimeFromLastPoint
    INTO    #Actions
    FROM    AgentActions;

select into 的结果#Actions，注意 7 小时的差距：

确定差距

以下 sql 确定差距，并插入管理表中的记录以填补差距。

将管理事件插入差距

WITH Gaps AS 
( SELECT   AC.Origin ,
           AC.Sequence ,
           AC.Event ,
           AC.Previous ,
           AC.Timestamp ,
           AC.TimeFromLastPoint
  FROM     #Actions AC
  WHERE    AC.TimeFromLastPoint > 5
)
INSERT  INTO #Actions ( [Origin] , [Event] , [Timestamp] , TimeFromLastPoint)
            SELECT  'Management' ,
                    [Event] ,
                    [Timestamp] ,
                    0                      -- We will figure this out after the insert.
            FROM    ManagementInteractions MAN
            WHERE   EXISTS ( SELECT *
                             FROM   Gaps
                             WHERE  MAN.Timestamp BETWEEN Gaps.Previous
                                                  AND     Gaps.Timestamp );

SELECT Origin , Sequence , Event , Previous , Timestamp , TimeFromLastPoint 
FROM #Actions ORDER BY Timestamp;

选择的结果。

总结报告

最终报告执行步骤 1 中的操作，重新排序并确定所有点之间的持续时间。

CTE Deja-vu 最终报告

WITH Combined AS 
( 
   SELECT Origin,
          ROW_NUMBER() OVER ( ORDER BY [Timestamp] ) AS [Sequence], -- Create an index number ordered by time.
          Event ,
          Timestamp
    FROM  #Actions
)
SELECT  Combined.Origin,
        Combined.Sequence ,
        Combined.Event ,
        ( SELECT    Other.Timestamp
          FROM      Combined Other
          WHERE     Other.Sequence = Combined.Sequence - 1
        ) AS Previous ,
        Combined.Timestamp ,
        ISNULL(DATEDIFF(HOUR,
                        ( SELECT    Other.Timestamp
                          FROM      Combined Other
                          WHERE     Other.Sequence = Combined.Sequence
                                    - 1
                        ), Combined.Timestamp), 0) AS TimeFromLastPoint

FROM  Combined;

这是结果

概括

是否可以组合任何步骤，或者我应该使用或避免使用上述 sql 代码的任何做法？