CREATE FUNCTION dbo.GetCurrentCount (@AllotmentNumber INT, @RoomTypeNumber INT, @DateStamp DATETIME)
RETURNS INTEGER AS  
/*
    Created by Jacob Pedersen 2005

    Last Changed:
    JP 20060626 OPTIMIZE: added dbo. to all tables
    JP 20060503 BUG: CurrentCount might be null
    KJ 20060428: Fix for Arrivaldate out of range
    JP20050916 BUG: fix for DepartureDate > 2070
    2005-08-22 fix for namelist bug
*/
BEGIN 
  DECLARE @CurrentCount INT

  SELECT @CurrentCount = ISNULL(SUM(NumberOfRooms), 0) FROM dbo.ReservationRoomLine RL WITH (NOLOCK) WHERE LineNumber IN (
  SELECT DISTINCT RL.LineNumber FROM dbo.ReservationRoomLine RL WITH (NOLOCK)
  INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RLX.ReferenceNumber>0 AND RL.LineNumber=RLX.LineNumber AND CONVERT(/*CANNOT BE SMALLDATETIME!*/DATETIME, RL.DepartureDate, 112)>@DateStamp AND CONVERT(/*CANNOT BE SMALLDATETIME!*/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)
  )

  IF @CurrentCount = NULL
    SET @CurrentCount = 0

  RETURN @CurrentCount
END

