Lets say that I've got two tables:

ROOM:
ID_ROOM : int
Name: Nvarchar(50)

RESERVATION
ID_RESERVATION : int // unique ID of reservation
ID_ROOM : int // which room is reserved
DATE: SmallDateTime // date of reservation
HOUR_BEGINNING : int // hour in which reservation begin
HOUR_END : int // hour in which reservation end

And now I want to find ALL rooms which can be reserved for at least DURATION(int) hours between HOUR_SEEK_START(int) and HOUR_SEEK_END(int) in day DAY(SmallDateTime). For example: I want to find all rooms which can be reserved for at least 2 hours between 10 and 16 on 16.05.2008. I hope I set the record straight.

With the above, you cannot make a reservation that goes over multiple days. I am assuming it is for an office of some sort?

What is your question exactly? You want a SQL SELECT query for the above?

SELECT

RO.Name

FROM

-- Reference to the two tables

ROOM AS RO, RESERVATION AS RES

WHERE

-- Assume that reservation must be without pause

RO.ID_ROOM IN

(

-- Subquery that gets all rooms that have more than the required hours available on the specific date

)

 There is possibly a direct set method way of getting the availability however here is a way of getting it:

1. Set up a temporary table for the output:
2. Set up boolean variables for each hour of the day
   
3. Set a cursor to read all the rooms
   
4. Read a room
5. Clear the boolean variables
   
6. For a given room and day select all the records, read them and set hour variables for each hour booked
7. When all the records for that room and day read, output a record to the temp table if the required period is unbooked
8. Read the next room record
9. At the end select from the temporary table
   

r.heinkens: Yes, reservation is only for a single day. The question is "How to make SQL query for the above" :)

TATWORTH: I thouht about similar solution, but i was concerned about effectiveness. If You suggest such sulution that probably means that it is good enought.

 >TATWORTH: I thouht about similar solution, but i was concerned about effectiveness. If You suggest such sulution that probably means that it is good enought.

A pure set-based solution if it was possible will be faster, but a well designed cursor solution will also be fast. The key thing about CURSOR is to always close and deallocate when finished with them. You need to build up the stored procedure piece by piece, executing it in a query window as you can use print statements to check the functioning.

You could try posting your query either on the MSDN SQL forum and/or www.sqlcentral.com

I always like to use table variables. You can initialize the table variable as a normal table. Use a bulk insert on it (you only need to store the Id's of the room. In my example I suggest to use a subquery. You can even better do this with a table variable. Declare a table variable and load it with all the room_id's. Use a where on the room_id SELECT that filters on the hours requirement and on the date requirement. You should use a subquery on the reservation table, where you join on room_id's with the two tables. Filter on the date and list the maximum available hours of the room on that date. If the maximum time available is equal or more, than you found a candidate room.

See http://www.sqlteam.com/article/using-table-variables for more on table variables.

 >You should use a subquery on the reservation table, where you join on room_id's with the two tables. Filter on the date and list the maximum available hours of the room on that date. If the maximum time available is equal or more, than you found a candidate room.

This sounds good but what if a 2-hour session is wanted? The maximum method will find 2 1-hour slots and treat them as a contiguous slot.

You should take one step back. Look at the reservation table. First, regard the reservation table as to have only information for one day, the day you want to make a reservation. This can be realized with the filter with a datediff. Then, you will have an overview of reservations with starting hours and ending hours. This is the inverse of what you are looking for. If a reservation is made for 13 to 15 hours, it means that all other hours are available.

Given the complexity, I would suggest to start off with a CURSOR (even though this imposes a performance penalty).

Start with looking at the time 00 hours (or opening hours, if that may be the case). Look at the first start time of reservation, if any, or closing. Mark the time in between as available (and here I would recommend to use a table variable). If the room is available when the reservation has ended, look for the next reservation time or closing time. Use a "global table variable" to do a union on all the intermediate results from the CURSOR.

Structure would be like:

Define global table variable

Define and open CURSOR

Define local table variable (same structure as global variable)

Get first available time slot and INSERT into local table variable

Determine if additional time slot is available and repeat the above

When all time slots are calculated, do a UNION on local and global table variable.

Close cursor

r.heinkens: I will surely think about your solution, but firstly I need to read more about curors and table variables because I'm not quite familiar with those two concepts.

Thank You both for your answers, I'm going to learn a lot from them.

Gobol:

 Lets say that I've got two tables:

ROOM:
ID_ROOM : int
Name: Nvarchar(50)

RESERVATION
ID_RESERVATION : int // unique ID of reservation
ID_ROOM : int // which room is reserved
DATE: SmallDateTime // date of reservation
HOUR_BEGINNING : int // hour in which reservation begin
HOUR_END : int // hour in which reservation end

And now I want to find ALL rooms which can be reserved for at least DURATION(int) hours between HOUR_SEEK_START(int) and HOUR_SEEK_END(int) in day DAY(SmallDateTime). For example: I want to find all rooms which can be reserved for at least 2 hours between 10 and 16 on 16.05.2008. I hope I set the record straight.

Not so obvious SELECT, though not that hard, in fact.

First, let's cast the phrase "for at least DURATION(int) hours between HOUR_SEEK_START(int) and HOUR_SEEK_END(int)" into something set oriented. Why not create a temp table to keep potentially good time intervals, those of DURATION(int) length that could fit between HOUR_SEEK_START(int) and HOUR_SEEK_END(int). Later for each room we'll just check out if among these intervals there will be one not spoiled by related reservation records.

  CREATE TABLE #Hours(
    IndexId int IDENTITY (0, 1) NOT NULL, 
    hr nchar(8))
    
  INSERT INTO #Hours(hr) VALUES ('0');  INSERT INTO #Hours(hr) VALUES ('1');
  INSERT INTO #Hours(hr) VALUES ('2');  INSERT INTO #Hours(hr) VALUES ('3');
  INSERT INTO #Hours(hr) VALUES ('4');  INSERT INTO #Hours(hr) VALUES ('5');
  INSERT INTO #Hours(hr) VALUES ('6');  INSERT INTO #Hours(hr) VALUES ('7');
  INSERT INTO #Hours(hr) VALUES ('8');  INSERT INTO #Hours(hr) VALUES ('9');
  INSERT INTO #Hours(hr) VALUES ('10');  INSERT INTO #Hours(hr) VALUES ('11');
  INSERT INTO #Hours(hr) VALUES ('12');  INSERT INTO #Hours(hr) VALUES ('13');
  INSERT INTO #Hours(hr) VALUES ('14');  INSERT INTO #Hours(hr) VALUES ('15');
  INSERT INTO #Hours(hr) VALUES ('16');  INSERT INTO #Hours(hr) VALUES ('17');
  INSERT INTO #Hours(hr) VALUES ('18');  INSERT INTO #Hours(hr) VALUES ('19');
  INSERT INTO #Hours(hr) VALUES ('20');  INSERT INTO #Hours(hr) VALUES ('21');
  INSERT INTO #Hours(hr) VALUES ('22');  INSERT INTO #Hours(hr) VALUES ('23');
  
  CREATE TABLE #TimeSpansRequested(hr1 int, hr2 int)
  
  INSERT INTO #TimeSpansRequested(hr1, hr2) 
  SELECT h1.IndexId, h2.IndexId  
  FROM #Hours h1, #Hours h2
  WHERE (h2.IndexId - h1.IndexId = @DURATION) 
        AND (h1.IndexId BETWEEN @HOUR_SEEK_START AND (@HOUR_SEEK_END - @DURATION))

