CODEWITHSUNDEEP
phpmysqlsql
user2135867
user2135867

Reputation:

PHP/SQL Show rows that are 60 days plus in the past

I have a table with a datetime column (Y-m-d H:i:s)

I want to be able to show all rows that have a datetime column that is 60 days plus in the past

i have tried this but it doesn't seem to be showing the correct rows:

SELECT * from customer_communication WHERE datetime < DATE_SUB(DATE(now()), INTERVAL 60 DAY) order by datetime DESC

There is a customer column in this table too because each customer will have multiple rows. each time they are contacted it adds a new row for that customer so will i need to group by customer or group by datetime?

Upvotes: 0

Views: 124

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Your question is a bit unclear. I am interpreting it as "get all customers that haven't had a communication in the past 60 days". The logic for this is to aggregate by the customer and look at the maximum communication date:

SELECT customer
from customer_communication
group by customer
having max(datetime) < DATE_SUB(DATE(now()), INTERVAL 60 DAY)
order by max(datetime) DESC;

Upvotes: 2

Related Questions