sqlmysqldataset

Excluding records between dates AND with an associating value

I am trying return a dataset that fall between a 30 day window and belong to another set of values.

I hope that makes sense.

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?

Solution

  • 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.