SQL query to get DateDiff of the last two records

Question

I have a table called Event with eventNum as the primary key and date as a datetime2(7) in SQL Server 2008 R2. I am trying to get the date of the last two rows in the table and get the difference in minutes. This is what I currently have:

Select DATEDIFF(MI, e.date,(Select e2.date from Event e2 where eventNum = (Select MAX(e2.eventNum))))
    From Event e
    Where eventNum = (Select MAX(e.eventNum)-1 from e)

and I get this error:

Invalid column name 'Select eventNum from Event Where eventNum = Select MAX(eventNum) from Event'.

I've changed this 100 times and can't get it to work. Any help?

Tim Schmelter · Answer 1 · 2012-11-01T22:47:02.220

6

You could use ROW_NUMBER

WITH CTE AS 
(
   SELECT RN = ROW_NUMBER() OVER (ORDER BY eventNum DESC)
        , date
   FROM Event 
)
SELECT Minutes = DATEDIFF(minute, 
           (SELECT date FROM CTE WHERE RN = 2),
           (SELECT date FROM CTE WHERE RN = 1))

Fiddle: http://www.sqlfiddle.com/#!3/3e9c8/17/0

edited Nov 01 '12 at 22:47

answered Nov 01 '12 at 21:42

Tim Schmelter

450,073
74
686
939

you could add a `top 1` clause. Now it repeats the result for every row present in the table – Cristian Lupascu Nov 01 '12 at 21:43
@w0lf: or remove the last `FROM CTE` – ypercubeᵀᴹ Nov 01 '12 at 21:44
The CTE is like a sledgehammer to crack a nut. You can tell by the query plan it produces. Also, the -1 was for when the answer was wrong, which you've since corrected. – RichardTheKiwi Nov 01 '12 at 23:16

RichardTheKiwi · Answer 2 · 2012-11-01T22:22:43.983

3

This doesn't have to go through the table twice like Tim's answer.

select datediff(mi, min(x.date), max(x.date))
from (
    select top(2) *
    from Event e
    order by eventNum desc
) x

enter image description here

Assuming you always have 2 records or more, and the time is monotonously increasing, then the above works.

If it has only one record, it returns 0 (since max=min=singular record).
If your times are not monotonously increasing, there's a simple tweak to this query

e.g.

select top(1) datediff(mi, x.date, y.date)
from event x
join event y on y.eventnum < x.eventnum
order by x.eventnum desc, y.eventnum desc

edited Nov 01 '12 at 22:22

answered Nov 01 '12 at 22:06

RichardTheKiwi

105,798
26
196
262

1

Nice, but the `date` may not be in the same order as `eventNum`. If the sign of the dates' difference matters, this is wrong. If it doesn't, it's correct. – ypercubeᵀᴹ Nov 01 '12 at 22:09
I'm not sure if a millisecond more or less is a key factor for this quesstion when OP just wants a single value, all the more because the order column is the primary key column. – Tim Schmelter Nov 01 '12 at 22:51

SQL query to get DateDiff of the last two records

2 Answers2

Linked