Number of Jobs created for a Part Number

Epicor ERP 10
1

I think my brain is mush at this point. I am creating a BAQ that has the following:
Part No, Est Set Up Hrs/job, Act Set Up Hrs/job, Cost of Part and then the Number of set up’s done performed. No problem.

I now want to find out how many jobs we have created to make this part. When I try to add a count ( JobAsmbl Or LaborDtl.JobNum) I get a count of 1 or a count of 35, which we have only created 25 jobs total.

At this point I have tried so many way’s I’m not quite sure where I am at this point. I’m attaching my code but please note at this point I am listing each job (though job number isn’t displaying (my choice)). I will eventually add everything together such as Total Hours in Set-Up, Total Number of Jobs, etc. so that there is only one line per part reporting on the Set-Up statistics. At this point I am listing each separately so that I can check my work.

How can I calculate just the number of jobs we have created for this part. This should be easy - but like I said, my brain is mush at this point :thinking:

Code:
select
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[TLESetupHours] as [JobAsmbl_TLESetupHours],
[JobAsmbl].[TLASetupHours] as [JobAsmbl_TLASetupHours],
[SOInfo].[Calculated_CalcUnitCost] as [Calculated_CalcUnitCost],
(count( LaborDtl.LaborType )) as [Calculated_TotalNumberOfSU]
from Erp.JobAsmbl as JobAsmbl
inner join Erp.LaborDtl as LaborDtl on
JobAsmbl.Company = LaborDtl.Company
and JobAsmbl.JobNum = LaborDtl.JobNum
and ( LaborDtl.LaborType = ‘S’ )

inner join (select
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
(max( OrderDtl.DocUnitPrice )) as [Calculated_CalcUnitCost],
(max( OrderHed.OrderNum )) as [Calculated_LastSO]
from Erp.OrderDtl as OrderDtl
inner join Erp.OrderHed as OrderHed on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
group by [OrderDtl].[PartNum]) as SOInfo on
SOInfo.OrderDtl_PartNum = JobAsmbl.PartNum
where (JobAsmbl.JobNum = @JobNum or JobAsmbl.PartNum = @NWPN)
group by [JobAsmbl].[PartNum],
[JobAsmbl].[TLESetupHours],
[JobAsmbl].[TLASetupHours],
[SOInfo].[Calculated_CalcUnitCost]

2

So before you group it, show the job number, and assembly seq, and look at how many rows that you get back.

You can have more than one assembly with the same part number on the job, so if you have 25 jobs, and 10 of them have the part in there twice, you are going to get 35 rows back.

You can also have more than one labor dtl per assembly and operation, so keep that in mind as well.

You can do count(distinct JobAsm.JobNum) and that will count the number of different job numbers that come back with your assemblies.

image
image721×405 20.2 KB

Always think about your tables and what kind of information is in those tables. Can you have more than one row with the same information? If if you think the results are off, run a test query without the aggregates for a test case and see where the “duplicates” are.

1 Like
3

Thank you for the information, I will try this first thing in the morning. You definitely gave me some information to think upon! :slight_smile:

4

You also don’t have a link to the assembly seq on here. Just joining Job Num and Company is going to give you all kinds of duplicate rows. A labor detail is a job number, assembly seq and operation seq. So you need to join on asm seq as well. And even then, you can have multiples of labor dtl on a single operation.

image

5

JobAsmbl will have a record for each assembly within every job.
LaborDtl will have a record for each clock-in on a job.

Count(Distinct) is a blunt instrument for de-duping, but really you should be structuring your query in a way that avoids the duplicates in the first place. E.g. If you’re solely interested in the final product of a job, look at JobHead rather than its child tables.

1 Like
6

preach GIF by Shalita Grant

I totally agree!

1 Like
7

Alternate Method.
Add the Job Head Table to the BAQ and like to the Part table.
Company, PartNum

Make is a summary table. Create a calculated field to count(job head records).
image

image
image525×550 12.6 KB

8

This worked, I finally received the correct Job Count!

However I have a new question and wouldn’t ask it if I hadn’t been trying to figure this out for the last 4 hours.

In the picture below is a list of what i have produced with Totals or Averages listed at the bottom. But like I stated earlier I want just the total number of set-ups, total number of times produced, total estimated setup hours and total actual setup hours…but when I try to do that the totals I receive don’t match what I’m showing in the picture.

image
image824×655 12.7 KB

I’m inserting the code that I have at this point - instead of showing you all the different ways I’ve tried to “Total” can you suggest how I might say in a BAQ:
Sum - EstSetupHours
Sum - ActSetupHours
Avg - CalcUnitCost
Sum - NoofSetups
Avg - TimesProduced
No need to worry about Job No’s I’m not going to be listing them, again they are there so I can check my work.

Please know that I am TOTALLY self taught - I know a miniscule of SQL and have only picked up BAQ’s by trial and error. Any help would be appreciated - or I’m going to have to throw in the towel and have this BAQ farmed out :roll_eyes:

