sql - Window Functions - Running Total with reset

Question

I am using SQL Server 2012 to build an inventory planning / reorder engine.

I have a bunch of dated transactions, call them credits and debits. I want to do two things at once:

1. Generate a Running Total (Daily net balance)
2. Generate replenish recommendations. Replenish will reset Running Total (in #1) back to zero.

The table looks like this:

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);  

I am using the SQL 2012 SUM OVER() window function to show the running total of these.

select TDate, Qty, RunningTotal, RecommendedReplenish from (
    select 
        TDate, 
        Qty, 
        SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED PRECEDING)  as RunningTotal,
        -1 * (CASE WHEN Qty < 0 AND SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED     PRECEDING) < 0 
                THEN 
            CASE WHEN Qty >  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

I need to find a way to reset the running total (aka RT) to zero if it dips below zero.

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.

I am not sure how to deduct this from the window running total.. would like to do this in a single statement if possible.

Here is a summary of the output I am seeking:

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

Itzik Ben-Gan, Joe Celko, or other SQL hero, are you out there? :)

Thanks in advance!

Answer 1

This can be done using a set-based solution:

1.Compute the normal running total (call it RT)

2.Compute the running minimum of RT (call it MN)

When MN is negative, -MN is the total quantity you had to replenish so far. Let replenish_rt be -MN when MN is negative. So, the new running total (call it new_rt) is rt + replenish_rt. And if you need to return the current replenish quantity needed, subtract the pervious replenish_rt (using LAG) from the current.

Here's the complete solution query:

with c1 as
(
  select *,
    sum(qty) over(order by tdate rows unbounded preceding) as rt
  from tx
),
c2 as
(
  select *,
    -- when negative, mn is the total qty that had to be
    -- replenished until now, inclusive
    min(rt) over(order by tdate rows unbounded preceding) as mn_cur
  from c1
)
select tdate, qty, rt,
  replenish_rt - lag(replenish_rt, 1, 0) over(order by tdate) as replenish,
  rt + replenish_rt as new_rt
from c2
  cross apply(values(case when mn_cur < 0 then -mn_cur else 0 end)) as a1(replenish_rt);

Cheers, Itzik