title: How to determine an Increase in Employee Salary from consecutive Contract Rows?
body: <p>I got a problem in my query :</p>
  
  <p>My table store data like this</p>
  
  <pre><code>ContractID | Staff_ID | EffectDate  | End Date   | Salary | active
  -------------------------------------------------------------------------
  1          | 1        | 2013-01-01  | 2013-12-30 | 100    | 0
  2          | 1        | 2014-01-01  | 2014-12-30 | 150    | 0
  3          | 1        | 2015-01-01  | 2015-12-30 | 200    | 1
  4          | 2        | 2014-05-01  | 2015-04-30 | 500    | 0
  5          | 2        | 2015-05-01  | 2016-04-30 | 700    | 1
  </code></pre>
  
  <p>I would like to write a query like below:</p>
  
  <pre><code>ContractID | Staff_ID | EffectDate  | End Date   | Salary | Increase
  -------------------------------------------------------------------------
  1          | 1        | 2013-01-01  | 2013-12-30 | 100    | 0
  2          | 1        | 2014-01-01  | 2014-12-30 | 150    | 50
  3          | 1        | 2015-01-01  | 2015-12-30 | 200    | 50
  4          | 2        | 2014-05-01  | 2015-04-30 | 500    | 0
  5          | 2        | 2015-05-01  | 2016-04-30 | 700    | 200  
  -------------------------------------------------------------------------
  </code></pre>
  
  <p>Increase column is calculated by current contract minus previous contract  </p>
  
  <p>I use sql server 2008 R2</p>