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

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:

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

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.