LaborDtl.OprSeq and LaborDtl.LaborQty

Epicor ERP 10
,
1

I am trying to say in a BAQ:

Find the min(LaborDtl.OprSeq) and then sum(LaborDtl.LaborQty) of that OprSeq

What is the correct way to do this?

I’m not familiar with SQL so I am using the Epicor Field Editor

Can someone help a girl out?

Thank you in advance for your time!

2

Hi Judy,

In other words, you would like to add up the quantities of parts that have moved through the first operation for all types of jobs since going live on Epicor?

Mark W.

3

Well, I’m only looking at jobs that have not been closed. But yes, I am looking at how many parts were completed on the first OP. I’m only doing this because the number of parts sheared may not be the number of parts completed. Therefore, the amount of material that was issued for the job, may not have been the amount of material required.

One of my issue’s is that OPSEQ 5 may not always be the first OPSEQ, that’s why I am trying to incorporate the "min(labordtl.oprseq) calculation.

4

I would do it with a subquery, that has your min plus your key fields. Then link to it on your main query with an inner join.

5

When I say: min(JobOper.OprSeq) is the value returned to me different than JobOper.QtyCompleted (I keep getting an error about a Boolean field) - if so, is there a way to pull these two together to say:

Sum the qty completed on the Minimum Operation Sequence and report that qty

I do have the min(JobOper.OprSeq) calculation working, it just won’t play nicely with the OpQty!

6

What does the query look like? Paste that in here it will help us to get you where you need to go.

7

OPRSeq and QtyCompleted.txt (3.6 KB)

8

Let look at this first. Do you want the first operation and then the sum of laborQty for all of that job. If there is labor on multiple operation you will get data that looks off.

Do you want to find the laborqty for the first operation on the job? Then you will need to do another sub query. Have the first subquery (jobOprActivity) be a list of jobs and the first operation.

Then next query will be the sum of laborqty from the jobOprActivity subquery to the JobDetail table.

This new query will then give you something like this. If this is off from what you are looking for let me know.

select LaborDtlMain.JobNum,  jobOperActivity.Calculated_MinOprSeq, sum( LaborDtlMain.LaborQty) from (
select 
	[LaborDtl].[JobNum] as [LaborDtl_JobNum],
	(min( LaborDtl.OprSeq )) as [Calculated_MinOprSeq]
from Erp.LaborDtl as LaborDtl
inner join Erp.JobOper as JobOper on 
	JobOper.Company = LaborDtl.Company
And
	JobOper.JobNum = LaborDtl.JobNum

 where (LaborDtl.ClockInDate >= '1/1/2016'  and LaborDtl.ClockInDate <= '10/25/2017')
group by [LaborDtl].[JobNum]) as jobOperActivity inner join Erp.LaborDtl as LaborDtlMain on LaborDtlMain.JobNum = jobOperActivity.LaborDtl_JobNum
group by LaborDtlMain.JobNum,jobOperActivity.Calculated_MinOprSeq