CREATE PROCEDURE DBO.CopyWeekToPeriod  @OldProductNumber int,  @SourceMonday datetime, @DestStartDate datetime, @DestEndDate datetime AS
SET NOCOUNT ON
/*
-- Input: The destination period MUST always start on a monday and end on a sunday, Otherwise Monday->Monday copying wont work.

-- Last changed:  
  KJ 20141219: Ext: Changed datatype for prices. Handle amount > 214.000.
  20050921 JP BUG: added IF @InsertDate <= @EndDate to avoid insert outside period
  20050502 JP EXT: money to smallmoney. removed ProductPriceNumber
  Jacob Pedersen 26-11-2002
*/
-- Declare all fields in ProductPrice
Declare @ProductNumber Int
Declare @DateStamp VarChar (8)
Declare @RoomtypeNumber int
Declare @AdultPrice1 money
Declare @AdultPrice2 money
Declare @AdultPrice3 money
Declare @AdultPrice4 money
Declare @AdultPrice5 money
Declare @AdultPrice6 money
Declare @AdultPrice7 money
Declare @AdultPrice8 money
Declare @AdultPrice9 money
Declare @Children1Price1 money
Declare @Children1Price2 money
Declare @Children1Price3 money
Declare @Children1Price4 money
Declare @Children1Price5 money
Declare @Children1Price6 money
Declare @Children1Price7 money
Declare @Children1Price8 money
Declare @Children1Price9 money
Declare @Children2Price1 money
Declare @Children2Price2 money
Declare @Children2Price3 money
Declare @Children2Price4 money
Declare @Children2Price5 money
Declare @Children2Price6 money
Declare @Children2Price7 money
Declare @Children2Price8 money
Declare @Children2Price9 money
Declare @AdultArrivalAddon smallmoney
Declare @AdultStayAddon smallmoney
Declare @AdultDepartureAddon smallmoney
Declare @Children1ArrivalAddon smallmoney
Declare @Children1StayAddon smallmoney
Declare @Children1DepartureAddon smallmoney
Declare @Children2ArrivalAddon smallmoney
Declare @Children2StayAddon smallmoney
Declare @Children2DepartureAddon smallmoney

