CREATE FUNCTION dbo.GetEarlyCheckoutType  (@ReferenceNumber INT, @GuestNumber INT, @CurrentDate DATETIME)
RETURNS INT
AS
BEGIN
  DECLARE @Return INT
  SELECT TOP 1 @Return= (CASE WHEN (Res.GuestNumber = 0 AND CONVERT(VARCHAR, Res.DepartureDate, 112)= CONVERT(VARCHAR, @CurrentDate, 112) AND CONVERT(VARCHAR, Res.ArrivalDate, 112) = CONVERT(VARCHAR, RL.DepartureDate, 112))
      OR (Res.GuestNumber > 0 AND CONVERT(VARCHAR, Res.DepartureDate, 112)=CONVERT(VARCHAR, @CurrentDate, 112) AND CONVERT(VARCHAR, Res.ArrivalDate, 112) = CONVERT(VARCHAR, Res.DepartureDate, 112)) THEN 2
      WHEN (Res.GuestNumber = 0 AND CONVERT(VARCHAR, RL.DepartureDate, 112)>CONVERT(VARCHAR, @CurrentDate, 112)) OR (Res.GuestNumber > 0 AND CONVERT(VARCHAR, Res.DepartureDate, 112)>CONVERT(VARCHAR, @CurrentDate, 112)) THEN 1
      ELSE 0 END)
  FROM RESERVATIONS Res WITH (NOLOCK)
    LEFT OUTER JOIN RESERVATIONROOMLINEXTAB RLX WITH (nolock) ON Res.ReferenceNumber = RLX.ReferenceNumber
      AND Res.GuestNumber = RLX.GuestNumber
    LEFT OUTER JOIN RESERVATIONROOMLINE RL WITH (nolock) ON RLX.LineNumber = RL.LINENUMBER
  WHERE Res.ReferenceNumber= @ReferenceNumber AND Res.GuestNumber= @GuestNumber

  RETURN @Return
END

