afuchs 47083 (4/4/2013)

---

Hi guys,

I tried to create a new userdefined function for calculating the average of top 10 values on different columns of my table:

Example:

SELECT AVG(a02) FROM (SELECT TOP 10 a02 FROM table01 WHERE pkart3='A' AND pkid='Y' ORDER BY lfnr DESC) u

This works fine. But I get an error on creating this function:

CREATE FUNCTION dbo.rollavg (@data_in char(3))

RETURNS int

AS

BEGIN

DECLARE @data_out int

SET @data_out = AVG(u.data) FROM (SELECT TOP 10 @data_in AS data FROM table01 WHERE pkart3='A' ORDER BY lfnr DESC) u

RETURN @data_out

END

I thought to use this function: SELECT dbo.rollavg(a02) AS [ra02] FROM table01

Can anyone help me?

Thanks a lot!