Calculate running total for net shortage with recursive adjustment and capped surplus

Question

I'm trying to calculate the net shortage on a series of values with the following conditions (shaped into this hypothetical):

• There is a static run day quota for any given set (production cycle) of data, e.g. 30
• There is a production yield for any given day
• Depending on the yield, there could be a shortage or a surplus for the day
• Shortage accumulates through the production cycle and is offset by surplus but only up to zero
• Surplus cannot be applied if there is no shortage, i.e. surplus cannot be "banked" for future shortage

Representation of a production run:

Date	Quota	Yield	Shortage	Surplus	NetShort
01-01-2000	30	30	0	0	0
01-05-2000	30	25	-5	0	-5
01-06-2000	30	30	0	0	-5
01-09-2000	30	28	-2	0	-7
01-15-2000	30	34	0	4	-3
01-17-2000	30	30	0	0	-3
01-19-2000	30	29	-1	0	-4
01-20-2000	30	37	0	7	0
01-21-2000	30	32	0	2	0
01-24-2000	30	27	-3	0	-3
01-25-2000	30	30	0	0	-3

For each day I need to know if there was a shortage or surplus and the net shortage for the entire run. I don't need to know the daily net but provided to illustrate how it accumulates. Based on the data set, the net shortage for the run is -3.

I've tried doing the calculation in a query using SUM() OVER(ORDER BY Date) but always get hung up because applying the surplus depends on the net shortage and the net shortage depends on the surplus. What I ended up doing was to query the above data sans the NetShortage, then loop through the result on the C# side using conditional logic to modify an accumulator. This works fine but I'm really curious if there is a way to do this directly in SQL (query, not just the equivalent loop in a stored procedure)

I've tried doing the calculation in a query using SUM() OVER(ORDER BY Date) but always get hung up because applying the surplus depends on the net shortage and the net shortage depends on the surplus. What I ended up doing was to query the above data sans the NetShortage column, then loop through the result on the C# side using conditional logic to modify an accumulator. This works fine but I'm really curious if there is a way to do this directly in SQL (query, not just the equivalent loop in a stored procedure).

Answer 1

You need to use a recursive CTE to compute these values. You can do:

with
x as (
  select t.*,
    least(yield - quota, 0) as shortage,
    greatest(yield - quota, 0) as surplus,
    row_number() over(order by date) as rn
  from t
),
n as (
  select x.*, least(shortage + surplus, 0) as netshort from x where rn = 1
 union all
  select x.*, least(n.netshort + x.shortage + x.surplus, 0)
  from n
  join x on x.rn = n.rn + 1
)  
select * from n;

Result:

 Date        Quota  Yield  shortage  surplus  rn  netshort 
 ----------- ------ ------ --------- -------- --- -------- 
 2000-01-01  30     30     0         0        1    0        
 2000-01-05  30     25     -5        0        2   -5       
 2000-01-06  30     30     0         0        3   -5       
 2000-01-09  30     28     -2        0        4   -7       
 2000-01-15  30     34     0         4        5   -3       
 2000-01-17  30     30     0         0        6   -3       
 2000-01-19  30     29     -1        0        7   -4       
 2000-01-20  30     37     0         7        8    0        
 2000-01-21  30     32     0         2        9    0        
 2000-01-24  30     27     -3        0        10  -3       
 2000-01-25  30     30     0         0        11  -3       

See running example at db<>fiddle.

Note: Consider that by default SQL Server ships with a low limit in recursive iterations (maxrecursion = 100?). If that's the case, you'll need to add an extra SQL clause to increase the number of iterations as needed.

Answer 2

You can use the query below. I omit the Shortage and Surplus columns.

SELECT [Date], Quota, Yield
    , SUM(Yield - Quota) OVER(ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NetShort
FROM #MyTable

Answer 3

Quirky Update

CREATE TABLE #t
(
    [Date] date NOT NULL PRIMARY KEY
    ,Quota int NOT NULL
    ,Yield int NOT NULL
    ,Shortage int NULL
    ,Surplus int NULL
    ,NetShort int NULL
);
INSERT INTO #t ([Date], Quota, Yield)
VALUES ('2000-01-01', 30, 30)
    ,('2000-01-05', 30, 25)
    ,('2000-01-06', 30, 30)
    ,('2000-01-09', 30, 28)
    ,('2000-01-15', 30, 34)
    ,('2000-01-17', 30, 30)
    ,('2000-01-19', 30, 29)
    ,('2000-01-20', 30, 37)
    ,('2000-01-21', 30, 32)
    ,('2000-01-24', 30, 27)
    ,('2000-01-25', 30, 30);

DECLARE @check int
    ,@Anchor date
    ,@NetShort int = 0;

UPDATE T
SET Shortage = CASE WHEN Yield < Quota THEN Yield - Quota ELSE 0 END
    ,Surplus = CASE WHEN Yield > Quota THEN Yield - Quota ELSE 0 END
    ,@NetShort = CASE
                    WHEN @NetShort + Yield - Quota < 0
                    THEN @NetShort + Yield - Quota
                    ELSE 0
                END
    ,NetShort = @NetShort
    ,@check = CASE WHEN [Date] > ISNULL(@Anchor, '1900') THEN 1 ELSE 1/0 END
    ,@Anchor = [Date]
FROM #t T WITH (TABLOCKX)
OPTION (MAXDOP 1);

SELECT [Date], Quota, Yield, Shortage, Surplus, NetShort
FROM #t
ORDER BY [Date];