Dear forum members,
I'm working on a piece about the model clause in which I'm comparing solutions for problems written with the model clause and solutions written without it.
For the next query I have a performant model clause solution, but I could not find a functionally equivalent solution without using the model clause and without using PL/SQL.
Maybe it is just not possible, but maybe some bright mind here has a solution.
Here are the tables:
create table deposits
as
select 1 customer, 1000 amount, date '2003-01-01' deposit_date from dual union all
select 1, 200, date '2004-01-01' from dual union all
select 1, 500, date '2005-01-01' from dual union all
select 1, 100, date '2006-01-01' from dual union all
select 1, 800, date '2007-01-01' from dual union all
select 2, 20, date '2003-01-01' from dual union all
select 2, 150, date '2004-01-01' from dual union all
select 2, 60, date '2005-01-01' from dual union all
select 2, 100, date '2006-01-01' from dual union all
select 2, 100, date '2007-01-01' from dual
/
create table interest_rates
as
select date '2003-01-01' startdate, 5 percentage from dual union all
select date '2004-01-01', 3.2 from dual union all
select date '2005-01-01', 4.1 from dual union all
select date '2006-01-01', 5.8 from dual union all
select date '2007-01-01', 4.9 from dual
/
The expected result is:
CUSTOMER AMOUNT STARTDATE PERCENTAGE BALANCE_AT_END_OF_YEAR
-------- ------ ---------- ---------- ----------------------
1 1000 01-01-2003 5 1050
1 200 01-01-2004 3,2 1290
1 500 01-01-2005 4,1 1863,39
1 100 01-01-2006 5,8 2077,27
1 800 01-01-2007 4,9 3018,26
2 20 01-01-2003 5 21
2 150 01-01-2004 3,2 176,47
2 60 01-01-2005 4,1 246,17
2 100 01-01-2006 5,8 366,25
2 100 01-01-2007 4,9 489,1
The calculation for the balance at the end of the year is done like this:
1050: 1000 * 1.05
1290: ((1000*1.05) + 200) * 1.032
1863.39: (((1000*1.05) + 200) * 1.032) + 500) * 1.041
et cetera.
Good luck!
Regards,
Rob.
Message was edited by:
Rob van Wijk
Changed a customer number from 3 to 2 in the create table statement.