Creating views to calculate annually indexed fees
Posted on 2003-11-02
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),
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.