Declare @AdultArrivalAddon2 smallmoney
Declare @AdultStayAddon2 smallmoney
Declare @AdultDepartureAddon2 smallmoney
Declare @Children1ArrivalAddon2 smallmoney
Declare @Children1StayAddon2 smallmoney
Declare @Children1DepartureAddon2 smallmoney
Declare @Children2ArrivalAddon2 smallmoney
Declare @Children2StayAddon2 smallmoney
Declare @Children2DepartureAddon2 smallmoney
Declare @AdultArrivalAddon3 smallmoney
Declare @AdultStayAddon3 smallmoney
Declare @AdultDepartureAddon3 smallmoney
Declare @Children1ArrivalAddon3 smallmoney
Declare @Children1StayAddon3 smallmoney
Declare @Children1DepartureAddon3 smallmoney
Declare @Children2ArrivalAddon3 smallmoney
Declare @Children2StayAddon3 smallmoney
Declare @Children2DepartureAddon3 smallmoney
Declare @AdultArrivalAddon4 smallmoney
Declare @AdultStayAddon4 smallmoney
Declare @AdultDepartureAddon4 smallmoney
Declare @Children1ArrivalAddon4 smallmoney
Declare @Children1StayAddon4 smallmoney
Declare @Children1DepartureAddon4 smallmoney
Declare @Children2ArrivalAddon4 smallmoney
Declare @Children2StayAddon4 smallmoney
Declare @Children2DepartureAddon4 smallmoney
Declare @AdultArrivalAddon5 smallmoney
Declare @AdultStayAddon5 smallmoney
Declare @AdultDepartureAddon5 smallmoney
Declare @Children1ArrivalAddon5 smallmoney
Declare @Children1StayAddon5 smallmoney
Declare @Children1DepartureAddon5 smallmoney
Declare @Children2ArrivalAddon5 smallmoney
Declare @Children2StayAddon5 smallmoney
Declare @Children2DepartureAddon5 smallmoney
Declare @AdultArrivalAddon6 smallmoney
Declare @AdultStayAddon6 smallmoney
Declare @AdultDepartureAddon6 smallmoney
Declare @Children1ArrivalAddon6 smallmoney
Declare @Children1StayAddon6 smallmoney
Declare @Children1DepartureAddon6 smallmoney
Declare @Children2ArrivalAddon6 smallmoney
Declare @Children2StayAddon6 smallmoney
Declare @Children2DepartureAddon6 smallmoney
Declare @AdultArrivalAddon7 smallmoney
Declare @AdultStayAddon7 smallmoney
Declare @AdultDepartureAddon7 smallmoney
Declare @Children1ArrivalAddon7 smallmoney
Declare @Children1StayAddon7 smallmoney
Declare @Children1DepartureAddon7 smallmoney
Declare @Children2ArrivalAddon7 smallmoney
Declare @Children2StayAddon7 smallmoney
Declare @Children2DepartureAddon7 smallmoney
Declare @AdultArrivalAddon8 smallmoney
Declare @AdultStayAddon8 smallmoney
Declare @AdultDepartureAddon8 smallmoney
Declare @Children1ArrivalAddon8 smallmoney
Declare @Children1StayAddon8 smallmoney
Declare @Children1DepartureAddon8 smallmoney
Declare @Children2ArrivalAddon8 smallmoney
Declare @Children2StayAddon8 smallmoney
Declare @Children2DepartureAddon8 smallmoney
Declare @AdultArrivalAddon9 smallmoney
Declare @AdultStayAddon9 smallmoney
Declare @AdultDepartureAddon9 smallmoney
Declare @Children1ArrivalAddon9 smallmoney
Declare @Children1StayAddon9 smallmoney
Declare @Children1DepartureAddon9 smallmoney
Declare @Children2ArrivalAddon9 smallmoney
Declare @Children2StayAddon9 smallmoney
Declare @Children2DepartureAddon9 smallmoney

Declare @ArrivalPacketNumber int
Declare @StayPacketNumber int
Declare @DeparturePacketNumber int

Declare @FormatedSourceMonday Char (10)
Declare @FormatedSourceSunday Char (10)
Declare @CurrentDate Char (10)
Declare @EndDate Char (10)

Declare @OffSet DateTime
Declare @InsertDate Char (10)

select  @FormatedSourceMonday =  Convert (Char (10), @SourceMonday, 112)
select  @FormatedSourceSunday =  Convert (Char (10), @SourceMonday+6, 112)
Select  @CurrentDate =  Convert (Char (10), @DestStartDate, 112)
Select  @EndDate =  Convert (Char (10), @DestEndDate, 112)

Delete From ProductPrice
Where
ProductNumber = @OldProductNumber and
DateStamp >= @CurrentDate and  -- Here the current date is same as the Destination StartDate
DateStamp <= @EndDate and
(DateStamp < @FormatedSourceMonday or  -- Do NOT delete the sourcedate
DateStamp > @FormatedSourceSunday)

