SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict rabbitdog's Avatar
    Join Date
    Jul 2001
    Location
    So. Tenn.
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Intersecting date ranges

    I have a problem I've been working on for a day now - and I'm officially ready to throw the computer out of the window.

    I have two date ranges, one stored as start and end in a database, and another coming via form input from a user.

    I need a way to return all the rows within the database where the two date ranges intersect.

    Assuming date range one is represented by s1 - e1 and date range two (database date range) is represented by s2 - e2, the following 4 conditions can exist, and would be TRUE (rows match):

    PHP Code:
    s1 s2 e1 e2 ]
    s2 s1 e2 e1 )
    s1 s2 e2 e1 )
    s2 s1 e1 e2 
    the following would be FALSE (rows don't match):

    PHP Code:
    s1 e1 ) [ s2 e2 ]
    s2 e2 ] ( s1 e1 
    The basic WHERE clause I came up with was (in pseudo code):

    PHP Code:
    (s1<=s2 and e1<=e2) or (s2<=s1 and e2<=e1) or (s1<=s2 and e2<=e1) or (s2<=s1 and e1<=e2) AND NOT ((s1 s2 AND e1 s2) OR (s2<s1 AND e2<s1)) 
    Which doesn't work (doesn't exclude rows at all)

    I also tried:

    PHP Code:
    ((s1<=s2 and e1<=e2) or (s2<=s1 and e2<=e1) or (s1<=s2 and e2<=e1) or (s2<=s1 and e1<=e2)) AND NOT ((s1 s2 AND e1 s2) OR (s2<s1 AND e2<s1)) 
    which returns no rows.

    Any clue about where I've gone wrong?

    Thanks in advance,

    liz.
    Mr Vector
    High quality, royalty free, vector graphics
    for t-shirt artists and graphic/web designers.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    look at it this way -- if both s1 and e1 are before s2, or if both s1 and s2 are after e2, then those are your "don't want" situations

    so just do WHERE NOT ( what i just said )

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •