Exam_June_2018 _TripService_Problem18

CREATE FUNCTION udf_GetAvailableRoom(@HotelId INT, @DATE DATE, @People INT)
RETURNS VARCHAR(200)
AS
BEGIN
    --(h.BaseRate + r.Price) * @People
    DECLARE @hotel INT = (SELECT h.Id FROM Hotels AS h WHERE h.Id = @HotelId)
    DECLARE @room INT = (SELECT r.Id FROM Rooms AS r WHERE r.HotelId = @hotel)
    DECLARE @roomBeds INT = (SELECT r.Beds FROM Rooms AS r WHERE r.HotelId = @hotel)
    DECLARE @roomType NVARCHAR(20) = (SELECT r.TYPE FROM Rooms AS r WHERE r.HotelId = @hotel)
    DECLARE @roomTripStart DATE = (SELECT t.ArrivalDate FROM Trips AS t WHERE t.RoomId = @room)
    DECLARE @roomTripEnd DATE = (SELECT t.ReturnDate FROM Trips AS t WHERE t.RoomId = @room)
    DECLARE @roomTripCancelled DATE = (SELECT t.CancelDate FROM Trips AS t WHERE t.RoomId = @room)
    DECLARE @hotelBaseRate DECIMAL(15,2) = (SELECT h.BaseRate FROM Hotels AS h WHERE h.Id = @HotelId)
    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)
    DECLARE @totalPrice DECIMAL(15,2) = (@hotelBaseRate + @roomPrice) * @People

    IF((@roomTripCancelled IS NOT NULL) AND (@DATE BETWEEN @roomTripStart AND @roomTripEnd))
    BEGIN
        RETURN 'No rooms available'
    END

    IF(@roomBeds < @People)
    BEGIN
        RETURN 'No rooms available'
    END

    RETURN 'Room ' + CAST(@room AS NVARCHAR(10)) + ': @roomType ' + CAST(@roomBeds AS NVARCHAR(10)) + ' beds - $' + CAST(@totalPrice AS NVARCHAR(30))
END