I spent a week on this and I’m stumped.
I have a table that looks like:
+---------+------------+-----------+-----------+------------+
| tableID | referenceID | OwnerID | entrydate | expdate |
| 0 | 3 | 1 | 2011-01-01 | 2011-02-01 |
| 1 | 3 | 4 | 2011-02-01 | 2011-02-22 |
| 2 | 4 | 5 | 2011-02-22 | 2011-03-22 |
| 3 | 5 | 1 | 2011-01-22 | 2011-02-12 |
| 4 | 6 | 1 | 2010-10-11 | 2010-11-22 |
| 5 | 6 | 6 | 2010-02-24 | 2010-04-10 |
| 6 | 6 | 5 | 2010-01-25 | 2010-02-08 |
| 7 | 8 | 1 | 2010-02-24 | 2010-12-10 |
| 8 | 8 | 5 | 2010-12-10 | 2011-02-08 |
+---------+------------+----------+------------+------------+
now() = 2011-01-05
I want to do a query that returns all the rows where
- OwnerID = 1
- now() between
EntryDate
andExpDate
- now() between
ExpDate
AND DATE_ADD(ExpDate
, INTERVAL 6 Month) - Do #3 as long as does_not_exist( a reference id that has a newer expdate then the one in number three for that particular referenceID)
I have 1-3 but I have a problem getting number 4.
Using the table above I highlight in green what I want and red what I do not.
+---------+------------+-----------+-----------+------------+
| tableID | referenceID | OwnerID | entrydate | expdate |
[COLOR="Blue"]| 0 | 3 | 1 | 2011-01-01 | 2011-02-01 |[/COLOR]
[COLOR="Red"]| 1 | 3 | 4 | 2011-02-01 | 2011-02-22 |
| 2 | 4 | 5 | 2011-02-22 | 2011-03-22 |[/COLOR]
[COLOR="blue"]| 3 | 5 | 1 | 2011-01-22 | 2011-02-12 |
| 4 | 6 | 1 | 2010-10-11 | 2010-11-22 |[/COLOR]
[COLOR="red"]| 5 | 6 | 6 | 2010-02-24 | 2010-04-10 |
| 6 | 6 | 5 | 2010-01-25 | 2010-02-08 |
| 7 | 8 | 1 | 2010-02-24 | 2010-12-10 |*
| 8 | 8 | 5 | 2010-12-10 | 2011-02-08 |[/COLOR]
+---------+------------+----------+------------+------------+
now() = 2011-01-05
As you can see Table ID 7 would not be valid because although
NOW() is between Expdate and 6months after expdate
Table ID 8 has an exp date which is newer than those in table id 7 for that Reference ID. I don’t know how to search for this in one query.