The above T-SQL code is self explaining. We self joined #Hours temp table to get a set of time intervals, and then restricted the intervals to those of @DURATION length that are between @HOUR_SEEK_START and @HOUR_SEEK_END hours. The result is getting into #TimeSpansRequested temp table. Its records are just pairs of (hr1, hr2) hours - borders of pottentially good intervals. And finally:

 SELECT * 
 FROM ROOM           
 WHERE EXISTS(SELECT hr1
              FROM #TimeSpansRequested
              WHERE NOT EXISTS(SELECT ID_TIMESPAN 
                               FROM RESERVATION  
                               WHERE (RESERVATION.ID_OBJ = ROOM.ID_OBJ)
                                      AND ([DATE] = @DAY)
                                      AND ((hr1 BETWEEN HOUR_BEGINNING AND HOUR_END)
                                      OR (hr2 BETWEEN HOUR_BEGINNING AND HOUR_END))
                                     )
              )

 In the code above we are checking out for each door if exists some potentially good interval that is not spoiled by any of reservation timespan. 

Hope this helps.

Thanks for the good puzzle.

 SergeyS : I'm the one that should thanks ;) Thanks that really clever solution, and quite easy to understand. I haven't expected that it could be made so easy. Thanks again.

 Is there a problem with this really simple query ?

SELECT  rms.*
FROM    Rooms                   AS rms
        INNER JOIN Reservations AS rss
        ON      rms.ID        = rss.RoomID
WHERE   rss.Date              = @rdate
    AND (rss.HourEnd - rss.HourBegin) >= @perdiod
    and rss.HourBegin >= @start
    and rss.HourEnd <= @end

I think it solves the problem.

Am I wrong ?

scifire:

 Is there a problem with this really simple query ?

 

SELECT  rms.*
FROM    Rooms                   AS rms
        INNER JOIN Reservations AS rss
        ON      rms.ID        = rss.RoomID
WHERE   rss.Date              = @rdate
    AND (rss.HourEnd - rss.HourBegin) >= @perdiod
    and rss.HourBegin >= @start
    and rss.HourEnd <= @end

 

I think it solves the problem.

Am I wrong ?

If so, you are selecting those rooms that for the chosen day 

- have reservations longer than @DURATION:   AND (rss.HourEnd - rss.HourBegin) >= @perdiod

- and the reservations are inside the desired time range:   and rss.HourBegin >= @start and rss.HourEnd <= @end

How does it connect to this thread's problem?

 Sorry SergeyS I have misunderstand the problem. My query doesn't return anything useful in the context of the initial problem.