A T-SQL simple puzzle on ASP.NET forum.

http://forums.asp.net/p/1299660/2535207.aspx#2535207

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.

 My reply was:

---

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.