Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

In mysql how to get users that have no event X in past 14 days and neither in the future

Consider I have the following table and current date is 2022-09-01:

enter image description here

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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:

enter image description here

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading