title: Calculate date difference based on column value on same table
body: <p>I have the following table structure with ID, State and Date</p>
  
  <pre><code>20130323000004  Final       3/23/2013
  20130323000004  Final       3/25/2013
  20130323000004  Final       3/26/2013
  20130323000004  Needs Review    4/5/2013
  20130323000004  Final       4/10/2013
  20130323000004  Final       5/1/2013
  </code></pre>
  
  <p>and I would need the result like</p>
  
  <pre><code>20130323000004  Final       3/23/2013     1
  20130323000004  Final       3/25/2013     2
  20130323000004  Final       3/26/2013     3
  20130323000004  Needs Review    4/5/2013      1
  20130323000004  Final       4/10/2013     1
  20130323000004  Final       5/1/2013      21
  </code></pre>
  
  <p>where the last column is the difference between the two records based on the state column.</p>
  
  <p>I tried doing a self join with max and min but not able to get the proper count. Can you please help me on calculating the sum in the SQL?</p>
  
  <p>Thanks</p>
  
  <p>Shankar.</p>