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.
You need to sign in to view this answers