Posted by Doug on 03/09/06 00:26

First, are your interest rules based upon Annual rates, monthly rates,
ro daily rates. You need to know this in order to figure out what is
wrong with your current data. Currently, either your annual or your
daily data is wrong for leap year.

build a table that contains the start of each revenue year.

so, the entry would be
2005 1/1/2005
2006 1/1/2006
2007 1/1/2007
etc.

I will get lamblasted for this, but I would use a, gasp, cursor. Scroll
through your years table calculating the payments for each year, for a
given key/period..
To do this, Using Datediff, calculate the number of days between the
beginning of the year and the end of the year. For the beginning and
end, you will do calcs either on daily or percentages of the annual.

Someone could probably figure out a set based solution to this.
Personally, my brain isn't big enough. There is not that much data, and
a cursor is easier to think about on this one.

Outside of this inner cursor, have another cursor scrolling through
each of the key/periods.
So, two nested cursors. First one is Key/period. Inner is the years.
Make sense?

[Back to original message]