CREATE FUNCTION dbo.FiveWeekPeriodInWinter(@RoomNumber INTEGER, @WinterStart DATETIME, @WinterEnd DATETIME, @BreakWhenFound TINYINT)
RETURNS INTEGER AS
/*
  Function which returns the number of 5 week periods in a specified winter period

  LOG:
  20060217 JP: Created

  NOTES:
  - 

*/
BEGIN 

DECLARE 
@ArrivalDate DATETIME,
@DepartureDate DATETIME,
--@OwnerSettlement BIT,
@FiveWeekPeriod INTEGER,
@CurrentPeriodStart DATETIME,
@CurrentPeriodEnd DATETIME,
@FiveWeekPeriods INTEGER

SET @FiveWeekPeriod=5*7
SET @CurrentPeriodStart = 0
SET @FiveWeekPeriods = 0

DECLARE #RoomLineCur CURSOR LOCAL FAST_FORWARD FOR

SELECT DISTINCT CONVERT(DATETIME, RL.ArrivalDate, 112), CONVERT(DATETIME, RL.DepartureDate, 112)/*, ISNULL(P.OwnerSettlement,0)*/ 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 CONVERT(DATETIME, RL.DepartureDate, 112) > @WinterStart AND CONVERT(DATETIME, RL.ArrivalDate, 112) <= @WinterEnd
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) -- DO NOT DELETE! Needed to sort reservations to have easy check for holes

OPEN #RoomLineCur

FETCH NEXT FROM #RoomLineCur
INTO @ArrivalDate, @DepartureDate--, @OwnerSettlement

WHILE (@@FETCH_STATUS <> -1)
BEGIN
--  IF @OwnerSettlement = 0 -- skip reservations with products with OwnerSettlement. this cannot be done in SQL because it would exclude reservations with no products also
    IF @@FETCH_STATUS <> -2 -- Row still exists?
    BEGIN
      IF @ArrivalDate < @WinterStart
        SET @ArrivalDate = @WinterStart
      IF @DepartureDate > @WinterEnd
        SET @DepartureDate = @WinterEnd

      IF @CurrentPeriodStart = 0
      BEGIN
        SET @CurrentPeriodStart = @ArrivalDate
        SET @CurrentPeriodEnd = @DepartureDate
      END
      ELSE
      BEGIN
        IF @CurrentPeriodEnd = @ArrivalDate -- match
          SET @CurrentPeriodEnd = @DepartureDate
        ELSE -- hole, new period
        BEGIN
          IF DATEDIFF(DAY, @CurrentPeriodStart, @CurrentPeriodEnd) >= @FiveWeekPeriod
            SET @FiveWeekPeriods = @FiveWeekPeriods + 1 -- a 5 week period is found, lets go look for more

          SET @CurrentPeriodStart = @ArrivalDate
          SET @CurrentPeriodEnd = @DepartureDate

          IF (@FiveWeekPeriods > 0) AND (@BreakWhenFound <= @FiveWeekPeriods)
            BREAK -- WHILE - optimization
        END
      END
    END 

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

CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur

IF DATEDIFF(DAY, @CurrentPeriodStart, @CurrentPeriodEnd) >= @FiveWeekPeriod
  SET @FiveWeekPeriods = @FiveWeekPeriods + 1  -- a 5 week period is found

RETURN @FiveWeekPeriods

END

