SQL - running total with max

Optional 0
Viewed 58

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.

cumulative-sum sql-server sql
1 Answers

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     |
+----+------+--------+---------+
Slava Rozhnev0
Related