CREATE FUNCTION [dbo].[GetReservationStatusAtDateFollowup] (@AReferenceNumber INT, @AGuestNumber INT, @ADate DATETIME)  

/*
  Created by Kent Johansen 20060517

  Return the ReservationStatus at a specific date for at specific reservation
  Changes:
    KJ 20191209: Bug: Fix for "Wrong status". Case #CR175357
    KJ 20191126: Bug: Fix for "Wrong status". Case #CR174617
    KJ 20191114: Ext: Rollback to 2010version where followup is used due to performance issue using Auditlog. Case #CR173863
    KJ 20151005: Bug: Fix for wrong date for online res that is cancelled because of uncomple res.
    KJ 20150921: Rewritten to use AuditLog. Req #2321
    KJ 20080709: Added ISNULL to result
*/

RETURNS INT AS  
BEGIN 
  DECLARE @Note VARCHAR(100)
  DECLARE @Status INT
  SET @Status = -1

  SELECT @Status = ReservationStatus FROM Followup WITH (NOLOCK)
  INNER JOIN ReservationFollowupXTab WITH (NOLOCK) ON ReservationFollowupXTab.FollowupNumber = Followup.FollowupNumber 
  WHERE ReferenceNumber = @AReferenceNumber
  AND GuestNumber = @AGuestNumber
  AND [Date] <= @ADate
  AND SubType IN (3,4)

  IF @@ROWCOUNT = 0 -- Reservation is not Inhouse, OutOfHouse, Cancelled, Noshow
  BEGIN
    SELECT TOP 1 @Status = ReservationStatus FROM Followup WITH (NOLOCK)
    INNER JOIN ReservationFollowupXTab WITH (NOLOCK) ON ReservationFollowupXTab.FollowupNumber = Followup.FollowupNumber 
    WHERE ReferenceNumber = @AReferenceNumber
    AND GuestNumber = @AGuestNumber
    AND [Date] <= @ADate
    AND UserCreated <> 1
    ORDER BY [Date] DESC  
  END
  ELSE
  BEGIN
    -- Cancelled OR Nowshow
    SELECT @Note = Note, @Status = ReservationStatus FROM Followup WITH (NOLOCK)
    INNER JOIN ReservationFollowupXTab WITH (NOLOCK) ON ReservationFollowupXTab.FollowupNumber = Followup.FollowupNumber 
    WHERE ReferenceNumber = @AReferenceNumber
    AND GuestNumber = @AGuestNumber
    AND [Date] <= @ADate
    AND SubType = 3
    IF @@ROWCOUNT > 0
    BEGIN
      IF @Note LIKE '%cancelled%'
        SET @Status = 7
      ELSE IF ISNULL(@Status, -1) = -1
        SET @Status = 8
      ELSE
        SET @Status = 7
    END 
    ELSE
    BEGIN
      -- Inhouse OR OutOfHouse
      SELECT @Status = ReservationStatus FROM Followup WITH (NOLOCK)
      INNER JOIN ReservationFollowupXTab WITH (NOLOCK) ON ReservationFollowupXTab.FollowupNumber = Followup.FollowupNumber 
      WHERE ReferenceNumber = @AReferenceNumber
      AND GuestNumber = @AGuestNumber
      AND [Date] <= @ADate
      AND SubType = 4
      AND ReservationStatus = 5
      IF @@ROWCOUNT > 0
        SET @Status = 6
      ELSE
        SET @Status = 5
    END
  END
  RETURN ISNULL(@Status, 0)
END