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.)
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.
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
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 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;
Result of a select into
#Actions, notice the 7 hour gap:
The following sql determines the gaps, and inserts the records from the Management table which fill the gap.
Insert Management Events Into Gaps
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;
Result of the select.
The final report does what is done in step 1, it re sequences and determines time durations between all points.
CTE Deja-vu Final Report
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;
Here are the results
Can any of the steps be combined or are there any practices I should use or avoid with the above sql code?