CREATE FUNCTION dbo.HolesInPeriod (@RoomNumber INT, @Arrival DATETIME, @Departure DATETIME)
RETURNS BIT AS
/*
  Function which returns 1 (True) if there is a hole in the specified room and period

  LOG:
  20070122 JP: BUG: if we only have one reservation, we have no holes regardless of it's length
  20061002 JP: BUG: returns 0 when there are no reservations (which is equal to one big hole!)
  20060222 JP: Created

  NOTES:
  - 
*/
BEGIN 

DECLARE 
@Result BIT,
@ArrivalDate DATETIME,
@DepartureDate DATETIME,
@CurrentPeriodStart DATETIME,
@CurrentPeriodEnd DATETIME

SET @CurrentPeriodStart = 0
SET @CurrentPeriodEnd = 0

SET @Result = 1 -- Assume no reservations and thereby one big hole

DECLARE #RoomLineCur CURSOR LOCAL FAST_FORWARD FOR

  SELECT DISTINCT CONVERT(DATETIME, RL.ArrivalDate,112), CONVERT(DATETIME, RL.DepartureDate,112) FROM ReservationRoomLine RL WITH (NOLOCK)
  INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RLX.ReferenceNumber>0 AND RL.RoomNumber=@RoomNumber AND RL.ArrivalDate <= @Departure AND RL.DepartureDate > @Arrival
  INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.Status<=6
  LEFT OUTER JOIN Products P WITH (NOLOCK) ON P.ProductNumber=R.ProductNumber
  WHERE R.ProductNumber IS NULL OR ISNULL(P.OwnerSettlement,0)=0
  ORDER BY CONVERT(DATETIME, RL.ArrivalDate,112) DESC -- DO NOT DELETE!

OPEN #RoomLineCur

FETCH NEXT FROM #RoomLineCur
INTO @ArrivalDate, @DepartureDate

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF @@FETCH_STATUS <> -2 -- Row still exists?
  BEGIN
    SET @Result = 0 -- ok, we do have reservations, but we have to look for holes

    IF @ArrivalDate < @Arrival
      SET @ArrivalDate = @Arrival
    IF @DepartureDate > @Departure
      SET @DepartureDate = @Departure

    IF @CurrentPeriodStart = 0 -- first time
    BEGIN
      SET @CurrentPeriodStart = @ArrivalDate
      SET @CurrentPeriodEnd = @DepartureDate
    END
    ELSE
    BEGIN
      IF @CurrentPeriodStart = @DepartureDate -- match
        SET @CurrentPeriodStart = @ArrivalDate
      ELSE -- hole! new period
      BEGIN
        SET @Result = 1
        BREAK -- WHILE - we have a hole and therefore not a 4-5 week period
      END
    END

  END 

  FETCH NEXT FROM #RoomLineCur
  INTO @ArrivalDate, @DepartureDate
END

CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur

-- If we have reservations but no hole was detected (eg if one res is found only) we need to check if our read combined period is really 4 weeks
IF (@Result = 0) AND (DATEDIFF(Day, @CurrentPeriodStart, @CurrentPeriodEnd) < 4*7)
  SET @Result = 1

RETURN @Result

END

