Calculating time between records in sql

Question

Guys, I have a table with a column called time. It captures the recording time of each record in the database. I want to query and return another column showing the duration between one record and the record before it. For example, if I store a record for john today at 12:00, and then Ali at 13:10, I need another column that will show 01:10:00 (i.e. HH: MM: SS).

As far as I understand, I can query each column number as follows.

SELECT ROW_NUMBER() OVER (ORDER BY [followuptime]) from [dbo].[FollowUp] . 

I needed to request the maximum number of AS lines, but it fails and returns the error "windowed ...."

 SELECT MAX(ROW_NUMBER() OVER (ORDER BY [followuptime])) from [dbo].[FollowUp] . 

I wanted to use the DATEDIFF(interval,start_time,end_time); function DATEDIFF(interval,start_time,end_time); for sql, but as of now, I'm stuck. Please rate your help or any other alternative.

Answer 1

Since SQL-Server 2008R2 does not support LAG / LEAD, you need to make a standalone connection using row_number to get the time from the previous row:

 WITH OrderedResults AS ( SELECT [id], [followuptime], [remark], RowNumber = ROW_NUMBER() OVER (ORDER BY [followuptime]) FROM [dbo].[FollowUp] ) SELECT a.ID, a.FollowUpTime, a.Remark, PreviousTime = b.FollowUpTime, MinutesDifference = DATEDIFF(MINUTE, b.FollowUpTime, a.FollowUpTime) FROM OrderedResults a LEFT JOIN OrderedResults b ON b.RowNumber = a.RowNumber - 1 ORDER BY a.FollowUpTime; 

SQL script example

Answer 2

You cannot apply MAX to ROW_NUMBER. Use CTE and request it.

 ;WITH MyCTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY [followuptime]) AS RowNum FROM [dbo].[FollowUp] ) SELECT MAX(RowNum) FROM MyCTE