Solved

# Creating views to calculate annually indexed fees

Posted on 2003-11-02
771 Views
I hope that somebody can help

I have a table that is used to store annual fees.  Fields included in this table are

id (primary key),
fee,
base_year
increment_year

Two separate things happen to these fees.

In the first case, fees that are set in what is referred to as a "base_year" and are set to increment (or not) by a set percentage in all consecutive years (ie. the "increment_year")

Also, for any given "base_year", the fee can be set to any amount - irrespective of either the set or incremented value in previous years.  When a new annual fee is set, base_year = increment_year

The situation is best shown by example

base_year                      increment_year                      fee                  example

2004                                 2004                                  a                      10000
2004                                 2005                                  b                    a + (a*4%)
2004                                 2006                                  c                     b + (b*6%)
2005                                 2005                                  d                      15000
2005                                 2006                                  e                     d + (d*6%)
2006                                 2006                                  f                       10000

2005 percentage = 4%
2006 percentage = 6%

Also where the base_year is not equal to the increment_year, the indexed fee (ie. b,c and e) must be amounts that are both divisible by 24 and rounded down to the nearest five dollars.

With reference to the applicaiton of the indexation percentage, for auditing purposes, I would prefer that the "annual increment percentage" be kept in a separate table.

I would appreciate help with the creation of a view(s) to manage this siuation.  In all, I have approximately 200 annual fees to keep track of.  I am still not sure whether it would be best to collate all of the data into a single view or to split it by year and use queries to draw it together.  At the end of the day, I am required to pass the data over to a separate system that requires it in a single table (or so they say).

Any help that can be given to me regarding my dilemma is greatly appreciated.
0
Question by:jainesteer
• 7
• 5

LVL 23

Expert Comment

If i understand you correctly, this should work:

for the illustration purpose, you two tables are called:

annual_fee
(
id (primary key),
fee,
base_year,
increment_year)

year_percentage
(year,
percentage)

create view annual_index_fee
as
select id, base_year, increment_year, decode((increment_year-base_year), 0, fee, fee*(1+ percentage))
from annual_fee af, year_percentage yp
where af.increment_year = yp.year;

0

LVL 8

Expert Comment

based on seazodiac's solution.

