CREATE FUNCTION dbo.GetFirstArrivingDateOnProduct (@ProductNumber INT)
RETURNS DATETIME AS  
/*
	Created by: Jacob Pedersen 20081127

             Last Changed:
              AV 2008 1202 Status < 7 altered to Status < 6

	Dependencies: Used by DeleteOldProducts

	NOTE: We don't care about the different dates on the +product lines since we cannot delete part of a reservation, so let's just take the first date
*/
BEGIN 
	DECLARE @Result DATETIME
	SELECT @Result = CONVERT(DATETIME, '20991231', 112) 

	-- Reservations
	DECLARE @DateSubResRoom DATETIME
	SELECT @DateSubResRoom = MIN (CONVERT(DATETIME, ArrivalDate, 112)) FROM dbo.Reservations WITH (NOLOCK) WHERE GuestNumber > 0 AND Status < 6 AND (ProductNumber=@ProductNumber OR ProductNumber2=@ProductNumber OR ProductNumber3=@ProductNumber OR ProductNumber4=@ProductNumber)
	IF @DateSubResRoom < @Result
		SET @Result = @DateSubResRoom

	-- ReservationRoomLine
	DECLARE @DateMainResRoom DATETIME
	SELECT @DateMainResRoom = MIN (CONVERT(DATETIME, RL.ArrivalDate, 112)) FROM dbo.ReservationRoomLine RL WITH (NOLOCK)
	INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND GuestNumber = 0 AND (ProductNumber=@ProductNumber OR ProductNumber2=@ProductNumber OR ProductNumber3=@ProductNumber OR ProductNumber4=@ProductNumber)
	INNER JOIN dbo.Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber = RLX.GuestNumber AND Status < 6 
	IF @DateMainResRoom < @Result
		SET @Result = @DateMainResRoom

	-- ReservationConferenceLine
	DECLARE @DateConfRes DATETIME
	SELECT @DateConfRes = MIN (CONVERT(DATETIME, RCL.ArrivalDate, 112)) FROM dbo.ReservationConferenceLine RCL WITH (NOLOCK) INNER JOIN dbo.Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RCL.ReferenceNumber AND R.Status < 6 AND RCL.ProductNumber=@ProductNumber
	IF @DateConfRes < @Result
		SET @Result = @DateConfRes

	-- TODO: customer fields per hotel - since we have no dates for these products, we have a problem. we can either ignore this and DeleteOldProducts fails when trying to delete the product or we can delete any reference first, or we can set a very low date which will block for ever deleting the product if used on a customer even if it's empty
	-- others?
	
	RETURN @Result
END

