-- DATABASE SYSTEMS 2019/20
-- LAB EXERCISE 13
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- FUNCTIONS
-- ~~~~~~~~~~~~~~~~~~~~
-- Create a FUNCTION to calculate the rental booking price for a property  for specified check-in and check-out dates.
/*
	Hints:
			* You can use the GetBookingDays FUNCTION to work out the days
			* What table stores the rental price for a property and there may be multiple matching prices
					Add an ORDER BY and (OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY OR SELECT TOP 1)
			* Don't forget to apply any discounts :)
*/





GO

-- Test your function
-- Uncomment the following and fill in the blanks (...)

--DECLARE @PropertyRef CHAR(7) = '...', @CheckInDate DATE = '...', @CheckOutDate DATE = '...'

--SELECT * FROM PropertyRentalPrice 
--WHERE ...;

--SELECT * FROM Property WHERE PropertyRef = ...;

--SELECT * FROM dbo.GetBookingDays(..., ...);

--SELECT dbo.GetRentalBookingPrice(..., ..., ...)



GO
-- STORED PROCEDURES
-- ~~~~~~~~~~~~~~~~~~~~

/*
	Write a STORED PROCEDURE to return: 
		the properties in a particular postcode area 
		that are AVAILABLE for specified check-in and check-out dates
			Hint: use the function created in class today
		sorted by the postcode. 
		Return the minimum and Maximum rental price as OUTPUT PARAMETERS for the the postcode area for the specified check-in and check-out dates.
			Hint: You will need an aggregate and you can also use the GetRentalBookingPrice formal created earlier
*/


GO



-- Test your stored procedure


GO