Sql server Balance Table Update Recursive

Question

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;

userGpjR · 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.

Sql server Balance Table Update Recursive

1 Answers