Databases DDL laboratory task preparation

allocate-customers-procedure.sql

USE Northwind;
GO
CREATE PROCEDURE AllocateEmployees
AS
	DECLARE @UnallocatedStayID int
	DECLARE @HotelID int
	DECLARE UnallocatedRooms CURSOR LOCAL FOR SELECT StayID, HotelID FROM HotelStays WHERE EndDate IS NULL AND RoomID IS NULL ORDER BY NEWID()
	OPEN UnallocatedRooms
	FETCH NEXT FROM UnallocatedRooms INTO @UnallocatedStayID, @HotelID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

		BEGIN TRANSACTION
			-- Try to find a free room in the same hotel
			DECLARE @FreeRoomID int
			DECLARE FreeRooms CURSOR LOCAL FOR SELECT RoomID FROM HotelRooms WHERE HotelID = @HotelID AND GuestCount < Capacity
			OPEN FreeRooms
			FETCH NEXT FROM FreeRooms INTO @FreeRoomID
			IF @@FETCH_STATUS = 0
			BEGIN
				-- has a room, allocate it
				UPDATE HotelStays SET RoomID = @FreeRoomID, EndDate = DATEADD(wk, 2, StartDate) WHERE StayID = @UnallocatedStayID;
				UPDATE HotelRooms SET GuestCount = GuestCount + 1 WHERE RoomID = @FreeRoomID;
			END
			ELSE
			BEGIN
				-- try to allocate a room in a hotel in the same city
				DECLARE @FreeHotelID int
				DECLARE FreeRoomsInOtherHotels CURSOR LOCAL FOR 
					SELECT TOP 1 H.HotelID, HR.RoomID FROM Hotels H
					JOIN HotelRooms HR ON HR.HotelID = H.HotelID
					WHERE H.City = (SELECT City FROM Hotels WHERE HotelID = @HotelID)
					AND HR.GuestCount < HR.Capacity;
				OPEN FreeRoomsInOtherHotels
				FETCH NEXT FROM FreeRoomsInOtherHotels INTO @FreeHotelID, @FreeRoomID
				IF @@FETCH_STATUS = 0
				BEGIN
					-- room in another hotel found
					UPDATE HotelStays SET HotelID = @FreeHotelID, RoomID = @FreeRoomID, EndDate = DATEADD(wk, 2, StartDate) WHERE StayID = @UnallocatedStayID;
					UPDATE HotelRooms SET GuestCount = GuestCount + 1 WHERE RoomID = @FreeRoomID;
				END

				CLOSE FreeRoomsInOtherHotels
				DEALLOCATE FreeRoomsInOtherHotels
			END

			CLOSE FreeRooms
			DEALLOCATE FreeRooms
		COMMIT
		FETCH NEXT FROM UnallocatedRooms INTO @UnallocatedStayID, @HotelID
	END
	CLOSE UnallocatedRooms
	DEALLOCATE UnallocatedRooms


	-- print and remove still unallocated stays
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

	BEGIN TRANSACTION
	DECLARE StillUnallocatedRooms CURSOR LOCAL FOR SELECT StayID FROM HotelStays WHERE EndDate IS NULL AND RoomID IS NULL;
	OPEN StillUnallocatedRooms
	FETCH NEXT FROM StillUnallocatedRooms INTO @UnallocatedStayID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT @UnallocatedStayID

		FETCH NEXT FROM StillUnallocatedRooms INTO @UnallocatedStayID
	END
	CLOSE StillUnallocatedRooms
	DEALLOCATE StillUnallocatedRooms

	DELETE FROM HotelStays WHERE EndDate IS NULL AND RoomID IS NULL;
	COMMIT
GO

create-and-alter-tables.sql

CREATE TABLE Hotels (
	HotelID int PRIMARY KEY NOT NULL,
	Name varchar(100) NOT NULL,
	City varchar(100) NOT NULL,
	Description text NOT NULL,
	Photo varbinary(1014) NULL
);

