Function that takes Average of TOP N records

• dan carr

  SSC Veteran

  Points: 221

  More actions

  December 20, 2004 at 3:26 pm

  #60623

  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

• logic64

  SSC-Addicted

  Points: 473

  More actions

  December 20, 2004 at 11:02 pm

  #534561

  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