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