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     |
+----+------+--------+---------+

SQL online editor