SQL decrement a value based on two columns till 0

Question

I have the following datasets (just a sample):

Table1:

ID    MAX    AMT  SORTED
1     20     0    1
1     30     0    2
1     40     0    3
1     50     0    4
2     0      0    1
2     30     0    2
2     40     0    3
2     40     0    4
...

Table2:

ID    AMT
1     75
2     70
...

I must update Table1.AMT from Table2.AMT using this rules:

1. Table1 and Table2 are joined on ID
2. Table1.AMT can't hold larger value than MAX
3. if Table2.AMT >= Table1.MAX then Table1.AMT = Table1.MAX... then on the next row update Table1.AMT with Table2.AMT - previous record AMT still using the above rules.

So the expected output would be

ID    MAX    AMT  SORTED
1     20     20   1
1     30     30   2
1     40     25   3
1     50     0    4
2     0      0    1
2     30     30   2
2     40     40   3
2     40     0    4
...

How can one achieve that?

I thought of creating a temp table with an aggregated SUM() of Table1.MAX, and using that as a reference to update Table1.AMT (if SUM(MAX) < Table2.AMT then Table1.AMT = Table1.MAX else Table1.AMT = previous records SUM(MAX)).

But can it be done without a temp table? (Sadly I can't create functions and procedures in my work env.)

Answer 1

More efficient solution can be made using specifics or Oracle PL/SQL.

Here is a generic solution:

select t1.ID, min(t1.MAX) as MAX, least(min(t1.MAX),coalesce(min(t2.AMT),0)-coalesce(least(sum(t1p.MAX-t1p.AMT), min(t2.AMT)),0)+min(t1.AMT)) as AMT, t1.SORTED
from Table1 t1
left join Table2 t2 on t2.ID = t1.ID
left join Table1 t1p on t1p.ID = t1.ID and t1p.SORTED < t1.SORTED
group by t1.ID, t1.SORTED 
order by t1.ID, t1.SORTED 

explanation of calculating AMT:

AMT is smallest of "MAX for the row" and "How much is possible"

least(min(t1.MAX),"How much is possible")

"How much is possible": max available - how much was given for previous rows + how much we already have

coalesce(min(t2.AMT),0) - "how much was given for previous rows" + min(t1.AMT)

"how much was given for previous rows": smalles of how much required to fill and how much possible

coalesce(least(sum(t1p.MAX-t1p.AMT), min(t2.AMT)),0)