How to Renumber Set/Subset of Table Based on Numbering of A Second Table

I have a table of numbered operations. I have a second table with materials tied to those operations. I need to "remove" two of the operations and reassign the materials to the next operation in line.

Here is my sample data:

DROP TABLE IF EXISTS [operations];
CREATE TABLE [operations] (
    [WorkOrder] varchar(50),
    [LotID] int,
    [OprSeq]    int,
    [Operation] varchar(20),
    [Old_Seq] int,
    [New_Seq] int
);
INSERT INTO [operations] VALUES
('RTA123456', 0, 10, 'PZZ', 10, 0),
('RTA123456', 0, 20, 'LA',  20, 0),
('RTA123456', 0, 30, 'LA',  30, 0),
('RTA123456', 0, 40, 'PZZ', 40, 0),
('RTA123456', 0, 50, 'PO',  50, 0),
('RTA123456', 0, 60, 'SHP', 60, 0);

DROP TABLE IF EXISTS [materials];
CREATE TABLE [materials] (
    [WorkOrder] varchar(50),
    [LotID] int,
    [PartID] varchar(20),
    [OpSeqNo]   int,
    [Old_OpSeq] int,
    [New_OpSeq] int
);
INSERT INTO [materials] VALUES
('RTA123456', 0, 'LA2',   10, 10, 0),
('RTA123456', 0, 'LA-P',  10, 10, 0),
('RTA123456', 0, '12345', 10, 10, 0),
('RTA123456', 0, '23456', 10, 10, 0),
('RTA123456', 0, '34567', 10, 10, 0),
('RTA123456', 0, '45678', 40, 40, 0),
('RTA123456', 0, '56789', 40, 40, 0),
('RTA123456', 0, '67890', 40, 40, 0);

I created a CTE that will renumber the operations:

-- Renumbers Operations
WITH NewSeqNo AS (
    SELECT *,
        (ROW_NUMBER() OVER (PARTITION BY [WorkOrder], [LotID] ORDER BY [Old_Seq])) *10 AS [New]
      FROM [operations]
WHERE [Operation] NOT IN ('PZZ', 'SHP')
)
    UPDATE [operations]
       SET [operations].[New_Seq] = seq.[New]
      FROM [operations] o
INNER JOIN [NewSeqNo] seq
        ON seq.[WorkOrder] = o.[WorkOrder]
       AND seq.[Old_Seq] = o.[Old_Seq]
       AND seq.[LotID] = o.[LotID]

So I'd look at Material table for WorkOrder RTA123456, and PartID LA2 that has Materials.OpSeqNo=10. Then look at the Operation table, and the next Operation.SeqNo after Materials.OpSeqNo=10 is Operation.SeqNo = 20. The Operation.NewSeq for SeqNo#20 is 10. I can then hop down to OpSeqNo = 40 and do the same thing. If I look at the next SeqNo, SeqNo#50 is NewSeq#30.

I managed an UPDATE that seems to work

UPDATE [materials]
SET [New_OpSeq] = (
    SELECT TOP(1) [New_Seq] 
      FROM [operations] 
      WHERE [WorkOrder] = [WorkOrder] 
        AND [LotID] = [LotID]
        AND [Old_Seq] > [Old_OpSeq]
        AND [New_Seq] > 0
    ORDER BY [Old_Seq] )
  WHERE [New_OpSeq] = 0

BUT, if materials are tied to an operation that doesn't have any operations after it I get an error that it can't insert NULL. The real issue is that this works for my sample data, but does NOT work on my larger set. Instead of 10 and 30 for the New_OpSeq it gives me 20 and 40. It seems to add 10 to whatever the result is. I have NO idea why!! The sample data is exactly the same (Except for the WO#). I even renamed the Seq fields to match just to rule that out.

I created a SQL Fiddle to show my work, and that it works. I am looking for ANY ideas on why the same thing won't work on my larger dataset!

Solution

As an idea.
You can numerate operations without skipping. Assign new_OpSeq for all rows.

WorkOrder	OprSeq	Operation	Old_Seq	New_Seq
RTA123456	10	PZZ	10	10
RTA123456	20	LA	20	10
RTA123456	30	LA	30	20
RTA123456	40	PZZ	40	30
RTA123456	50	PO	50	30
RTA123456	60	SHP	60	40
RTA123456	70	NXX	70	40

WITH NewSeqNo AS (
  select *
     ,dense_rank()over(PARTITION BY [WorkOrder], [LotID]  ORDER BY [newSeq0])*10 new
  from(
          select * 
            ,min(case when [Operation] IN ('PZZ', 'SHP')then 1000 else OprSeq end) 
                  over(PARTITION BY [WorkOrder], [LotID]  ORDER BY [Old_Seq] 
                       rows between current row and unbounded following)
             newSeq0
          from operations
    )a
)

WorkOrder	PartID	OpSeqNo	Old_OpSeq	New_OpSeq
RTA123456	LA2	10	10	10
RTA123456	LA-P	10	10	10
RTA123456	12345	10	10	10
RTA123456	23456	10	10	10
RTA123456	34567	10	10	10
RTA123456	45678	40	40	30
RTA123456	56789	40	40	30
RTA123456	67890	40	40	30
RTA123456	67891	70	70	40

And set new seq

WITH NewSeqNo AS (
  select * ,dense_rank()over(PARTITION BY [WorkOrder], [LotID]  ORDER BY [newSeq0])*10 new
  from(
          select * 
            ,min(case when [Operation] IN ('PZZ', 'SHP')then 1000 else OprSeq end) 
                  over(PARTITION BY [WorkOrder], [LotID]  ORDER BY [Old_Seq] 
                       rows between current row and unbounded following)
             newSeq0
          from operations
    )a
)

UPDATE [materials] 
SET [New_OpSeq] = (
    SELECT TOP(1) [new] 
      FROM [NewSeqNo] t2
      WHERE t2.[WorkOrder] = [WorkOrder] 
        AND t2.[LotID] = [LotID]
       AND t2.[OprSeq] = [OpSeqNo]
       --  AND [New_Seq] > 0
     ORDER BY t2.new )
  WHERE [New_OpSeq] = 0
  ;

WITH NewSeqNo AS (
  select * ,dense_rank()over(PARTITION BY [WorkOrder], [LotID]  ORDER BY [newSeq0])*10 new
  from(
          select * 
            ,min(case when [Operation] IN ('PZZ', 'SHP')then 1000 else OprSeq end) 
                  over(PARTITION BY [WorkOrder], [LotID]  ORDER BY [Old_Seq] 
                       rows between current row and unbounded following)
             newSeq0
          from operations
    )a
)
UPDATE [operations]
       SET [operations].[New_Seq] = seq.[new]
      FROM [operations] o
INNER JOIN [NewSeqNo] seq
        ON seq.[WorkOrder] = o.[WorkOrder]
       AND seq.[LotID] = o.[LotID]
       AND seq.[Old_Seq] = o.[Old_Seq]
      ;

Example