I came across a situation where I had to write a query to calculate difference of 2 rows of datetime column based on a condition of another field.
I was troubleshooting performance issues of few stored procedures which are setup as series of steps in a SQL Job. Hence I had setup code within each step to record the START and END times of the SP execution into a table in DBA database.
Below is how the data looks like:
I need to get the duration each proc as taken in each day (or a particular time period) which means I have take the difference of 2 consecutive rows of Timestamp column and filter by Stage value of START and END.
There are 2 ways of getting this done.
Method – 1:
- Add an ID column to add row number
- Split the table into 2 based on the START and END values of Stage column below is how it looks:
- Join the tables on nth row and n+1th row while matching the Proc
START
END
Script:
--Create a Table
;WITH TimeDuration ( Number, Sproc, Stage, logdate, logtime)
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY SPROC, Timestamp) Number
, Proc, Stage, CONVERT(date, TimeStamp), Timestamp
FROM Timings
)
--Actual query
SELECT s1.Proc, s1.logtime as Start_Time, s2.logtime as End_Time, DATEDIFF(MINUTE, s1.logtime, s2.logtime) as Duration
FROM
(SELECT * FROM TimeDuration WHERE Stage = 'START') s1
INNER JOIN
(SELECT * FROM TimeDuration WHERE Stage = 'END') s2
ON s2.Number = s1.Number+1
AND s2.Sproc = S1.Sproc
AND s2.logdate = s1.logdate
ORDER BY s1.Sproc, s1.logdate
Method – 2:
This is the easiest method which uses LEAD and LAG functions introduced from SQL 2012.
LEAD – Used to go to the next row
LAG – Used to go to the previous row
SELECT * FROM ( Select Proc, Timestamp, convert(date, Timestamp) as Logdate, CASE Stage WHEN 'START' THEN DATEDIFF(minute, Timestamp, Lead(tdate, 1) OVER(ORDER BY Proc)) ELSE NULL END AS Duration FROM Timings ) s1 WHERE s1.duration is not null order by Logdate
I hope this article is helpful.
-Hari Mindi