CREATE PROCEDURE [dbo].[GA_BookStatusIntoPast] @FormatedStartDate CHAR(8), @FormatedEndDate CHAR(8), @TheHotelNumber INT AS
SET NOCOUNT ON

DECLARE
@DateStamp CHAR(8),
@RoomTypeNumber INT,
@SegmentNumber INT,
@PointOfSaleNumber INT,
@HotelNumber INT,
@SoldRooms SMALLINT,
@Adults SMALLINT,
@Children1 SMALLINT,
@Children2 SMALLINT,
@NumberOfConfirmed SMALLINT,
@NumberOfProvisionals SMALLINT,
@NumberOfGuaranteed SMALLINT,
@NumberOfTentative SMALLINT,
@NumberOfWaitinglists SMALLINT,
@NumberOfDayrooms SMALLINT,
@FreeRoomsAllotment SMALLINT,
@TotalRoomsAllotment SMALLINT,
@PaxAllotment SMALLINT,
@NumberOfRoomsSoldToday SMALLINT,
@NumberOfOutOfOrder SMALLINT,
@SoldPrice MONEY

IF @TheHotelNumber > -1
BEGIN
  DELETE FROM RoomBookingStatus WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND HotelNumber = @TheHotelNumber

  DECLARE #RoomBookingStatusCur CURSOR FAST_FORWARD FOR

  SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms, FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment, NumberOfRoomsSoldToday, NumberOfOutOfOrder, SoldPrice
  FROM #RoomBookingStatus
  WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND HotelNumber = @TheHotelNumber OPTION (KEEP PLAN)
END
ELSE -- all hotels
BEGIN
  DELETE FROM RoomBookingStatus WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate

  DECLARE #RoomBookingStatusCur CURSOR FAST_FORWARD FOR

  SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment, NumberOfRoomsSoldToday, NumberOfOutOfOrder, SoldPrice
  FROM #RoomBookingStatus
  WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate OPTION (KEEP PLAN)
END

OPEN #RoomBookingStatusCur
FETCH NEXT FROM #RoomBookingStatusCur
INTO @DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @SoldRooms, @Adults, @Children1, @Children2, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, @FreeRoomsAllotment, @TotalRoomsAllotment, @PaxAllotment, @NumberOfRoomsSoldToday, @NumberOfOutOfOrder, @SoldPrice

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
  UPDATE RoomBookingStatus SET
    SoldRooms=@SoldRooms, Adults=@Adults, Children1=@Children1, Children2=@Children2, NumberOfConfirmed=@NumberOfConfirmed, NumberOfProvisionals=@NumberOfProvisionals, NumberOfGuaranteed=@NumberOfGuaranteed, NumberOfTentative=@NumberOfTentative, SoldPrice=@SoldPrice,
    NumberOfWaitinglists=@NumberOfWaitinglists, NumberOfDayrooms=@NumberOfDayrooms, FreeRoomsAllotment=@FreeRoomsAllotment, TotalRoomsAllotment=@TotalRoomsAllotment, PaxAllotment=@PaxAllotment, NumberOfRoomsSoldToday=@NumberOfRoomsSoldToday, NumberOfOutOfOrder=@NumberOfOutOfOrder
  WHERE DateStamp=@DateStamp AND RoomTypeNumber=@RoomTypeNumber AND SegmentNumber=@SegmentNumber AND PointOfSaleNumber=@PointOfSaleNumber AND HotelNumber=@HotelNumber
  IF @@rowcount = 0
    INSERT INTO RoomBookingStatus (DateStamp, RoomTypeNumber, SegmentNumber, PointOfSaleNumber, HotelNumber, SoldRooms, Adults, Children1, Children2, NumberOfConfirmed, NumberOfProvisionals, NumberOfGuaranteed, NumberOfTentative, NumberOfWaitinglists, NumberOfDayrooms, FreeRoomsAllotment, TotalRoomsAllotment, PaxAllotment, NumberOfRoomsSoldToday, NumberOfOutOfOrder, SoldPrice)
    VALUES (@DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @SoldRooms, @Adults, @Children1, @Children2, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, @FreeRoomsAllotment, @TotalRoomsAllotment, @PaxAllotment, @NumberOfRoomsSoldToday, @NumberOfOutOfOrder, @SoldPrice)

  FETCH NEXT FROM #RoomBookingStatusCur
  INTO @DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @SoldRooms, @Adults, @Children1, @Children2, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, @FreeRoomsAllotment, @TotalRoomsAllotment, @PaxAllotment, @NumberOfRoomsSoldToday, @NumberOfOutOfOrder, @SoldPrice
END

CLOSE #RoomBookingStatusCur
DEALLOCATE #RoomBookingStatusCur





