CREATE FUNCTION [dbo].[GetMainReservationLinkNumber](@ReferenceNumber INT, @AddressType INT)
RETURNS int
AS
BEGIN
	DECLARE @LinkNumber INT

	IF @AddressType > 0
	BEGIN
		-- Find the AddressType ask for on the reservation
		SET @LinkNumber = (Select TOP 1 RESERVATIONADDRESS.Link	
		   FROM RESERVATIONADDRESSXTAB WITH (NOLOCK)
			INNER JOIN RESERVATIONADDRESS WITH (NOLOCK) ON RESERVATIONADDRESSXTAB.ADDRESSNUMBER = RESERVATIONADDRESS.AddressNumber
			INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONADDRESSXTAB.REFERENCENUMBER = RESERVATIONS.ReferenceNumber AND RESERVATIONADDRESSXTAB.GUESTNUMBER = RESERVATIONS.GuestNumber
		   WHERE 
			RESERVATIONS.REFERENCENUMBER = @ReferenceNumber and
			RESERVATIONS.GuestNumber = 0 and
			RESERVATIONADDRESS.ADDRESSTYPE = @AddressType  )
	END
	ELSE
	BEGIN
		-- Find the highest AddressType on the reservation that has a linknumber
		SET @LinkNumber = (SELECT TOP 1 RESERVATIONADDRESS.Link	
		   FROM RESERVATIONADDRESSXTAB WITH (NOLOCK)
			INNER JOIN RESERVATIONADDRESS WITH (NOLOCK) ON RESERVATIONADDRESSXTAB.ADDRESSNUMBER = RESERVATIONADDRESS.AddressNumber
			INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONADDRESSXTAB.REFERENCENUMBER = RESERVATIONS.ReferenceNumber AND RESERVATIONADDRESSXTAB.GUESTNUMBER = RESERVATIONS.GuestNumber
		   WHERE 
			RESERVATIONS.REFERENCENUMBER = @ReferenceNumber and RESERVATIONS.GuestNumber = 0 and isnull(RESERVATIONADDRESS.Link, -1) > 0
		   GROUP BY 
			  RESERVATIONADDRESS.ADDRESSTYPE,
			  RESERVATIONADDRESS.LINK
		   ORDER BY RESERVATIONADDRESS.ADDRESSTYPE DESC)
	END


	RETURN isnull(@LinkNumber, -1) -- Be sure to return something if null

END