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?

CodePudding user response：

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

CodePudding user response：

You can perform a self join to produce the baseline running totals, and then find the minimum running total for each given sort that, when subtracted by that sort's amount, does not fall below the limit:

with aggs(s, a) as (
   select v.sort, sum(coalesce(v1.amount, 0)) from vals v 
   left join vals v1 on v1.sort < v.sort 
   group by v.sort order by v.sort
),
r_m(s, a) as (
   select v.sort, min(v.totalamount - a1.a) from vals v 
   join aggs a1 on v.totalamount - a1.a >= v.lmt and v.sort >= a1.s
   group by v.sort
   order by v.sort
)
select v.*, case when r_m.a - v.amount >= v.lmt 
                 then r_m.a - v.amount else r_m.a end 
from vals v join r_m on v.sort = r_m.s

Output: