SQLTeam.com | Weblogs | Forums

Finding time difference every other row

Hello

I'm trying to determine the length of time in days between using the AR_Event_Creation_Date_Time for every other row. For example, the number of days between the 1 and 2 row, 3rd and 4th, 5th and 6th etc. In other words, there will be a number of days value for every even row and NULL for every odd row. My code below works if there are only two rows per borrower number but falls down when there are more than two.

SELECT  Borrower_Number, 
Workgroup_Name,
FORMAT(AR_Event_Creation_Date_Time,'d','en-us') AS Tag_Date,
Usr_Usrnm,
DATEDIFF(day, LAG(AR_Event_Creation_Date_Time,1) OVER(PARTITION BY 
Borrower_Number Order By Borrower_Number), AR_Event_Creation_Date_Time) Diff
FROM Control_Mail

Results:

Notice the change in 1002092539

thank you

If every odd record will be null, you can do something like this

SELECT Borrower_Number,
Workgroup_Name,
FORMAT(AR_Event_Creation_Date_Time,'d','en-us') AS Tag_Date,
Usr_Usrnm,
case when ROW_NUMBER() over (partition by Borrower Order By Borrower_Number, AR_Event_Creation_Date_Time, Usr_Usrnm) % 2 = 0
then
DATEDIFF(day, LAG(AR_Event_Creation_Date_Time,1) OVER(PARTITION BY
Borrower_Number Order By Borrower_Number), AR_Event_Creation_Date_Time)
else null
end Diff
FROM Control_Mail

That works really well for me. Thanks a lot.