title: SUM() OVER With Condition Based on Current Sum Value
body: <p>I have a table (TableA) that's somewhat like this:</p>
  
  <pre><code>Id  Type    TimeBlock   Value
  1   1       1           100
  2   1       1           150
  3   2       1           100
  4   1       1           1000
  5   1       1           100
  6   1       2           50
  6   1       2           50
  </code></pre>
  
  <p>I'd like to be able to take the <code>SUM</code> of the Value column partitioning it by TimeBlock, however I'd like to only <code>SUM</code> the row based upon some condition. The condition is to only <code>SUM</code> the row if the current sum value 
  is greater than or equal to the Value column</p>
  
  <p>I assume the query would start with something like:</p>
  
  <pre><code>DECLARE @Amount int = 500;
  SELECT *, @Amount - SUM (Value) OVER (PARTITION BY TimeBlock ORDER BY Id) AS Sum
  FROM TableA
  </code></pre>
  
  <p>Which I'd like the result to look like:</p>
  
  <pre><code>Id  Type    TimeBlock   Value   Sum
  1   1       1           100     400     (500 - 100)
  2   1       1           150     250     (400 - 150)
  3   2       1           1000    NULL    (This does not count since its of type = 2 and 250 &lt; 1000)
  4   1       1           1000    0       (250 - 1000 = -750 there its filled the @Amount so we'll leave it at 0)
  5   1       1           100     NULL    (Since @Amount has been filled anything that comes after in this time block is NULL)
  6   1       2           50      450     (500 - 50)
  6   1       2           50      400     (450 - 50)
  </code></pre>
  
  <p>As you can see I need to be able to get the current values of the <code>SUM</code> function to be able to compare it to the Value column which I'm not so sure how to do. I'd like to be able to do something like </p>
  
  <pre><code>DECLARE @Amount int = 500;
  SELECT *, @Amount - SUM (CASE WHEN Type = 2 AND SUM(Value) OVER (PARTITION BY TimeBlock ORDER BY Id) &lt; Value THEN 0 ELSE Value END) OVER (PARTITION BY TimeBlock ORDER BY Id) AS Sum
  FROM TableA
  </code></pre>
  
  <p><strong>EDIT</strong>
  Type = 1 - sum the Value column no matter what
  Type = 2 - only sum the Value column in the row if the current sum value is greater than or equal to the Value</p>