www.webdeveloper.com
Results 1 to 4 of 4

Thread: search for overlaping booking times

  1. #1
    Join Date
    Jan 2013
    Posts
    20

    search for overlaping booking times

    Hello,
    I've got the following problem:

    My table contains 5 rows including two rooms beeing booked several having different booking start time and booking finish time and date booked.

    1 )Room code = AS1, start time (char) = 1800, finish time (char) = 1900, bookingDate (date) = '1-MAR-2013'
    2) Room code = AS1, start time (char) = 1400, finish time (char) = 2000, bookingDate (date) = '1-MAR-2013'
    3) Room code = AS1, start time (char) = 2000, finish time (char) = 2200, bookingDate (date) = '1-MAR-2013'
    4) Room code = AS2, start time (char) = 1800, finish time (char) = 2100, bookingDate (date) = '1-MAR-2013'
    5) Room code = AS2, start time (char) = 2200, finish time (char) = 2300, bookingDate (date) = '1-MAR-2013'

    This data is wrong according to database implementation and is only inserted for producing the following query:
    Basically i need to write a SQL query that will return the rooms that have overlaping booking times.
    So the output must be 1 and 2 lines as their start and finish times overlap. Can anyone please tell me how this can be done?
    Thanks in advance.
    ____________________________________________
    Don't confuse your path with your destination ...

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,251
    If you can change the start/end time fields to be TIME instead of CHAR (or even DATETIME or TIMESTAMP), and have the end_time be something like 18:59 (or 18:59.59) instead of 19:00, you could maybe do something like
    Code:
    select * from the_table as t1
    inner join the_table as t2 on t1.room_code = t2.room_code
    where
        t1.start_time between t2.start_time and t2.end_time
    or
        t1.end_time between t2.start_time and t2.end_time
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jan 2013
    Posts
    20
    NotDog,
    Thanks very much for your reply. I've already tried exactly the same code and some versions of it, but it does not work. The problem is that I am given already this scenario so I am not allowed to change the date type.
    ____________________________________________
    Don't confuse your path with your destination ...

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,251
    I suppose you could cast them to integer and subtract 1 from the end_time, perhaps? Oh, and I just realized you'd need to add a check in the where clause that the records are for the same date.
    Code:
    select * from the_table as t1
    inner join the_table as t2 on t1.room_code = t2.room_code
    where
        t1.booking_date = t2.booking_date
    and (
        cast(t1.start_time as unsigned) between cast(t2.start_time as unsigned) and (cast(t2.end_time as unsigned) - 1)
    or
        (cast(t1.end_time as unsigned) - 1) between cast(t2.start_time as unsigned) and (cast(t2.end_time as unsigned) - 1)
    )
    However, the downside of this is that it will require scanning of all records where the booking dates match, since it will have to cast every start/end time, and thus cannot take advantage of any indexes on those fields, I believe.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles