I Have a SQL Server database with a table named Balance
The table:
Id | Date | IN | OUT | Balance |
---|---|---|---|---|
3345312 | 2022-08-07 | 100 | 50 | 250 |
5435245 | 2022-08-06 | 50 | 50 | 200 |
4353451 | 2022-08-05 | 0 | 100 | 200 |
5762454 | 2022-08-04 | 20 | 100 | 300 |
7634523 | 2022-08-03 | 400 | 100 | 380 |
5623456 | 2022-08-02 | 100 | 20 | 80 |
4524354 | 2022-08-01 | 0 | 0 | 0 |
- Id = Unique Identifier
- Date = Balance Date
- IN = IN Ammount
- OUT = OUT Ammount
- Balance = Last Day Balance + IN – OUT
The value of Balance column always should be equal to (Last Day Balance + IN – OUT)
That is, if on 2022-08-04 the number of entries is changed from 20 to 100, the balance from 2022-08-04 onwards must be recalculated:
Id | Date | IN | OUT | Balance |
---|---|---|---|---|
3345312 | 2022-08-07 | 100 | 50 | 330 |
5435245 | 2022-08-06 | 50 | 50 | 280 |
4353451 | 2022-08-05 | 0 | 100 | 280 |
5762454 | 2022-08-04 | 100 | 100 | 380 |
I’ve tried to make a recursive query to update this balance
It didn’t worked. It just updates the balance of the day I updated ignoring the next days.
Here is what I did until now:
DECLARE @BalanceDate DATE = '2022-09-04'; DECLARE @BalanceLastDay DECIMAL(19,5) = (SELECT TOP 1 COALESCE(Balance, 0) Balances WHERE BalanceDate < @BalanceDate ORDER BY BalanceDate DESC); WITH Inventory AS ( SELECT Id, BalanceDate, IN, OUT, Balance, LAG(Balance) OVER (ORDER BY BalanceDate) AS BalanceLastDay, FROM Balances WHERE BalanceDate >= @BalanceDate) ), InventoryUpdated AS ( SELECT inv.*, (COALESCE(BalanceLastDay, @BalanceLastDay) + IN - OUT) AS RealBalance, FROM Inventory inv ) UPDATE Balances SET Saldo = invUpdt.SaldoReal FROM Balances INNER JOIN InventoryUpdated invUpdt on Balances.Id = invUpdt.Id WHERE invUpdt.Saldo <> invUpdt.SaldoReal;
Advertisement
Answer
You may use the following:
Select Id, [Date], [IN], [OUT], SUM([IN] - [OUT]) Over (Order By [Date]) AS Balance From your_table Order By [Date] DESC;
SUM([IN] - [OUT]) Over (Order By [Date])
will calculate the cumulative sum of (IN – OUT) over the increasing of date.
Note: I think it’s not a good practice to store the balance value in the table because it’s a calculated value, see this post.
But if you want to do that you may use updatable CTE as the following:
With CTE AS ( Select Id, Balance, SUM([IN] - [OUT]) Over (Order By [Date]) AS bl From your_table ) Update CTE Set Balance = bl;
See a demo from db<>fiddle.
Update, according to the new requirement stated in the comments, that you have multiple products in the table and you want to perform the update on a specific product from a specific date up.
To find the running/ cumulative sum for multiple products you have to add Partition By Pid
to the Over
clause, where Pid is the product Id, so the select query will be:
Select Pid, Id, [Date], [IN], [OUT], SUM([IN] - [OUT]) Over (Partition By Pid Order By [Date]) AS Balance From your_table Order By Pid, [Date] DESC;
And the update query:
Update your_table Set [in] = 100 Where [Date] = '2022-08-04' And Pid = 1; With CTE AS ( Select Pid, Id, Balance, Date, [IN] ,[OUT], SUM([IN] - [OUT]) Over (Partition By Pid Order By [Date]) AS bl From your_table ) Update CTE Set Balance = bl Where [Date] >= '2022-08-04' And Pid = 1;
See a demo.