I would like to create a function that returns the average of the top n
records in a table (where 'n' is a parameter).
The only way I can think of doing this is in a stored proc by:
(1) Creating a dynamic SQL statement SELECT TOP @N FROM THETABLE
(2) Taking the average of that.
Unfortunately, can not call a stored proc from a function (or a view) and
can not use dynamic sql (EXEC) in a function
Table Example
Date Amount
1/1/2004 5
1/4/2004 8
1/11/2004 7
1/23/2004 6
1/25/2004 9
1/28/2004 7
1/29/2004 11
1/31/2004 12
Assuming Function is called TopAverage.
TopAverage (3) = 6.66667 (5+8+7)/3
TopAverage (6) = 7.00 (5+8+7+6+9+7)/6