title: Window Functions - Running Total with reset
body: <p>I am using SQL Server 2012 to build an inventory planning / reorder engine.</p>
  
  <p>I have a bunch of dated transactions, call them credits and debits.  I want to do two things at once:</p>
  
  <ol>
  <li>Generate a Running Total (Daily net balance) </li>
  <li>Generate replenish recommendations.  Replenish will reset Running
  Total (in #1) back to zero.</li>
  </ol>
  
  <p>The table looks like this:</p>
  
  <pre><code>CREATE TABLE TX (TDate DATETIME, Qty   INT);
  
  INSERT INTO TX VALUES ('2014-03-01', 20);  
  INSERT INTO TX VALUES ('2014-03-02',-10); 
  INSERT INTO TX VALUES ('2014-03-03',-20); 
  INSERT INTO TX VALUES ('2014-03-04',-10); 
  INSERT INTO TX VALUES ('2014-03-05', 30); 
  INSERT INTO TX VALUES ('2014-03-06',-20);  
  INSERT INTO TX VALUES ('2014-03-07', 10);  
  INSERT INTO TX VALUES ('2014-03-08',-20); 
  INSERT INTO TX VALUES ('2014-03-09', -5);  
  </code></pre>
  
  <p>I am using the SQL 2012 SUM OVER() window function to show the running total of these.  </p>
  
  <pre><code>select TDate, Qty, RunningTotal, RecommendedReplenish from (
      select 
          TDate, 
          Qty, 
          SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED PRECEDING)  as RunningTotal,
          -1 * (CASE WHEN Qty &lt; 0 AND SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED     PRECEDING) &lt; 0 
                  THEN 
              CASE WHEN Qty &gt;  SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED PRECEDING)     THEN Qty ELSE SUM(Qty) OVER (ORDER                        BY TDate ROWS UNBOUNDED PRECEDING) END
          ELSE 0 END) as RecommendedReplenish
          /* Wrong, does not account for balance resetting to zero */
      from TX 
  ) T order by TDate
  </code></pre>
  
  <p>I need to find a way to reset the running total (aka RT) to zero if it dips below zero.</p>
  
  <p>My query where both Qty and RT are negative, and takes the greater (less negative) of these as the first recommended replenish.  This works correctly the first time.</p>
  
  <p>I am not sure how to deduct this from the window running total..  would like to do this in a single statement if possible.</p>
  
  <p>Here is a summary of the output I am seeking:</p>
  
  <pre><code>TDate        Qty    R.Tot  Replenish     New RT
  -----------  ----   -----  -----------  ---------
  3/1/2014     20      20                    20
  3/2/2014    -10      10                    10
  3/3/2014    -20     -10       10            0
  3/4/2014    -10     -20       10            0
  3/5/2014     30      10                    30
  3/6/2014    -20     -10                    10
  3/7/2014     10       0                    20
  3/8/2014    -20     -20                     0
  3/9/2014    - 5     -25        5            0
  </code></pre>
  
  <p>Itzik Ben-Gan, Joe Celko, or other SQL hero, are you out there? :)</p>
  
  <p>Thanks in advance!</p>