Hi everyone!
I am working on a BAQ that looks at the minimum and average minutes per piece, for each part/op. I am working under the assumption that every job for the part uses the same set of operations. This may be a flawed assumption but should be close with some clever date filtering.
I have a good BAQ that pulls the labor details for open/engineered jobs. Then I made sure to only consider data when I have at least 2 labor data entries for that op. Next I tried to calculate the potential time savings for each operation based on the lowest mins/piece in the past labor detail data.
This is more or less working, but I am having trouble trying to include a field in an aggregate calculation, but I don’t want that field in my data. The field is JobOper_RunQty. I want to use the run qty to estimate a savings of time per op/part. But I don’t want to post the run qty as an output field. I may have many jobs for a simple part, and those jobs might have differing run qtys. In the end I only want one record for each part/op, then I can use grouping and summaries to see the potential savings across the entire part if we are able to reduce op times to the minimum.
Here is what I have as a BAQ and SQL:
JTWL-LastJobs2.baq (66.8 KB)
select
[SubQuery21].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
[SubQuery21].[JobOper_OprSeq] as [JobOper_OprSeq],
[SubQuery3].[Calculated_LabCount] as [Calculated_LabCount],
[SubQuery21].[JobOper1_ProdStandard] as [JobOper1_ProdStandard],
[SubQuery21].[JobOper_RunQty] as [JobOper_RunQty],
(min(SubQuery21.Calculated_TimePerPart)) as [Calculated_MinOpTime],
(SubQuery21.JobOper1_ProdStandard - MinOpTime) as [Calculated_SavingsPerPart],
(SubQuery21.JobOper_RunQty * SavingsPerPart) as [Calculated_SavingsTotPartNum]
from (select
[SubQuery1].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
[SubQuery1].[JobOper_OprSeq] as [JobOper_OprSeq],
[SubQuery1].[JobOpDtl_OpDtlDesc] as [JobOpDtl_OpDtlDesc],
[JobOper1].[ProdStandard] as [JobOper1_ProdStandard],
[SubQuery1].[Calculated_TimePerPart] as [Calculated_TimePerPart],
[SubQuery1].[LaborDtl_LaborHrs] as [LaborDtl_LaborHrs],
[SubQuery1].[LaborDtl_LaborQty] as [LaborDtl_LaborQty],
[SubQuery1].[LaborDtl_JobNum] as [LaborDtl_JobNum],
[SubQuery1].[LaborDtl_EmployeeNum] as [LaborDtl_EmployeeNum],
(JobOper1.ProdStandard/ SubQuery1.Calculated_TimePerPart) as [Calculated_EmpEff],
[SubQuery1].[JobOper_RunQty] as [JobOper_RunQty]
from (select
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[RevisionNum] as [JobAsmbl_RevisionNum],
[JobHead].[RevisionNum] as [JobHead_RevisionNum],
[JobHead].[DrawNum] as [JobHead_DrawNum],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
[JobOper].[RunQty] as [JobOper_RunQty],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOpDtl].[ResourceGrpID] as [JobOpDtl_ResourceGrpID],
[JobOpDtl].[OpDtlDesc] as [JobOpDtl_OpDtlDesc],
[JobOper].[SubContract] as [JobOper_SubContract],
[JobOper].[Description] as [JobOper_Description],
[JobOper].[DaysOut] as [JobOper_DaysOut],
[JobOper].[EstSetHours] as [JobOper_EstSetHours],
[JobOper].[EstProdHours] as [JobOper_EstProdHours],
[JobOper].[CommentText] as [JobOper_CommentText],
((iif(LaborDtl.LaborHrs=0,0.01, LaborDtl.LaborHrs) / iif(LaborDtl.LaborQty=0,0.01, LaborDtl.LaborQty)) * 60) as [Calculated_TimePerPart],
[LaborDtl].[LaborHrs] as [LaborDtl_LaborHrs],
[LaborDtl].[LaborQty] as [LaborDtl_LaborQty],
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[LaborDtl].[AssemblySeq] as [LaborDtl_AssemblySeq],
[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
[LaborDtl].[ResourceGrpID] as [LaborDtl_ResourceGrpID]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.JobOper as JobOper on
JobAsmbl.Company = JobOper.Company
and JobAsmbl.JobNum = JobOper.JobNum
and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
and ( JobOper.OprSeq <> 900 )
inner join Erp.JobOpDtl as JobOpDtl on
JobOper.Company = JobOpDtl.Company
and JobOper.JobNum = JobOpDtl.JobNum
and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
and JobOper.OprSeq = JobOpDtl.OprSeq
and ( not JobOpDtl.CapabilityID like '"L"%' )
inner join Erp.LaborDtl as LaborDtl on
JobOper.Company = LaborDtl.Company
and JobOper.JobNum = LaborDtl.JobNum
and JobOper.AssemblySeq = LaborDtl.AssemblySeq
and JobOper.OprSeq = LaborDtl.OprSeq
and ( LaborDtl.LaborType = '"P"' and LaborDtl.PayrollDate >= dateadd (year, -4, Constants.Today) and LaborDtl.LaborQty > 0 )
where (JobHead.JobClosed = false and JobHead.JobReleased = true)) as SubQuery1
left outer join Erp.JobOper as JobOper1 on
SubQuery1.JobAsmbl_JobNum = JobOper1.JobNum
and SubQuery1.JobAsmbl_AssemblySeq = JobOper1.AssemblySeq
and SubQuery1.JobOper_OprSeq = JobOper1.OprSeq) as SubQuery21
inner join (select
[SubQuery2].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
[SubQuery2].[JobOper_OprSeq] as [JobOper_OprSeq],
(count(SubQuery2.LaborDtl_LaborHrs)) as [Calculated_LabCount],
[SubQuery2].[JobOper_RunQty] as [JobOper_RunQty]
from (select
[SubQuery1].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
[SubQuery1].[JobOper_OprSeq] as [JobOper_OprSeq],
[SubQuery1].[JobOpDtl_OpDtlDesc] as [JobOpDtl_OpDtlDesc],
[JobOper1].[ProdStandard] as [JobOper1_ProdStandard],
[SubQuery1].[Calculated_TimePerPart] as [Calculated_TimePerPart],
[SubQuery1].[LaborDtl_LaborHrs] as [LaborDtl_LaborHrs],
[SubQuery1].[LaborDtl_LaborQty] as [LaborDtl_LaborQty],
[SubQuery1].[LaborDtl_JobNum] as [LaborDtl_JobNum],
[SubQuery1].[LaborDtl_EmployeeNum] as [LaborDtl_EmployeeNum],
(JobOper1.ProdStandard/ SubQuery1.Calculated_TimePerPart) as [Calculated_EmpEff],
[SubQuery1].[JobOper_RunQty] as [JobOper_RunQty]
from (select
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[RevisionNum] as [JobAsmbl_RevisionNum],
[JobHead].[RevisionNum] as [JobHead_RevisionNum],
[JobHead].[DrawNum] as [JobHead_DrawNum],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
[JobOper].[RunQty] as [JobOper_RunQty],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOpDtl].[ResourceGrpID] as [JobOpDtl_ResourceGrpID],
[JobOpDtl].[OpDtlDesc] as [JobOpDtl_OpDtlDesc],
[JobOper].[SubContract] as [JobOper_SubContract],
[JobOper].[Description] as [JobOper_Description],
[JobOper].[DaysOut] as [JobOper_DaysOut],
[JobOper].[EstSetHours] as [JobOper_EstSetHours],
[JobOper].[EstProdHours] as [JobOper_EstProdHours],
[JobOper].[CommentText] as [JobOper_CommentText],
((iif(LaborDtl.LaborHrs=0,0.01, LaborDtl.LaborHrs) / iif(LaborDtl.LaborQty=0,0.01, LaborDtl.LaborQty)) * 60) as [Calculated_TimePerPart],
[LaborDtl].[LaborHrs] as [LaborDtl_LaborHrs],
[LaborDtl].[LaborQty] as [LaborDtl_LaborQty],
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[LaborDtl].[AssemblySeq] as [LaborDtl_AssemblySeq],
[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
[LaborDtl].[ResourceGrpID] as [LaborDtl_ResourceGrpID]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.JobOper as JobOper on
JobAsmbl.Company = JobOper.Company
and JobAsmbl.JobNum = JobOper.JobNum
and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
and ( JobOper.OprSeq <> 900 )
inner join Erp.JobOpDtl as JobOpDtl on
JobOper.Company = JobOpDtl.Company
and JobOper.JobNum = JobOpDtl.JobNum
and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
and JobOper.OprSeq = JobOpDtl.OprSeq
and ( not JobOpDtl.CapabilityID like '"L"%' )
inner join Erp.LaborDtl as LaborDtl on
JobOper.Company = LaborDtl.Company
and JobOper.JobNum = LaborDtl.JobNum
and JobOper.AssemblySeq = LaborDtl.AssemblySeq
and JobOper.OprSeq = LaborDtl.OprSeq
and ( LaborDtl.LaborType = '"P"' and LaborDtl.PayrollDate >= dateadd (year, -4, Constants.Today) and LaborDtl.LaborQty > 0 )
where (JobHead.JobClosed = false and JobHead.JobReleased = true)) as SubQuery1
left outer join Erp.JobOper as JobOper1 on
SubQuery1.JobAsmbl_JobNum = JobOper1.JobNum
and SubQuery1.JobAsmbl_AssemblySeq = JobOper1.AssemblySeq
and SubQuery1.JobOper_OprSeq = JobOper1.OprSeq) as SubQuery2
group by [SubQuery2].[JobAsmbl_PartNum],
[SubQuery2].[JobOper_OprSeq],
[SubQuery2].[JobOper_RunQty]) as SubQuery3 on
SubQuery21.JobAsmbl_PartNum = SubQuery3.JobAsmbl_PartNum
and SubQuery21.JobOper_OprSeq = SubQuery3.JobOper_OprSeq
and ( SubQuery3.Calculated_LabCount > 1 )
group by [SubQuery21].[JobAsmbl_PartNum],
[SubQuery21].[JobOper_OprSeq],
[SubQuery3].[Calculated_LabCount],
[SubQuery21].[JobOper1_ProdStandard],
[SubQuery21].[JobOper_RunQty]