Is anyone help me to correct following query,,

See more:

SQL-Server-2005

SQL-Server

CREATE FUNCTION [dbo].[f_Get_Average_Order_Size_Median]
(
@ITEM char(15)
)
RETURNS decimal(21,6)
AS
BEGIN

SELECT @Median = AVG(1.0 * QTYSHP)
FROM
(
SELECT o.QTYSHP, rn = ROW_NUMBER() OVER (ORDER BY o.QTYSHP), c.c
FROM dbo.tblSOTRAN AS o WHERE RQDATE >=DATEADD (mm,-6, GETDATE()) AND PRICE != '0' AND SALESMN != 'WB' AND item = @ITEM )

+
SELECT o.QTYSHP, rn = ROW_NUMBER() OVER (ORDER BY o.QTYSHP), c.c

FROM tblSOYTRN WHERE RQDATE >=DATEADD (mm,-6, GETDATE()) AND PRICE != '0' AND SALESMN != 'WB' AND item = @ITEM

CROSS JOIN (SELECT c = COUNT(*) FROM dbo.tblSOTRAN) AS c

WHERE RQDATE >=DATEADD (mm,-6, GETDATE()) AND PRICE != '0' AND SALESMN != 'WB' AND item = @ITEM

+ (SELECT c = COUNT(*) FROM dbo.tblSOYTRN) AS c

WHERE RQDATE >=DATEADD (mm,-6, GETDATE()) AND PRICE != '0' AND SALESMN != 'WB' AND item = @ITEM

) AS x
WHERE rn IN ((c + 1)/2, (c + 2)/2);


@Return = @Median
BEGIN

END
RETURN @Return

END TRANSACTION