You can include a user-defined column in your WHERE and GROUP BY clauses, but you have to use the full declaration instead of the name.
Applied to your example, this would give:
SELECT
CustName
,Count(CustName)
,PurchaseDate
,DATENAME(dw,PurchaseDate) as Day
FROM
[CustmrSrc]
WHERE
DATENAME(dw,PurchaseDate) = 'Monday'
OR DATENAME(dw,PurchaseDate) = 'Tuesday'
OR DATENAME(dw,PurchaseDate) = 'Wednesday'
OR DATENAME(dw,PurchaseDate) = 'Thursday'
OR DATENAME(dw,PurchaseDate) = 'Friday'
GROUP BY
CustName
,PurchaseDate
A few notes:
- Here you need OR conditions, not AND. A given date cannot be a monday and a tuesday at the same time.
- Since you already included the PurchaseDate column in the GROUP BY clause, you do not need to also include the day name (the PurchaseDate already defines the week-day).
Kindly.