Consider I have the following table and current date is 2022-09-01:
Requirement: I want to get all users that have no event_name like cbt care in the past 14 days and onwards into the future.
I have this query:
SELECT * FROM test_table
WHERE event_name LIKE "%cbt care%"
AND start_date <= DATE_SUB(NOW(), INTERVAL 14 DAY)
;
Which returns:
The issue is that user_id = x does have a cbt care event in 2022-09-10 which is 9 days ahead of current date (2022-09-01).
How to return only users satisfy requirement posted above?
>Solution :
SELECT user_id,
COUNT(CASE WHEN event_name LIKE '%cbt care%' AND start_date
> CURDATE() - INTERVAL 14 day THEN 1 END) AS count_recent
FROM test_table
GROUP BY user_id
HAVING count_recent = 0;
https://www.db-fiddle.com/f/64j7L1VZsVdLYqmcQ2NrvV/0

