-- create a table
CREATE TABLE Transactions (
Id int primary key identity(1,1),
Type char(1),
Amount int
);
INSERT INTO Transactions (Type, Amount) VALUES
('D', 300),
('D', 400),
('D', 500),
('W', 300),
('D', 200),
('W', 500);
SELECT * FROM Transactions;
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;