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

Answer 2

Ugh, based on your comments, the only thing I can think to do is use a cursor, which I hate doing.

SQL Fiddle

declare @Date date
declare @Qty int
declare @RR int


declare @running int  = 0

declare @results table
(dt date,
 qty int,
 rt int,
 rr int
)

declare C cursor for
select TDate, Qty,
RecommendedReplenish 
from (
    select 
        TDate, 
        Qty,
        -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

open c
fetch next from c into @date,@qty,@rr
WHILE @@FETCH_STATUS = 0
BEGIN



  set @running = @running + @qty
  if @running <0
    begin
      set @running = 0
    end

  insert into @results values (@date,@qty,@running,@rr)

  fetch next from c into @date,@qty,@rr
end
close c
deallocate c
select
*
from @results

Which as far as I can tell, gives you the desired result. It ain't pretty, I'm sure it could use some cleanup, but it works.

+-------------+------+-----+----+
|     DT      | QTY  | RT  | RR |
+-------------+------+-----+----+
| 2014-03-01  |  20  | 20  |  0 |
| 2014-03-02  | -10  | 10  |  0 |
| 2014-03-03  | -20  |  0  | 10 |
| 2014-03-04  | -10  |  0  | 10 |
| 2014-03-05  |  30  | 30  |  0 |
| 2014-03-06  | -20  | 10  | 10 |
| 2014-03-07  |  10  | 20  |  0 |
| 2014-03-08  | -20  |  0  | 20 |
| 2014-03-09  |  -5  |  0  |  5 |
+-------------+------+-----+----+

Answer 3

Using a temp-table you could apply the Replenishment as you go. Not sure if it would be much faster than the cursor approach from @Andrew; probably depends on how often the RT dips below zero. I used a simple subquery to calculate the RT, less typing, same result although I agree it takes an extra step.

SQL Fiddle

CREATE TABLE TX (TDate DATETIME, Qty   INT, Replenish INT NULL, RT INT NULL);

INSERT INTO TX VALUES ('2014-03-01', 20, NULL, NULL);  
INSERT INTO TX VALUES ('2014-03-02',-10, NULL, NULL); 
INSERT INTO TX VALUES ('2014-03-03',-20, NULL, NULL); 
INSERT INTO TX VALUES ('2014-03-04',-10, NULL, NULL); 
INSERT INTO TX VALUES ('2014-03-05', 30, NULL, NULL); 
INSERT INTO TX VALUES ('2014-03-06',-20, NULL, NULL);  
INSERT INTO TX VALUES ('2014-03-07', 10, NULL, NULL);  
INSERT INTO TX VALUES ('2014-03-08',-20, NULL, NULL); 
INSERT INTO TX VALUES ('2014-03-09', -5, NULL, NULL);
GO

-- calculate (real) running-totals
UPDATE TX 
   SET RT = (SELECT SUM(p.Qty)
               FROM TX p
              WHERE p.TDate <= upd.TDate)
  FROM TX upd
GO

-- create a loop to find if there are negative RT's and fix them untill there are none left
DECLARE @below_zero_date DATETIME,
        @below_zero_value INT

-- SELECT * FROM TX ORDER BY TDate

SELECT @below_zero_value = NULL
SELECT TOP 1 @below_zero_date = TDate,
             @below_zero_value = RT
  FROM TX
 WHERE RT < 0
 ORDER BY TDate

WHILE @below_zero_value IS NOT NULL
    BEGIN
        UPDATE TX
           SET RT = RT - @below_zero_value,
               Replenish = (CASE TDate WHEN @below_zero_date THEN - @below_zero_value ELSE NULL END)
         WHERE TDate >= @below_zero_date

        -- SELECT * FROM TX ORDER BY TDate

        SELECT @below_zero_value = NULL

        SELECT TOP 1 @below_zero_date = TDate,
                     @below_zero_value = RT
          FROM TX
         WHERE RT < 0
           AND TDate > @below_zero_date
         ORDER BY TDate
    END

SELECT * FROM TX ORDER BY TDate

UPDATE: added AND TDate > @below_zero_date as (minor) improvement; it will only have a significant effect when there is 'quite a bit' of data in the table.