Oracle (10) SQL: calculating final sum depending on two fields
First, a disclaimer: I've never studied programming in high school and just have to deal with various SQL problems (too).
So now I have two tables, TABLE1:
ACCNO BAL1 BAL2
11111 20 10
And TABLE2 (which of course has an ACCNO key) associated lines with '11111':
DATENUM AMT
1 -5
2 -10
3 8
4 -23
5 100
6 -120
7 140
Now I need to find new BAL1 and BAL2 using the following rules:
- BAL1 AMT must be subtracted or added to BAL1 as long as BAL1 == 0 (and BAL2> 0)
- if BAL1 reaches 0, then (if any) the remainder of BAL1 must be subtracted from BAL2
- if BAL2 also reaches 0, then only BAL1 needs to be changed from now on.
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?
source to share
I think I'll do it 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;
Outputs
120, 0
It looks like your last line is wrong, added 40
instead 140
.
source to share
If you had one BALANCE column, it would be very easy to do in SQL. We could use analytical SUM () to generate the total AMT and apply it to BAL1 on each line ...
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 is juggling two columns and passing some arithmetic between lines, which is much more complicated. It might be possible with the MODEL () clause, but thinking about it always changes the forehead.
source to share
In addition to a simple (= boring) cursor solution, you can probably do this by creating an aggregate function (or rather 2 aggregate functions to calculate balance 1 and one to calculate balance 2). The problem is that you can only use one argument for an aggregate function, so the argument must be compound. In pseudocode (I haven't 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 using analytic functions. If you are only interested in the final value for each account, you can:
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 strong broadcast, 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 this with cursors instead of aggregates (which will most likely 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 lines:
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;
source to share