CREATE FUNCTION dbo.GetAvailableMins(@RoomNumber INT, @Arrival DATETIME, @IncludeInhouse BIT = 1)
RETURNS INT AS
/*
    Created by Jacob Pedersen 2006-07-17

    Last Changed:
    20061011 JP BUG: do not check outofhouse status when cashier enabled (see IsRoomAvailable)

    NOTES: @IncludeInhouse is used when allocating a room while checking in. If you want a room which is not already inhouse
*/
BEGIN 

DECLARE
@Result INT,
@tmpArrival DATETIME,
@CashierDisabled BIT

SELECT @CashierDisabled = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='CashierDisabling'

SELECT TOP 1 @tmpArrival=CONVERT(DATETIME, RL.ArrivalDate, 112)+CONVERT(DATETIME, RL.ArrivalTime, 108) FROM dbo.ReservationRoomLine RL WITH (NOLOCK)
INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RLX.ReferenceNumber NOT IN (-1) AND RL.RoomNumber=@RoomNumber /*AND CONVERT(DATETIME, RL.ArrivalDate, 112)+CONVERT(DATETIME, RL.ArrivalTime, 108) < @Departure*/ AND CONVERT(DATETIME, RL.DepartureDate, 112)+CONVERT(DATETIME, RL.DepartureTime, 108) >= @Arrival
--INNER JOIN dbo.Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND (R.Status IN (0,1,2,3,4,6)/*OutOfHouse*/ OR (R.Status=5 AND @IncludeInhouse=1))
INNER JOIN dbo.Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND (R.Status IN (0,1,2,3,4) OR (R.Status=5 AND @IncludeInhouse=1) OR (R.Status=6 AND @CashierDisabled=1))
ORDER BY CONVERT(DATETIME, RL.ArrivalDate, 112)+CONVERT(DATETIME, RL.ArrivalTime, 108)
IF @tmpArrival is null -- available in the whole period we are checking so set result to this many days
  SET @Result = 10000000 -- 10 mill for eternity DATEDIFF(MINUTE, @Arrival, @Departure)
ELSE
  SET @Result = DATEDIFF(MINUTE, @Arrival, @tmpArrival)

RETURN @Result

END

