[SQL] Get back the NULL date records between another set of dates

In MySQL 5.7, I have a table that looks like this:

| email | a_last_sent | a_last_open | b_last_sent | b_last_open | ...more columns
----------------------------------------------------------------------------------
| email1 | 2024-06-12 | 2024-06-25 | 2024-03-06 | 2024-06-06 |
----------------------------------------------------------------------------------
| email2 | 2024-06-01 | 2024-06-16 | 2024-06-05 | NULL |
----------------------------------------------------------------------------------
| email3 | 2024-06-01 | NULL | 2024-05-12 | 2024-06-10 |
----------------------------------------------------------------------------------
| email4 | NULL | NULL | 2024-04-02 | 2024-05-11 |
----------------------------------------------------------------------------------
| email5 | 2024-06-09 | 2024-05-01 | NULL | NULL |
----------------------------------------------------------------------------------


I am trying to return the last_open rows that are NULL that were last_sent 180 days (from June).

So the results need to have a last_sent date going back 180 days AND a NULL last_open date.

In the above example, there are some that have a NULL last_sent date AND a NULL last_open date - I don't need to return those records.

Just the last_sent going back 180 days (from June) AND NULL last_open.

Here is what I tried:

SELECT * FROM `table` WHERE
(`a_last_sent` BETWEEN '2024-01-02' AND '2024-06-30' AND `a_last_open` IS NULL)
+ (`b_last_sent` BETWEEN '2024-01-02' AND '2024-06-30') AND (`b_last_open` IS NULL)
+ (`c_last_sent` BETWEEN '2024-01-02' AND '2024-06-30') AND (`c_last_open` IS NULL)
...more columns


Using the above table example, I was hoping to get results like this:

| email | a_last_sent | a_last_open | b_last_sent | b_last_open | ...more columns
----------------------------------------------------------------------------------
| email2 | 2024-06-01 | 2024-06-16 | 2024-06-05 | NULL |
----------------------------------------------------------------------------------
| email3 | 2024-06-01 | NULL | 2024-05-12 | 2024-06-10 |
----------------------------------------------------------------------------------


Since email2 and email3 have a last_sent date that falls within 180 days from June AND a NULL last_open date, these results would be what I'm looking for.

But using the above query, I am getting zero results.

Please help me figure this out, or let me know if this can even be done.

Thank you.

Continue reading...