FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

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),
('RTA123456', 0, 70, 'NXX', 70, 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),
('RTA123456', 0, '67891', 70, 70, 0);
-- Renumbers Operations
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
  ;

-- SELECT * FROM [materials] ORDER BY [Workorder], [LotID], [Old_OpSeq]
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]
      ;
SELECT * FROM [operations] ORDER BY [Workorder], [LotID], [Old_Seq];
SELECT * FROM [materials] ORDER BY [Workorder], [LotID], [OpSeqNo];
 
 --  select * from NewSeqNo ORDER by WorkOrder,LotId,OprSeq;
/*
 select *
  ,(
	SELECT  top 1 new -- TOP(1)
	  FROM [NewSeqNo] t2
	  WHERE t2.[WorkOrder] = [WorkOrder] 
	    AND t2.[LotID] = [LotID]
	    AND t2.[OprSeq] = [OpSeqNo]
	   --  AND [New_Seq] > 0
	 ORDER BY t2.new 
    )
from materials;
*/

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

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

(7 rows affected)

(9 rows affected)

(9 rows affected)

(7 rows affected)

(7 rows affected)

(9 rows affected)