December 20, 2004 at 3:26 pm
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
December 20, 2004 at 11:02 pm
Hi Dan, this question appears to be fairly common with no easy answer due to limitations of UDF.
Try this script below, hopefully it will do what you want.
CREATE FUNCTION udf_Top_Average (@top_n int)
RETURNS decimal(18,2)
AS
BEGIN
DECLARE @tbl table (value numeric)
INSERT INTO @tbl
SELECT a.Amount
FROM THETABLE a
inner join THETABLE b
on a.PK_COLUMN >= b. PK_COLUMN
GROUP BY a.Amount
HAVING count(*) <= @top_n
RETURN (select avg(value) from @tbl)
END
Use your table and column names. May not be the most elegant solution, but it should do the trick.
To test: select dbo.udf_Top_Average(3)
All the best.
Gregory
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply