


CREATE PROCEDURE DBO.MergeOutOfOrderPeriodsDate @Date CHAR(8) AS
SET NOCOUNT ON
/*
   Description: Merges all overlapping Room OutOfOrder periods for the specified day

   Changelog:
   JP20051205 Created

*/

DECLARE
@RoomNumber INT

DECLARE #Cursor CURSOR FORWARD_ONLY FOR

SELECT RoomNumber FROM ReservationRoomLine RL WITH (NOLOCK) INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND ReferenceNumber=0
AND ArrivalDate<=@Date AND DepartureDate>@Date
GROUP BY RoomNumber
HAVING COUNT(*)>1

OPEN #Cursor

FETCH NEXT FROM #Cursor
INTO @RoomNumber

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
  EXEC MergeOutOfOrderPeriods @RoomNumber

  FETCH NEXT FROM #Cursor
  INTO @RoomNumber
END

CLOSE #Cursor
DEALLOCATE #Cursor

