Find the time difference between two consecutive rows in the same table in sql

Vote count:

0

Let me tell ya, so much help here, my skill set is rapidly increasing. Thank you all for your help!!

MSSMS 2008 R2 Database is SQL 2008 SP1

However, I'm stuck. I've looked for an answer, but can't seem to find subtracting time in the same table from two different rows of the same table that fits. I'm having a difficult time with the following query. In the table below, I want to differentiate the TimeOut from one row to the TimeIn of the next row. Consider in the following table of finding the difference in minutes between the TimeOut in Row 1 (10:35am) and the TimeIn in Row 2 (10:38am).

Table 1: TIMESHEET

ROW    EmpID       TimeIn                   TimeOut
1       138         2014-01-05 10:04:00      2014-01-05 10:35:00   
2       138         2014-01-05 10:38:00      2014-01-05 10:59:00 
3       138         2014-01-05 11:05:00      2014-01-05 11:30:00  

Expected results

ROW    EmpID       TimeIn                   TimeOut                  Minutes
1       138         2014-01-05 10:04:00      2014-01-05 10:35:00       
2       138         2014-01-05 10:38:00      2014-01-05 10:59:00       3
3       138         2014-01-05 11:05:00      2014-01-05 11:30:00       6
etc
etc
etc

Basically, I need to differentiate the times in the query to show how long employees where on break. I've tried doing a join, but that doesn't seem to work and I don't know if OVER with PARTITION is the way to go, because I cannot seem to follow the logic (Yeah, I'm still learning). I also considering two temp tables and comparing them, but that doesn't work when I start changing days or employee ID's. Finally, I am thinking maybe LEAD in an OVER statement? Or is it just simple to do a DATEDIFF with a CAST?

Any help would be greatly appreciated!! Thanks in advance.

asked 1 min ago

NWHikerOR

7

Find the time difference between two consecutive rows in the same table in sql