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

sql, sql-server, sql-server-2008, ssms

Solution

I have solved this for similar problems and it need not be that the rows even be sorted:

select t1.EmpID, t1.TimeIn, t1.TimeOut, 
       datediff(minute, max(t2.TimeOut), t1.TimeIn) as minutes
from timesheet t1 left join timesheet t2 on t1.EmpID = t2.EmpID 
       and t2.TimeOut < t1.TimeIn
group by t1.EmpID, t1.TimeIn, t1.TimeOut

Let me know if this works.

Here is a sql fiddle: http://sqlfiddle.com/#!3/89a43/1

Problem

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 were 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`?

Original source