title: How to subtract two consecutive rows in MS SQL Server?
body: <p>A table looks like:</p>
  
  <pre><code>id    | location | datetime
  ------| ---------| --------
  CD123 | loc001   | 2010-10-21 13:30:15
  ZY123 | loc001   | 2010-10-21 13:40:15
  YU333 | loc001   | 2010-10-21 13:41:00
  AB456 | loc002   | 2011-1-21 14:30:30
  FG121 | loc002   | 2011-1-21 14:31:00
  BN010 | loc002   | 2011-1-21 14:32:00
  </code></pre>
  
  <p>Assume the table has been sorted by ascending datetime. I am trying to find the elapse (in seconds) between two consecutive rows <strong>within a location</strong>.</p>
  
  <p>The result table is supposed to be:</p>
  
  <pre><code>| location | elapse 
  | loc001   | 600  
  | loc001   | 45
  | loc002   | 30
  | loc002   | 60
  </code></pre>
  
  <p>Since the <code>id</code> is randomly generated, it is difficult to write something like <code>a.id = b.id + 1</code> in a query. And only rows within the same location is consecutively subtracted, not across different locations.</p>
  
  <p>How should I write a query in MS SQL Server to accomplish it?</p>