Solved

Creating views to calculate annually indexed fees

Posted on 2003-11-02
13
784 Views
Last Modified: 2009-07-29
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
Comment
Question by:jainesteer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9668266
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)


your view definition will be:

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

by:Danielzt
ID: 9668476

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

by:seazodiac
ID: 9668686
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:jainesteer
ID: 9684312
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

by:seazodiac
ID: 9684490
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

by:jainesteer
ID: 9684699
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

by:seazodiac
ID: 9686324
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

by:jainesteer
ID: 9691257
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

by:seazodiac
ID: 9691868
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:seazodiac
ID: 9691880
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

by:jainesteer
ID: 9692277
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

by:
seazodiac earned 500 total points
ID: 9693542
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

by:jainesteer
ID: 9698166
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question