So I am having SQL error: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." I know you will ask me to just put it on a variable but this piece of code is inside a view. SQL does not allow us to put variable inside a view.
SELECT top 1000
ProductID,
(
NULLIF(
AVG(
CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < MAX(QtyShip_RAW_adjusted) THEN QtyShip_RAW_adjusted END
)/COUNT(*),
0
) + SUM(
CASE WHEN QtyShip_RAW_adjusted >= 0 THEN QtyShip_RAW_adjusted END
) - (
SELECT
MAX(QtyShip_RAW_adjusted)
FROM
QtyShip_RAW_adjusted_test
) + (
SELECT
TOP 1 QtyShip_RAW_adjusted
FROM
(
SELECT
TOP 3 QtyShip_RAW_adjusted
FROM
QtyShip_RAW_adjusted_test
ORDER BY
QtyShip_RAW_adjusted DESC
) AS Comp
ORDER BY
QtyShip_RAW_adjusted ASC
)
) / COUNT(
CASE WHEN QtyShip_RAW_adjusted >= 0 THEN 1 END
) as AverageLessPeakWeek
FROM
QtyShip_RAW_adjusted_test
GROUP BY
ProductID
I already tried putting in a derived table.
FROM
(
SELECT
MAX(QtyShip_RAW_adjusted) as MaxQty
FROM
QtyShip_RAW_adjusted_test
) t
But putting it inside derived tables gave me more errors.
CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < t.MaxQty THEN QtyShip_RAW_adjusted END
I already made some google search and nothing seems to be working. Thanks Guys!