SQL - running total with max-CodePudding

I have a bunch of transactions (deposits/withdrawals) that i want to track a running balance for, but the trick is this - there is an automation of the balance that will skim money off when the balance goes over a certain balance so that not too much cash is left in this account ( think risk, security etc).I'm trying to work out the optimal level this 'skimming' should occur so that we don't have to top the account up either.

Target - using $1000 skim balance

ID	Type	Amount	Running Balance
1	Deposit	100	100
2	Deposit	150	850
3	Deposit	1500	1000 (capped)
4	Withdrawal	100	900
5	Deposit	500	1000 ( capped)
6	Deposit	150	1000 (still capped)

Appreciate any assistance that you can offer.

CodePudding user response：

This problem can be solved by recursive cte

WITH CTE AS (
    SELECT Id, Type, Amount, Amount AS Balance FROM Transactions WHERE Id = 1
    UNION ALL 
    SELECT Transactions.Id, Transactions.Type, Transactions.Amount, 
        CASE 
            WHEN
              CASE 
                  WHEN Transactions.Type = 'D' THEN CTE.Balance   Transactions.Amount 
                  ELSE CTE.Balance - Transactions.Amount 
              END > 1000
            THEN 1000
            ELSE
              CASE 
                  WHEN Transactions.Type = 'D' THEN CTE.Balance   Transactions.Amount 
                  ELSE CTE.Balance - Transactions.Amount 
              END
        END
    FROM Transactions 
    JOIN CTE ON CTE.Id   1 = Transactions.Id
) SELECT * FROM CTE;

Where we loop trow table rows and apply condition after each balance update

SQL online editor

Result:

 ==== ====== ======== ========= 
| Id | Type | Amount | Balance |
 ==== ====== ======== ========= 
| 1  | D    | 300    | 300     |
 ---- ------ -------- --------- 
| 2  | D    | 400    | 700     |
 ---- ------ -------- --------- 
| 3  | D    | 500    | 1000    |
 ---- ------ -------- --------- 
| 4  | W    | 300    | 700     |
 ---- ------ -------- --------- 
| 5  | D    | 200    | 900     |
 ---- ------ -------- --------- 
| 6  | W    | 500    | 400     |
 ---- ------ -------- ---------