CREATE FUNCTION [dbo].[GetNextGuestForCheckout] (@DepartureDate DATETIME, @RoomlineNumber INT, @GuestNumber INT)
RETURNS INT AS
BEGIN
  /*
    Created by Nenad Blagojevic Avgust 2012
  */

  DECLARE @Result INT
  --Try to find the next guest number on same room
  SELECT TOP 1 @Result = RLX1.GuestNumber
  FROM ReservationRoomlineXTab RLX1  WITH (NOLOCK) 
    INNER JOIN Reservations R1  WITH (NOLOCK) ON RLX1.ReferenceNumber = R1.ReferenceNumber AND RLX1.GuestNumber = R1.GuestNumber 
      AND DATEDIFF(DAY, R1.DepartureDate, @DepartureDate) = 0 
      AND R1.Status = 5
  WHERE RLX1.LineNumber = @RoomlineNumber AND RLX1.GuestNumber <> 0 AND RLX1.GuestNumber <> @GuestNumber
  ORDER BY RLX1.GuestNumber 
  --If not found find the next guest number on group reservation
  IF ISNULL(@Result, -1) = -1 AND @GuestNumber > 0
    SELECT TOP 1 @Result = R1.GuestNumber 
    FROM Reservations R1  WITH (NOLOCK) 
    WHERE R1.ReferenceNumber = (
      SELECT TOP 1 ReferenceNumber 
      FROM dbo.ReservationRoomlineXTab RLX  WITH (NOLOCK) 
      WHERE RLX.LineNumber = @RoomlineNumber
      ) AND ISNULL(R1.GuestNumber, 0) <> 0 AND R1.GuestNumber <> @GuestNumber
      AND DATEDIFF(DAY, R1.DepartureDate, @DepartureDate) = 0 AND R1.Status = 5
    ORDER BY R1.GuestNumber 
  RETURN @Result
END
GO


