How to calculate expiring voucher balances with partially used vouchers are using sql server

Uncategories How to calculate expiring voucher balances with partially used vouchers are using sql server

UPDATE Customers

SET Balance = Balance - (

SELECT ISNULL(SUM(CASE

WHEN ExpirationDate <= GETDATE() THEN RemainingValue

ELSE Value

END), 0)

FROM Vouchers

WHERE CustomerID = Customers.CustomerID

);

-- Optional: Set negative balances to 0

UPDATE Customers

SET Balance = 0

WHERE Balance < 0;