Development of a SQL Server 2008 UDA (User Defined Aggregate) or Table Value Function to perform chronological Compound Rate of Return Results from Monthly Percent Based Gains. ? The UDA should be eligible for use among other common aggregates (i.e. COUNT, AVG, MAX, MIN, SUM), however, this UDA requires Chronological Calculation (in order of earliest date to latest date). ? It may be necessary to specify, as an input to the function, which datefield to use for chronology. ? A function is needed so results can by handled through SQL Query combined with other results not handled specifically by the function.
## Deliverables
Example Table of Monthly Results
January - 15%
February - 0%
March - 12%
April - (-8%)
May - 0%
June - (-2%)
July - 18%
WHERE Initial Amount could be set to 1, while Result returned will be Percentage.
1 * 15% + 1 = 1.15
1.15 * 0% + 1.15 = 1.15
1.15 * 12% + 1.15 = 1.288
1.288 * -8% + 1.288 = 1.18496
1.18496 * 0% + 1.18496 = 1.18496
1.18496 * -2% + 1.18496 = 1.1612608
1.1612608 * 18% + 1.1612608 = 1.370287744
For this Period, the final result is 1.370287744
which is a Compound Return for the period of 37.02% (Value to Return)
The function must consider all dates passed through the original query.
i.e.
SELECT FieldA, UDA(MRET, PDate) -- Where MRET is the Monthly Return and PDate is the Date Sort Field
FROM TABLE
WHERE PDate Between '01/01/2002' AND '07/31/2002'
OR
SELECT FieldA, Year, UDA(MRET, PDate)
FROm TABLE
WHERE PDate BETWEEN '01/01/2002' AND '12/31/2008'
In the 2nd case, each Year would have it's own Compound Return.