Posted by datagal on 03/08/06 20:08

I have a requirement (motivated by a SOX thing) that is just giving me
fits. I know it should be easy and I'm probably overthinking it, but I
just can seem to find the best way to get where I need to go.

I have some payment projection data derived from a huge procedure that
I'm dumping into a temp table that looks like looks this:

Key Pd Start End AnnualAmt MonthAmt DailyAmt
6789 1 2005-06-01 2010-05-31 49,500.00 4,125.00 135.616438
6789 2 2010-06-01 2015-05-31 54,450.00 4,537.50 149.178082
6789 3 2015-06-01 2020-05-31 59,895.00 4,991.25 164.095890
6789 4 2020-06-01 2024-05-31 65,884.50 5,490.38 180.505479

(there are actually 6 levels of keys, but you get the idea)

I need it to get into a reporting table looking like this:

Key Rev Year ProjectedAmt
6789 2005 29,021.92
6789 2006 49,500.00
6789 2007 49,500.00
6789 2008 49,500.00
6789 2009 49,500.00
6789 2010 20,478.08

6789 2010 31,924.11
6789 2011 54,450.00
6789 2012 54,450.00
6789 2013 54,450.00
6789 2014 54,450.00
6789 2015 22,525.88

6789 2015 35,117.40
6789 2016 59,895.00
6789 2017 59,895.00
6789 2018 59,895.00
6789 2019 59,895.00
6789 2020 24.779.10

etc...

I'm having a problem wrapping my head around how to get the rows in the
middle of each period.

The other, probably minor and statistically insignificant, issue is
proration on a leap year. If a proration occurs on a leap year and I
have to calculate the proration based on a DATEDIFF and an Annual or
Monthly Amount, I'm going to be a day over.

Anybody have any tricks or ideas???

Thanks so much for your help!

Jody

[Back to original message]