Oracle(10) SQL: calculating final sum depending on two field
- by Zsolt Botykai
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:
BAL1 AMT must be substracted from or added to BAL1 until BAL1 == 0 (and BAL2 0)
if BAL1 reaches 0 then the (if any) remainder of BAL1 must be substracted from BAL2
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?
