#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    43
    Rep Power
    11

    Question Select events occurring during set period


    Hi, I have a table of events that I want to select from for a set period.

    This is via a start date and an end date which seems ok with the following query:

    PHP Code:
    WHERE (Date_Start BETWEEN '$month_start' AND '$month_end')
     OR (
    Date_End BETWEEN '$month_start' AND '$month_end'
    However the problem I have is what about an event that starts before my start date and ends after my end date?

    The workaround that I have come up with is
    PHP Code:
    OR (Date_Start '$month_start' AND Date_End '$month_end'
    I'm concerned that this may not be the most efficient way of doing this so would appreciate some help and advice to get me going down the right path...
    Last edited by gizz; December 12th, 2012 at 01:44 AM. Reason: typo
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,007
    Rep Power
    9398
    How about events starting before the period but ending during it? And starting during but ending after? I'm guessing what you want is all the events that are active during that time period?
    Or in other words, the event started before the period ended and the event ends after the period started.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    here are all the ways the date ranges can overlap
    Code:
                          $month_start              $month_end
                                |                         |               
    1    date_start---date_end  |                         |               
                                |                         |               
    2               date_start--|--date_end               |               
                                |                         |               
    3                           |  date_start---date_end  |               
                                |                         |               
    4               date_start--|-------------------------|--date_end       
                                |                         |               
    5                           |             date_start--|--date_end 
                                |                         |               
    6                           |                         |  date_start---date_end
    with me so far?

    okay, so you want to find all events for case 2 through case 5

    the simplest sql for this is --
    Code:
    WHERE date_end   >= $month_start  /* eliminates case 1 */
      AND date_start <= $month_end    /* eliminates case 6 */
    sweet, eh?

    Comments on this post

    • gizz agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    43
    Rep Power
    11
    Sweet - Yes, that's a much shorter version.

    The former worked but it just looked bloated!
  8. #5
  9. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,007
    Rep Power
    9398
    God knows I'm not one to revive threads, but over here we discovered that <= and >= aren't quite right.

    Start reading around this post.
    Originally Posted by Jacques1
    Um, I just realized that r937's query and diagram are wrong, because they don't allow schedules to directly follow each other.

    The ">=" and "<=" should be ">" and "<" like you said in your text:
    Code:
    WHERE
    	date_end > $data_start
    	AND date_start < $date_end
    And here's an updated diagram:

    Originally Posted by requinix
    Actually the diagram and SQL is valid but it uses a slightly different definition of "overlap": the ranges overlap even if they connect at the endpoints. Like 9:00-10:00 overlaps with 10:00-11:00. That may be true for some cases but I think for most that's not what's intended.

    Code:
                          $month_start              $month_end
                                |                         |               
    1a   date_start---date_end  |                         |               
                                |                         |               
    1b   date_start------date_end                         |               
                                |                         |               
    2               date_start--|--date_end               |               
                                |                         |               
    3a                          |  date_start---date_end  |               
                                |                         |               
    3b                          date_start------date_end  |               
                                |                         |               
    3c                          |  date_start------date_end
                                |                         |               
    3d                          date_start---------date_end
                                |                         |               
    4a              date_start--|-------------------------|--date_end       
                                |                         |               
    4b                          date_start----------------|--date_end       
                                |                         |               
    4c              date_start--|------------------date_end       
                                |                         |               
    5                           |             date_start--|--date_end 
                                |                         |               
    6a                          |                         |  date_start---date_end
                                |                         |               
    6b                          |                         date_start------date_end
    So yes, > and <.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Originally Posted by requinix

    Actually the diagram and SQL is valid but it uses a slightly different definition of "overlap": the ranges overlap even if they connect at the endpoints. Like 9:00-10:00 overlaps with 10:00-11:00. That may be true for some cases but I think for most that's not what's intended.
    especially if you need some time -- like, around a half a day or a day -- to clean the room before the next guest arrives

    it's a business decision whether >= and <= versus > and < are called for

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Well, if you do need some time between the events, you'll want a specific amount of time rather than an "infinitesimal pause".

    On the other hand, an event directly following another one may be perfectly valid in some cases -- like phone calls or whatever.

    So I think ">" and "<" really are the only sensible options in this case -- either that or a much more sophisticated approach, which accounts for travel times, pauses and whatnot.

    But let's not argue about details. I just brought it up because I stumbled upon it while going though some examples.
    Last edited by Jacques1; January 15th, 2013 at 03:14 PM.

IMN logo majestic logo threadwatch logo seochat tools logo