NelIfandieva

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