Determine Gap Records, Fill and Return Result

The following working code targets SQL-Server 2012 and I have distilled the actual problem into a manageable scenario with the code example shown below also presented on SQL Fiddle.

I seek feedback on best practices on using CTE's and temp tables and am wondering if the following could be streamlined as such.

(Note this was also asked on Code Review (Insert Of Gap Records Report Process) with no responses so far.)

Premise

Two Tables exist which house timestamped events for Agents and Management. A report will be run on the agent table. But within the Agent table, if there are event gap(s) of more than 5 hours, the resultant report has to be filled from the Management table of events for that gap.


Agent

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

Management

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

Initial Report (or first step)

With the following sql which places the report into the temp table, it calculates the time difference between the rows and sets up a sequence which was not present in the originating data.


CTE To Temp Table

```
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 VARCHA

I think that the approach you came up with is a pretty good one. Here is a proposed simplification that avoids the self-join on dbo.AgentInteractions by using the new SQL Server 2012 LAG function as well as CTEs to distill the logic into a single query (also in SQL Fiddle form).

WITH AgentActions AS (
    -- Sequence the agent actions
    SELECT a.Event,
        LAG(a.TimeStamp, 1) OVER (ORDER BY TimeStamp ASC) AS Previous,
        a.TimeStamp,
        DATEDIFF(HOUR, LAG(a.TimeStamp, 1) OVER (ORDER BY TimeStamp ASC), a.TimeStamp) AS TimeFromLastPoint
    FROM dbo.AgentInteractions a
),
allActionsToReport AS (
    --  Agent action
    SELECT 'Agent' AS Origin, a.Event, a.TimeStamp
    FROM AgentActions a
    UNION ALL
    -- Any management actions during a gap of > 5 hours between agent interactions
    SELECT 'Management' AS Origin, m.event, m.TimeStamp
    FROM AgentActions a
    JOIN ManagementInteractions m
        ON m.TimeStamp BETWEEN a.Previous AND a.TimeStamp
    WHERE a.TimeFromLastPoint > 5
)
-- For all actions (with gaps already filled), perform final sequencing
SELECT a.Origin,
    ROW_NUMBER() OVER (ORDER BY a.TimeStamp ASC) AS Sequence,
    a.Event,
    LAG(a.TimeStamp, 1) OVER (ORDER BY TimeStamp ASC) AS Previous,
    a.TimeStamp,
    ISNULL(DATEDIFF(HOUR, LAG(a.TimeStamp, 1) OVER (ORDER BY TimeStamp ASC), a.TimeStamp), 0) AS TimeFromLastPoint
FROM allActionsToReport a

From taking a look at the query plan for this single-query version, it does exactly what you'd expect: sort the agent actions by timestamp, fill in the gaps by accessing the management actions, and then sort this combined set of resulting actions in order to do the final sequencing:

StackExchange Database Administrators Q#106948, answer score: 2