'Running total in SQL with a reset condition

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.

sqlsql-serverinventory-management


Solution 1:[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;

Solution 2:[2]

What about something like this...

--Add a column...
ALTER TABLE PartsInventory ADD RowNumWithinPart int;

--Assign row numbers partitioned by Part...
UPDATE PartsInventory
SET RowNumWithinPart = x.rownum
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY Part ORDER BY [Date]) as rownum, *
    FROM PartsInventory
) x
WHERE x.Part = PartsInventory.Part AND x.Date = PartsInventory.Date

--Recursive CTE solution:
WITH InvTotalsCTE AS
(
    SELECT [Date], RowNumWithinPart, Part, Net_Quantity, Net_Quantity as Closing_Inventory
    FROM PartsInventory
    WHERE RowNumWithinPart = 1

    UNION ALL

    SELECT Q.[Date], Q.RowNumWithinPart, Q.Part, Q.Net_Quantity, iif(P.Closing_Inventory < 0, 0, P.Closing_Inventory) + Q.Net_Quantity as Closing_Inventory
    FROM InvTotalsCTE as P
        JOIN PartsInventory Q ON (Q.RowNumWithinPart - 1) = P.RowNumWithinPart AND Q.Part = P.Part
)
SELECT [Date], Part, RowNumWithinPart, Net_Quantity, Closing_Inventory
FROM InvTotalsCTE
ORDER BY Part, RowNumWithinPart

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jeff
Solution 2 General Grievance

Related Questions