SQL tricks
September 13, 2022

# Calculate transactions rolling balance with max limit

One more case using CTE (common table expressions). Assume we have table of financial transactions like posted below, where Id is transaction identifier integer auto increment without gaps, Type is D for deposits and W for withdrawals

```+====+======+========+
| Id | Type | Amount |
+====+======+========+
| 1  | D    | 300    |
+----+------+--------+
| 2  | D    | 400    |
+----+------+--------+
| 3  | D    | 500    |
+----+------+--------+
| 4  | W    | 300    |
+----+------+--------+
| 5  | D    | 200    |
+----+------+--------+
| 6  | W    | 500    |
+----+------+--------+```

We need to calculate balance after each transaction that not over 1000. We can not use here standard window functions because limitation the balance, so we going to solve the problem using CTE

```WITH CTE AS (
-- get first transaction
SELECT Id, Type, Amount, Amount AS Balance FROM Transactions WHERE Id = 1
UNION ALL
-- get each next trancaction
SELECT Transactions.Id, Transactions.Type, Transactions.Amount,
-- check if balance not over limit
CASE
-- when over limit set max balance
WHEN
CASE
WHEN Transactions.Type = 'D' THEN CTE.Balance + Transactions.Amount
ELSE CTE.Balance - Transactions.Amount
END > 1000
THEN 1000
-- other case calculate new balance value using previous and
-- current transaction type and amount
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;```
```Result:
+====+======+========+=========+
| Id | Type | Amount | Balance |
+====+======+========+=========+
| 1  | D    | 300    | 300     |
+----+------+--------+---------+
| 2  | D    | 400    | 700     |
+----+------+--------+---------+
| 3  | D    | 500    | 1000    | <-- here balance limited by max
+----+------+--------+---------+
| 4  | W    | 300    | 700     |
+----+------+--------+---------+
| 5  | D    | 200    | 900     |
+----+------+--------+---------+
| 6  | W    | 500    | 400     |
+----+------+--------+---------+```