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


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.