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);

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);

-- 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]
       
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
  
SELECT * FROM [operations] ORDER BY [Workorder], [LotID], [Old_Seq]
SELECT * FROM [materials] ORDER BY [Workorder], [LotID], [Old_OpSeq]

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

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

(6 rows affected)

(8 rows affected)

(3 rows affected)

(8 rows affected)

(6 rows affected)

(8 rows affected)