Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Hotel Puzzle

Here is a table for recording guests’ stays at a hotel (arrDate denotes arrival date, depDate is departure date). A new entry (for a new guest) could be accidentally put in for a room number, even before the existing guest in that room has not checked out. How would you redesign the table to fix this issue. Note – This question was asked @ Microsoft.

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE HotelStays
(roomNum INTEGER NOT NULL,
arrDate DATE NOT NULL,
depDate DATE NOT NULL,
guestName CHAR(30) NOT NULL,
PRIMARY KEY (roomNum, arrDate));

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--

CREATE FUNCTION checkdata(@RoomNo int , @DepDate date)
RETURNS DATE
AS
BEGIN
	DECLARE @dep Date
		IF ((SELECT COUNT(1) FROM Rom WHERE RoomNo = @RoomNo AND DepDate = @DepDate) > 1)
		SELECT @dep = Depdate FROM Rom WHERE RoomNo = @RoomNo
		ELSE
		SELECT @dep = ISNULL(( SELECT MAX(CAST(DepDate AS DATE)) DepDate FROM Rom 
		WHERE RoomNo = @RoomNo AND DepDate <> @DepDate),CAST('1900-01-01' AS DATE))
    RETURN @dep         
END
GO

--

ALTER TABLE HotelStays
	ADD CONSTRAINT chk_date_1 CHECK  (DATEDIFF(day,arrdate,dbo.checkdata(RoomNo,DepDate)) < 0 )
GO
--

--

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com