alexbancheva

Task_11_Available Room_21June2020

Feb 6th, 2021
848
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(MAX)
  3. AS
  4. BEGIN
  5.     DECLARE @RoomsBooked TABLE (Id INT)
  6.     INSERT INTO @RoomsBooked
  7.         SELECT DISTINCT r.Id
  8.         FROM Rooms AS r
  9.         LEFT JOIN Trips AS t ON t.RoomId = r.Id
  10.         WHERE r.HotelId = @HotelId AND @Date BETWEEN t.ArrivalDate AND t.ReturnDate AND t.CancelDate IS NULL
  11.  
  12.     DECLARE @Rooms TABLE (Id INT, Price DECIMAL(15,2), [Type] VARCHAR(20), Beds INT, TotalPrice DECIMAL(15,2))
  13.     INSERT INTO @Rooms
  14.         SELECT TOP(1) r.Id, r.Price, r.[Type], r.Beds, ((h.BaseRate + r.Price) * @People) AS TotalPrice
  15.         FROM Rooms AS r
  16.         LEFT JOIN Hotels AS h ON r.HotelId = h.Id
  17.         WHERE r.HotelId = @HotelId AND r.Beds >= @People AND r.Id NOT IN (SELECT Id
  18.                                                                             FROM @RoomsBooked)
  19.         ORDER BY TotalPrice DESC
  20.  
  21.     DECLARE @RoomCount INT = (SELECT COUNT(*)  FROM @Rooms)
  22.     IF (@RoomCount < 1)
  23.         BEGIN
  24.             RETURN 'No rooms available'
  25.         END
  26.  
  27.     DECLARE @Result VARCHAR(MAX) = (SELECT CONCAT('Room ', Id, ': ', [Type],' (', Beds, ' beds',')', ' - ', '$', TotalPrice)
  28.                                         FROM @Rooms)
  29.     RETURN @Result
  30. END
RAW Paste Data