title: Find the time difference between two consecutive rows in the same table in sql
body: <p>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).</p>
  
  <p>Table 1: <code>TIMESHEET</code></p>
  
  <pre><code>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  
  </code></pre>
  
  <p>Expected results</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Basically, I need to differentiate the times in the query to show how long employees were on break.</p>
  
  <p>I've tried doing a join, but that doesn't seem to work and I don't know if <code>OVER</code> with <code>PARTITION</code> 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 <code>LEAD</code> in an <code>OVER</code> statement? Or is it just simple to do a <code>DATEDIFF</code> with a <code>CAST</code>?</p>