create view annual_index_fee
as
select id, base_year, increment_year, decode((increment_year-base_year), 0, fee, floor( (fee*(1+ percentage)/24+5)/5) *5
from annual_fee af, year_percentage yp
where af.increment_year = yp.year;

0

LVL 23

Expert Comment

Thanks Danielzt,
the moment I pressed the submit , I forget the round by 25 and five dollars thingy.

but anyhow ,jainesteer,  you get the solution.
0

Author Comment

Thankyou but unfortunately this does not give me what I need

Firstly - there was a right parenthesis and alias missing from the decode statement but I got that sorted

Then, the view requires that I adjusts the increment year in the annual fee table - I do not want to do this, I want incremented values automatically calculated according to whatever percentage amounts (for all listed future years) appear in the year_percentage table.  That is, the annual fee table is only updated with the annual fees set for the nominated year (ie. only in the instance where base_year = increment_year).  In retrospect, my annual_fee table need not contain the field "increment_year", as it is only ever the base year and associated fee that should be stored in this table.  Just so that we're clear, I want the view to show both sets of fees - ie. the annual fee (where base_year = increment_year) and all incremented fees for the year/percent combination in the year_percentage table.  If a new fee is introduced, increments should only show for those years where yp.year > af.base_year. ncrements should only appear

Also, the formula provided by Danielzt for the new fee to be both divisible by 24 and rounded down to the nearest \$5 does not give the correct answer.  For example, I changed an increment year in the annual_fee table from 2003 to 2004, made the 2004 percent increase 4%, and applied it against a 2003 base_year fee of \$11000.  The view calculated the "new" value as 480, the actual amount should be 11400.

Sorry about the delay in getting back to this earlier.
0

LVL 23

Expert Comment

I think this query will satisfy all your requirements:

1. I do a CROSS JOIN on the annual_fee table and year_percentage table and using the filter "base_year<= yp.year" in the where clause to make sure all the years in the year_percentage greater than base_year are pulled into the result set.

2. Using expression "af.fee*(1+ percentage) - mod(af.fee*(1+ percentage), 24*5)" will give you the correct indexed fee.

check it out:

create view annual_index_fee
as
select af.id, af.base_year, yp.year "increment_year", decode((yp.year-af.base_year), 0, af.fee, af.fee*(1+ percentage) - mod(af.fee*(1+ percentage), 24*5))
from annual_fee af, year_percentage yp
where af.base_year<= yp.year;
0

Author Comment

Thanks seazodiac - this is definitely starting to take form.

I have made a few changes to the fields in the tables and so the code that I have is now

create view annual_index_fee
as
select af.cricos_id, af.base_year, yp.year "increment_year",
decode((yp.year-af.base_year), 0, af.fee, af.fee*(1+ increment_percent) - mod(af.fee*(1+ increment_percent), 24*5)) new
from lut_cricos_incremental af, lut_year_percent yp
where af.base_year <= yp.year

The main problem that I now have is that it does not seem to be compounding the fee

Eg.  I have the year/percent set

2003   0
2004   4
2005   4

Using the data from before, the view gives

2003    2003    11000
2003    2004    11400
2003    2005    11400
2004    2004    12000
2004    2005    12480

The 2003/2005 (row 3) set needs to be calculated by compounding the 2003/2004 (row 2) figure at the 2005 rate set in the yp table.

Any further suggestions?
0

LVL 23

Expert Comment

sorry I forget that.
this will fix the compound indexed fee, this time my query contains a function on the new fee "power( (1+increment_percent, (yp.year - af.base_year))" this will get the annual percentage increase into the form of "(1+percent) to the power of number of years differential".

create view annual_index_fee
as
select af.cricos_id, af.base_year, yp.year "increment_year",
decode((yp.year-af.base_year), 0, af.fee, af.fee*(power((1+ increment_percent), (yp.year-af.base_year)))- mod(af.fee*(power((1+ increment_percent), (yp.year-af.base_year))), 24*5)) new
from lut_cricos_incremental af, lut_year_percent yp
where af.base_year <= yp.year
0

Author Comment

Once again this is getting better but there is still certainly problems - particularly when fee increments are either not applied (set to 0) or decreased from the previous year.

I have provided you with some data based on the following annual percentage increments

2003    0
2004    4
2005    4
2006    4
2007    2
2008    0
2009    0
2010    2
2011    4
2012   20
2013    5

base    increment_year   your result     actual result

2003            2003            12000             12000
2003            2004            12480             12480
2003            2005            12960             12960
2003            2006            13440             13440
2003            2007            12960             13680
2003            2008            12000             13680
2003            2009            12000             13680
2003            2010            13680             13920
2003            2011            16320             14400
2003            2012            61800             17280
2003            2013            19440             18120

Evidently there is still something amiss - and once again I would be extremely grateful for any help in resolving the issue.

0

LVL 23

Expert Comment

Ok, I did NOT thought it's this complex.

then you need to create a function to solve this complicated year_percentage.
this function recursively calls itself to compute the indexed fee from p_base_year to p_increment_year, and return the calculated adjusted percentage.

CREATE OR REPLACE FUNCTION indexed_fee
(p_base_year IN NUMBER,
p_increment_year IN NUMBER) RETURN NUMBER
AS
l_query VARCHAR2(200);
l_increment_percent NUMBER;
BEGIN
IF (p_increment_year - p_base_year) =0 THEN
return 1;
ELSE
l_query :='SELECT increment_percent FROM lut_year_percent where  year = ' || p_increment_year;
execute immediate l_query  INTO l_increment_percent;
return (1+l_increment_percent)*indexed_fee(p_base_year, p_increment_year -1);  --recursive function.
END IF;
END;
/

Now we are ready to create a view using this function:

create view annual_index_fee
as
select af.cricos_id, af.base_year, yp.year "increment_year",
decode((yp.year-af.base_year), 0, af.fee,
af.fee*indexed_fee(af.base_year, yp.year) - mod(af.fee*indexed_fee(af.base_year, yp.year), 24*5)) new
from lut_cricos_incremental af, lut_year_percent yp
where af.base_year <= yp.year

Hope this helps

0

LVL 23

Expert Comment

By the way,  jainesteer, I think I already changed the table, and column name to fit your needs, and you should be able to copy and paste to use. But just in case it does not, you will be looking for the table name or column name correction.  At last, you should be able to understand the logic of the function and the SQL statement for the view with ease.

GOOD LUCK!!!
0

Author Comment

You probably don't want to hear that there still appears to be a problem.  I have created the function, and dropped and recreated the view - and thankyou I only have to copy your code over.  When I compare the result to those that I gave last time, all goes well until it hits the year 2010.  I have listed the new results below

base    increment_year   your result     actual result

2003            2003            12000             12000
2003            2004            12480             12480
2003            2005            12960             12960
2003            2006            13440             13440
2003            2007            13680             13680
2003            2008            13680             13680
2003            2009            13680             13680
2003            2010            14040             13920
2003            2011            14520             14400
2003            2012            17520             17280
2003            2013            18360             18120

I have played around with the percentages and the point at which it falls might have some relationship to the increment that is applied.  Apply 0% - all correct
1% - all correct
2% - error at 2011
3% - error at 2009
4% - error at 2007
5% - error at 2007
6% - error at 2006
10% - error at 2008 (well that blew that theory!!)

I am happy to work within this 10 year window, but certainly need to address the obvious problem that still exists.

Is there anything that you can think of/do that will fix this current problem??

I look forward to your response.
0

LVL 23

Accepted Solution

seazodiac earned 500 total points
Nothin' particular jumps to my mind. but I can give two pointers to nail the problem:

1. In the view definition, you can certainly add an extra for the calculated increment_percentage, so that you can have an idea it's because of the function or the SQL query.

for example:

create view annual_index_fee
as
select af.cricos_id, af.base_year, yp.year "increment_year",
indexed_fee(af.base_year, yp.year) calculated_percentage, ---add a new column here for the function only
decode((yp.year-af.base_year), 0, af.fee,
af.fee*indexed_fee(af.base_year, yp.year) - mod(af.fee*indexed_fee(af.base_year, yp.year), 24*5)) new
from lut_cricos_incremental af, lut_year_percent yp
where af.base_year <= yp.year;

2. make sure that all the years are continuous so that the function don't throw an exception.
as you can see, the function is not complete and it's missing the exception handler.

CREATE OR REPLACE FUNCTION indexed_fee
(p_base_year IN NUMBER,
p_increment_year IN NUMBER) RETURN NUMBER
AS
l_query VARCHAR2(200);
l_increment_percent NUMBER;
BEGIN
IF (p_increment_year - p_base_year) =0 THEN
return 1;
ELSE
l_query :='SELECT increment_percent FROM lut_year_percent where  year = ' || p_increment_year;
execute immediate l_query  INTO l_increment_percent;
return (1+l_increment_percent)*indexed_fee(p_base_year, p_increment_year -1);  --recursive function.
END IF;
EXCEPTION WHEN OTHERS THEN
NULL; --you can change to something useful to help you to debug what might go wrong.
END;
/

Anyhow, I will leave the rest to you for fun. It's time to clean this thread up..

enjoy!
0

Author Comment

Seazodiac - You have no idea how much I appreciate your help.  I now think that I know where the discrepancy between our numbers lies.  Calculations that I have been doing have always been using the rounded figure from the previous year; through your view, the calculation always refers back to the base year and and is rounded down after that point.  In view of what I am trying to actually achieve, your model is correct.  Again thankyou so much for your time and patience in the resollution of this issue.
0

## Featured Post

### Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup