We are going to discuss two scenarios where we might need to reset the values of running totals.
But first please create the practice table and data.
--DROP TABLE INVENTORY;
CREATE TABLE Inventory (TransactionDate DATE, ProductID INT , Qty INT);
INSERT INTO Inventory VALUES ('2022-09-01', 1, 100);
INSERT INTO Inventory VALUES ('2022-09-02', 1, 200);
INSERT INTO Inventory VALUES ('2022-09-03',1, -500);
INSERT INTO Inventory VALUES ('2022-09-04', 1, 150);
INSERT INTO Inventory VALUES ('2022-09-05', 1, 400);
INSERT INTO Inventory VALUES ('2022-09-06', 1, 250);
INSERT INTO Inventory VALUES ('2022-09-07', 1,-850);
INSERT INTO Inventory VALUES ('2022-09-08', 1, 600);
INSERT INTO Inventory VALUES ('2022-09-01', 2, 150);
INSERT INTO Inventory VALUES ('2022-09-02', 2, -200);
INSERT INTO Inventory VALUES ('2022-09-04', 2, 200);
INSERT INTO Inventory VALUES ('2022-09-05', 2, 500);
INSERT INTO Inventory VALUES ('2022-09-06', 2, 500);
Scenario 1 - Reset running total based on some column value -
We are going to reset the running total on every 5th of the Month.
Here is the SQL Query -
-- Reset Running Total on every 5th of the month
With ResetFlag as
(Select *,
Case When datepart(d,TransactionDate) = 5 then 1 else 0 end as Reset_Flag
from Inventory)
, GroupCTE as
(Select *, SUM(Reset_Flag) OVER (Partition by ProductID Order by TransactionDate) as Grp
From ResetFlag)
Select *,
SUM(Qty) OVER (Partition by ProductID, Grp Order by TransactionDate) as Running_Total
FROM GroupCTE
Scenario 2 - Reset running total when it turns negative.
Here, first we calculate the normal running total, then identify the negative values to be added back to reset the values and correct the subsequent values. Please watch the video tutorial for step by step explanation.
Here is the SQL Query -
With RunningTotal as
(Select *,
SUM(Qty) Over (Partition by ProductID Order by TransactionDate) as Run_Total
FROM Inventory)
, MinRunTotal as
(Select *, Min(Run_Total) Over (Partition by ProductID Order by TransactionDate) as min_run_total
FROM RunningTotal)
Select *,
Run_Total + IIF(min_run_total < 0 , -min_run_total, 0) as New_Running_Total
From MinRunTotal