-- Declare and allocate Cursor
Declare CopyCur Cursor For
  Select ProductNumber, DateStamp, RoomtypeNumber,
    AdultPrice1, AdultPrice2, AdultPrice3, AdultPrice4, AdultPrice5, AdultPrice6, AdultPrice7, AdultPrice8, AdultPrice9,
    Children1Price1, Children1Price2, Children1Price3, Children1Price4, Children1Price5, Children1Price6, Children1Price7, Children1Price8, Children1Price9,
    Children2Price1, Children2Price2, Children2Price3, Children2Price4, Children2Price5, Children2Price6, Children2Price7, Children2Price8, Children2Price9,
    AdultArrivalAddon, AdultStayAddon, AdultDepartureAddon, Children1ArrivalAddon, Children1StayAddon, Children1DepartureAddon, Children2ArrivalAddon, Children2StayAddon, Children2DepartureAddon,
    AdultArrivalAddon2, AdultStayAddon2, AdultDepartureAddon2, Children1ArrivalAddon2, Children1StayAddon2, Children1DepartureAddon2, Children2ArrivalAddon2, Children2StayAddon2, Children2DepartureAddon2,
    AdultArrivalAddon3, AdultStayAddon3, AdultDepartureAddon3, Children1ArrivalAddon3, Children1StayAddon3, Children1DepartureAddon3, Children2ArrivalAddon3, Children2StayAddon3, Children2DepartureAddon3,
    AdultArrivalAddon4, AdultStayAddon4, AdultDepartureAddon4, Children1ArrivalAddon4, Children1StayAddon4, Children1DepartureAddon4, Children2ArrivalAddon4, Children2StayAddon4, Children2DepartureAddon4,
    AdultArrivalAddon5, AdultStayAddon5, AdultDepartureAddon5, Children1ArrivalAddon5, Children1StayAddon5, Children1DepartureAddon5, Children2ArrivalAddon5, Children2StayAddon5, Children2DepartureAddon5,
    AdultArrivalAddon6, AdultStayAddon6, AdultDepartureAddon6, Children1ArrivalAddon6, Children1StayAddon6, Children1DepartureAddon6, Children2ArrivalAddon6, Children2StayAddon6, Children2DepartureAddon6,
    AdultArrivalAddon7, AdultStayAddon7, AdultDepartureAddon7, Children1ArrivalAddon7, Children1StayAddon7, Children1DepartureAddon7, Children2ArrivalAddon7, Children2StayAddon7, Children2DepartureAddon7,
    AdultArrivalAddon8, AdultStayAddon8, AdultDepartureAddon8, Children1ArrivalAddon8, Children1StayAddon8, Children1DepartureAddon8, Children2ArrivalAddon8, Children2StayAddon8, Children2DepartureAddon8,
    AdultArrivalAddon9, AdultStayAddon9, AdultDepartureAddon9, Children1ArrivalAddon9, Children1StayAddon9, Children1DepartureAddon9, Children2ArrivalAddon9, Children2StayAddon9, Children2DepartureAddon9,
    ArrivalPacketNumber, StayPacketNumber, DeparturePacketNumber From ProductPrice WITH (NOLOCK)
  Where DateStamp >= @FormatedSourceMonday and DateStamp <= @FormatedSourceSunday and ProductNumber = @OldProductNumber

