SergeyS's blog

That ASP.NET Forums / SQL Server, SQL Server Express / is getting rather interesting sometimes. Last time it was the puzzle "Not so obvious SELECT"

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. 

Hope this helps.

Thanks for the good puzzle.