Query Brain Teaser - Revenue Projections

[datagal]

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

[Doug]

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?

[datagal]

Doug:

Thanks for the reply. There's nothing wrong with the data (at least
not up to this point in the process) and I'm not calculating interest,
just the prorated Annual Amount by year.

The Annual Amount is the base, is never adjusted up for a leap year and
could make the full years easy if it makes sense to use it.

MonthlyAmt is Annual/12 and isn't really worth much in this context.

DailyAmt is just Annual/365 (366 in a leap year) and is necessary for
partial month proration. I suppose I could create a date table for the
next 25 years with no 2/29's in it, and do my DATEDIFFs against that...
I was hoping to avoid the cursor thing...

Jody

[datagal]

I got it! (and it doesn't run too bad....)

SELECT pt.Key,
pt.Period,
y.Year AS RevYear,
CASE WHEN Year = DATEPART(YEAR,Start) THEN

DailyAmt*DATEDIFF(DAY,Start,DATEADD(MS,-3,DATEADD(YY,DATEDIFF(YY,0,Start
)+1,0)+1))
WHEN Year = DATEPART(YEAR,End) THEN
DailyAmt*DATEDIFF(DAY,DATEADD(YY, DATEDIFF(YY,0,End),0)-1,End)
ELSE DailyAmt*365
END as ProratedAnnualRent
INTO Report
FROM #ProjectionTemp pt
JOIN (SELECT DISTINCT Year
FROM dim_fiscal_day) y
ON y.Year >= DATEPART(YEAR,pt.Start)
AND y.Year <= DATEPART(YEAR,pt.End)