-- Loop from startdate to enddate
While @CurrentDate <= @EndDate
Begin
  -- Open the cursor and fetch the first record
  If (@CurrentDate < @FormatedSourceMonday) or (@CurrentDate > @FormatedSourceSunday)  -- Do NOT insert on sourcedate
  Begin
    Open CopyCur
    Fetch Next From CopyCur
    Into @ProductNumber, @DateStamp, @RoomtypeNumber,
    @AdultPrice1, @AdultPrice2, @AdultPrice3, @AdultPrice4, @AdultPrice5, @AdultPrice6, @AdultPrice7, @AdultPrice8, @AdultPrice9,
    @Children1Price1, @Children1Price2, @Children1Price3, @Children1Price4, @Children1Price5, @Children1Price6, @Children1Price7, @Children1Price8, @Children1Price9,
    @Children2Price1, @Children2Price2, @Children2Price3, @Children2Price4, @Children2Price5, @Children2Price6, @Children2Price7, @Children2Price8, @Children2Price9,
    @AdultArrivalAddon, @AdultStayAddon, @AdultDepartureAddon, @Children1ArrivalAddon, @Children1StayAddon, @Children1DepartureAddon, @Children2ArrivalAddon, @Children2StayAddon, @Children2DepartureAddon,
    @AdultArrivalAddon2, @AdultStayAddon2, @AdultDepartureAddon2, @Children1ArrivalAddon2, @Children1StayAddon2, @Children1DepartureAddon2, @Children2ArrivalAddon2, @Children2StayAddon2, @Children2DepartureAddon2,
    @AdultArrivalAddon3, @AdultStayAddon3, @AdultDepartureAddon3, @Children1ArrivalAddon3, @Children1StayAddon3, @Children1DepartureAddon3, @Children2ArrivalAddon3, @Children2StayAddon3, @Children2DepartureAddon3,
    @AdultArrivalAddon4, @AdultStayAddon4, @AdultDepartureAddon4, @Children1ArrivalAddon4, @Children1StayAddon4, @Children1DepartureAddon4, @Children2ArrivalAddon4, @Children2StayAddon4, @Children2DepartureAddon4,
    @AdultArrivalAddon5, @AdultStayAddon5, @AdultDepartureAddon5, @Children1ArrivalAddon5, @Children1StayAddon5, @Children1DepartureAddon5, @Children2ArrivalAddon5, @Children2StayAddon5, @Children2DepartureAddon5,
    @AdultArrivalAddon6, @AdultStayAddon6, @AdultDepartureAddon6, @Children1ArrivalAddon6, @Children1StayAddon6, @Children1DepartureAddon6, @Children2ArrivalAddon6, @Children2StayAddon6, @Children2DepartureAddon6,
    @AdultArrivalAddon7, @AdultStayAddon7, @AdultDepartureAddon7, @Children1ArrivalAddon7, @Children1StayAddon7, @Children1DepartureAddon7, @Children2ArrivalAddon7, @Children2StayAddon7, @Children2DepartureAddon7,
    @AdultArrivalAddon8, @AdultStayAddon8, @AdultDepartureAddon8, @Children1ArrivalAddon8, @Children1StayAddon8, @Children1DepartureAddon8, @Children2ArrivalAddon8, @Children2StayAddon8, @Children2DepartureAddon8,
    @AdultArrivalAddon9, @AdultStayAddon9, @AdultDepartureAddon9, @Children1ArrivalAddon9, @Children1StayAddon9, @Children1DepartureAddon9, @Children2ArrivalAddon9, @Children2StayAddon9, @Children2DepartureAddon9,
    @ArrivalPacketNumber, @StayPacketNumber, @DeparturePacketNumber

    -- Loop through records of sourcedate
    While  (@@FETCH_STATUS <> -1)
    Begin
     -- Insert with NEW date
     Set  @Offset = Convert (DateTime, @DateStamp) - Convert (DateTime, @SourceMonday)
     Select  @InsertDate =  Convert (Char (10), @DestStartDate+@OffSet, 112)

      IF @InsertDate <= @EndDate
      Insert Into ProductPrice
      (ProductNumber, DateStamp, RoomtypeNumber, AdultPrice1, AdultPrice2, AdultPrice3, AdultPrice4, AdultPrice5, AdultPrice6, AdultPrice7, AdultPrice8, AdultPrice9,
      Children1Price1, Children1Price2, Children1Price3, Children1Price4, Children1Price5, Children1Price6, Children1Price7, Children1Price8, Children1Price9,
      Children2Price1, Children2Price2, Children2Price3, Children2Price4, Children2Price5, Children2Price6, Children2Price7, Children2Price8, Children2Price9,
      AdultArrivalAddon, AdultStayAddon, AdultDepartureAddon,Children1ArrivalAddon,Children1StayAddon, Children1DepartureAddon,Children2ArrivalAddon,Children2StayAddon, Children2DepartureAddon,
      AdultArrivalAddon2, AdultStayAddon2, AdultDepartureAddon2, Children1ArrivalAddon2, Children1StayAddon2, Children1DepartureAddon2, Children2ArrivalAddon2, Children2StayAddon2, Children2DepartureAddon2,
      AdultArrivalAddon3, AdultStayAddon3, AdultDepartureAddon3, Children1ArrivalAddon3, Children1StayAddon3, Children1DepartureAddon3, Children2ArrivalAddon3, Children2StayAddon3, Children2DepartureAddon3,
      AdultArrivalAddon4, AdultStayAddon4, AdultDepartureAddon4, Children1ArrivalAddon4, Children1StayAddon4, Children1DepartureAddon4, Children2ArrivalAddon4, Children2StayAddon4, Children2DepartureAddon4,
      AdultArrivalAddon5, AdultStayAddon5, AdultDepartureAddon5, Children1ArrivalAddon5, Children1StayAddon5, Children1DepartureAddon5, Children2ArrivalAddon5, Children2StayAddon5, Children2DepartureAddon5,
      AdultArrivalAddon6, AdultStayAddon6, AdultDepartureAddon6, Children1ArrivalAddon6, Children1StayAddon6, Children1DepartureAddon6, Children2ArrivalAddon6, Children2StayAddon6, Children2DepartureAddon6,
      AdultArrivalAddon7, AdultStayAddon7, AdultDepartureAddon7, Children1ArrivalAddon7, Children1StayAddon7, Children1DepartureAddon7, Children2ArrivalAddon7, Children2StayAddon7, Children2DepartureAddon7,
      AdultArrivalAddon8, AdultStayAddon8, AdultDepartureAddon8, Children1ArrivalAddon8, Children1StayAddon8, Children1DepartureAddon8, Children2ArrivalAddon8, Children2StayAddon8, Children2DepartureAddon8,
      AdultArrivalAddon9, AdultStayAddon9, AdultDepartureAddon9, Children1ArrivalAddon9, Children1StayAddon9, Children1DepartureAddon9, Children2ArrivalAddon9, Children2StayAddon9, Children2DepartureAddon9,
      ArrivalPacketNumber,StayPacketNumber, DeparturePacketNumber)
      Values (@ProductNumber, @InsertDate, @RoomtypeNumber,
      @AdultPrice1, @AdultPrice2, @AdultPrice3, @AdultPrice4, @AdultPrice5, @AdultPrice6, @AdultPrice7, @AdultPrice8, @AdultPrice9,
      @Children1Price1, @Children1Price2, @Children1Price3, @Children1Price4, @Children1Price5, @Children1Price6, @Children1Price7, @Children1Price8, @Children1Price9,
      @Children2Price1, @Children2Price2, @Children2Price3, @Children2Price4, @Children2Price5, @Children2Price6, @Children2Price7, @Children2Price8, @Children2Price9,
      @AdultArrivalAddon, @AdultStayAddon, @AdultDepartureAddon,@Children1ArrivalAddon,@Children1StayAddon, @Children1DepartureAddon,@Children2ArrivalAddon,@Children2StayAddon, @Children2DepartureAddon,
      @AdultArrivalAddon2, @AdultStayAddon2, @AdultDepartureAddon2, @Children1ArrivalAddon2, @Children1StayAddon2, @Children1DepartureAddon2, @Children2ArrivalAddon2, @Children2StayAddon2, @Children2DepartureAddon2,
      @AdultArrivalAddon3, @AdultStayAddon3, @AdultDepartureAddon3, @Children1ArrivalAddon3, @Children1StayAddon3, @Children1DepartureAddon3, @Children2ArrivalAddon3, @Children2StayAddon3, @Children2DepartureAddon3,
      @AdultArrivalAddon4, @AdultStayAddon4, @AdultDepartureAddon4, @Children1ArrivalAddon4, @Children1StayAddon4, @Children1DepartureAddon4, @Children2ArrivalAddon4, @Children2StayAddon4, @Children2DepartureAddon4,
      @AdultArrivalAddon5, @AdultStayAddon5, @AdultDepartureAddon5, @Children1ArrivalAddon5, @Children1StayAddon5, @Children1DepartureAddon5, @Children2ArrivalAddon5, @Children2StayAddon5, @Children2DepartureAddon5,
      @AdultArrivalAddon6, @AdultStayAddon6, @AdultDepartureAddon6, @Children1ArrivalAddon6, @Children1StayAddon6, @Children1DepartureAddon6, @Children2ArrivalAddon6, @Children2StayAddon6, @Children2DepartureAddon6,
      @AdultArrivalAddon7, @AdultStayAddon7, @AdultDepartureAddon7, @Children1ArrivalAddon7, @Children1StayAddon7, @Children1DepartureAddon7, @Children2ArrivalAddon7, @Children2StayAddon7, @Children2DepartureAddon7,
      @AdultArrivalAddon8, @AdultStayAddon8, @AdultDepartureAddon8, @Children1ArrivalAddon8, @Children1StayAddon8, @Children1DepartureAddon8, @Children2ArrivalAddon8, @Children2StayAddon8, @Children2DepartureAddon8,
      @AdultArrivalAddon9, @AdultStayAddon9, @AdultDepartureAddon9, @Children1ArrivalAddon9, @Children1StayAddon9, @Children1DepartureAddon9, @Children2ArrivalAddon9, @Children2StayAddon9, @Children2DepartureAddon9,
      @ArrivalPacketNumber,@StayPacketNumber, @DeparturePacketNumber)

      Fetch Next From CopyCur
      Into @ProductNumber, @DateStamp, @RoomtypeNumber,
      @AdultPrice1, @AdultPrice2, @AdultPrice3, @AdultPrice4, @AdultPrice5, @AdultPrice6, @AdultPrice7, @AdultPrice8, @AdultPrice9,
      @Children1Price1, @Children1Price2, @Children1Price3, @Children1Price4, @Children1Price5, @Children1Price6, @Children1Price7, @Children1Price8, @Children1Price9,
      @Children2Price1, @Children2Price2, @Children2Price3, @Children2Price4, @Children2Price5, @Children2Price6, @Children2Price7, @Children2Price8, @Children2Price9,
      @AdultArrivalAddon, @AdultStayAddon, @AdultDepartureAddon, @Children1ArrivalAddon, @Children1StayAddon, @Children1DepartureAddon, @Children2ArrivalAddon, @Children2StayAddon, @Children2DepartureAddon,
      @AdultArrivalAddon2, @AdultStayAddon2, @AdultDepartureAddon2, @Children1ArrivalAddon2, @Children1StayAddon2, @Children1DepartureAddon2, @Children2ArrivalAddon2, @Children2StayAddon2, @Children2DepartureAddon2,
      @AdultArrivalAddon3, @AdultStayAddon3, @AdultDepartureAddon3, @Children1ArrivalAddon3, @Children1StayAddon3, @Children1DepartureAddon3, @Children2ArrivalAddon3, @Children2StayAddon3, @Children2DepartureAddon3,
      @AdultArrivalAddon4, @AdultStayAddon4, @AdultDepartureAddon4, @Children1ArrivalAddon4, @Children1StayAddon4, @Children1DepartureAddon4, @Children2ArrivalAddon4, @Children2StayAddon4, @Children2DepartureAddon4,
      @AdultArrivalAddon5, @AdultStayAddon5, @AdultDepartureAddon5, @Children1ArrivalAddon5, @Children1StayAddon5, @Children1DepartureAddon5, @Children2ArrivalAddon5, @Children2StayAddon5, @Children2DepartureAddon5,
      @AdultArrivalAddon6, @AdultStayAddon6, @AdultDepartureAddon6, @Children1ArrivalAddon6, @Children1StayAddon6, @Children1DepartureAddon6, @Children2ArrivalAddon6, @Children2StayAddon6, @Children2DepartureAddon6,
      @AdultArrivalAddon7, @AdultStayAddon7, @AdultDepartureAddon7, @Children1ArrivalAddon7, @Children1StayAddon7, @Children1DepartureAddon7, @Children2ArrivalAddon7, @Children2StayAddon7, @Children2DepartureAddon7,
      @AdultArrivalAddon8, @AdultStayAddon8, @AdultDepartureAddon8, @Children1ArrivalAddon8, @Children1StayAddon8, @Children1DepartureAddon8, @Children2ArrivalAddon8, @Children2StayAddon8, @Children2DepartureAddon8,
      @AdultArrivalAddon9, @AdultStayAddon9, @AdultDepartureAddon9, @Children1ArrivalAddon9, @Children1StayAddon9, @Children1DepartureAddon9, @Children2ArrivalAddon9, @Children2StayAddon9, @Children2DepartureAddon9,
      @ArrivalPacketNumber, @StayPacketNumber, @DeparturePacketNumber
    End

    -- Close Cursor
    Close CopyCur
  End

  Set @DestStartDate = @DestStartDate +7
  Select  @CurrentDate =  Convert (Char (10), @DestStartDate, 112)
End

--Deallocate Cursor
Deallocate CopyCur

