CREATE FUNCTION [dbo].[GetReservationCreateStatus] (@ref INT)  
RETURNS INT AS  
BEGIN 
  declare @cStatus int
  select @cStatus = ReservationStatus
  from followup WITH (nolock) 
    inner join reservationfollowupxtab WITH (nolock) 
      on reservationfollowupxtab.followupnumber = followup.followupnumber 
  where referencenumber = @ref and guestnumber = 0 and subtype = 1
  if @cStatus is null
  begin
    select top 1 @cStatus = ReservationStatus
    from followup WITH (nolock) 
      inner join reservationfollowupxtab WITH (nolock) 
        on reservationfollowupxtab.followupnumber = followup.followupnumber 
    where referencenumber = @ref and guestnumber = 0 and note like '%created%'
  end
  if @cStatus is null
    SELECT @cStatus = status FROM reservations WITH (nolock) WHERE ReferenceNumber = @ref
  return @cStatus
END

