Oracle(10) SQL: calculating final sum depending on two field

Question

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   120   0

And I need the last two BAL1 and BAL2.

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

Answer 1

In addition to the simple (=boring) solution with a cursor, you can probably do this by creating an aggregate function (or rather, 2 aggregate functions, one to calculate balance 1 and one to calculate balance 2). The trouble is that you can only use one argument to an aggregate function, so that argument would have to be a composite type. In pseudo code (I have not used Oracle for so long):

CREATE TYPE tuple_type(amt number, bal1 number, bal2 number);

CREATE FUNCTION calc_bal1(arg IN tuple_type) RETURN number AGGREGATE USING some_implementing_type;
CREATE FUNCTION calc_bal2(arg IN tuple_type) RETURN number AGGREGATE USING some_implementing_type;

Then you can query them with analytic functions. If you are only interested in the final value for each account, you can do:

SELECT t1.acct_no,
       calc_bal1(tuple_type(t2.amt, t1.bal1, t1.bal2)) OVER (PARTITION BY t1.acct_no ORDER BY t2.datenum),
       calc_bal2(tuple_type(t2.amt, t1.bal1, t1.bal2)) OVER (PARTITION BY t1.acct_no ORDER BY t2.datenum)
  FROM table1 t1
  JOIN (SELECT acct_no, datenum, amt FROM table2
        UNION ALL
        SELECT acct_no, 0, 0) t2
    ON t1.acct_no = t2.acct_no;
 WHERE t1.datenum = 0;

If you want every singe transction, do:

SELECT t1.acct_no,
       calc_bal1(tuple_type(t2.amt, t1.bal1, t1.bal2))
                 OVER (PARTITION BY t1.acct_no
                 ORDER BY t2.datenum
                 ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW),
       calc_bal1(tuple_type(t2.amt, t1.bal1, t1.bal2))
                 OVER (PARTITION BY t1.acct_no
                 ORDER BY t2.datenum
                 ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW)
  FROM table1 t1
  JOIN (SELECT acct_no, datenum, amt FROM table2
        UNION ALL
        SELECT acct_no, 0, 0) t2
    ON t1.acct_no = t2.acct_no;

You can also do it with cursors instead of aggregates (which is very likely to have terrible performance):

CREATE FUNCTION calc_bal1(c IN sys.ref_cursor, bal1 IN number, bal2 IN number) RETURN number AS ...;
CREATE FUNCTION calc_bal2(c IN sys.ref_cursor, bal1 IN number, bal2 IN number) RETURN number AS ...;

If you want all rows:

SELECT t1.acct_no,
       calc_bal1(CURSOR(SELECT amt FROM table2 x WHERE x.acct_no = t1.acct_no AND x.datenum <= t2.datenum ORDER BY x.datenum), t1.bal1, t1.bal2),
       calc_bal2(CURSOR(SELECT amt FROM table2 x WHERE t2.acct_no = t1.acct_no AND x.datenum <= t2.datenum ORDER BY t2.datenum), t1.bal1, t1.bal2)
  FROM table1 t1
  JOIN (SELECT acct_no, datenum, amt FROM table2
        UNION ALL
        SELECT acct_no, 0, 0) t2
    ON t1.acct_no = t2.acct_no;

If you only want the final values:

SELECT t1.acct_no,
       calc_bal1(CURSOR(SELECT amt FROM table2 t2 WHERE t2.acct_no = t1.acct_no ORDER BY t2.datenum), t1.bal1, t1.bal2),
       calc_bal2(CURSOR(SELECT amt FROM table2 t2 WHERE t2.acct_no = t1.acct_no ORDER BY t2.datenum), t1.bal1, t1.bal2)
  FROM table1 t1;

Answer 2

If you had a single BALANCE column this would be quite easy to do in SQL. We could use an analytic SUM() to generate the rolling total of AMT and apply that to BAL1 in each row...

SQL> select accno
  2         , bal1
  3         , datenum
  4         , amt
  5         , rolling_amt
  6         , bal1 + rolling_amt as rolling_bal1
  7  from (
  8      select t1.accno
  9               , t2.datenum
 10               , t2.amt
 11               , t1.bal1
 12               , sum ( t2.amt) over
 13                         ( partition by t2.accno
 14                           order by t2.datenum rows unbounded preceding )
 15                                           as rolling_amt
 16      from t1 join t2 on (t2.accno = t1.accno)
 17      where t1.accno = 11111
 18      order by t2.datenum
 19  )
 20  /

     ACCNO       BAL1    DATENUM        AMT ROLLING_AMT ROLLING_BAL1
---------- ---------- ---------- ---------- ----------- ------------
     11111         20          1         -5          -5           15
     11111         20          2        -10         -15            5
     11111         20          3          8          -7           13
     11111         20          4        -23         -30          -10
     11111         20          5        100          70           90
     11111         20          6       -120         -50          -30
     11111         20          7        140          90          110

7 rows selected.

SQL>

However your requirement juggles two columns and passes some arithmetic between the rows which is a lot more complicated. It might be possible to do it with the MODEL() clause but thinking about that always makes my forehead bleed.

Answer 3

I think I'd do this with PL/SQL:

DECLARE
  v_bal1  table1.bal1%TYPE;
  v_bal2  table1.bal2%TYPE;
  v_accno table1.accno%TYPE;
BEGIN
  v_accno := 11111;
  SELECT bal1, bal2
  INTO v_bal1, v_bal2  
  FROM table1
  WHERE accno = v_accno;

  FOR c IN ( SELECT amt
             FROM table2
             WHERE accno = v_accno
             ORDER BY datenum )
  LOOP
    v_bal1 := v_bal1 + c.amt;
    IF( v_bal1 < 0 AND v_bal2 > 0 ) THEN
      v_bal2 := v_bal2 + v_bal1;  --# v_bal1 < 0, so "add" to v_bal2
      IF( v_bal2 < 0 ) THEN
        v_bal1 := v_bal1 + v_bal2; --# "remove" remainder
        v_bal2 := 0;
      ELSE
        v_bal1 := 0;
      END IF;
    END IF;
  END LOOP;
  dbms_output.put_line( v_bal1 || ', ' || v_bal2 );
END;

This outputs

120, 0

Looks like your last line is wrong, added 40 instead of 140.