BAQ to find Next operation

Kinetic ERP
1

I am trying to write a baq that will show me the Job Next Operation and I am not having much luck.

Hoping to find a thread i am missing - i am not having much luck.

I have a subquery that i can use to show greater than current JobOpSeq. However that only works if there is one other operation.

I am not concerned with operation complete - just need to show the next operation.

Am i making this too difficult? (i tend to do that :slight_smile: )

Any help appreciated.
DaveO

2

IIRC, next-op is in the BO dataset… wonder if a uBAQ could be fudged to get it for you. All our job status dashboards just list all the open Ops.

3

This is one for finding the previous operation, you can probably modify it to find the next operation.

2 Likes
4

Would changing that to greater than or equal be sufficient ?

Next op is the next one, or if no other one, it’s the same?

1 Like
5

yup… But I was gonna let @DaveOlender figure that out. He’s a smart guy!

Michael Scott Wink GIF

1 Like
6

You and me both brother. That’s why I have you guys.

7

You’ll probably have to change the order by as well.

1 Like
8

Why not just do a query that brings back all operations greater than and then put a MIN on the results? You could also throw a count field in on the operations left so that if it is 0 then there are no more operations.

9

Because the MIN is going to be the min for the whole table/subquery that way. So you’ll only get one. With the single sub select, each row is it’s own little query.

assm op
01 10
01 20
01 30
01 10
02 10
02 20
02 30
02 40

min like you are talking about will get you 1 row back. asm 01 op 10. But you really need 2, asm 01 op 10 and asm 02 op 10.

The single sub joins to each row individually.

1 Like
10

Thank you all @Banderson @Randy @klincecum @jkane.

I tried do the Greater than as a SubQuery condition and then created a calculated field to show Min() however, i am getting an error when i try to analyze.

I will take a closer look at the @Banderson link (Rob Busek - have not seen him in years). Used to see him at the Minneapolis User group meetings.

DaveO

11

I was not even thinking about subassemblies :joy:

12

I have a couple of versions of this, I haven’t bothered to write it up on its own since it’s actually pretty quick to make from scratch. I’ll see if I can find a minute to extract this particular bit into a baq later if someone needs it.

Basically… In a subquery or CTE select your join fields and the operation sequence. Add a calculated field, I’ll call it OpSeqRank and it looks something like

rank() over(partition by JobOper.JobNum order by JobOper.OprSeq asc)

I’m pulling that out of thin air but that’s the general idea.

In your top level query join that subquery (I’ll call it OperRankCurrent) on the current operation to bring in the incrementing rank for your operations. Finally, left outer join your subquery on OperRankCurrent (I’ll call this one OperRankNext) where OperRankCurrent.OpSeqRank = OperRankNext.OpSeqRank + 1.

edit - that’ll work with assembly levels too, you can order by a rank() windowed expression by more than one field.

1 Like
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
14

@kananga I wanted to thank you for this example. I am not a sql guy, but this gave me an example to help me find a previous value in a table along with @jdtrent’s post about making a concatenated field to rank on, so I can compare EDI 830 supplier changes from week to week.

It still took me 12 tries, but I finally got it working this morning.

Greg

1 Like