title: Running Totals Skipping Over Certain Values
body: <p>I need to calculate a column that has a value column subtracted from a Total column but can skip rows until it can no longer find a smaller value.  The sequence relates to dates so the order must be preserved. The value (Need) cannot be larger than the total as those are deleted prior.</p>
  
  <p>This is for SQL Server 2016.  My initial thought process was to use window functions and a running total, but I cannot figure out how to skip the 400 and continue to the 2 rows below. I included my attempts in the CASE statement as TransferQty and the running total as ReferenceCol. </p>
  
  <p>Code to reproduce:</p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE @i TABLE
  (
      sequence INT IDENTITY(1,1)
      ,Total INT
      ,Need INT
  )
  INSERT INTO @i 
  VALUES (500,100)
  ,(500,200)
  ,(500,50)
  ,(500,400)
  ,(500,50)
  ,(500,50)
  
  SELECT 
       sequence
      ,Total
      ,Need
      ,CASE 
          WHEN Total - SUM(Need) OVER (ORDER BY sequence) &gt; 0 
          THEN Need 
      ELSE 0 
      END AS TransferQty
      ,Total - SUM(Need) OVER (ORDER BY sequence) as ReferenceCol
  FROM @i
  </code></pre>
  
  <p>Current Results</p>
  
  <pre><code>+----------+-------+------+-------------+--------------+
  | Sequence | Total | Need | TransferQty | ReferenceCol |
  +----------+-------+------+-------------+--------------+
  |        1 |   500 |  100 |         100 |          400 |
  |        2 |   500 |  200 |         200 |          200 |
  |        3 |   500 |   50 |          50 |          150 |
  |        4 |   500 |  400 |           0 |         -250 |
  |        5 |   500 |   50 |           0 |         -300 |
  |        6 |   500 |   50 |           0 |         -350 |
  +----------+-------+------+-------------+--------------+
  </code></pre>
  
  <p>Desired Results</p>
  
  <pre><code>+----------+-------+------+-------------+--------------+
  | Sequence | Total | Need | TransferQty | ReferenceCol |
  +----------+-------+------+-------------+--------------+
  |        1 |   500 |  100 |         100 |          400 |
  |        2 |   500 |  200 |         200 |          200 |
  |        3 |   500 |   50 |          50 |          150 |
  |        4 |   500 |  400 |           0 |          150 | --skip calc
  |        5 |   500 |   50 |          50 |          100 |
  |        6 |   500 |   50 |          50 |           50 |
  +----------+-------+------+-------------+--------------+
  </code></pre>