Calculate Time Difference Between Two Consecutive Rows

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

Any ideas?

Thanks in advance!

score 11 · Accepted Answer · answered Oct 05 '16 at 15:21

11

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.

answered Oct 05 '16 at 15:21

Gordon Linoff

1,242,037
58
646
786

1

Elegant, simple and effective. Thanks! I'll have to learn these lag functions, seems like I'll be using them more often now. – controller Oct 07 '16 at 12:28

score 1 · Answer 2 · answered Oct 05 '16 at 15:24

1

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

answered Oct 05 '16 at 15:24

JohnHC

10,935
1
24
40

score 0 · Answer 3 · answered Oct 05 '16 at 15:21

0

Maybe a bit hacky:

SELECT RecordId / 2 AS id, min(TransDate) AS TransDate, max(TransDate) - min(TransDate) AS Diff GROUP BY RecordId / 2

Untested, because I have no SQL Server available right now.

answered Oct 05 '16 at 15:21

mrks

8,033
1
33
62

score 0 · Answer 4 · answered Dec 10 '18 at 22:20

Tried creating a cursor to get the date difference.

--- Query to get date difference between two rows 

declare @table table (olddate datetime, newdate datetime)
create table #table (olddate datetime, newdate datetime)

DECLARE db_cursor CURSOR FOR 

  SELECT  CONVERT(date,[utl_recycle_date] ) as RecycleDate
  FROM XYZ
  WHERE account_number = '6900' AND match_status = 'F' 
  AND [utl_recycle_date] IS NOT NULL
  AND [utl_recycle_date] > '11/01/2018'
  GROUP BY DATEDIFF(DAY, CONVERT(date,[utl_recycle_date] ), CONVERT(date, GETDATE())),CONVERT(date,[utl_recycle_date] )
  ORDER BY 1

DECLARE @RecycleDate datetime 
DECLARE @NewDate datetime 

OPEN db_cursor 

FETCH next FROM db_cursor INTO @RecycleDate 

WHILE @@FETCH_STATUS = 0 
  BEGIN 

      FETCH next FROM db_cursor INTO @NewDate

      insert INTO #table (olddate, newdate) values (cast(@RecycleDate as date), cast(@NewDate as date))
      set @RecycleDate = @NewDate

  END 

CLOSE db_cursor 

DEALLOCATE db_cursor 

select 
olddate, newdate,
CASE 
  WHEN DATEDIFF(DAY, olddate, newdate) = 0 THEN 1 
  WHEN DATEDIFF(DAY, olddate, newdate) > 0 THEN DATEDIFF(DAY, olddate, newdate)
  END AS RecyclerFrequency  

FROM #table

drop table #table

Calculate Time Difference Between Two Consecutive Rows

4 Answers4

Linked