CREATE FUNCTION [dbo].[LastReservationPeriod] (@RoomNumber INTEGER) 
RETURNS @Result TABLE
(
  RoomNumber INT,
  Arrival DATETIME,
  Departure DATETIME
) AS
/*
  Function which returns the period of the last stay in the room taking ownersettlement into account.

  LOG:
  20060221 JP: Created

  NOTES:
  - 

*/
BEGIN 
  INSERT @Result
    SELECT @RoomNumber, MAX(CONVERT(DATETIME,RL.ArrivalDate,112)+CONVERT(DATETIME,RL.ArrivalTime,108)), MAX(CONVERT(DATETIME, RL.DepartureDate, 112)+CONVERT(DATETIME, RL.DepartureTime, 108)) FROM ReservationRoomLine RL WITH (NOLOCK) 
    INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RL.RoomNumber=@RoomNumber
    INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.Status IN (5,6)
    LEFT OUTER JOIN Products P WITH (NOLOCK) ON P.ProductNumber=R.ProductNumber
    WHERE R.ProductNumber IS NULL OR ISNULL(P.OwnerSettlement,0) = 0

  RETURN
END