1

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?

2

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"

| improve this answer | |
  • I'm getting the error "An unmatched parenthesis occurs in the query." – Hailee Marcinek Apr 9 at 19:36
  • 1
    Check that you copied the query correctly (including the very last parenthesis). – Sebastian Kessel Apr 9 at 19:38
  • 1
    good call - thank you Sebastian! – Hailee Marcinek Apr 9 at 19:46
  • You're welcome! – Sebastian Kessel Apr 9 at 19:46
  • 1
    MaxOrders is an alias for the table that your Subquery is "creating" – Sebastian Kessel May 8 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.