DECLARE @PREFIX VARCHAR(50) --='Z'
SELECT @PREFIX=cwo_prefix
FROM invparms
WHERE parm_key=0
SELECT
ROW_NUMBER() OVER(PARTITION BY jr.job, jr.suffix ORDER BY jr.oper_num) * 10 AS NEW_OPER_NUM
,jr.*
FROM dbo.jobroute jr
WHERE jr.job LIKE @PREFIX+'%'
SELECT
ROW_NUMBER() OVER(PARTITION BY jm.job, jm.suffix, jm.[SEQUENCE] ORDER BY jm.oper_num) * 10 AS NEW_OPER_NUM
,jm.*
FROM dbo.jobmatl jm
WHERE jm.job LIKE @PREFIX+'%'
/*
DELETE dbo.jobroute
where job LIKE @PREFIX+'%'
AND oper_num IN (130,140,150,160)
DELETE dbo.jobmatl
where job LIKE @PREFIX+'%'
AND oper_num IN (130,140,150,160)
-- renumber remaining...
update dbo.jobroute
set oper_num=ROW_NUMBER() OVER(PARTITION BY job, suffix ORDER BY oper_num) * 10
where job LIKE @PREFIX+'%'
update dbo.jobmatl
set oper_num=ROW_NUMBER() OVER(PARTITION BY job, suffix, [sequence] ORDER BY oper_num) * 10
where job LIKE @PREFIX+'%'
*/
Comments