CREATE FUNCTION dbo.IsRoomInhouse (@RoomNumber INTEGER)
RETURNS BIT AS  
/*
  Function which returns True if specified room is inhouse now

  LOG:
  20060720 JP: OPTIMIZE: 33% faster SQL
  20060703 JP: true/false inverted, and problem if res 0 is inhouse
  20060215 JP: Created

  NOTES:
  - Same logic as in TRoomFX.IsRoomInhouse function

*/
BEGIN 
  DECLARE
  @Result BIT,
  @ResCount INTEGER

--  SELECT @ResCount = COUNT(*) FROM dbo.ReservationRoomLine RL (NOLOCK)
--  INNER JOIN dbo.ReservationRoomLineXTab RLX (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RL.RoomNumber=@RoomNumber AND RLX.ReferenceNumber > 0
--  INNER JOIN dbo.Reservations R (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.Status=5

  SELECT @ResCount =  COUNT(*) FROM dbo.Reservations R WITH (NOLOCK) 
  INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.ReferenceNumber>0 AND R.Status=5
  INNER JOIN dbo.ReservationRoomLine RL WITH (NOLOCK) ON  RL.LineNumber=RLX.LineNumber AND RL.RoomNumber=@RoomNumber 

  IF @ResCount > 0
    SET @Result = 1
  ELSE
    SET @Result = 0

  RETURN @Result
END

