# Exam_June_2018 _TripService_Problem18

Feb 14th, 2019
128
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
1. CREATE FUNCTION udf_GetAvailableRoom(@HotelId INT, @DATE DATE, @People INT)
2. RETURNS VARCHAR(200)
3. AS
4. BEGIN
5.     --(h.BaseRate + r.Price) * @People
6.     DECLARE @hotel INT = (SELECT h.Id FROM Hotels AS h WHERE h.Id = @HotelId)
7.     DECLARE @room INT = (SELECT r.Id FROM Rooms AS r WHERE r.HotelId = @hotel)
8.     DECLARE @roomBeds INT = (SELECT r.Beds FROM Rooms AS r WHERE r.HotelId = @hotel)
9.     DECLARE @roomType NVARCHAR(20) = (SELECT r.TYPE FROM Rooms AS r WHERE r.HotelId = @hotel)
10.     DECLARE @roomTripStart DATE = (SELECT t.ArrivalDate FROM Trips AS t WHERE t.RoomId = @room)
11.     DECLARE @roomTripEnd DATE = (SELECT t.ReturnDate FROM Trips AS t WHERE t.RoomId = @room)
12.     DECLARE @roomTripCancelled DATE = (SELECT t.CancelDate FROM Trips AS t WHERE t.RoomId = @room)
13.     DECLARE @hotelBaseRate DECIMAL(15,2) = (SELECT h.BaseRate FROM Hotels AS h WHERE h.Id = @HotelId)
14.     DECLARE @roomPrice DECIMAL(15,2) = (SELECT r.Price FROM Rooms AS r JOIN Hotels AS h ON r.HotelId = h.Id WHERE h.Id = @HotelId)
15.     DECLARE @totalPrice DECIMAL(15,2) = (@hotelBaseRate + @roomPrice) * @People
16.
17.     IF((@roomTripCancelled IS NOT NULL) AND (@DATE BETWEEN @roomTripStart AND @roomTripEnd))
18.     BEGIN
19.         RETURN 'No rooms available'
20.     END
21.
22.     IF(@roomBeds < @People)
23.     BEGIN
24.         RETURN 'No rooms available'
25.     END
26.
27.     RETURN 'Room ' + CAST(@room AS NVARCHAR(10)) + ': @roomType ' + CAST(@roomBeds AS NVARCHAR(10)) + ' beds - \$' + CAST(@totalPrice AS NVARCHAR(30))
28. END
RAW Paste Data