SQL Select statements that return the difference in minutes from the next datetime from the same table
I have a table of user actions. I want to see the difference between each process in minutes. More specifically, it is partial data from a table.
Date |TranType| Prod | Loc
-----------------------------------------------------------
02/27/12 3:17:21 PM | PICK | LIrishGreenXL | E01C015
02/27/12 3:18:18 PM | PICK | LAntHeliconiaS | E01A126
02/27/12 3:19:00 PM | PICK | LAntHeliconiaL | E01A128
02/27/12 3:19:07 PM | PICK | LAntHeliconiaXL | E01A129
I want to get the time difference in minutes between the first and second process than the second and third and .... Thank you
+3
source to share
1 answer
Something like this will work in MS SQL, just change the field names to match yours:
select a.ActionDate, datediff(minute,a.ActionDate,b.ActionDate) as DiffMin (select ROW_NUMBER() OVER(ORDER BY ActionDate) AS Row, ActionDate from [dbo].[Attendance]) a inner join (select ROW_NUMBER() OVER(ORDER BY ActionDate) AS Row, ActionDate from [dbo].[Attendance]) b on a.Row +1 = b.Row
+1
source to share