CREATE FUNCTION dbo.GetLastReservationDate (@ReferenceNumber INTEGER, @GuestNumber INTEGER, @BreakIfGreater DATETIME = 0)
RETURNS DATETIME AS
/*
   Created by Jacob Pedersen January 2006

   NOTE: It should not be needed to look in Arrangement and FollowUp tables. Overkill
*/
BEGIN 

DECLARE
@LastDate DATETIME,
@tmpLastDate DATETIME,
@tmpLastDate2 DATETIME

SET @LastDate = 0

BEGIN -- Reservations
  SELECT @tmpLastDate = MAX(CONVERT(DATETIME, ArrivalDate, 112)), @tmpLastDate2 = MAX(CONVERT(DATETIME, DepartureDate, 112)) FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND (@GuestNumber = -1 OR GuestNumber = @GuestNumber)

  IF @tmpLastDate > @LastDate
    SET @LastDate = @tmpLastDate
  IF @tmpLastDate2 > @LastDate
    SET @LastDate = @tmpLastDate2
END

IF (@BreakIfGreater = 0) OR (@LastDate <= @BreakIfGreater) -- ReservationRoomLines
BEGIN
  SELECT @tmpLastDate = MAX(CONVERT(DATETIME, ArrivalDate, 112)), @tmpLastDate2 = MAX(CONVERT(DATETIME, DepartureDate, 112)) FROM ReservationRoomLine RL WITH (NOLOCK) INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON
    RL.LineNumber = RLX.LineNumber AND RLX.ReferenceNumber=@ReferenceNumber AND (@GuestNumber = -1 OR RLX.GuestNumber = @GuestNumber)

  IF @tmpLastDate > @LastDate
    SET @LastDate = @tmpLastDate
  IF @tmpLastDate2 > @LastDate
    SET @LastDate = @tmpLastDate2
END

IF (@BreakIfGreater = 0) OR (@LastDate <= @BreakIfGreater) -- ReservationConferenceLine
BEGIN
  SELECT @tmpLastDate = MAX(CONVERT(DATETIME, ArrivalDate, 112)), @tmpLastDate2 = MAX(CONVERT(DATETIME, DepartureDate, 112)) FROM ReservationConferenceLine WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND (@GuestNumber = -1 OR GuestNo = @GuestNumber)

  IF @tmpLastDate > @LastDate
    SET @LastDate = @tmpLastDate
  IF @tmpLastDate2 > @LastDate
    SET @LastDate = @tmpLastDate2
END

RETURN @LastDate
END

