Create a list of lapsed customers from a shopping database

Databases
1

Hi,
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?

Many thanks.
A

2

I think that query does actually work. I’d been playing with the database records which is why I wasn’t getting the result I expected. When I put the database records back to as they should be it worked.

I’ll leave the post up in case anyone can come up with a more elegant way of working it out.

Many thanks,
A

3

no, it isn’t

OrderDate >= CURRENT_DATE - INTERVAL 3 MONTH looks specifically for orders within the last three months

your query should do exactly what you say you want

4

Thanks for your query. To list customers who have not made any purchase/orders for the last 3 months, use the below query:

SELECT custId, custname, custemail FROM tblcustomers where custId not in (select OrderCustId from tblorders where OrderDate between DATE_SUB(CURDATE(),INTERVAL 3 MONTH ) and CURDATE())

Let me know if it works

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.