Running total in SQL with a reset condition

Question

I have a data that combines inventory, sales forecast, and future supply data for different parts. I have combined it to give me a table that gives a net quantity each month for each part. See below:

Date	Part	Net Quantity
30/06/2021	A	1000
31/07/2021	A	-150
31/08/2021	A	-200
30/09/2021	A	-500
31/10/2021	A	-200
30/11/2021	A	-200
31/12/2021	A	50
30/06/2021	B	100
31/07/2021	B	-80
31/08/2021	B	20
30/09/2021	B	-30
31/10/2021	B	-35
30/11/2021	B	-40
31/12/2021	B	-150

I need to create a running total view that partitions by a part number and resets in the next month if it goes below 0. The starting point for the running total needs to be 0 if the previous month's closing inventory was negative.

My expected outcome is this:

Date	Part	Net Quantity	Closing Inventory (Expected Outcome)
30/06/2021	A	1000	1000
31/07/2021	A	-150	850
31/08/2021	A	-200	650
30/09/2021	A	-500	150
31/10/2021	A	-200	-50
30/11/2021	A	-200	-200
31/12/2021	A	50	50
30/06/2021	B	100	100
31/07/2021	B	-80	20
31/08/2021	B	20	40
30/09/2021	B	-30	10
31/10/2021	B	-35	-25
30/11/2021	B	-40	-40
31/12/2021	B	-150	-150

My current code is:

SELECT 
Date, 
Part, 
Net_Quantity, 
sum(Net_Quantity) over (partition by Part order by date) 'Closing_Inventory' 
FROM grouped

I am unsure how to make a condition based on the last value in the running total, the lag function is not able to view the previous row of the running total.

Answer 1

It would have been easier if you had provided some sample data, but here is how I would solve this problem:

Declare @testData Table ([Date] date, Part char(1), NetQuantity int);
 Insert Into @testData ([Date], Part, NetQuantity)
 Values ('2021-06-30', 'A', 1000)
      , ('2021-07-31', 'A', -150)
      , ('2021-08-31', 'A', -200)
      , ('2021-09-30', 'A', -500)
      , ('2021-10-31', 'A', -200)
      , ('2021-11-30', 'A', -200)
      , ('2021-12-31', 'A',   50)
      , ('2021-06-30', 'B',  100)
      , ('2021-07-31', 'B',  -80)
      , ('2021-08-31', 'B',   20)
      , ('2021-09-30', 'B',  -30)
      , ('2021-10-31', 'B',  -35)
      , ('2021-11-30', 'B',  -40)
      , ('2021-12-31', 'B', -150);

   With runningTotal
     As (
 Select *
      , Inventory = sum(td.NetQuantity) over(Partition By td.Part Order By td.[Date])
   From @testData               td      
        )
 Select [Date]
      , Part
      , NetQuantity
      , ClosingInventory = iif(lag(Inventory, 1, 0) over(Partition By Part Order By [Date]) < 0, NetQuantity, Inventory)
   From runningTotal;