Calculate Time Difference Between Two Consecutive Rows [closed]

Question

I have a table like this:

RecordID     TransDate
1            05-Oct-16 9:33:32 AM
2            05-Oct-16 9:33:37 AM
3            05-Oct-16 9:33:41 AM
4            05-Oct-16 9:33:46 AM
5            05-Oct-16 9:33:46 AM

I need to get the difference between consecutive TransDate values. I am using SQL Server 2014, and am aware of a way to use the LAG functions to do this, but I don't know how to do it.

I need this output:

RecordID     TransDate              Diff
1            05-Oct-16 9:33:32 AM   0:00:00
2            05-Oct-16 9:33:37 AM   0:00:05
3            05-Oct-16 9:33:41 AM   0:00:04
4            05-Oct-16 9:33:46 AM   0:00:05
5            05-Oct-16 9:33:46 AM   0:00:00

Answer 1

How about this:

select recordid, transdate,
       cast( (transdate - lag(transdate) over (order by transdate)) as time) as diff
from t;

In other words, you can subtract two datetime values and cast the result as a time. You can then format the result however you like.

Answer 2

A non Lag/lead approach...

select T1.recordId, T1.TransDate, datediff(ss, T1.TransDate, T2.Transdate) as Diff
from Table1 T1
left join Table1 T2
on T1.Recordid = T2.RecordId +1