First the disclaimer: I never learnt any programming in school, and just have to deal with various SQL problems (too).

So now I've got two tables, TABLE1:

ACCNO BAL1 BAL2
11111   20   10

And TABLE2 (which has the ACCNO key, of course) related rows to '11111':

DATENUM AMT
1       -5
2       -10
3       8
4       -23
5       100
6       -120
7       140

Now I have to find the new BAL1 and BAL2 using the following rules:

1. BAL1 AMT must be substracted from or added to BAL1 until BAL1 == 0 (and BAL2 > 0)
2. if BAL1 reaches 0 then the (if any) remainder of BAL1 must be substracted from BAL2
3. if BAL2 reaches 0 too, from then only BAL1 should be modified.

So using the above data:

DATENUM AMT   BAL1 BAL2
0       0     20   10   /*starting record*/
1       -5    15   10   
2       -10   5    10
3       8     13   10
4       -23   0    0
5       100   100  0
6       -120  -20  0
7       140   20   0

And I need the last two BAL1 and BAL2.

How can I calculate them using (Oracle 10) SQL?