CODEWITHSUNDEEP
mysqlsql
user3032605
user3032605

Reputation: 51

SQL, select entries older than 30 days, filter entries older than 30 days that have entries within the last 30 days

Not the best worded question I'll admit. I am trying to write a query to select entries older than 30 days. I have this so far

SELECT farm_list.customer_id, data_stored.`timestamp`  
FROM farm_db.data_stored data_stored
       INNER JOIN farm_db.farm_list farm_list
          ON (data_stored.farm_code = farm_list.farm_code)
WHERE data_stored.`timestamp` >= CURDATE() - 30
GROUP BY farm_list.customer_id

This works to select all the entries older than 30 days and group them by customer.

So some customers have entries within the last 30 days AND older than 30 days. I would like to exclude those customers. The aim of the query is to highlight inactive customers. The data_stored table is updated every time there is customer activity on the server.

If I've been unclear, please just ask and I'll try to clarify. Any help would be much appreciated.

Upvotes: 0

Views: 1700

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269473

What you are saying is that the most recent timestamp is older than 30 days for a customer. So, let's implement this:

SELECT farm_list.customer_id, max(data_stored.`timestamp`) as maxts
FROM hathor_hb.data_stored data_stored INNER JOIN
     farm_db.farm_list farm_list
     ON (data_stored.farm_code = farm_list.farm_code)
GROUP BY farm_list.customer_id
HAVING maxts <= CURDATE() - 30;

Upvotes: 2

Related Questions