I’m trying to pull historic labor hours by part number but when I pull the count of rows used for the average I’m getting 100k+ counts for a part that would be the equivalent of our factory at 100% for 30 years. Ideally I just want one laborhr entry from LaborDtl, but instead I’m getting about six per part made.
You probably have a join in the BAQ that isn’t set up right so you are getting duplicated rows. What tables are in your BAQ?
1 Like
Someone at work said LabHrs may be associated with multiple stages of production, but only account for one actual block of production time.
I believe you are correct and rows of LaborDtl are getting duplicated around 6 times per part being produced. I think the joins are where I should be looking and that you are 100% correct
I also realize I should start with PDR’s for each part I’m trying to get info from and if they agree with one another I’ll know I am pulling relatively consistent information.
Please post your query as well. This sounds like a joining issue.
My BAQ skills are about as green as it gets so if it makes you cringe be brutal with me.
select
[JobOper].[JobNum] as [JobOper_JobNum],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOper].[OpCode] as [JobOper_OpCode],
[JobOper].[ActSetupHours] as [JobOper_ActSetupHours],
[JobOper].[ActSetupRwkHours] as [JobOper_ActSetupRwkHours],
[JobOper].[QtyCompleted] as [JobOper_QtyCompleted],
(avg(LaborDtl.LaborHrs)) as [Calculated_avgLabour],
(sum(LaborDtl.LaborHrs)) as [Calculated_sumLabor],
(JobOper.QtyCompleted/sum(LaborDtl.LaborHrs)) as [Calculated_poundsPerLaborHour]
from Erp.LaborDtl as LaborDtl
inner join Erp.JobOper as JobOper on
LaborDtl.Company = JobOper.Company
and LaborDtl.JobNum = JobOper.JobNum
and LaborDtl.AssemblySeq = JobOper.AssemblySeq
and LaborDtl.OprSeq = JobOper.OprSeq
inner join Erp.JobHead as JobHead on
JobOper.Company = JobHead.Company
and JobOper.JobNum = JobHead.JobNum
and ( JobHead.PartNum = ‘*****’ )
inner join Erp.OrderDtl as OrderDtl on
OrderDtl.PartNum = JobHead.PartNum
group by [JobOper].[JobNum],
[OrderDtl].[PartNum],
[JobOper].[OprSeq],
[JobOper].[OpCode],
[JobOper].[ActSetupHours],
[JobOper].[ActSetupRwkHours],
[JobOper].[QtyCompleted]
All good. We can get you to where you want to go.
I am a fan of building up the BAQ one table at a time.
Since you are green to all of this, it might be a good idea to try and do the same, vs adding 5 tables and trying to figure out where the issue is.
Start with the labor details.
add a few display fields
set a criteria on the labor details (jobnum)
Test BAQ see number of results
add the next table.
add a few display fields for new table
Test BAQ see number of results
If the record count doesn’t look right, then we will need to look at the join.
Rinse and repeat until all of the tables which are needed are added.
The following may be fixed by working through the BAQ.
This will join every order for the part with every job of that part. I’m guessing that is not what you mean to do.
inner join Erp.OrderDtl as OrderDtl on
OrderDtl.PartNum = JobHead.PartNum
3 Likes
This fixed it. My thoughts on joining customers were to get custName and ID from the part number, I need to fix that join to not count LaborHrs more than once
I suggest you always add join by Company if it makes sense in the query.
like inner join Erp.OrderDtl as OrderDtl on
OrderDtl.Comany = JobHead.Company and
OrderDtl.PartNum = JobHead.PartNum
it will help SQL server to reuse indexed it has
2 Likes
When I link OrderDtl to part, labor goes from 1.5 to 20. All types of joins result in the same loss of accuracy in laborhrs. I’m adding OrderDtl in an attempt to get unit price from the order. I know part usually has unit price, but our price is market driven so it is too variable to tie to the part table. At least that is what I think.
Interesting results:
When I add unit price from OrderDtl, I get three duplicate rows on each jobnumber. 2 of the 3 rows report the correct sum of labor and the 3rd is off by by a factor of ~10
Do you have the labor details showing you what you want?
Sounds like you need multiple queries. You just cannot join a part to part. There are few more tables you will need to bring into the query to keep the row count
Query1
One to get all the labor details for each job/AssemblySeq/opr.
Query2
One to show all jobs/Assembly/part for each order details
Tables
OrderDtl/JobProd/JobAssembly
Query 3
join 1 and 2
1 Like
Before you start grouping and summing, get the individual rows and make sure it’s what you are looking for. Then when you have the rows that you know you need start the grouping and summing process. Otherwise it’s hard to tell what’s messing you up.
2 Likes