title: Group records with incrementing count based on flag
body: <p>Take two...</p>
  
  <p>Actual data:</p>
  
  <pre><code>division    ID          date            flag
  ABC123      ZZZ123      1/17/2013       Y
  ABC123      ZZZ123      1/25/2013       N
  ABC123      ZZZ123      2/22/2013       Y
  ABC123      ZZZ123      2/26/2013       N
  ABC123      YYY222      3/20/2013       Y
  ABC123      YYY222      5/17/2013       N
  XYZ456      ZZZ999      1/15/2012       N
  XYZ456      ZZZ999      1/30/2012       N
  XYZ456      ZZZ123      2/09/2012       N
  XYZ456      ZZZ123      4/13/2012       Y
  XYZ456      ZZZ123      6/23/2012       N
  XYZ456      ZZZ123      10/5/2012       Y
  XYZ456      ZZZ123      11/18/2012      N
  </code></pre>
  
  <p>I need to build a new column, ORDER_group, that will populate based on the following rules:</p>
  
  <ul>
  <li>Each division and ID combination is considered a "group", sorted by date, and should have an ORDER_group (starting with 1) assigned to it.</li>
  <li>Each time a "group" encounters a flag of "Y", it should increment the ORDER_group by 1.</li>
  <li>If the "group" starts (first record with the earliest date) with a flag = "N", it should still start with ORDER_group = 1.</li>
  <li>If the "group" starts (first record with the earliest date) with a flag = "Y", it should still start with ORDER_group = 1.</li>
  <li>Each subsequent record should be the same ORDER_group number, unless a new "group" (division/ID) is encountered, at which, it should reset back to 1, or the next flag = "Y" is encountered.</li>
  </ul>
  
  <p>Expected results:</p>
  
  <pre><code>division    ID          date            flag    ORDER_group
  ABC123      ZZZ123      1/17/2013       Y       1
  ABC123      ZZZ123      1/25/2013       N       1
  ABC123      ZZZ123      2/22/2013       Y       2
  ABC123      ZZZ123      2/26/2013       N       2
  ABC123      YYY222      3/20/2013       Y       1
  ABC123      YYY222      5/17/2013       N       1
  XYZ456      ZZZ999      1/15/2012       N       1
  XYZ456      ZZZ999      1/30/2012       N       1
  XYZ456      ZZZ123      2/09/2012       N       1
  XYZ456      ZZZ123      4/13/2012       Y       2
  XYZ456      ZZZ123      6/23/2012       N       2
  XYZ456      ZZZ123      10/5/2012       Y       3
  XYZ456      ZZZ123      11/18/2012      N       3
  </code></pre>
  
  <p>Ideally this should be accomplished without a loop/cursor, unless there are performance reasons with CTE/temp tables.  What is the best way to populate this new column?</p>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p>SQL Fiddler for Actual data: <a href="http://sqlfiddle.com/#!3/5cca0/2" rel="nofollow">http://sqlfiddle.com/#!3/5cca0/2</a></p>