MYSQL - Selecting existing users who have not made an entry in the last 3 months, but have made an entry within the last 6 months.

Answer 1

There are several ways to do this. But I think you're close. You don't seem to have the date range correct for 3-6 months in your primary query. You were checking for anything from 6 months until now. That doesn't match the spec of the OP - users of an event 3-6 months ago, but not within the last 3 months. Logically it seems the same, but try this.

SELECT DISTINCT e.id, e.insertion_date, c.name
FROM event e
JOIN client c ON c.id = e.clientFK
WHERE e.clientFK NOT IN (
  SELECT e.clientFK FROM event e
  WHERE e.insertion_date > (NOW() - INTERVAL 3 MONTH)
)
AND e.insertion_date BETWEEN (NOW() - INTERVAL 3 MONTH) AND (NOW() - INTERVAL 6 MONTH)
AND e.officeFK = 1
ORDER BY e.insertion_date DESC