|
|
Posted by datagal on 03/09/06 03:37
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)
Navigation:
[Reply to this message]
|