select 
	[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
	[SetupHours].[Calculated_EstSetupHours] as [Calculated_EstSetupHours],
	[SetupHours].[Calculated_ActSetupHours] as [Calculated_ActSetupHours],
	[SOInfo].[Calculated_CalcUnitCost] as [Calculated_CalcUnitCost],
	[SetupHours].[Calculated_NoofSetups] as [Calculated_NoofSetups],
	[Jobs].[Calculated_TimesProduced] as [Calculated_TimesProduced],
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum]
from Erp.JobAsmbl as JobAsmbl
inner join Erp.LaborDtl as LaborDtl on 
	JobAsmbl.Company = LaborDtl.Company
	and JobAsmbl.JobNum = LaborDtl.JobNum
	and ( LaborDtl.LaborType = 'S'  )

inner join  (select 
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	(max( OrderDtl.DocUnitPrice )) as [Calculated_CalcUnitCost],
	(max( OrderHed.OrderNum )) as [Calculated_LastSO]
from Erp.OrderDtl as OrderDtl
inner join Erp.OrderHed as OrderHed on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
group by [OrderDtl].[PartNum])  as SOInfo on 
	SOInfo.OrderDtl_PartNum = JobAsmbl.PartNum
inner join  (select 
	[JobAsmbl2].[JobNum] as [JobAsmbl2_JobNum],
	[JobAsmbl2].[PartNum] as [JobAsmbl2_PartNum],
	(sum( Distinct JobAsmbl2.TLESetupHours )) as [Calculated_EstSetupHours],
	(sum( Distinct JobAsmbl2.TLASetupHours )) as [Calculated_ActSetupHours],
	(count( LaborDtl1.LaborType )) as [Calculated_NoofSetups]
from Erp.JobAsmbl as JobAsmbl2
inner join Erp.LaborDtl as LaborDtl1 on 
	JobAsmbl2.Company = LaborDtl1.Company
	and JobAsmbl2.JobNum = LaborDtl1.JobNum
	and JobAsmbl2.AssemblySeq = LaborDtl1.AssemblySeq
	and ( LaborDtl1.LaborType = 'S'  )

group by [JobAsmbl2].[JobNum],
	[JobAsmbl2].[PartNum])  as SetupHours on 
	SetupHours.JobAsmbl2_JobNum = JobAsmbl.JobNum
inner join  (select 
	[JobAsmbl3].[PartNum] as [JobAsmbl3_PartNum],
	(count( Distinct JobAsmbl3.JobNum )) as [Calculated_TimesProduced]
from Erp.JobAsmbl as JobAsmbl3
group by [JobAsmbl3].[PartNum])  as Jobs on 
	Jobs.JobAsmbl3_PartNum = JobAsmbl.PartNum
where (JobAsmbl.JobNum = @JobNum  or JobAsmbl.PartNum = @NWPN)
group by [JobAsmbl].[PartNum],
	[SetupHours].[Calculated_EstSetupHours],
	[SetupHours].[Calculated_ActSetupHours],
	[SOInfo].[Calculated_CalcUnitCost],
	[SetupHours].[Calculated_NoofSetups],
	[Jobs].[Calculated_TimesProduced],
	[JobAsmbl].[JobNum]
9

ok, Count(distinct ) I can understand. But Sum(distinct ) doesn’t make sense why you would would do that.

job setupOp hours
123 10 1
123 20 1
123 30 5
789 10 1.5
789 20 3.5

Say this is my job data.

If I want to count the number of jobs, I can see that I have 2 different jobs. So count() would give me 5, because there are 5 lines, but count(distinct) would give me 2 because there are 2 different jobs.

If I were to want to sum up the hours, I would group by job number, then if I were to use sum(distinct) if we take the job 123 as an example, I would be summing 1,1,and 5. But since you used the distinct, it would only be seeing 5,1 so it would total 6. But you really want to sum all of the hours so you want sum() so that it sees all of them and adds all of them (1,1 and 5) up to give you 7.

image

Does that make sense on what the difference are between those two?

10

Perfectly - at one point the number of hours I received was that jobs total number of set hours multiplied by the number of actual set-ups. I was “hoping” that if I said distinct it would just give me the hours for that job, not multiplied by the number of actual set-ups.

11

I think we need to break this down into smaller parts of the problem, then combine. Definitions are important here too.

Sum - EstSetupHours
This is taking the joboper table estimated hours and adding all of the hours in each row together.

Sum - ActSetupHours
This is taking the information from the laborDtl table and adding all of the hours clocked against said operations.

Avg - CalcUnitCost
I would wait on this until you have other pieces in place, but I am curious how you are defining/where you are getting the unit cost?

