SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Discriminating Date Range based on ID

    I spent a week on this and I'm stumped.

    I have a table that looks like:
    Code:
    +---------+------------+-----------+-----------+------------+
    | 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.
    Code:
    +---------+------------+-----------+-----------+------------+
    | 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
    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.
    Please...Never describe anything to me using foo and bar.

  2. #2
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Please...Never describe anything to me using foo and bar.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •