SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql query help

    Hi to all,

    I am developing a booking system .I have problem to get records .

    Scenario is.

    I have a database table where i define two fields arrival_date and departure_date.

    I have a calendar when i move to month beneath the calendar i am showing all records of that month.

    My query is

    SELECT * FROM `roombooking` WHERE arrival_date between '2010-05-01' and '2010-05-31' or departure_date between '2010-05-01' and '2010-05-31'

    it show me all records whose arrival_date or departure_date is between '2010-05-01' and '2010-05-31'

    For example if a person book a room with arrival_date is '2010-04-01' and departure date is '2010-06-01'

    this records should also come in month of may because month of may is between april and june .

    kindly help me

    regards

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    here are all the ways that a booking can overlap the month of may --
    Code:
                     2010-05-01            2010-05-31         
                           |                     |               
    1 arrival---departure  |                     |               
                           |                     |               
    2             arrival--|--departure          |               
                           |                     |               
    3                      | arrival---departure |               
                           |                     |               
    4            arrival---|---------------------|---departure       
                           |                     |               
    5                      |            arrival--|--departure 
                           |                     |               
    6                      |                     |  arrival---departure
    with me so far?

    your query is successful for cases 2, 3, and 5, but you are missing case 4, right?

    the easiest way to include it is like this --
    Code:
    ... WHERE departure >= '2010-05-01'  /* eliminates case 1 */
          AND arrival   <= '2010-05-31'  /* eliminates case 6 */
    notice that it's an AND which means both of those have to be true
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937

    Yes i missed 4th point .

    thanks again


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
  •