SQL Query Max Order Date

Question

I'm trying to find customers whos last order date was exactly 3 months ago. The Data Extension I'm pulling from is called 'Purchase' and has the orderDate and email for every order a customer has made.

Here is the Query:

SELECT Email as emailaddress, orderDate
FROM [Purchase]
GROUP BY Email, orderDate
HAVING MAX(CAST(OrderDate AS DATE)) = CAST(DATEADD(M,-3,GETDATE()) AS DATE)

While its successfully bringing up customers who have an order date of exactly 3 months ago its not necessarily their last order date. How can I edit this to ONLY include customers whos last order date was 3 months ago and they dont have any orderDates after that?

Answer 1

In SQL, you can get that data by doing the following

SELECT emailaddress, MaxDate
FROM (SELECT Email as emailaddress, MAX(CAST(OrderDate AS DATE)) MaxDate
      FROM [Purchase]
      GROUP BY Email) MaxOrders
WHERE MaxDate = CAST(DATEADD(M,-3,GETDATE()) AS DATE)

Your subquery gets the max date for each record, then you can compare that max with "3 months ago"