title: Oracle(10) SQL: calculating final sum depending on two field
body: <p>First the disclaimer: I never learnt any programming in school, and just have to deal with various SQL problems (too).</p>
  
  <p>So now I've got two tables, TABLE1:</p>
  
  <pre><code>ACCNO BAL1 BAL2
  11111   20   10
  </code></pre>
  
  <p>And TABLE2 (which has the ACCNO key, of course) related rows to '11111':</p>
  
  <pre><code>DATENUM AMT
  1       -5
  2       -10
  3       8
  4       -23
  5       100
  6       -120
  7       140
  </code></pre>
  
  <p>Now I have to find the new BAL1 and BAL2 using the following rules:</p>
  
  <ol>
  <li>BAL1 AMT must be substracted from or added to BAL1 until BAL1 == 0 (and BAL2 > 0)</li>
  <li>if BAL1 reaches 0 then the (if any) remainder of BAL1 must be substracted from BAL2</li>
  <li>if BAL2 reaches 0 too, from then only BAL1 should be modified.</li>
  </ol>
  
  <p>So using the above data:</p>
  
  <pre><code>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   120   0
  </code></pre>
  
  <p>And I need the last two BAL1 and BAL2.</p>
  
  <p>How can I calculate them using (Oracle 10) SQL?</p>