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

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]