CREATE FUNCTION [dbo].[GetLeadDays] (@AllotmentNumber INT, @RoomTypeNumber INT, @DateStamp DATETIME)
  RETURNS TABLE AS RETURN
/*
    Created by Kent Johansen 2019

    Last Changed:
*/
(
  SELECT 
  DATEDIFF(DAY, R.ReservationCreateDate, CONVERT(DATETIME, RL.ArrivalDate, 112)) AS LeadDays
  FROM dbo.ReservationRoomLine RL WITH (NOLOCK)
  INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber = RLX.LineNumber
    AND RLX.ReferenceNumber > 0
    AND CONVERT(DATETIME, RL.DepartureDate, 112) > @DateStamp
    AND CONVERT(DATETIME, RL.ArrivalDate, 112) <= @DateStamp
  INNER JOIN dbo.Reservations R WITH (NOLOCK) ON R.ReferenceNumber = RLX.ReferenceNumber
    AND R.GuestNumber = RLX.GuestNumber
    AND R.Status <= 6
  INNER JOIN dbo.ReservationMain RM WITH (NOLOCK) ON RM.ReferenceNumber = R.ReferenceNumber
    AND RM.AllotmentNumber = @AllotmentNumber
  LEFT OUTER JOIN dbo.Rooms WITH (NOLOCK) ON Rooms.RoomNumber = RL.RoomNumber
  WHERE (RL.RoomNumber IS NULL AND RL.BookedTypeNumber = @RoomTypeNumber) OR (RL.RoomNumber > 0 AND Rooms.RoomTypeNumber = @RoomTypeNumber)
)