title: How can I find consecutive active weeks in SQL?
body: <p>What I would like to do is find the number of consecutive weeks that someone is active on Sundays and assign them a value. They have to participate in at least 2 races a day to be counted as active for the week. </p>
  
  <p>If they are active for 2 consecutive weeks I would like to assign a value of 100, 3 consecutive weeks a value of 200, 4 consecutive weeks a value of 300, and continuing up to 9 consecutive weeks. </p>
  
  <p>My difficulty is not determining consecutive weeks, but breaks in between consecutive dates. Suppose the following dataset:</p>
  
  <pre><code>CustomerID  RaceDate    Races
  1           2/2/2014    2
  1           2/9/2014    5
  1           2/16/2014   3
  1           2/23/2014   3
  1           3/2/2014    4
  1           3/9/2014    3
  1           3/16/2014   3
  2           2/2/2014    2
  2           2/9/2014    3
  2           3/2/2014    2
  2           3/9/2014    4  
  2           3/16/2014   3
  </code></pre>
  
  <p>CustomerID 1 would have 7 consecutive weeks for a value of 600.</p>
  
  <p>The hard part for me is CustomerID 2. They would have 2 consecutive weeks AND 3 consecutive weeks. So their total value would be 100 + 200 = 300.</p>
  
  <p>I would like to be able to do this with any different combination of consecutive weeks.</p>
  
  <p>Any help please?</p>
  
  <p>EDIT: I am using SQL Server 2008 R2.</p>