Skip to content
Advertisement

Sql server Balance Table Update Recursive

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.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement