title: How can i get a gap from a history table for repeating groups over time
body: <p>I have a historical table with data like as bellow :</p>
  <pre><code>SK ID STATUS EFF_DT EXP_DT 
  1 486007909 APP 7/22/2009  8/22/2009
  2 486007909 APP 8/22/2009  10/01/2009
  3 486007909 CAN 10/01/2009 11/01/2009
  4 486007909 CAN 11/02/2009 12/12/2009
  5 486007909 APP 12/12/2009 NULL
  </code></pre>
  <p>The <code>EXP_DT is null</code> mean that the row is active.</p>
  <p>I want to return a group of data each time <code>Status</code> changes</p>
  <p>The expected result like as bellow :</p>
  <pre><code>SK ID STATUS EFF_DT EXP_DT                 GAP
  1 486007909 APP 7/22/2009  8/22/2009        1
  2 486007909 APP 8/22/2009  10/01/2009       1
  3 486007909 CAN 10/01/2009 11/01/2009       2
  4 486007909 CAN 11/02/2009 12/12/2009       2
  5 486007909 APP 12/12/2009 NULL             3
  </code></pre>
  <p>Thanks for help !</p>