CREATE PROCEDURE dbo.RearrangeProductPricePacket @AProductNumber INT AS
/*
  Created by Kent Johansen, May 2022
*/

SET NOCOUNT ON

DECLARE @cnt INT
DECLARE @FK_ProductNumber INT
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @DateStamp DATETIME 
DECLARE @PacketType INT
DECLARE @FK_PacketNumber INT
DECLARE @CurDateStamp DATETIME 
DECLARE @CurPacketType INT
DECLARE @CurFK_PacketNumber INT
DECLARE @BeginDateStamp DATETIME

DECLARE #db_cur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT COUNT(*), FK_ProductNumber, MIN(StartDate), MAX(EndDate)
  FROM ProductPricePacket WITH (NOLOCK)
  WHERE (@AProductNumber = -1 OR FK_ProductNumber = @AProductNumber)
  GROUP BY FK_ProductNumber
  HAVING COUNT(*) > 1000
OPEN #db_cur

FETCH NEXT FROM #db_cur INTO @cnt, @FK_ProductNumber, @StartDate, @EndDate

WHILE @@FETCH_STATUS = 0
BEGIN
  CREATE TABLE #PackageInfo
  (
    View_DateStamp DATETIME,
    View_Id INT,
    View_FK_ProductNumber INT,
    View_PacketType INT
  )
  
  INSERT INTO #PackageInfo
    EXEC ProductPricePacketInfo @StartDate, @EndDate, @FK_ProductNumber

  DECLARE #db_cur1 CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT View_DateStamp, View_PacketType, ProductPricePacket.FK_PacketNumber
    FROM #PackageInfo
    INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo. View_Id
    ORDER BY View_PacketType, View_DateStamp
  OPEN #db_cur1

  FETCH NEXT FROM #db_cur1 INTO @DateStamp, @PacketType, @FK_PacketNumber

  DELETE ProductPricePacket WHERE FK_ProductNumber = @FK_ProductNumber

  SET @CurDateStamp = @DateStamp 
  SET @CurPacketType = @PacketType
  SET @CurFK_PacketNumber = @FK_PacketNumber
  SET @BeginDateStamp = @DateStamp 

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @PacketType <> @CurPacketType OR @FK_PacketNumber <> @CurFK_PacketNumber
    BEGIN
      INSERT INTO ProductPricePacket (FK_ProductNumber, PacketType, StartDate, EndDate, FK_PacketNumber) VALUES 
        (@FK_ProductNumber, @CurPacketType, @BeginDateStamp, @CurDateStamp, @CurFK_PacketNumber)
      SET @BeginDateStamp = @DateStamp 
    END

    SET @CurPacketType = @PacketType
    SET @CurFK_PacketNumber = @FK_PacketNumber
    SET @CurDateStamp = @DateStamp 

    FETCH NEXT FROM #db_cur1 INTO @DateStamp, @PacketType, @FK_PacketNumber
  END
  INSERT INTO ProductPricePacket (FK_ProductNumber, PacketType, StartDate, EndDate, FK_PacketNumber) VALUES 
    (@FK_ProductNumber, @CurPacketType, @BeginDateStamp, @CurDateStamp, @CurFK_PacketNumber)

  CLOSE #db_cur1
  DEALLOCATE #db_cur1

  DROP TABLE #PackageInfo

  FETCH NEXT FROM #db_cur INTO @cnt, @FK_ProductNumber, @StartDate, @EndDate
END

CLOSE #db_cur
DEALLOCATE #db_cur