Calculate date difference of 2 consecutive rows of a column – TSQL

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