CREATE FUNCTION dbo.NumberOfDaysBookedInWinter(@RoomNumber INTEGER, @WinterStart DATETIME, @WinterEnd DATETIME, @BreakWhenGreaterOrEqual INTEGER)
RETURNS INTEGER AS
/*
  Function which returns the number of days a room has been booked in the specified winter period

  LOG:
  20060217 JP: Created

  NOTES:
  - @BreakWhenGreaterOrEqual can be set to a value to have routine exit when this value is reached. Set to -1 to ignore

*/
BEGIN 

DECLARE 
@ArrivalDate DATETIME,
@DepartureDate DATETIME,
@NumberOfDays INTEGER--,
--@OwnerSettlement BIT

SET @NumberOfDays = 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

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

      SET @NumberOfDays=@NumberOfDays + DATEDIFF(DAY, @ArrivalDate, @DepartureDate)
      IF (@BreakWhenGreaterOrEqual > -1) AND (@NumberOfDays >= @BreakWhenGreaterOrEqual) -- optimization
        BREAK -- WHILE
    END 

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

CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur

RETURN @NumberOfDays

END

