Running subtraction but subtracting only if the amount is still above a limit

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

Advertisement

Answer