1. Bogdan Varlamov
  2. 2013_03_23

Commits

Bogdan Varlamov  committed d440798

adding another example that will complicate things further and break the initial answer and the extended answer, requiring an answer with more conditional checks

  • Participants
  • Parent commits f6db277
  • Branches master

Comments (0)

Files changed (4)

File 1_FirstAnswer.sql

View file
  • Ignore whitespace
 
 -- however, this query only works if nobody ever reserves the same room 
 -- more than once 
-SELECT        RoomNum
+SELECT DISTINCT        RoomNum
 FROM            Reservations
 WHERE        ([End] < '2013-02-14') OR
                          (Start > '2013-02-17')

File 3_ExtendedAnswer.sql

View file
  • Ignore whitespace
 
 
 --we want all the room numbers that aren't in the conflict set
-SELECT 	RoomNum
+SELECT DISTINCT	RoomNum
 FROM 	Reservations
 WHERE	RoomNum NOT IN
 (--this get's all of the conflicting reservations

File 4_AnotherReservationBreaksExtendedAnswer.sql

View file
  • Ignore whitespace
+-- this reservation starts before the start date of our vacation
+-- and ends after the end date; therefore the room is unavailable.
+-- but both the initial answer query and the extended answer
+-- incorrectly returns room 4 as available
+INSERT INTO Reservations(RoomNum, Start, [End])
+VALUES        (4, '2013-02-10', '2013-02-20')

File 5_FinalAnswer.sql

View file
  • Ignore whitespace
+-- this query is just like the extended answer
+-- except it also counts date ranges that span the target reservation date
+-- as being in the conflict set
+
+SELECT DISTINCT	RoomNum
+FROM 	Reservations
+WHERE	RoomNum NOT IN
+(--this get's all of the conflicting reservations
+	SELECT        RoomNum
+	FROM          Reservations AS innerRS
+	WHERE        (Start BETWEEN '2013-02-14' AND  '2013-02-17') -- reservations can't start in our target date range
+			  OR ([End] BETWEEN '2013-02-14' AND '2013-02-17') -- reservations can't end in our target date range
+			  OR (Start < '2013-02-14') AND ([End] > '2013-02-17') -- reservations can't span our target date range
+)