title: Group rows by condition
body: <p>I have this data:</p>
  
  <pre><code>Start   End     Quantity
  425     449     24
  450     474     24
  475     499     24
  500     524     24
  2300    2324    24
  2400    2499    99
  2500    2599    99
  2800    2899    99
  2900    2999    99
  3200    3249    49
  3250    3299    49
  3300    3349    49
  3350    3399    49
  3400    3449    49
  3500    3549    49
  3600    3624    24
  3650    3674    24
  3700    3724    24
  3950    3964    14
  4000    4000    0
  4150    4399    249
  4400    4499    99
  5034    5075    41
  </code></pre>
  
  <p>Quantity is a result of <code>End - Start</code>.</p>
  
  <p>I would like to obtain the following data, the <code>Generated</code> rows:</p>
  
  <pre><code>Start   End     Quantity
  425     449     24
  450     474     24
  475     499     24
  500     524     24
  425     524     96
  2300    2324    24
  2300    2324    24
  2400    2499    99
  2500    2599    99
  -----GENERATED----
  425     2599    438
  ------------------
  
  2800    2899    99
  2900    2999    99
  3200    3249    49
  3250    3299    49
  3300    3349    49
  3350    3399    49
  3400    3449    49
  3500    3549    49
  -----GENERATED-----
  2800    3549    492
  ------------------
  
  3600    3624    24
  3650    3674    24
  3700    3724    24
  3950    3964    14
  4000    4000    0
  4150    4399    249
  4400    4499    99
  5034    5075    41
  -----GENERATED-----
  3600    5075    475
  ------------------
  </code></pre>
  
  <p>The condition is that it has to sum all the quantities until 500. If it passes 500 do a new count. </p>
  
  <p>I have tried with Rollup but I couldnt find the right condition to make it work. </p>
  
  <p>Of course, this is way easier to do by programming code instead of SQL, but we must do it in database environment. The tools to get the generated rows can be anything, looping functions, new tables etc. </p>
  
  <p><strong>Error solving</strong></p>
  
  <p>I got into an error while running @Prdp's query:</p>
  
  <pre><code>Msg 530, Level 16, State 1, Line 1
  The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
  </code></pre>
  
  <p>I found the solution here:
  <a href="http://sqlhints.com/tag/the-statement-terminated-the-maximum-recursion-100-has-been-exhausted-before-statement-completion/" rel="nofollow noreferrer">http://sqlhints.com/tag/the-statement-terminated-the-maximum-recursion-100-has-been-exhausted-before-statement-completion/</a></p>
  
  <p><strong>Update 1</strong></p>
  
  <p>Using @Prdp's query we got the following:</p>
  
  <pre><code>Start   End     rn  st
  (400)   424     1   24
  425     449     2   48
  450     474     3   72
  475     499     4   96
  500     524     5   120
  2300    2324    6   144
  2400    2499    7   243
  2500    2599    8   342
  2800    (2899)  9   (441)  
  (2900)  2999    10  99
  3200    3249    11  148
  3250    3299    12  197
  3300    3349    13  246
  3350    3399    14  295
  3400    3449    15  344
  3500    3549    16  393
  3600    3624    17  417
  3650    3674    18  441
  3700    3724    19  465
  3950    3964    20  479
  4000    (4000)  21  (479) 
  (4150)  4399    22  249
  4400    4499    23  348
  5034    (5075)  24  (389) 
  </code></pre>
  
  <p>Its getting closer to what we need. Would it be possible to extract only the data in between <code>(</code> and <code>)</code> while discarding the other data?
  We can use cursors too. </p>