CREATE FUNCTION [dbo].[GetReservationStatusAtDate] (@AReferenceNumber INT, @AGuestNumber INT, @ADate SMALLDATETIME)  

/*
  Created by Kent Johansen 20060517

  Return the ReservationStatus at a specific date for at specific reservation
  Changes:
    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 @Status INT
  SET @Status = -1

  SELECT TOP 1 @Status = New_Value
  FROM Audit_Log_Transactions WITH (NOLOCK)
  INNER JOIN Audit_Log_Data WITH (NOLOCK) ON Audit_Log_Data.Audit_Log_Transaction_Id = Audit_Log_Transactions.Audit_Log_Transaction_Id
  WHERE Audit_Log_Transactions.Table_Name = 'Reservations'
  AND Audit_Log_Data.Col_Name = 'Status'
  AND Audit_Log_Data.Key1 = CAST(@AReferenceNumber AS VARCHAR)
  AND Audit_Log_Data.Key2 = CAST(@AGuestNumber AS VARCHAR)
  AND Audit_Log_Transactions.Modified_Date <= CAST(@ADate + ' 23:59:59:997' AS DATETIME)
  ORDER BY Audit_Log_Data_Id DESC

  IF ISNULL(@Status, -1) <= -1
    SELECT @Status = Status FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @AReferenceNumber AND GuestNumber = @AGuestNumber
  RETURN @Status
END

