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?

Answer 1

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