23

I would like to query a database using sql to show the difference in time between id 1,2,3 and so on. basically it will compare the row below it for all records. any help would be appreciated. 

IDCODE  DATE TIME        DIFFERENCE (MINS)
1      02/03/2011 08:00        0
2      02/03/2011 08:10        10
3      02/03/2011 08:23        13
4       02/03/2011 08:25        2
5       02/03/2011 09:25        60
6       02/03/2011 10:20        55
7       02/03/2011 10:34        14

Thanks!

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
Tyrone2011
  • 239
  • 1
  • 2
  • 3

3 Answers3

34

If using SQL Server, one way is to do:

DECLARE @Data TABLE (IDCode INTEGER PRIMARY KEY, DateVal DATETIME)
INSERT @Data VALUES (1, '2011-03-02 08:00')
INSERT @Data VALUES (2, '2011-03-02 08:10')
INSERT @Data VALUES (3, '2011-03-02 08:23')
INSERT @Data VALUES (4, '2011-03-02 08:25')
INSERT @Data VALUES (5, '2011-03-02 09:25')
INSERT @Data VALUES (6, '2011-03-02 10:20')
INSERT @Data VALUES (7, '2011-03-02 10:34')

SELECT t1.IDCode, t1.DateVal, ISNULL(DATEDIFF(mi, x.DateVal, t1.DateVal), 0) AS Mins
FROM @Data t1
    OUTER APPLY (
        SELECT TOP 1 DateVal FROM @Data t2 
        WHERE t2.IDCode < t1.IDCode ORDER BY t2.IDCode DESC) x

Another way is using a CTE and ROW_NUMBER(), like this:

;WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY IDCode) AS RowNo, IDCode, DateVal FROM @Data)

SELECT t1.IDCode, t1.DateVal, ISNULL(DATEDIFF(mi, t2.DateVal, t1.DateVal), 0) AS Mins
FROM CTE t1
    LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo + 1
ORDER BY t1.IDCode
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • 1
    I like the first one, it saved me a lot of hassle, but my experience with CTE's is not good. With large tables they are too much of a resource hit on a database. – stonypaul Aug 12 '15 at 11:59
  • Is there a way to add the count if the IDCODE is same for 2 dates and get the diff between those two rows with the same IDCODE and not the incremental way? For example: Let's assume, the first two dates have IDCODE of 1, so, I want to get the datediff between only those 2 rows. – Nitin Deb Mar 19 '20 at 19:04
19

Standard ANSI SQL solution. Should work in PostgreSQL, Oracle, DB2 and Teradata:

SELECT idcode, 
       date_time, 
       date_time - lag(date_time) over (order by date_time) as difference
FROM your_table
1

As @a_horse_with_no_name mentioned using the lag()

I wanted in millisecond what the diff was.

Select idcode,
       datetime,
       Difference = datediff(millisecond, lag(convert(datetime2,datetime)) over (order by convert(datetime2,datetime)),convert(datetime2,datetime))
From your_table

in my case I needed to convert a string to a datetime2

Timmy B
  • 11
  • 1