Sum - NoofSetups
How are you determining the number of setups? If you are counting labor dtl records, this doesn’t necessarily equate to how many times a setup happen. Can multiple people clock in on the same setup? Can someone clock out and back into the same setup? Any of these things will inflate the “number” that you count on this field. Also, this should be a count, not a sum.

Avg - TimesProduced
Right now, you are counting the number of different jobs that this part is an assembly on. Maybe right? Not sure.

Another question I want to ask is something that @Patrick asked about a little earlier, is this. Are you looking for information on the part that is being produceed by this job? Or are you looking for information based on the subassemblies within the job. If you only look for the finished goods coming out of this job, the part number is in the job header.

So, do each piece separately in some test baqs. Start without grouping, and filter down to a specific part or whatever you need to be able to manually do the calulcation, then add the calculated field, do the grouping, and remove the columns that mess up your grouping. Once you have all of those small pieces, you can make each one of those it’s own subquery, then join them all together by the part number.

This isn’t really a SQL thing or a BAQ thing, it’s about understanding your data and tables and working the logic through the joins and the grouping. Break the problem down into smaller pieces and you can work through it.

12

I’m using the LaborDtl Table. specifying that LaborType=S and then doing a:
count ( LaborDtl.LaborType )

Avg - TimesProduced
Right now, you are counting the number of different jobs that this part is an assembly on. Maybe right? Not sure.
Yes

Lets start with these…

13

So joining laborDtl to JobAsm is going to potentially give you duplicates. So, if counting the number of clockin is legitimate to get the number of setups, you should count them in a subquery, then group it down to the job, asm, and op sequence, then bring that subquery in. Otherwise, if you have 2 clockins, you’re going to get double rows on your jobAsm table, and it will inflate your TLA numbers.

image
image716×425 9.64 KB

14

I’ve done as you suggested by taking the subqueries and running them individually - which did show me some errors and so I fixed them.

But…When I’m trying to get the Total Count of Setups I’m getting wacky results. The following picture show’s the correct number of setup’s when I list them by Job Number.

image
image359×610 5.82 KB

But when I try to “Total” the setup’s I get this and remove the Job Number, I get this:
image

I know this has something to do with my grouping - Also, I have tried Counting “ClockInDate” and this result is from LaborDtl.LaborType. ClockInDate was not giving me the correct number of setup’s.

Again, here is the code:
select
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[LaborDtl1].[Calculated_TotalSUs] as [Calculated_TotalSUs]
from Erp.JobAsmbl as JobAsmbl
inner join (select
[LaborDtl].[Company] as [LaborDtl_Company],
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[LaborDtl].[AssemblySeq] as [LaborDtl_AssemblySeq],
[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
(count( LaborDtl.LaborType )) as [Calculated_TotalSUs]
from Erp.LaborDtl as LaborDtl
where (LaborDtl.LaborType = ‘S’)
group by [LaborDtl].[Company],
[LaborDtl].[JobNum],
[LaborDtl].[AssemblySeq],
[LaborDtl].[OprSeq]) as LaborDtl1 on
LaborDtl1.LaborDtl_AssemblySeq = JobAsmbl.AssemblySeq
and LaborDtl1.LaborDtl_JobNum = JobAsmbl.JobNum
LaborDtl1.LaborDtl_Company = JobAsmbl.Company
where (JobAsmbl.PartNum = ‘10156’)
group by [JobAsmbl].[PartNum],
[LaborDtl1].[Calculated_TotalSUs]

Have I mentioned how grateful I am for your help? :wink:

15

Can you post screenshots of your BAQ? (I can understand it better from the BAQ)

But from what I can gather from the code, are you grouping on a subquery and then grouping again in the top level of this query? You should only need to group and sum on one level. There should be no reason to group once, then group again. (in this instance). Conversely, you could count on one level and then count again on the next, but why complicate things?

To illustrate this, if you take your first grid of stuff, remove the jobs, then remove all of the duplicate rows (where the rows have the same total) you will notice it matches the second grid.

image
image793×545 27.7 KB

16

I’ll pick this up again tomorrow…

As I mentioned, I have taken each Subquery apart and run them separately and tomorrow I will attempt to put them all together. The only one that remains to give me trouble is calculating the Total Number of Setup Hours.

I see what you are saying and will look into that tomorrow.

When I do put all these together into Top Level I understand that I’ll need a table to connect them all to. Any suggestions?

Have a good evening!

17

Not really. Just bring in all of your sub queries together and join on part number.

18

Once all my subqueries were added to a Top Level, joining by Part Number…Dare I say it is working!!!

Big smile :grinning:

1 Like
19

One last thing for anyone wondering what finally worked.

It was the suggestion that I build each Subquery separately making sure each one worked. Then add them to the Top Level one by one to confirm that they continue to work as expected or modify if need be. This also helped in “simplifying” the BAQ!

1 Like
20

Glad you figured out, and I’m glad you learned something. While someone could have “done it for you” your skills would still be at the same place they were.