I’m stuck trying to create a query that will combine two database tables and show me a list of customers who have not made a purchase in the last 3 months.
My customers are stored in tblcustomers
custId (unique ID)
custname (their name)
custemail (their email)
remindersent (Y or N depending on whether a reminder has already been sent to this customer)
My orders are stored in tblorders
OrderId (unique ID)
OrderCustId (customer ID, relates to custId in tblcustomers)
OrderDate (DATETIME when last order was made)
I’d like to query the database to get a list of all customers who have not made a purchase/order in the last 3 months. I came up with this but it’s not quite right:
SELECT custId, custname, custemail FROM tblcustomers LEFT OUTER JOIN tblshoporders ON OrderCustId = custId AND OrderDate >= CURRENT_DATE - INTERVAL 3 MONTH WHERE OrderCustId IS NULL AND remindersent = 'N'
this query does list customers who placed an order over 3 months ago but still lists them even if they’ve made a subsequent order recently - i.e. it’s only looking at orders over 3 months ago and ignoring if they’ve made a recent purchase.
Another way of thinking about it is to list all customers but ignore/don’t show ones who have made an order in the last 3 months.
Can anyone help?