CODEWITHSUNDEEP
phpmysqljoin
jasonhughes
jasonhughes

Reputation: 33

SQL: selecting customers that haven't had an entry in past 2 weeks

I have a database that has multiple tables to track customers and our interactions with them, among other things. I'm creating a report using PHP that should display all customers that have an open status and don't have an interaction logged in the pas 2 weeks. I was trying to do a query, then while processing those results it would run another query based on that, but it was very slow and I couldn't get it to work correctly. I think the JOIN function is my answer, but I'm not entirely sure how to implement it.

my tables have these fields:

customers: customer_id
customers_marketing_options: customer_id, status
customer_interactions: customer_id, created_on (yyyy-mm-dd hh:mm:ss)

right now, I have it selecting all customers that have an open status. Then with those results, I select all interactions with that customer id, created_on being at most 2 weeks prior, sorting that by the created_on date and selecting the top row (most recent interaction). however, that returns the interaction for customers that have a warm status. However, it doesn't check to see if they have an interaction in the past 2 weeks.

This probably sounds confusing, however any help or guidance would be appreciated.

Upvotes: 0

Views: 114

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Give this a try:

SELECT ci.customer_id, ci.created_on FROM customer_interactions ci
LEFT JOIN customer_interactions ci2
  ON (ci.customer_id = ci2.customer_id AND ci.created_on < ci2.created_on)
JOIN customers_marketing_options cmo
  ON (ci.customer_id = cmo.customer_id AND status = 'Open')
WHERE ci2.created_on IS NULL AND ci.created_on < now() - interval 2 week

Check the working example here.

Upvotes: 0

fthiella
fthiella

Reputation: 49049

SELECT cmo.customer_id,
       Max(ci.created_on) as last_interaction
FROM customer_marketing_options cmo
     inner join customer_interactions ci
     on cmo.customer_id = ci.customer_id
WHERE
  cmo.status = 'open'
GROUP By
  cmo.customer_id
HAVING Max(ci.created_on) < DATE_SUB(CURDATE(),INTERVAL 14 DAY)

Upvotes: 1

Marc B
Marc B

Reputation: 360602

SELECT customer_ID, created_on
FROM customer_interactions
WHERE MAX(created_on) <= (now() - INTERVAL 2 week)
  AND ... status stuff ...
GROUP BY customer_ID

Upvotes: 1

Related Questions