Discriminating Date Range based on ID

Databases
1

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

  1. OwnerID = 1
  2. now() between EntryDate and ExpDate
  3. now() between ExpDate AND DATE_ADD(ExpDate, INTERVAL 6 Month)
  4. 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.

2

I think I figured #4 out. Compare the current last exp date of that member record with the max expiration date of all the records… If I don’t reply to this thread again then that is the answer. YAY