SQL & PL/SQL

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.