I have a table with the following structure, I want to subtract the TotalAmount to Amount but only if the remaining amount would still above the limit. The sorting is the sequence which amount should be tried first before moving on to the next data

Sort Amount Limit TotalAmount
1 200 500 2000
3 500 500 2000
10 1000 500 2000
17 30 500 2000

Expected Result

Sort Amount Limit TotalAmount RemainingAmount
1 200 500 2000 1800
3 500 500 2000 1300
10 1000 500 2000 1300 --This is still 1300 because 1300 - 1000 is 300
17 30 500 2000 1270

I’ve been trying window functions, but I don’t know how to apply the logic of keeping the same amount with a condition.

SELECT Sort, Amount, Limit, TotalAmount, TotalAmount - SUM(Amount) OVER (ORDER BY Sort) as RemainingAmount

I also tried something with case and checking if i’m below the limit

CASE WHEN TotalAmount - SUM(Amount) OVER (ORDER BY Sort) < Limit THEN --readd the amount again?

## Answer

Declare a running variable `@Amount`

to remember the amount deducted and do the calculation for each row selected.

SELECT Sort, Amount, `Limit`, TotalAmount,
CASE WHEN TotalAmount - @Amount - Amount < `Limit`
THEN TotalAmount - @Amount ELSE TotalAmount - (@Amount := @Amount + Amount)
END AS RemainingAmount
FROM v
JOIN ( SELECT @Amount := 0 ) f
ORDER BY Sort