I am trying return a dataset that fall between a 30 day window and belong to another set of values.
Here is how the table looks:
| EMAIL | LIST | CREATED_DATE |
---------------------------------------------
| email1 prop1 2024-06-12 |
| email2 prop2 2024-06-05 |
| email3 prop1 2024-05-11 |
| email4 prop3 2024-06-30 |
---------------------------------------------
So I want to exclude anything on LIST prop1, prop2, and prop3 that was created during June.
The return records should look like this:
| EMAIL | LIST | CREATED_DATE |
---------------------------------------------
| email3 prop1 2024-05-11 |
---------------------------------------------
Although email3 is on prop1, it falls outside of the date criteria.
Here is the query I have now:
SELECT * FROM table
WHERE (`created_date` NOT BETWEEN '2024-06-01' AND '2024-06-30'
AND `LIST` NOT IN ('prop1','prop2','prop3'))
But that query removes everything that is in prop1, prop2, and prop3.
I still need to return the prop1 records that falls outside of the date range.
How can I fix this?
Combine the two conditions, then negate it to make it an exclusion.
SELECT *
FROM TableName
WHERE NOT (
created_date BETWEEN '2024-06-01' AND '2024-06-30'
AND list IN ('prop1','prop2','prop3')
)
And if you then apply deMorgan's Laws to this, you get the equivalent:
SELECT *
FROM TableName
WHERE created_date NOT BETWEEN '2024-06-01' AND '2024-06-30'
OR list NOT IN ('prop1','prop2','prop3')
which is the same as your query, except AND is replaced with OR.