Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Unanswered: gaps in integer ranges

    hello, i have quite a challenge on my hands here and would appreciate any help.

    I have a table variable that stores integer ranges representing times of the day:

    select * from @reservations

    room date | starttime | endtime
    1 2004-12-11 0 1440 (represents an entire day in minutes)
    2 2004-12-12 420 1020
    3 2004-12-14 200 600
    4 2004-12-15 0 200
    4 2004-12-15 500 1000


    I need to be able to return the minutes that are open for each room. The @reservations table shows me the times that are blocked.

    I'd like to analyze each row and return an integer range representing gaps in the day, where 0-1440 represents an entire day.

    Based on the @reservations table above, I'd like to write something that returns:

    room date starttime endtime
    2 2004-12-12 0 420
    2 2004-12-12 1020 1440
    3 2004-12-14 0 200
    3 2004-12-14 600 1440
    4 2004-12-15 200 500
    4 2004-12-15 1000 1440

    This result represents the times in minutes that are available.

    I have no clue how to do this without using a numbers table and checking each minute in each day for each row in the table. Id like to not do that because of sheer performance reasons. There is a possiblity that I will have hundreds of rows in the @reservations table.

    I was hoping someone could provide some insight as to how to approach this. Thank you ahead of time!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in the results, do you also want

    1 2004-12-12 0 1440
    1 2004-12-13 0 1440
    1 2004-12-14 0 1440
    1 2004-12-15 0 1440
    2 2004-12-13 0 1440
    2 2004-12-14 0 1440
    2 2004-12-15 0 1440
    3 2004-12-11 0 1440
    3 2004-12-12 0 1440
    3 2004-12-13 0 1440
    3 2004-12-15 0 1440
    4 2004-12-11 0 1440
    4 2004-12-12 0 1440
    4 2004-12-13 0 1440
    4 2004-12-14 0 1440

    or do you just want the available ranges for dates where some part of the date is already booked?
    Last edited by r937; 12-13-04 at 12:56.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    53
    I want the available ranges for the dates where some part of the date is already booked

    and I just thought that I will also need to know the available times if there is nothing booked in the room...yikes...

    so if I have no row in my @reservations table for room 1 on 2004-12-21 then I would want to return

    1 2004-12-21 0 1440

    Hmmm...would something like this work for that scenario??

    I have a stored procedure that going to give me these results. I am writing a search. In the stored procedure I pass in the start date of the search and the end date of the search. So i could potentially, check that if there are no rows in the @reservations table for that date and room, i would need to return 0-1440

    thank you!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The best way I have found to handle this functionality it to create a table in your database that consists of nothing but incrementing value from zero to ....well, whatever the highest number you end up needing.

    By supplying a starting date to a SELECT query based upon this table, you can use the values to create a list of time intervals of whatever duration you choose. From this dataset, left join your Reservations table and return only the records where no corresponding Reservation exists. Voila! Your resultset shows all the unbooked times.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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