BAQ to find Next operation

Kinetic ERP
13

Super quick proof of concept (with the silly mistake in my previous comment corrected, as a treat)

with [OpRank] as 
(select 
	[JobOper].[Company] as [JobOper_Company],
	[JobOper].[JobNum] as [JobOper_JobNum],
	[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
	[JobOper].[OprSeq] as [JobOper_OprSeq],
	(rank() over(partition by JobOper.Company, JobOper.JobNum, JobOper.AssemblySeq order by JobOper.OprSeq asc)) as [Calculated_OpSeqRank]
from Erp.JobOper as JobOper)

select 
	[JobOper1].[Company] as [JobOper1_Company],
	[JobOper1].[JobNum] as [JobOper1_JobNum],
	[JobOper1].[AssemblySeq] as [JobOper1_AssemblySeq],
	[JobOper1].[OprSeq] as [JobOper1_OprSeq],
	[OpRankNext].[JobOper_OprSeq] as [JobOper_OprSeq]
from Erp.JobOper as JobOper1
inner join  OpRank  as OpRankCurrent on 
	JobOper1.Company = OpRankCurrent.JobOper_Company
	and JobOper1.JobNum = OpRankCurrent.JobOper_JobNum
	and JobOper1.AssemblySeq = OpRankCurrent.JobOper_AssemblySeq
	and JobOper1.OprSeq = OpRankCurrent.JobOper_OprSeq
left outer join  OpRank  as OpRankNext on 
	OpRankCurrent.JobOper_Company = OpRankNext.JobOper_Company
	and OpRankCurrent.JobOper_JobNum = OpRankNext.JobOper_JobNum
	and OpRankCurrent.JobOper_AssemblySeq = OpRankNext.JobOper_AssemblySeq
	and OpRankCurrent.Calculated_OpSeqRank = OpRankNext.Calculated_OpSeqRank - 1
where (JobOper1.JobNum = @JobNum)
3 Likes