CREATE TABLE HotelRooms (
	RoomID int PRIMARY KEY NOT NULL,
	HotelID int FOREIGN KEY REFERENCES Hotels(HotelID) NOT NULL,
	Name char(10) NULL,
	Capacity int NOT NULL,
	DailyCost money NOT NULL
)

CREATE TABLE HotelStays (
	StayID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
	EmployeeID int FOREIGN KEY REFERENCES Employees(EmployeeID) NOT NULL,
	HotelID int FOREIGN KEY REFERENCES Hotels(HotelID) NOT NULL,
	RoomID int FOREIGN KEY REFERENCES HotelRooms(RoomID) NULL,
	StartDate date NOT NULL,
	EndDate date NULL,
	Cost money NULL
)


-- Alter tables
ALTER TABLE HotelRooms
ADD GuestCount int DEFAULT 0;

ALTER TABLE HotelRooms
ADD CONSTRAINT CHK_GuestCount CHECK (GuestCount <= Capacity);

ALTER TABLE HotelRooms
ALTER COLUMN Name char(50);


-- Populate hotels
INSERT INTO Hotels (HotelID, Name, City, Description) VALUES (1, 'Grand Hotel', 'Warsaw', 'Very good, I recommend');

INSERT INTO HotelRooms (RoomID, HotelID, Name, Capacity, DailyCost, GuestCount) VALUES 
	(1, 1, 'A', 5, 100, 1),
	(2, 1, 'B', 10, 150, 2),
	(3, 1, 'C', 8, 800, 3),
	(4, 1, 'D', 4, 100, 4)
  
 -- Populate hotel stays (with subquery)
 INSERT INTO HotelStays (HotelID, RoomID, Cost, EmployeeID, StartDate) 
SELECT Anything.HotelID, Anything.RoomID, 1 AS Cost, Anything.EmployeeID, dateadd(day, datediff(day, 1, GETDATE()), 0) AS StartDate
FROM (SELECT TOP 5 H.HotelID, HR.RoomID, E.EmployeeID FROM Hotels H
		JOIN HotelRooms HR ON H.HotelID = HR.HotelID
		JOIN Employees E ON 1 = 1
		ORDER BY NEWID()) Anything;

UPDATE HotelStays SET RoomID = NULL WHERE EndDate IS NULL;

update-stay-cost-trigger.sql

CREATE TRIGGER UpdateStayCost ON HotelStays
INSTEAD OF UPDATE
AS

DECLARE @HotelID int;
DECLARE @RoomID int;
DECLARE @StartDate date;
DECLARE @EndDate date;
DECLARE @EmployeeID int;
DECLARE @StayID int;
DECLARE UpdatedRows CURSOR LOCAL FOR SELECT HotelID, RoomID, StartDate, EndDate, EmployeeID, StayID FROM inserted;
OPEN UpdatedRows
FETCH NEXT FROM UpdatedRows INTO @HotelID, @RoomID, @StartDate, @EndDate, @EmployeeID, @StayID;

WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @PreviousStays int;
	SELECT @PreviousStays = COUNT(*) FROM HotelStays WHERE StartDate < @EndDate AND EmployeeID = @EmployeeID;

	DECLARE @StayLength int;
	SELECT @StayLength = datediff(day, @StartDate, @EndDate);

	DECLARE @DailyCost money;
	SELECT @DailyCost = DailyCost FROM HotelRooms WHERE RoomID = @RoomID;

	DECLARE @Cost money;
	SELECT @Cost = @StayLength * @DailyCost * (
		CASE
			WHEN @PreviousStays >= 2 THEN 0.9
			WHEN @PreviousStays >= 5 THEN 0.8
			ELSE 1
		END
	);

	UPDATE HotelStays SET HotelID = @HotelID, RoomID = @RoomID, EndDate = @EndDate, Cost = @Cost WHERE StayID = @StayID;

	FETCH NEXT FROM UpdatedRows INTO @HotelID, @RoomID, @StartDate, @EndDate, @EmployeeID, @StayID;
END

CLOSE UpdatedRows
DEALLOCATE UpdatedRows