Q:

SQL Server: Issue with BETWEEN clause

SELECT C_SousCategorie as y, Enseigne as x, Count(DISTINCT r.Reference_Produit) as val  
                FROM RawData r, Day d
                WHERE r.DayId = d.DayId
                    AND d.WeekNo BETWEEN MAX(d.WeekNo) AND (MAX(d.WeekNo)-2)
                GROUP BY Enseigne, C_SousCategorie 
                ORDER BY 2, 1 ;

The above query gives the following error:
Lookup Error - SQL Server Database Error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Is there a better way around? I tried the IN clause as well but I get the same error.

A:

You can't use an aggregate function (MAX) inside a WHERE clause. The WHERE clause is executed before the GROUP BY.
You have to move your BETWEEN condition to an HAVING clause :
SELECT C_SousCategorie as y, Enseigne as x, Count(DISTINCT r.Reference_Produit) as val  
FROM RawData r, Day d
WHERE r.DayId = d.DayId
GROUP BY Enseigne, C_SousCategorie 
HAVING d.WeekNo BETWEEN MAX(d.WeekNo) AND (MAX(d.WeekNo)-2)

The HAVING clause is executed after the GROUP BY
You also should avoid using column numbers in your ORDER BY, this is dangerous.

