Diagnosing Complex BAQ Slowness Kinetic

[NateS (Nate Schoonover)]

Working in the Kinetic BAQ editor is terrible. I want the features they added put into classic. Saving a BAQ is a nightmare. It takes forever, lags, errors out, sometimes does save, sometimes produces a handful of different deadlock errors.

That all sucks, but fine, I have to deal with it. I am looking for any new ways to diagnose where my BAQ is sucking up all the time. I am running a BAQ that references another BAQ. It works, but takes 30-60 seconds to run.

This set of BAQs attempts to apply some kind of milestone logic based on our job operation statuses. There is a lot going on. The referenced BAQ is JobStatus:
JobStatus.baq (35.8 KB)

This looks at the assemblies in a job, and assigns the lowest status to the entire job, based on the lowest status of the unfinished assemblies.

The BAQ that uses JobStatus is WIPvsSalesValue.
WIPvsSalesValue.baq (65.2 KB)
This recreates the WIP report. I think I got the base for this from someone on here (thank you! I still don’t get CTEs). Then it combines the sales order value of each job, so we can see a WIP value and a Sales order value. Finally, it applies a status from JobSatus to each job, allowing us to put into buckets the WIP value and Sales Value. For example, we can get a total of all the WIP value for jobs sitting at the status “Farmout”, and we can see the associated sales value for that set of jobs.

This is far from perfect, and the magic lies in how we set the job status flags. We are still fine-tuning those rules. In the meantime, what can I do to see where the major time is being used up in this BAQ? Is there feature in Devtools that allows me to track this level of timing details? I’d like to see how long each step takes, so I can focus my efforts. It takes so long to make a change to a BAQ, then get it to actually save that change. Just adding Company back in to this as a key field took nearly 30 minutes and 6+ tries of clicking save. Waiting for the blue bar to go away, and then checking to see if it saved. If I could edit this BAQ in classic, I totally would.

Thank you for your time!
Nate

[mbayley (Michael Bayley)]

Do you mind posting your query phrase? I can’t load those BAQs into my environment because of versions

[NateS (Nate Schoonover)]

JobStatus:

/*  
 * Disclaimer!!! 
 * This is not a real query being executed, but a simplified version for general vision. 
 * Executing it with any other tool may produce a different result. 
 */

select  
	[FinalTop].[JobHead6_Company] as [JobHead6_Company], 
	(iif(FinalTop.JobHead6_JobNum is null, FinalTop.JobHead6_JobNum01,FinalTop.JobHead6_JobNum)) as [Calculated_JobNum], 
	[FinalTop].[Calculated_Status] as [Calculated_Status] 

from  (select  
	[TopStatus].[JobHead6_Company] as [JobHead6_Company], 
	[AsmStatus].[JobHead6_JobNum] as [JobHead6_JobNum], 
	[TopStatus].[JobHead6_JobNum] as [JobHead6_JobNum01], 
	(iif(TopStatus.Calculated_TopStatus=0,AsmStatus.Calculated_Status,iif(AsmStatus.Calculated_Status<TopStatus.Calculated_TopStatus,AsmStatus.Calculated_Status,TopStatus.Calculated_TopStatus))) as [Calculated_Status], 
	[TopStatus].[Calculated_TopStatus] as [Calculated_TopStatus] 

from  (select  
	[Top2].[JobHead6_Company] as [JobHead6_Company], 
	[Top2].[JobHead6_JobNum] as [JobHead6_JobNum], 
	[Top2].[Calculated_MtlIssued] as [Calculated_MtlIssued], 
	[Top2].[Calculated_BackFromHT] as [Calculated_BackFromHT], 
	[Top2].[Calculated_Farmout] as [Calculated_Farmout], 
	[Top2].[Calculated_InProcess] as [Calculated_InProcess], 
	[Top2].[Calculated_OVNotFinal] as [Calculated_OVNotFinal], 
	[Top2].[Calculated_OVFinalOrBack] as [Calculated_OVFinalOrBack], 
	(CASE
    WHEN Calculated_OVFinalOrBack = 1 THEN 6
    WHEN Calculated_OVNotFinal = 1 THEN 5
    WHEN Calculated_InProcess = 1 THEN 4
    WHEN Calculated_Farmout = 1 THEN 3
    WHEN Calculated_BackFromHT = 1 THEN 2
    WHEN Calculated_MtlIssued = 1 THEN 1
    ELSE 0
END) as [Calculated_TopStatus] 

from  (select distinct 
	[JobHead6].[Company] as [JobHead6_Company], 
	[JobHead6].[JobNum] as [JobHead6_JobNum], 
	[JobHead6].[PartNum] as [JobHead6_PartNum], 
	[JobAsmbl6].[AssemblySeq] as [JobAsmbl6_AssemblySeq], 
	[JobAsmbl6].[PartNum] as [JobAsmbl6_PartNum], 
	[MtlIssued].[Calculated_MtlIssued] as [Calculated_MtlIssued], 
	[HeatTreat].[Calculated_BackFromHT] as [Calculated_BackFromHT], 
	[Farmout].[Calculated_Farmout] as [Calculated_Farmout], 
	(iif((NewIP=1 or NewIP2=1),1,0)) as [Calculated_InProcess], 
	[OVNotFinal].[Calculated_OVNotFinal] as [Calculated_OVNotFinal], 
	[OVFinalOrBack].[Calculated_OVFinalOrBack] as [Calculated_OVFinalOrBack], 
	(iif(MtlIssued.Calculated_MtlIssued = 1, iif(Labor.Calculated_LaborHrs>0,1,0),0)) as [Calculated_NewIP], 
	(iif(HeatTreat.Calculated_BackFromHT=1, iif(Labor.Calculated_LaborHrs=0,1,0),0)) as [Calculated_NewIP2], 
	(iif(DetailComplete2.Calculated_LaborTotal>=JobHead6.ProdQty,1,0)) as [Calculated_DetailComplete] 

from Erp.JobHead as [JobHead6]
left outer join Erp.JobAsmbl as [JobAsmbl6] on 
	  JobHead6.Company = JobAsmbl6.Company
	and  JobHead6.JobNum = JobAsmbl6.JobNum
left outer join  (select distinct 
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum], 
	[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq], 
	(1) as [Calculated_MtlIssued] 

from Erp.JobHead as [JobHead]
inner join Erp.JobAsmbl as [JobAsmbl] on 
	  JobHead.Company = JobAsmbl.Company
	and  JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.PartTran as [PartTran] on 
	  JobAsmbl.Company = PartTran.Company
	and  JobAsmbl.JobNum = PartTran.JobNum
	and  JobAsmbl.AssemblySeq = PartTran.AssemblySeq
	and ( PartTran.TranClass = 'I'  
and PartTran.TranQty > 0  ))  as [MtlIssued] on 
	  JobAsmbl6.JobNum = MtlIssued.JobAsmbl_JobNum
	and  JobAsmbl6.AssemblySeq = MtlIssued.JobAsmbl_AssemblySeq
left outer join  (select  
	[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum], 
	[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq], 
	(1) as [Calculated_BackFromHT] 

from Erp.JobHead as [JobHead1]
inner join Erp.JobAsmbl as [JobAsmbl1] on 
	  JobHead1.Company = JobAsmbl1.Company
	and  JobHead1.JobNum = JobAsmbl1.JobNum
inner join Erp.JobOper as [JobOper1] on 
	  JobAsmbl1.Company = JobOper1.Company
	and  JobAsmbl1.JobNum = JobOper1.JobNum
	and  JobAsmbl1.AssemblySeq = JobOper1.AssemblySeq
	and ( JobOper1.OpCode = 'HT-OVOP'  
and JobOper1.OpComplete = true  ))  as [HeatTreat] on 
	  JobAsmbl6.JobNum = HeatTreat.JobAsmbl1_JobNum
	and  JobAsmbl6.AssemblySeq = HeatTreat.JobAsmbl1_AssemblySeq
left outer join  (select  
	[JobHead2].[JobNum] as [JobHead2_JobNum], 
	[JobAsmbl2].[AssemblySeq] as [JobAsmbl2_AssemblySeq], 
	(1) as [Calculated_Farmout] 

from Erp.JobHead as [JobHead2]
inner join Erp.JobAsmbl as [JobAsmbl2] on 
	  JobHead2.Company = JobAsmbl2.Company
	and  JobHead2.JobNum = JobAsmbl2.JobNum
inner join Erp.JobOper as [JobOper2] on 
	  JobAsmbl2.Company = JobOper2.Company
	and  JobAsmbl2.JobNum = JobOper2.JobNum
	and  JobAsmbl2.AssemblySeq = JobOper2.AssemblySeq
	and ( JobOper2.OpCode IN ('OMC-OVOP', 'OMP-OVOP')  
and JobOper2.OpComplete = false  )
inner join Erp.PORel as [PORel] on 
	  JobOper2.Company = PORel.Company
	and  JobOper2.JobNum = PORel.JobNum
	and  JobOper2.AssemblySeq = PORel.AssemblySeq
	and  JobOper2.OprSeq = PORel.JobSeq
	and ( PORel.OpenRelease = true  ))  as [Farmout] on 
	  JobAsmbl6.JobNum = Farmout.JobHead2_JobNum
	and  JobAsmbl6.AssemblySeq = Farmout.JobAsmbl2_AssemblySeq
left outer join  (select  
	[JobHead3].[JobNum] as [JobHead3_JobNum], 
	[JobAsmbl3].[AssemblySeq] as [JobAsmbl3_AssemblySeq], 
	(1) as [Calculated_OVNotFinal], 
	[PORel1].[OpenRelease] as [PORel1_OpenRelease], 
	[PORel1].[PONum] as [PORel1_PONum], 
	[PORel1].[POLine] as [PORel1_POLine], 
	[PORel1].[PORelNum] as [PORel1_PORelNum], 
	[PORel1].[DueDate] as [PORel1_DueDate], 
	[PORel1].[XRelQty] as [PORel1_XRelQty], 
	[PORel1].[RelQty] as [PORel1_RelQty], 
	[PORel1].[JobNum] as [PORel1_JobNum], 
	[PORel1].[AssemblySeq] as [PORel1_AssemblySeq], 
	[PORel1].[JobSeq] as [PORel1_JobSeq] 

from Erp.JobHead as [JobHead3]
inner join Erp.JobAsmbl as [JobAsmbl3] on 
	  JobHead3.Company = JobAsmbl3.Company
	and  JobHead3.JobNum = JobAsmbl3.JobNum
inner join Erp.JobOper as [JobOper3] on 
	  JobAsmbl3.Company = JobOper3.Company
	and  JobAsmbl3.JobNum = JobOper3.JobNum
	and  JobAsmbl3.AssemblySeq = JobOper3.AssemblySeq
	and ( JobOper3.SubContract = true  
and JobOper3.OpComplete = false  
and not JobOper3.OpCode IN ('OMC-OVOP', 'OMP-OVOP')  )
left outer join  (select  
	[JobHead4].[JobNum] as [JobHead4_JobNum], 
	[JobAsmbl4].[AssemblySeq] as [JobAsmbl4_AssemblySeq], 
	(max(JobOper4.OprSeq)) as [Calculated_LastOVOp] 

from Erp.JobHead as [JobHead4]
inner join Erp.JobAsmbl as [JobAsmbl4] on 
	  JobHead4.Company = JobAsmbl4.Company
	and  JobHead4.JobNum = JobAsmbl4.JobNum
inner join Erp.JobOper as [JobOper4] on 
	  JobAsmbl4.Company = JobOper4.Company
	and  JobAsmbl4.JobNum = JobOper4.JobNum
	and  JobAsmbl4.AssemblySeq = JobOper4.AssemblySeq
	and ( JobOper4.SubContract = true  
and not JobOper4.OpCode IN ('OMC-OVOP', 'OMP-OVOP')  )
group by 
	[JobHead4].[JobNum], 
	[JobAsmbl4].[AssemblySeq])  as [LastOVOp1] on 
	  JobOper3.JobNum = LastOVOp1.JobHead4_JobNum
	and  JobOper3.AssemblySeq = LastOVOp1.JobAsmbl4_AssemblySeq
	and not JobOper3.OprSeq = LastOVOp1.Calculated_LastOVOp
inner join Erp.PORel as [PORel1] on 
	  JobOper3.Company = PORel1.Company
	and  JobOper3.JobNum = PORel1.JobNum
	and  JobOper3.AssemblySeq = PORel1.AssemblySeq
	and  JobOper3.OprSeq = PORel1.JobSeq
	and ( PORel1.OpenRelease = true  ))  as [OVNotFinal] on 
	  JobAsmbl6.JobNum = OVNotFinal.JobHead3_JobNum
	and  JobAsmbl6.AssemblySeq = OVNotFinal.JobAsmbl3_AssemblySeq
left outer join  (select  
	[JobHead5].[JobNum] as [JobHead5_JobNum], 
	[JobAsmbl5].[AssemblySeq] as [JobAsmbl5_AssemblySeq], 
	(1) as [Calculated_OVFinalOrBack], 
	[PORel2].[OpenRelease] as [PORel2_OpenRelease], 
	[PORel2].[VoidRelease] as [PORel2_VoidRelease], 
	[PORel2].[PONum] as [PORel2_PONum], 
	[PORel2].[POLine] as [PORel2_POLine], 
	[PORel2].[PORelNum] as [PORel2_PORelNum], 
	[PORel2].[DueDate] as [PORel2_DueDate], 
	[PORel2].[RelQty] as [PORel2_RelQty], 
	[PORel2].[JobNum] as [PORel2_JobNum], 
	[PORel2].[AssemblySeq] as [PORel2_AssemblySeq], 
	[PORel2].[JobSeq] as [PORel2_JobSeq], 
	[PORel2].[Status] as [PORel2_Status] 

from Erp.JobHead as [JobHead5]
inner join Erp.JobAsmbl as [JobAsmbl5] on 
	  JobHead5.Company = JobAsmbl5.Company
	and  JobHead5.JobNum = JobAsmbl5.JobNum
inner join  (select  
	[JobHead4].[JobNum] as [JobHead4_JobNum], 
	[JobAsmbl4].[AssemblySeq] as [JobAsmbl4_AssemblySeq], 
	(max(JobOper4.OprSeq)) as [Calculated_LastOVOp] 

from Erp.JobHead as [JobHead4]
inner join Erp.JobAsmbl as [JobAsmbl4] on 
	  JobHead4.Company = JobAsmbl4.Company
	and  JobHead4.JobNum = JobAsmbl4.JobNum
inner join Erp.JobOper as [JobOper4] on 
	  JobAsmbl4.Company = JobOper4.Company
	and  JobAsmbl4.JobNum = JobOper4.JobNum
	and  JobAsmbl4.AssemblySeq = JobOper4.AssemblySeq
	and ( JobOper4.SubContract = true  
and not JobOper4.OpCode IN ('OMC-OVOP', 'OMP-OVOP')  )
group by 
	[JobHead4].[JobNum], 
	[JobAsmbl4].[AssemblySeq])  as [LastOVOp] on 
	  JobAsmbl5.JobNum = LastOVOp.JobHead4_JobNum
	and  JobAsmbl5.AssemblySeq = LastOVOp.JobAsmbl4_AssemblySeq
inner join Erp.JobOper as [JobOper5] on 
	  LastOVOp.JobHead4_JobNum = JobOper5.JobNum
	and  LastOVOp.JobAsmbl4_AssemblySeq = JobOper5.AssemblySeq
	and  LastOVOp.Calculated_LastOVOp = JobOper5.OprSeq
	and ( not JobOper5.OpCode IN ('OMC-OVOP', 'OMP-OVOP')  )
inner join Erp.PORel as [PORel2] on 
	  JobOper5.Company = PORel2.Company
	and  JobOper5.JobNum = PORel2.JobNum
	and  JobOper5.AssemblySeq = PORel2.AssemblySeq
	and  JobOper5.OprSeq = PORel2.JobSeq)  as [OVFinalOrBack] on 
	  JobAsmbl6.JobNum = OVFinalOrBack.JobHead5_JobNum
	and  JobAsmbl6.AssemblySeq = OVFinalOrBack.JobAsmbl5_AssemblySeq
left outer join  (select  
	[LaborDtl].[Company] as [LaborDtl_Company], 
	[LaborDtl].[JobNum] as [LaborDtl_JobNum], 
	[LaborDtl].[AssemblySeq] as [LaborDtl_AssemblySeq], 
	(sum(LaborDtl.LaborHrs)) as [Calculated_LaborHrs] 

from Erp.LaborDtl as [LaborDtl]
group by 
	[LaborDtl].[Company], 
	[LaborDtl].[JobNum], 
	[LaborDtl].[AssemblySeq])  as [Labor] on 
	  JobAsmbl6.Company = Labor.LaborDtl_Company
	and  JobAsmbl6.JobNum = Labor.LaborDtl_JobNum
	and  JobAsmbl6.AssemblySeq = Labor.LaborDtl_AssemblySeq
left outer join  (select  
	[DetailComplete1].[JobHead7_Company] as [JobHead7_Company], 
	[DetailComplete1].[JobHead7_JobNum] as [JobHead7_JobNum], 
	[DetailComplete1].[JobAsmbl7_AssemblySeq] as [JobAsmbl7_AssemblySeq], 
	(sum(LaborDtl1.LaborQty)) as [Calculated_LaborTotal] 

from  (select  
	[JobHead7].[Company] as [JobHead7_Company], 
	[JobHead7].[JobNum] as [JobHead7_JobNum], 
	[JobAsmbl7].[AssemblySeq] as [JobAsmbl7_AssemblySeq], 
	[JobHead7].[ProdQty] as [JobHead7_ProdQty], 
	(max(JobOper.OprSeq)) as [Calculated_LastOpNotInsp] 

from Erp.JobHead as [JobHead7]
inner join Erp.JobAsmbl as [JobAsmbl7] on 
	  JobHead7.Company = JobAsmbl7.Company
	and  JobHead7.JobNum = JobAsmbl7.JobNum
inner join Erp.JobOper as [JobOper] on 
	  JobAsmbl7.Company = JobOper.Company
	and  JobAsmbl7.JobNum = JobOper.JobNum
	and  JobAsmbl7.AssemblySeq = JobOper.AssemblySeq
	and ( JobOper.OpCode <> '9-OP'  )
group by 
	[JobHead7].[Company], 
	[JobHead7].[JobNum], 
	[JobAsmbl7].[AssemblySeq], 
	[JobHead7].[ProdQty])  as [DetailComplete1]
inner join Erp.LaborDtl as [LaborDtl1] on 
	  DetailComplete1.JobHead7_Company = LaborDtl1.Company
	and  DetailComplete1.JobHead7_JobNum = LaborDtl1.JobNum
	and  DetailComplete1.JobAsmbl7_AssemblySeq = LaborDtl1.AssemblySeq
	and  DetailComplete1.Calculated_LastOpNotInsp = LaborDtl1.OprSeq
group by 
	[DetailComplete1].[JobHead7_Company], 
	[DetailComplete1].[JobHead7_JobNum], 
	[DetailComplete1].[JobAsmbl7_AssemblySeq])  as [DetailComplete2] on 
	  JobAsmbl6.Company = DetailComplete2.JobHead7_Company
	and  JobAsmbl6.JobNum = DetailComplete2.JobHead7_JobNum
	and  JobAsmbl6.AssemblySeq = DetailComplete2.JobAsmbl7_AssemblySeq)  as [Top2]
where (Top2.JobAsmbl6_AssemblySeq = 0))  as [TopStatus]
left outer join  (select  
	[Top].[JobHead6_JobNum] as [JobHead6_JobNum], 
	[Top].[JobAsmbl6_AssemblySeq] as [JobAsmbl6_AssemblySeq], 
	[Top].[Calculated_MtlIssued] as [Calculated_MtlIssued], 
	[Top].[Calculated_BackFromHT] as [Calculated_BackFromHT], 
	[Top].[Calculated_Farmout] as [Calculated_Farmout], 
	[Top].[Calculated_InProcess] as [Calculated_InProcess], 
	[Top].[Calculated_OVNotFinal] as [Calculated_OVNotFinal], 
	[Top].[Calculated_OVFinalOrBack] as [Calculated_OVFinalOrBack], 
	(CASE
    WHEN Calculated_OVFinalOrBack = 1 THEN 6
    WHEN Calculated_OVNotFinal = 1 THEN 5
    WHEN Calculated_InProcess = 1 THEN 4
    WHEN Calculated_Farmout = 1 THEN 3
    WHEN Calculated_BackFromHT = 1 THEN 2
    WHEN Calculated_MtlIssued = 1 THEN 1
    ELSE 0
END) as [Calculated_Status] 

from  (select distinct 
	[JobHead6].[Company] as [JobHead6_Company], 
	[JobHead6].[JobNum] as [JobHead6_JobNum], 
	[JobHead6].[PartNum] as [JobHead6_PartNum], 
	[JobAsmbl6].[AssemblySeq] as [JobAsmbl6_AssemblySeq], 
	[JobAsmbl6].[PartNum] as [JobAsmbl6_PartNum], 
	[MtlIssued].[Calculated_MtlIssued] as [Calculated_MtlIssued], 
	[HeatTreat].[Calculated_BackFromHT] as [Calculated_BackFromHT], 
	[Farmout].[Calculated_Farmout] as [Calculated_Farmout], 
	(iif((NewIP=1 or NewIP2=1),1,0)) as [Calculated_InProcess], 
	[OVNotFinal].[Calculated_OVNotFinal] as [Calculated_OVNotFinal], 
	[OVFinalOrBack].[Calculated_OVFinalOrBack] as [Calculated_OVFinalOrBack], 
	(iif(MtlIssued.Calculated_MtlIssued = 1, iif(Labor.Calculated_LaborHrs>0,1,0),0)) as [Calculated_NewIP], 
	(iif(HeatTreat.Calculated_BackFromHT=1, iif(Labor.Calculated_LaborHrs=0,1,0),0)) as [Calculated_NewIP2], 
	(iif(DetailComplete2.Calculated_LaborTotal>=JobHead6.ProdQty,1,0)) as [Calculated_DetailComplete] 

from Erp.JobHead as [JobHead6]
left outer join Erp.JobAsmbl as [JobAsmbl6] on 
	  JobHead6.Company = JobAsmbl6.Company
	and  JobHead6.JobNum = JobAsmbl6.JobNum
left outer join  (select distinct 
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum], 
	[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq], 
	(1) as [Calculated_MtlIssued] 

from Erp.JobHead as [JobHead]
inner join Erp.JobAsmbl as [JobAsmbl] on 
	  JobHead.Company = JobAsmbl.Company
	and  JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.PartTran as [PartTran] on 
	  JobAsmbl.Company = PartTran.Company
	and  JobAsmbl.JobNum = PartTran.JobNum
	and  JobAsmbl.AssemblySeq = PartTran.AssemblySeq
	and ( PartTran.TranClass = 'I'  
and PartTran.TranQty > 0  ))  as [MtlIssued] on 
	  JobAsmbl6.JobNum = MtlIssued.JobAsmbl_JobNum
	and  JobAsmbl6.AssemblySeq = MtlIssued.JobAsmbl_AssemblySeq
left outer join  (select  
	[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum], 
	[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq], 
	(1) as [Calculated_BackFromHT] 

from Erp.JobHead as [JobHead1]
inner join Erp.JobAsmbl as [JobAsmbl1] on 
	  JobHead1.Company = JobAsmbl1.Company
	and  JobHead1.JobNum = JobAsmbl1.JobNum
inner join Erp.JobOper as [JobOper1] on 
	  JobAsmbl1.Company = JobOper1.Company
	and  JobAsmbl1.JobNum = JobOper1.JobNum
	and  JobAsmbl1.AssemblySeq = JobOper1.AssemblySeq
	and ( JobOper1.OpCode = 'HT-OVOP'  
and JobOper1.OpComplete = true  ))  as [HeatTreat] on 
	  JobAsmbl6.JobNum = HeatTreat.JobAsmbl1_JobNum
	and  JobAsmbl6.AssemblySeq = HeatTreat.JobAsmbl1_AssemblySeq
left outer join  (select  
	[JobHead2].[JobNum] as [JobHead2_JobNum], 
	[JobAsmbl2].[AssemblySeq] as [JobAsmbl2_AssemblySeq], 
	(1) as [Calculated_Farmout] 

from Erp.JobHead as [JobHead2]
inner join Erp.JobAsmbl as [JobAsmbl2] on 
	  JobHead2.Company = JobAsmbl2.Company
	and  JobHead2.JobNum = JobAsmbl2.JobNum
inner join Erp.JobOper as [JobOper2] on 
	  JobAsmbl2.Company = JobOper2.Company
	and  JobAsmbl2.JobNum = JobOper2.JobNum
	and  JobAsmbl2.AssemblySeq = JobOper2.AssemblySeq
	and ( JobOper2.OpCode IN ('OMC-OVOP', 'OMP-OVOP')  
and JobOper2.OpComplete = false  )
inner join Erp.PORel as [PORel] on 
	  JobOper2.Company = PORel.Company
	and  JobOper2.JobNum = PORel.JobNum
	and  JobOper2.AssemblySeq = PORel.AssemblySeq
	and  JobOper2.OprSeq = PORel.JobSeq
	and ( PORel.OpenRelease = true  ))  as [Farmout] on 
	  JobAsmbl6.JobNum = Farmout.JobHead2_JobNum
	and  JobAsmbl6.AssemblySeq = Farmout.JobAsmbl2_AssemblySeq
left outer join  (select  
	[JobHead3].[JobNum] as [JobHead3_JobNum], 
	[JobAsmbl3].[AssemblySeq] as [JobAsmbl3_AssemblySeq], 
	(1) as [Calculated_OVNotFinal], 
	[PORel1].[OpenRelease] as [PORel1_OpenRelease], 
	[PORel1].[PONum] as [PORel1_PONum], 
	[PORel1].[POLine] as [PORel1_POLine], 
	[PORel1].[PORelNum] as [PORel1_PORelNum], 
	[PORel1].[DueDate] as [PORel1_DueDate], 
	[PORel1].[XRelQty] as [PORel1_XRelQty], 
	[PORel1].[RelQty] as [PORel1_RelQty], 
	[PORel1].[JobNum] as [PORel1_JobNum], 
	[PORel1].[AssemblySeq] as [PORel1_AssemblySeq], 
	[PORel1].[JobSeq] as [PORel1_JobSeq] 

from Erp.JobHead as [JobHead3]
inner join Erp.JobAsmbl as [JobAsmbl3] on 
	  JobHead3.Company = JobAsmbl3.Company
	and  JobHead3.JobNum = JobAsmbl3.JobNum
inner join Erp.JobOper as [JobOper3] on 
	  JobAsmbl3.Company = JobOper3.Company
	and  JobAsmbl3.JobNum = JobOper3.JobNum
	and  JobAsmbl3.AssemblySeq = JobOper3.AssemblySeq
	and ( JobOper3.SubContract = true  
and JobOper3.OpComplete = false  
and not JobOper3.OpCode IN ('OMC-OVOP', 'OMP-OVOP')  )
left outer join  (select  
	[JobHead4].[JobNum] as [JobHead4_JobNum], 
	[JobAsmbl4].[AssemblySeq] as [JobAsmbl4_AssemblySeq], 
	(max(JobOper4.OprSeq)) as [Calculated_LastOVOp] 

from Erp.JobHead as [JobHead4]
inner join Erp.JobAsmbl as [JobAsmbl4] on 
	  JobHead4.Company = JobAsmbl4.Company
	and  JobHead4.JobNum = JobAsmbl4.JobNum
inner join Erp.JobOper as [JobOper4] on 
	  JobAsmbl4.Company = JobOper4.Company
	and  JobAsmbl4.JobNum = JobOper4.JobNum
	and  JobAsmbl4.AssemblySeq = JobOper4.AssemblySeq
	and ( JobOper4.SubContract = true  
and not JobOper4.OpCode IN ('OMC-OVOP', 'OMP-OVOP')  )
group by 
	[JobHead4].[JobNum], 
	[JobAsmbl4].[AssemblySeq])  as [LastOVOp1] on 
	  JobOper3.JobNum = LastOVOp1.JobHead4_JobNum
	and  JobOper3.AssemblySeq = LastOVOp1.JobAsmbl4_AssemblySeq
	and not JobOper3.OprSeq = LastOVOp1.Calculated_LastOVOp
inner join Erp.PORel as [PORel1] on 
	  JobOper3.Company = PORel1.Company
	and  JobOper3.JobNum = PORel1.JobNum
	and  JobOper3.AssemblySeq = PORel1.AssemblySeq
	and  JobOper3.OprSeq = PORel1.JobSeq
	and ( PORel1.OpenRelease = true  ))  as [OVNotFinal] on 
	  JobAsmbl6.JobNum = OVNotFinal.JobHead3_JobNum
	and  JobAsmbl6.AssemblySeq = OVNotFinal.JobAsmbl3_AssemblySeq
left outer join  (select  
	[JobHead5].[JobNum] as [JobHead5_JobNum], 
	[JobAsmbl5].[AssemblySeq] as [JobAsmbl5_AssemblySeq], 
	(1) as [Calculated_OVFinalOrBack], 
	[PORel2].[OpenRelease] as [PORel2_OpenRelease], 
	[PORel2].[VoidRelease] as [PORel2_VoidRelease], 
	[PORel2].[PONum] as [PORel2_PONum], 
	[PORel2].[POLine] as [PORel2_POLine], 
	[PORel2].[PORelNum] as [PORel2_PORelNum], 
	[PORel2].[DueDate] as [PORel2_DueDate], 
	[PORel2].[RelQty] as [PORel2_RelQty], 
	[PORel2].[JobNum] as [PORel2_JobNum], 
	[PORel2].[AssemblySeq] as [PORel2_AssemblySeq], 
	[PORel2].[JobSeq] as [PORel2_JobSeq], 
	[PORel2].[Status] as [PORel2_Status] 

from Erp.JobHead as [JobHead5]
inner join Erp.JobAsmbl as [JobAsmbl5] on 
	  JobHead5.Company = JobAsmbl5.Company
	and  JobHead5.JobNum = JobAsmbl5.JobNum
inner join  (select  
	[JobHead4].[JobNum] as [JobHead4_JobNum], 
	[JobAsmbl4].[AssemblySeq] as [JobAsmbl4_AssemblySeq], 
	(max(JobOper4.OprSeq)) as [Calculated_LastOVOp] 

from Erp.JobHead as [JobHead4]
inner join Erp.JobAsmbl as [JobAsmbl4] on 
	  JobHead4.Company = JobAsmbl4.Company
	and  JobHead4.JobNum = JobAsmbl4.JobNum
inner join Erp.JobOper as [JobOper4] on 
	  JobAsmbl4.Company = JobOper4.Company
	and  JobAsmbl4.JobNum = JobOper4.JobNum
	and  JobAsmbl4.AssemblySeq = JobOper4.AssemblySeq
	and ( JobOper4.SubContract = true  
and not JobOper4.OpCode IN ('OMC-OVOP', 'OMP-OVOP')  )
group by 
	[JobHead4].[JobNum], 
	[JobAsmbl4].[AssemblySeq])  as [LastOVOp] on 
	  JobAsmbl5.JobNum = LastOVOp.JobHead4_JobNum
	and  JobAsmbl5.AssemblySeq = LastOVOp.JobAsmbl4_AssemblySeq
inner join Erp.JobOper as [JobOper5] on 
	  LastOVOp.JobHead4_JobNum = JobOper5.JobNum
	and  LastOVOp.JobAsmbl4_AssemblySeq = JobOper5.AssemblySeq
	and  LastOVOp.Calculated_LastOVOp = JobOper5.OprSeq
	and ( not JobOper5.OpCode IN ('OMC-OVOP', 'OMP-OVOP')  )
inner join Erp.PORel as [PORel2] on 
	  JobOper5.Company = PORel2.Company
	and  JobOper5.JobNum = PORel2.JobNum
	and  JobOper5.AssemblySeq = PORel2.AssemblySeq
	and  JobOper5.OprSeq = PORel2.JobSeq)  as [OVFinalOrBack] on 
	  JobAsmbl6.JobNum = OVFinalOrBack.JobHead5_JobNum
	and  JobAsmbl6.AssemblySeq = OVFinalOrBack.JobAsmbl5_AssemblySeq
left outer join  (select  
	[LaborDtl].[Company] as [LaborDtl_Company], 
	[LaborDtl].[JobNum] as [LaborDtl_JobNum], 
	[LaborDtl].[AssemblySeq] as [LaborDtl_AssemblySeq], 
	(sum(LaborDtl.LaborHrs)) as [Calculated_LaborHrs] 

from Erp.LaborDtl as [LaborDtl]
group by 
	[LaborDtl].[Company], 
	[LaborDtl].[JobNum], 
	[LaborDtl].[AssemblySeq])  as [Labor] on 
	  JobAsmbl6.Company = Labor.LaborDtl_Company
	and  JobAsmbl6.JobNum = Labor.LaborDtl_JobNum
	and  JobAsmbl6.AssemblySeq = Labor.LaborDtl_AssemblySeq
left outer join  (select  
	[DetailComplete1].[JobHead7_Company] as [JobHead7_Company], 
	[DetailComplete1].[JobHead7_JobNum] as [JobHead7_JobNum], 
	[DetailComplete1].[JobAsmbl7_AssemblySeq] as [JobAsmbl7_AssemblySeq], 
	(sum(LaborDtl1.LaborQty)) as [Calculated_LaborTotal] 

from  (select  
	[JobHead7].[Company] as [JobHead7_Company], 
	[JobHead7].[JobNum] as [JobHead7_JobNum], 
	[JobAsmbl7].[AssemblySeq] as [JobAsmbl7_AssemblySeq], 
	[JobHead7].[ProdQty] as [JobHead7_ProdQty], 
	(max(JobOper.OprSeq)) as [Calculated_LastOpNotInsp] 

from Erp.JobHead as [JobHead7]
inner join Erp.JobAsmbl as [JobAsmbl7] on 
	  JobHead7.Company = JobAsmbl7.Company
	and  JobHead7.JobNum = JobAsmbl7.JobNum
inner join Erp.JobOper as [JobOper] on 
	  JobAsmbl7.Company = JobOper.Company
	and  JobAsmbl7.JobNum = JobOper.JobNum
	and  JobAsmbl7.AssemblySeq = JobOper.AssemblySeq
	and ( JobOper.OpCode <> '9-OP'  )
group by 
	[JobHead7].[Company], 
	[JobHead7].[JobNum], 
	[JobAsmbl7].[AssemblySeq], 
	[JobHead7].[ProdQty])  as [DetailComplete1]
inner join Erp.LaborDtl as [LaborDtl1] on 
	  DetailComplete1.JobHead7_Company = LaborDtl1.Company
	and  DetailComplete1.JobHead7_JobNum = LaborDtl1.JobNum
	and  DetailComplete1.JobAsmbl7_AssemblySeq = LaborDtl1.AssemblySeq
	and  DetailComplete1.Calculated_LastOpNotInsp = LaborDtl1.OprSeq
group by 
	[DetailComplete1].[JobHead7_Company], 
	[DetailComplete1].[JobHead7_JobNum], 
	[DetailComplete1].[JobAsmbl7_AssemblySeq])  as [DetailComplete2] on 
	  JobAsmbl6.Company = DetailComplete2.JobHead7_Company
	and  JobAsmbl6.JobNum = DetailComplete2.JobHead7_JobNum
	and  JobAsmbl6.AssemblySeq = DetailComplete2.JobAsmbl7_AssemblySeq)  as [Top]
where (Top.Calculated_DetailComplete = false  
and Top.JobAsmbl6_AssemblySeq <> 0))  as [AsmStatus] on 
	  TopStatus.JobHead6_JobNum = AsmStatus.JobHead6_JobNum)  as [FinalTop]

WIPvsSalesValue:

/*  
 * Disclaimer!!! 
 * This is not a real query being executed, but a simplified version for general vision. 
 * Executing it with any other tool may produce a different result. 
 */

-- Retrieve data from 'JobStatus' query into JobStatus 
with [JobCost] as 
(select  
	[JobAsmbl].[Company] as [JobAsmbl_Company], 
	[JobAsmbl].[Plant] as [JobAsmbl_Plant], 
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum], 
	[JobHead].[JobClosed] as [JobHead_JobClosed], 
	(ROUND(SUM(JobAsmbl.TLAMaterialCost),2)) as [Calculated_JobMaterial], 
	(ROUND(SUM(JobAsmbl.TLALaborCost),2)) as [Calculated_JobLabor], 
	(ROUND(SUM(JobAsmbl.TLABurdenCost),2)) as [Calculated_JobBurden], 
	(ROUND(SUM(JobAsmbl.TLAMtlBurCost),2)) as [Calculated_JobMtlBur], 
	(ROUND(SUM(JobAsmbl.TLASubcontractCost),2)) as [Calculated_JobSubcontract], 
	(MIN(JobAsmbl.StartDate)) as [Calculated_JobScheduledOn], 
	(JobHead.CreateDate) as [Calculated_JobCreatedOn] 

from Erp.JobAsmbl as [JobAsmbl]
inner join Erp.JobHead as [JobHead] on 
	  JobAsmbl.Company = JobHead.Company
	and  JobAsmbl.JobNum = JobHead.JobNum
	and ( JobHead.WIPCleared = 0  
and JobHead.JobFirm = 1  )
group by 
	[JobAsmbl].[Company], 
	[JobAsmbl].[Plant], 
	[JobAsmbl].[JobNum], 
	[JobHead].[JobClosed], 
	(JobHead.CreateDate))
 ,[InventoryCost] as 
(select  
	[PartTran].[Company] as [PartTran_Company], 
	[PartTran].[Plant] as [PartTran_Plant], 
	[PartTran].[JobNum] as [PartTran_JobNum], 
	(ROUND(SUM(PartTran.TranQty*PartTran.MtlUnitCost),2)) as [Calculated_InvMtl], 
	(ROUND(SUM(PartTran.TranQty*PartTran.LbrUnitCost),2)) as [Calculated_InvLbr], 
	(ROUND(SUM(PartTran.TranQty*PartTran.BurUnitCost),2)) as [Calculated_InvBur], 
	(ROUND(SUM(PartTran.TranQty*PartTran.MtlBurUnitCost),2)) as [Calculated_InvMtlBur], 
	(ROUND(SUM(PartTran.TranQty*PartTran.SubUnitCost),2)) as [Calculated_InvSub] 

from Erp.PartTran as [PartTran]
where (PartTran.JobNum <> ''  
and PartTran.TranType = 'MFG-STK'  
and PartTran.TranDate <= Constants.Today)
group by 
	[PartTran].[Company], 
	[PartTran].[Plant], 
	[PartTran].[JobNum])
 ,[COGS] as 
(select  
	[PartTran1].[Company] as [PartTran1_Company], 
	[PartTran1].[Plant] as [PartTran1_Plant], 
	[PartTran1].[JobNum] as [PartTran1_JobNum], 
	(ROUND(SUM(PartTran1.TranQty * PartTran1.MtlUnitCost),2)) as [Calculated_COGSMtl], 
	(ROUND(SUM(PartTran1.TranQty * PartTran1.LbrUnitCost),2)) as [Calculated_COGSLbr], 
	(ROUND(SUM(PartTran1.TranQty * PartTran1.BurUnitCost),2)) as [Calculated_COGSBur], 
	(ROUND(SUM(PartTran1.TranQty * PartTran1.MtlBurUnitCost),2)) as [Calculated_COGSMtlBur], 
	(ROUND(SUM(PartTran1.TranQty * PartTran1.SubUnitCost),2)) as [Calculated_COGSSub] 

from Erp.PartTran as [PartTran1]
where (PartTran1.TranType = 'MFG-CUS'  
and PartTran1.TranDate <= '10/13/2025')
group by 
	[PartTran1].[Company], 
	[PartTran1].[Plant], 
	[PartTran1].[JobNum])
 ,[MFGVAR] as 
(select  
	[PartTran2].[Company] as [PartTran2_Company], 
	[PartTran2].[Plant] as [PartTran2_Plant], 
	[PartTran2].[JobNum] as [PartTran2_JobNum], 
	(ROUND(SUM(PartTran2.TranQty * PartTran2.MtlUnitCost),2)) as [Calculated_VARMtl], 
	(ROUND(SUM(PartTran2.TranQty * PartTran2.LbrUnitCost),2)) as [Calculated_VARLbr], 
	(ROUND(SUM(PartTran2.TranQty * PartTran2.BurUnitCost),2)) as [Calculated_VARBur], 
	(ROUND(SUM(PartTran2.TranQty * PartTran2.MtlBurUnitCost),2)) as [Calculated_VARMtlBur], 
	(ROUND(SUM(PartTran2.TranQty * PartTran2.SubUnitCost),2)) as [Calculated_VARSub] 

from Erp.PartTran as [PartTran2]
where (PartTran2.TranType = 'MFG-VAR'  
and PartTran2.TranDate <= '10/13/2025')
group by 
	[PartTran2].[Company], 
	[PartTran2].[Plant], 
	[PartTran2].[JobNum])
 ,[DMR] as 
(select  
	[DMRHead].[Company] as [DMRHead_Company], 
	[DMRHead].[Plant] as [DMRHead_Plant], 
	[DMRHead].[JobNum] as [DMRHead_JobNum], 
	(ROUND(SUM((DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty) * DMRHead.AvgMtlUnitCost),2)) as [Calculated_DMRMtl], 
	(ROUND(SUM((DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty) * DMRHead.AvgLbrUnitCost),2)) as [Calculated_DMRLbr], 
	(ROUND(SUM((DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty) * DMRHead.AvgBurUnitCost),2)) as [Calculated_DMRBur], 
	(ROUND(SUM((DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty) * DMRHead.AvgMtlBurUnitCost),2)) as [Calculated_DMRMtlBur], 
	(ROUND(SUM((DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty) * DMRHead.AvgSubUnitCost),2)) as [Calculated_DMRSub] 

from Erp.DMRHead as [DMRHead]
where (DMRHead.PONum = 0  
and DMRHead.MtlSeq = 0)
group by 
	[DMRHead].[Company], 
	[DMRHead].[Plant], 
	[DMRHead].[JobNum])
 ,[COMBPREP] as 
(select  
	[JobCost].[JobAsmbl_Company] as [JobAsmbl_Company], 
	[JobCost].[JobAsmbl_Plant] as [JobAsmbl_Plant], 
	[JobCost].[JobAsmbl_JobNum] as [JobAsmbl_JobNum], 
	[JobCost].[Calculated_JobCreatedOn] as [Calculated_JobCreatedOn], 
	[JobCost].[Calculated_JobScheduledOn] as [Calculated_JobScheduledOn], 
	[JobCost].[JobHead_JobClosed] as [JobHead_JobClosed], 
	[JobCost].[Calculated_JobMaterial] as [Calculated_JobMaterial], 
	[JobCost].[Calculated_JobLabor] as [Calculated_JobLabor], 
	[JobCost].[Calculated_JobBurden] as [Calculated_JobBurden], 
	[JobCost].[Calculated_JobMtlBur] as [Calculated_JobMtlBur], 
	[JobCost].[Calculated_JobSubcontract] as [Calculated_JobSubcontract], 
	(ISNULL(InventoryCost.Calculated_InvMtl, 0)) as [Calculated_InvMtl], 
	(ISNULL(InventoryCost.Calculated_InvLbr, 0)) as [Calculated_InvLbr], 
	(ISNULL(InventoryCost.Calculated_InvBur, 0)) as [Calculated_InvBur], 
	(ISNULL(InventoryCost.Calculated_InvMtlBur, 0)) as [Calculated_InvMtlBur], 
	(ISNULL(InventoryCost.Calculated_InvSub, 0)) as [Calculated_InvSub], 
	(ISNULL(COGS.Calculated_COGSMtl, 0)) as [Calculated_COGSMtl], 
	(ISNULL(COGS.Calculated_COGSLbr, 0)) as [Calculated_COGSLbr], 
	(ISNULL(COGS.Calculated_COGSBur, 0)) as [Calculated_COGSBur], 
	(ISNULL(COGS.Calculated_COGSMtlBur, 0)) as [Calculated_COGSMtlBur], 
	(ISNULL(COGS.Calculated_COGSSub, 0)) as [Calculated_COGSSub], 
	(ISNULL(MFGVAR.Calculated_VARMtl, 0)) as [Calculated_VARMtl], 
	(ISNULL(MFGVAR.Calculated_VARLbr, 0)) as [Calculated_VARLbr], 
	(ISNULL(MFGVAR.Calculated_VARBur, 0)) as [Calculated_VARBur], 
	(ISNULL(MFGVAR.Calculated_VARMtlBur, 0)) as [Calculated_VARMtlBur], 
	(ISNULL(MFGVAR.Calculated_VARSub, 0)) as [Calculated_VARSub], 
	(ISNULL(DMR.Calculated_DMRMtl, 0)) as [Calculated_DMRMtl], 
	(ISNULL(DMR.Calculated_DMRLbr, 0)) as [Calculated_DMRLbr], 
	(ISNULL(DMR.Calculated_DMRBur, 0)) as [Calculated_DMRBur], 
	(ISNULL(DMR.Calculated_DMRMtlBur, 0)) as [Calculated_DMRMtlBur], 
	(ISNULL(DMR.Calculated_DMRSub, 0)) as [Calculated_DMRSub] 

from  JobCost  as [JobCost]
left outer join  InventoryCost  as [InventoryCost] on 
	  JobCost.JobAsmbl_Company = InventoryCost.PartTran_Company
	and  JobCost.JobAsmbl_JobNum = InventoryCost.PartTran_JobNum
left outer join  COGS  as [COGS] on 
	  JobCost.JobAsmbl_Company = COGS.PartTran1_Company
	and  JobCost.JobAsmbl_JobNum = COGS.PartTran1_JobNum
left outer join  MFGVAR  as [MFGVAR] on 
	  JobCost.JobAsmbl_Company = MFGVAR.PartTran2_Company
	and  JobCost.JobAsmbl_JobNum = MFGVAR.PartTran2_JobNum
left outer join  DMR  as [DMR] on 
	  JobCost.JobAsmbl_Company = DMR.DMRHead_Company
	and  JobCost.JobAsmbl_JobNum = DMR.DMRHead_JobNum)
 ,[COMB] as 
(select  
	[COMBPREP].[JobAsmbl_Company] as [JobAsmbl_Company], 
	[COMBPREP].[JobAsmbl_Plant] as [JobAsmbl_Plant], 
	[COMBPREP].[JobAsmbl_JobNum] as [JobAsmbl_JobNum], 
	[COMBPREP].[Calculated_JobCreatedOn] as [Calculated_JobCreatedOn], 
	[COMBPREP].[Calculated_JobScheduledOn] as [Calculated_JobScheduledOn], 
	[COMBPREP].[Calculated_JobMaterial] as [Calculated_JobMaterial], 
	[COMBPREP].[Calculated_JobLabor] as [Calculated_JobLabor], 
	[COMBPREP].[Calculated_JobBurden] as [Calculated_JobBurden], 
	[COMBPREP].[Calculated_JobMtlBur] as [Calculated_JobMtlBur], 
	[COMBPREP].[Calculated_JobSubcontract] as [Calculated_JobSubcontract], 
	[COMBPREP].[Calculated_InvMtl] as [Calculated_InvMtl], 
	[COMBPREP].[Calculated_InvLbr] as [Calculated_InvLbr], 
	[COMBPREP].[Calculated_InvBur] as [Calculated_InvBur], 
	[COMBPREP].[Calculated_InvMtlBur] as [Calculated_InvMtlBur], 
	[COMBPREP].[Calculated_InvSub] as [Calculated_InvSub], 
	[COMBPREP].[Calculated_COGSMtl] as [Calculated_COGSMtl], 
	[COMBPREP].[Calculated_COGSLbr] as [Calculated_COGSLbr], 
	[COMBPREP].[Calculated_COGSBur] as [Calculated_COGSBur], 
	[COMBPREP].[Calculated_COGSMtlBur] as [Calculated_COGSMtlBur], 
	[COMBPREP].[Calculated_COGSSub] as [Calculated_COGSSub], 
	(ROUND(COMBPREP.Calculated_VARMtl + CASE
                                  WHEN COMBPREP.JobHead_JobClosed = 1 THEN
                                      COMBPREP.Calculated_JobMaterial - COMBPREP.Calculated_InvMtl - COMBPREP.Calculated_COGSMtl - COMBPREP.Calculated_VARMtl - COMBPREP.Calculated_DMRMtl
                                  ELSE
                                      0
                              END,2)) as [Calculated_VARMtl], 
	(ROUND(COMBPREP.Calculated_VARLbr + CASE
                                  WHEN COMBPREP.JobHead_JobClosed = 1 THEN
                                      COMBPREP.Calculated_JobLabor - COMBPREP.Calculated_InvLbr - COMBPREP.Calculated_COGSLbr - COMBPREP.Calculated_VARLbr - COMBPREP.Calculated_DMRLbr
                                  ELSE
                                      0
                              END,2)) as [Calculated_VARLbr], 
	(ROUND(COMBPREP.Calculated_VARBur + CASE
                                  WHEN COMBPREP.JobHead_JobClosed = 1 THEN
                                      COMBPREP.Calculated_JobBurden - COMBPREP.Calculated_InvBur - COMBPREP.Calculated_COGSBur - COMBPREP.Calculated_VARBur - COMBPREP.Calculated_DMRBur
                                  ELSE
                                      0
                              END,2)) as [Calculated_VARBur], 
	(ROUND(COMBPREP.Calculated_VARMtlBur + CASE
                                  WHEN COMBPREP.JobHead_JobClosed = 1 THEN
                                      COMBPREP.Calculated_JobMtlBur - COMBPREP.Calculated_InvMtlBur - COMBPREP.Calculated_COGSMtlBur - COMBPREP.Calculated_VARMtlBur - COMBPREP.Calculated_DMRMtlBur
                                  ELSE
                                      0
                              END,2)) as [Calculated_VARMtlBur], 
	(ROUND(COMBPREP.Calculated_VARSub + CASE
                                  WHEN COMBPREP.JobHead_JobClosed = 1 THEN
                                      COMBPREP.Calculated_JobSubcontract - COMBPREP.Calculated_InvSub - COMBPREP.Calculated_COGSSub - COMBPREP.Calculated_VARSub - COMBPREP.Calculated_DMRSub
                                  ELSE
                                      0
                              END,2)) as [Calculated_VARSub], 
	[COMBPREP].[Calculated_DMRMtl] as [Calculated_DMRMtl], 
	[COMBPREP].[Calculated_DMRLbr] as [Calculated_DMRLbr], 
	[COMBPREP].[Calculated_DMRBur] as [Calculated_DMRBur], 
	[COMBPREP].[Calculated_DMRMtlBur] as [Calculated_DMRMtlBur], 
	[COMBPREP].[Calculated_DMRSub] as [Calculated_DMRSub] 

from  COMBPREP  as [COMBPREP])
 ,[FINAL] as 
(select  
	[COMB].[JobAsmbl_Company] as [JobAsmbl_Company], 
	[COMB].[JobAsmbl_Plant] as [JobAsmbl_Plant], 
	[COMB].[JobAsmbl_JobNum] as [JobAsmbl_JobNum], 
	[JobHead1].[JobClosed] as [JobHead1_JobClosed], 
	[JobHead1].[ClosedDate] as [JobHead1_ClosedDate], 
	[JobHead1].[WIPCleared] as [JobHead1_WIPCleared], 
	[COMB].[Calculated_JobCreatedOn] as [Calculated_JobCreatedOn], 
	[COMB].[Calculated_JobScheduledOn] as [Calculated_JobScheduledOn], 
	(ISNULL(PartPlant.PartNum,'')) as [Calculated_PartNum], 
	(ISNULL(part.ProdCode,'')) as [Calculated_ProdCode], 
	(PartPlant.PersonID) as [Calculated_Planner], 
	(ROUND(COMB.Calculated_JobLabor - COMB.Calculated_InvLbr - COMB.Calculated_COGSLbr - COMB.Calculated_VARLbr - COMB.Calculated_DMRLbr, 2)) as [Calculated_WIPLbr], 
	(ROUND(COMB.Calculated_JobBurden - COMB.Calculated_InvBur - COMB.Calculated_COGSBur - COMB.Calculated_VARBur - COMB.Calculated_DMRBur, 2)) as [Calculated_WIPBur], 
	(ROUND(COMB.Calculated_JobMaterial - COMB.Calculated_InvMtl - COMB.Calculated_COGSMtl - COMB.Calculated_VARMtl - COMB.Calculated_DMRMtl, 2)) as [Calculated_WIPMtl], 
	(ROUND(COMB.Calculated_JobSubcontract - COMB.Calculated_InvSub - COMB.Calculated_COGSSub - COMB.Calculated_VARSub - COMB.Calculated_DMRSub, 2)) as [Calculated_WIPSubcontract], 
	(ROUND(COMB.Calculated_JobMtlBur - COMB.Calculated_InvMtlBur - COMB.Calculated_COGSMtlBur - COMB.Calculated_VARMtlBur - COMB.Calculated_DMRMtlBur, 2)) as [Calculated_WIPMtlBur], 
	(CASE WHEN Constants.CompanyID = 'F001' AND COMB.JobAsmbl_Company <> 'F101' THEN 1 ELSE CASE WHEN COMB.JobAsmbl_Company = Constants.CompanyID THEN 1 ELSE 0 END END) as [Calculated_show] 

from  COMB  as [COMB]
inner join Erp.JobHead as [JobHead1] on 
	  COMB.JobAsmbl_Company = JobHead1.Company
	and  COMB.JobAsmbl_Plant = JobHead1.Plant
	and  COMB.JobAsmbl_JobNum = JobHead1.JobNum
left outer join Erp.PartPlant as [PartPlant] on 
	  JobHead1.Company = PartPlant.Company
	and  JobHead1.PartNum = PartPlant.PartNum
	and  JobHead1.Plant = PartPlant.Plant
left outer join Erp.Part as [Part] on 
	  PartPlant.Company = Part.Company
	and  PartPlant.PartNum = Part.PartNum)

select  
	[ElFin].[JobAsmbl_JobNum] as [JobAsmbl_JobNum], 
	[ElFin].[Calculated_PartNum] as [Calculated_PartNum], 
	[ElFin].[Calculated_WIPTotal] as [Calculated_WIPTotal], 
	(case 
        when JobStatus.Calculated_Status = 1 then '1. Material Issued'
        when JobStatus.Calculated_Status = 2 then '2. Back From HT'
        when JobStatus.Calculated_Status = 3 then '3. Farmout'
        when JobStatus.Calculated_Status = 4 then '4. In Process'
        when JobStatus.Calculated_Status = 5 then '5. OV Not Final'
        when JobStatus.Calculated_Status = 6 then '6. OV Final or Back'
        else '0. Unknown'
        end) as [Calculated_ActualStatus], 
	(sum(OpenValue1.Calculated_OpenValue)) as [Calculated_OpenValTot], 
	[JobStatus].[Calculated_Status] as [JobStatus_Calculated_Status] 

from  (select  
	[FINAL].[JobAsmbl_Company] as [JobAsmbl_Company], 
	[FINAL].[JobAsmbl_Plant] as [JobAsmbl_Plant], 
	[FINAL].[JobAsmbl_JobNum] as [JobAsmbl_JobNum], 
	[FINAL].[JobHead1_JobClosed] as [JobHead1_JobClosed], 
	[FINAL].[JobHead1_ClosedDate] as [JobHead1_ClosedDate], 
	[FINAL].[Calculated_JobCreatedOn] as [Calculated_JobCreatedOn], 
	[FINAL].[Calculated_JobScheduledOn] as [Calculated_JobScheduledOn], 
	[FINAL].[Calculated_PartNum] as [Calculated_PartNum], 
	[FINAL].[Calculated_ProdCode] as [Calculated_ProdCode], 
	[FINAL].[Calculated_Planner] as [Calculated_Planner], 
	[FINAL].[Calculated_WIPLbr] as [Calculated_WIPLbr], 
	[FINAL].[Calculated_WIPBur] as [Calculated_WIPBur], 
	[FINAL].[Calculated_WIPMtl] as [Calculated_WIPMtl], 
	[FINAL].[Calculated_WIPSubcontract] as [Calculated_WIPSubcontract], 
	[FINAL].[Calculated_WIPMtlBur] as [Calculated_WIPMtlBur], 
	(FINAL.Calculated_WIPLbr + FINAL.Calculated_WIPBur + FINAL.Calculated_WIPMtl + FINAL.Calculated_WIPSubcontract + FINAL.Calculated_WIPMtlBur) as [Calculated_WIPTotal] 

from  FINAL  as [FINAL]
where (FINAL.JobHead1_WIPCleared = 0  
and FINAL.Calculated_show = 1) and ( FINAL.Calculated_WIPLbr + FINAL.Calculated_WIPBur + FINAL.Calculated_WIPMtl + FINAL.Calculated_WIPSubcontract + FINAL.Calculated_WIPMtlBur <> 0  ))  as [ElFin]
left outer join  (select  
	[JobProd].[Company] as [JobProd_Company], 
	[JobProd].[JobNum] as [JobProd_JobNum], 
	(sum(OrderRel.OurReqQty-OrderRel.OurJobShippedQty-OrderRel.OurStockShippedQty)) as [Calculated_RemainQty], 
	((RemainQty * OrderDtl.UnitPrice)) as [Calculated_OpenValue], 
	[OrderDtl].[UnitPrice] as [OrderDtl_UnitPrice] 

from Erp.JobProd as [JobProd]
inner join Erp.OrderRel as [OrderRel] on 
	  JobProd.Company = OrderRel.Company
	and  JobProd.OrderNum = OrderRel.OrderNum
	and  JobProd.OrderLine = OrderRel.OrderLine
	and  JobProd.OrderRelNum = OrderRel.OrderRelNum
inner join Erp.OrderDtl as [OrderDtl] on 
	  OrderRel.Company = OrderDtl.Company
	and  OrderRel.OrderNum = OrderDtl.OrderNum
	and  OrderRel.OrderLine = OrderDtl.OrderLine
inner join Erp.OrderHed as [OrderHed] on 
	  OrderDtl.Company = OrderHed.Company
	and  OrderDtl.OrderNum = OrderHed.OrderNum
group by 
	[JobProd].[Company], 
	[JobProd].[JobNum], 
	[OrderDtl].[UnitPrice])  as [OpenValue1] on 
	  ElFin.JobAsmbl_Company = OpenValue1.JobProd_Company
	and  ElFin.JobAsmbl_JobNum = OpenValue1.JobProd_JobNum
left outer join JobStatus as [JobStatus] on 
	  ElFin.JobAsmbl_Company = JobStatus.JobHead6_Company
	and  ElFin.JobAsmbl_JobNum = JobStatus.Calculated_JobNum
group by 
	[ElFin].[JobAsmbl_JobNum], 
	[ElFin].[Calculated_PartNum], 
	[ElFin].[Calculated_WIPTotal], 
	(case 
        when JobStatus.Calculated_Status = 1 then '1. Material Issued'
        when JobStatus.Calculated_Status = 2 then '2. Back From HT'
        when JobStatus.Calculated_Status = 3 then '3. Farmout'
        when JobStatus.Calculated_Status = 4 then '4. In Process'
        when JobStatus.Calculated_Status = 5 then '5. OV Not Final'
        when JobStatus.Calculated_Status = 6 then '6. OV Final or Back'
        else '0. Unknown'
        end), 
	[JobStatus].[Calculated_Status]

Just noticed and PartTran1.TranDate <= ‘10/13/2025’)
GAH~ This is one of the things I have tried over and over to change and save. It should be set to current date +0 days. I hardcoded it at one point and cant get it to save when I change it. Going back to try again.

Update This is the SQL without the hardcoded dates:
WIPvsSalesValue:

/*  
 * Disclaimer!!! 
 * This is not a real query being executed, but a simplified version for general vision. 
 * Executing it with any other tool may produce a different result. 
 */

-- Retrieve data from 'JobStatus' query into JobStatus 
with [JobCost] as 
(select  
	[JobAsmbl].[Company] as [JobAsmbl_Company], 
	[JobAsmbl].[Plant] as [JobAsmbl_Plant], 
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum], 
	[JobHead].[JobClosed] as [JobHead_JobClosed], 
	(ROUND(SUM(JobAsmbl.TLAMaterialCost),2)) as [Calculated_JobMaterial], 
	(ROUND(SUM(JobAsmbl.TLALaborCost),2)) as [Calculated_JobLabor], 
	(ROUND(SUM(JobAsmbl.TLABurdenCost),2)) as [Calculated_JobBurden], 
	(ROUND(SUM(JobAsmbl.TLAMtlBurCost),2)) as [Calculated_JobMtlBur], 
	(ROUND(SUM(JobAsmbl.TLASubcontractCost),2)) as [Calculated_JobSubcontract], 
	(MIN(JobAsmbl.StartDate)) as [Calculated_JobScheduledOn], 
	(JobHead.CreateDate) as [Calculated_JobCreatedOn] 

from Erp.JobAsmbl as [JobAsmbl]
inner join Erp.JobHead as [JobHead] on 
	  JobAsmbl.Company = JobHead.Company
	and  JobAsmbl.JobNum = JobHead.JobNum
	and ( JobHead.WIPCleared = 0  
and JobHead.JobFirm = 1  )
group by 
	[JobAsmbl].[Company], 
	[JobAsmbl].[Plant], 
	[JobAsmbl].[JobNum], 
	[JobHead].[JobClosed], 
	(JobHead.CreateDate))
 ,[InventoryCost] as 
(select  
	[PartTran].[Company] as [PartTran_Company], 
	[PartTran].[Plant] as [PartTran_Plant], 
	[PartTran].[JobNum] as [PartTran_JobNum], 
	(ROUND(SUM(PartTran.TranQty*PartTran.MtlUnitCost),2)) as [Calculated_InvMtl], 
	(ROUND(SUM(PartTran.TranQty*PartTran.LbrUnitCost),2)) as [Calculated_InvLbr], 
	(ROUND(SUM(PartTran.TranQty*PartTran.BurUnitCost),2)) as [Calculated_InvBur], 
	(ROUND(SUM(PartTran.TranQty*PartTran.MtlBurUnitCost),2)) as [Calculated_InvMtlBur], 
	(ROUND(SUM(PartTran.TranQty*PartTran.SubUnitCost),2)) as [Calculated_InvSub] 

from Erp.PartTran as [PartTran]
where (PartTran.JobNum <> ''  
and PartTran.TranType = 'MFG-STK'  
and PartTran.TranDate <= Constants.Today)
group by 
	[PartTran].[Company], 
	[PartTran].[Plant], 
	[PartTran].[JobNum])
 ,[COGS] as 
(select  
	[PartTran1].[Company] as [PartTran1_Company], 
	[PartTran1].[Plant] as [PartTran1_Plant], 
	[PartTran1].[JobNum] as [PartTran1_JobNum], 
	(ROUND(SUM(PartTran1.TranQty * PartTran1.MtlUnitCost),2)) as [Calculated_COGSMtl], 
	(ROUND(SUM(PartTran1.TranQty * PartTran1.LbrUnitCost),2)) as [Calculated_COGSLbr], 
	(ROUND(SUM(PartTran1.TranQty * PartTran1.BurUnitCost),2)) as [Calculated_COGSBur], 
	(ROUND(SUM(PartTran1.TranQty * PartTran1.MtlBurUnitCost),2)) as [Calculated_COGSMtlBur], 
	(ROUND(SUM(PartTran1.TranQty * PartTran1.SubUnitCost),2)) as [Calculated_COGSSub] 

from Erp.PartTran as [PartTran1]
where (PartTran1.TranType = 'MFG-CUS'  
and PartTran1.TranDate <= Constants.Today)
group by 
	[PartTran1].[Company], 
	[PartTran1].[Plant], 
	[PartTran1].[JobNum])
 ,[MFGVAR] as 
(select  
	[PartTran2].[Company] as [PartTran2_Company], 
	[PartTran2].[Plant] as [PartTran2_Plant], 
	[PartTran2].[JobNum] as [PartTran2_JobNum], 
	(ROUND(SUM(PartTran2.TranQty * PartTran2.MtlUnitCost),2)) as [Calculated_VARMtl], 
	(ROUND(SUM(PartTran2.TranQty * PartTran2.LbrUnitCost),2)) as [Calculated_VARLbr], 
	(ROUND(SUM(PartTran2.TranQty * PartTran2.BurUnitCost),2)) as [Calculated_VARBur], 
	(ROUND(SUM(PartTran2.TranQty * PartTran2.MtlBurUnitCost),2)) as [Calculated_VARMtlBur], 
	(ROUND(SUM(PartTran2.TranQty * PartTran2.SubUnitCost),2)) as [Calculated_VARSub] 

from Erp.PartTran as [PartTran2]
where (PartTran2.TranType = 'MFG-VAR'  
and PartTran2.TranDate <= Constants.Today)
group by 
	[PartTran2].[Company], 
	[PartTran2].[Plant], 
	[PartTran2].[JobNum])
 ,[DMR] as 
(select  
	[DMRHead].[Company] as [DMRHead_Company], 
	[DMRHead].[Plant] as [DMRHead_Plant], 
	[DMRHead].[JobNum] as [DMRHead_JobNum], 
	(ROUND(SUM((DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty) * DMRHead.AvgMtlUnitCost),2)) as [Calculated_DMRMtl], 
	(ROUND(SUM((DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty) * DMRHead.AvgLbrUnitCost),2)) as [Calculated_DMRLbr], 
	(ROUND(SUM((DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty) * DMRHead.AvgBurUnitCost),2)) as [Calculated_DMRBur], 
	(ROUND(SUM((DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty) * DMRHead.AvgMtlBurUnitCost),2)) as [Calculated_DMRMtlBur], 
	(ROUND(SUM((DMRHead.TotDiscrepantQty - DMRHead.TotAcceptedQty) * DMRHead.AvgSubUnitCost),2)) as [Calculated_DMRSub] 

from Erp.DMRHead as [DMRHead]
where (DMRHead.PONum = 0  
and DMRHead.MtlSeq = 0)
group by 
	[DMRHead].[Company], 
	[DMRHead].[Plant], 
	[DMRHead].[JobNum])
 ,[COMBPREP] as 
(select  
	[JobCost].[JobAsmbl_Company] as [JobAsmbl_Company], 
	[JobCost].[JobAsmbl_Plant] as [JobAsmbl_Plant], 
	[JobCost].[JobAsmbl_JobNum] as [JobAsmbl_JobNum], 
	[JobCost].[Calculated_JobCreatedOn] as [Calculated_JobCreatedOn], 
	[JobCost].[Calculated_JobScheduledOn] as [Calculated_JobScheduledOn], 
	[JobCost].[JobHead_JobClosed] as [JobHead_JobClosed], 
	[JobCost].[Calculated_JobMaterial] as [Calculated_JobMaterial], 
	[JobCost].[Calculated_JobLabor] as [Calculated_JobLabor], 
	[JobCost].[Calculated_JobBurden] as [Calculated_JobBurden], 
	[JobCost].[Calculated_JobMtlBur] as [Calculated_JobMtlBur], 
	[JobCost].[Calculated_JobSubcontract] as [Calculated_JobSubcontract], 
	(ISNULL(InventoryCost.Calculated_InvMtl, 0)) as [Calculated_InvMtl], 
	(ISNULL(InventoryCost.Calculated_InvLbr, 0)) as [Calculated_InvLbr], 
	(ISNULL(InventoryCost.Calculated_InvBur, 0)) as [Calculated_InvBur], 
	(ISNULL(InventoryCost.Calculated_InvMtlBur, 0)) as [Calculated_InvMtlBur], 
	(ISNULL(InventoryCost.Calculated_InvSub, 0)) as [Calculated_InvSub], 
	(ISNULL(COGS.Calculated_COGSMtl, 0)) as [Calculated_COGSMtl], 
	(ISNULL(COGS.Calculated_COGSLbr, 0)) as [Calculated_COGSLbr], 
	(ISNULL(COGS.Calculated_COGSBur, 0)) as [Calculated_COGSBur], 
	(ISNULL(COGS.Calculated_COGSMtlBur, 0)) as [Calculated_COGSMtlBur], 
	(ISNULL(COGS.Calculated_COGSSub, 0)) as [Calculated_COGSSub], 
	(ISNULL(MFGVAR.Calculated_VARMtl, 0)) as [Calculated_VARMtl], 
	(ISNULL(MFGVAR.Calculated_VARLbr, 0)) as [Calculated_VARLbr], 
	(ISNULL(MFGVAR.Calculated_VARBur, 0)) as [Calculated_VARBur], 
	(ISNULL(MFGVAR.Calculated_VARMtlBur, 0)) as [Calculated_VARMtlBur], 
	(ISNULL(MFGVAR.Calculated_VARSub, 0)) as [Calculated_VARSub], 
	(ISNULL(DMR.Calculated_DMRMtl, 0)) as [Calculated_DMRMtl], 
	(ISNULL(DMR.Calculated_DMRLbr, 0)) as [Calculated_DMRLbr], 
	(ISNULL(DMR.Calculated_DMRBur, 0)) as [Calculated_DMRBur], 
	(ISNULL(DMR.Calculated_DMRMtlBur, 0)) as [Calculated_DMRMtlBur], 
	(ISNULL(DMR.Calculated_DMRSub, 0)) as [Calculated_DMRSub] 

from  JobCost  as [JobCost]
left outer join  InventoryCost  as [InventoryCost] on 
	  JobCost.JobAsmbl_Company = InventoryCost.PartTran_Company
	and  JobCost.JobAsmbl_JobNum = InventoryCost.PartTran_JobNum
left outer join  COGS  as [COGS] on 
	  JobCost.JobAsmbl_Company = COGS.PartTran1_Company
	and  JobCost.JobAsmbl_JobNum = COGS.PartTran1_JobNum
left outer join  MFGVAR  as [MFGVAR] on 
	  JobCost.JobAsmbl_Company = MFGVAR.PartTran2_Company
	and  JobCost.JobAsmbl_JobNum = MFGVAR.PartTran2_JobNum
left outer join  DMR  as [DMR] on 
	  JobCost.JobAsmbl_Company = DMR.DMRHead_Company
	and  JobCost.JobAsmbl_JobNum = DMR.DMRHead_JobNum)
 ,[COMB] as 
(select  
	[COMBPREP].[JobAsmbl_Company] as [JobAsmbl_Company], 
	[COMBPREP].[JobAsmbl_Plant] as [JobAsmbl_Plant], 
	[COMBPREP].[JobAsmbl_JobNum] as [JobAsmbl_JobNum], 
	[COMBPREP].[Calculated_JobCreatedOn] as [Calculated_JobCreatedOn], 
	[COMBPREP].[Calculated_JobScheduledOn] as [Calculated_JobScheduledOn], 
	[COMBPREP].[Calculated_JobMaterial] as [Calculated_JobMaterial], 
	[COMBPREP].[Calculated_JobLabor] as [Calculated_JobLabor], 
	[COMBPREP].[Calculated_JobBurden] as [Calculated_JobBurden], 
	[COMBPREP].[Calculated_JobMtlBur] as [Calculated_JobMtlBur], 
	[COMBPREP].[Calculated_JobSubcontract] as [Calculated_JobSubcontract], 
	[COMBPREP].[Calculated_InvMtl] as [Calculated_InvMtl], 
	[COMBPREP].[Calculated_InvLbr] as [Calculated_InvLbr], 
	[COMBPREP].[Calculated_InvBur] as [Calculated_InvBur], 
	[COMBPREP].[Calculated_InvMtlBur] as [Calculated_InvMtlBur], 
	[COMBPREP].[Calculated_InvSub] as [Calculated_InvSub], 
	[COMBPREP].[Calculated_COGSMtl] as [Calculated_COGSMtl], 
	[COMBPREP].[Calculated_COGSLbr] as [Calculated_COGSLbr], 
	[COMBPREP].[Calculated_COGSBur] as [Calculated_COGSBur], 
	[COMBPREP].[Calculated_COGSMtlBur] as [Calculated_COGSMtlBur], 
	[COMBPREP].[Calculated_COGSSub] as [Calculated_COGSSub], 
	(ROUND(COMBPREP.Calculated_VARMtl + CASE
                                  WHEN COMBPREP.JobHead_JobClosed = 1 THEN
                                      COMBPREP.Calculated_JobMaterial - COMBPREP.Calculated_InvMtl - COMBPREP.Calculated_COGSMtl - COMBPREP.Calculated_VARMtl - COMBPREP.Calculated_DMRMtl
                                  ELSE
                                      0
                              END,2)) as [Calculated_VARMtl], 
	(ROUND(COMBPREP.Calculated_VARLbr + CASE
                                  WHEN COMBPREP.JobHead_JobClosed = 1 THEN
                                      COMBPREP.Calculated_JobLabor - COMBPREP.Calculated_InvLbr - COMBPREP.Calculated_COGSLbr - COMBPREP.Calculated_VARLbr - COMBPREP.Calculated_DMRLbr
                                  ELSE
                                      0
                              END,2)) as [Calculated_VARLbr], 
	(ROUND(COMBPREP.Calculated_VARBur + CASE
                                  WHEN COMBPREP.JobHead_JobClosed = 1 THEN
                                      COMBPREP.Calculated_JobBurden - COMBPREP.Calculated_InvBur - COMBPREP.Calculated_COGSBur - COMBPREP.Calculated_VARBur - COMBPREP.Calculated_DMRBur
                                  ELSE
                                      0
                              END,2)) as [Calculated_VARBur], 
	(ROUND(COMBPREP.Calculated_VARMtlBur + CASE
                                  WHEN COMBPREP.JobHead_JobClosed = 1 THEN
                                      COMBPREP.Calculated_JobMtlBur - COMBPREP.Calculated_InvMtlBur - COMBPREP.Calculated_COGSMtlBur - COMBPREP.Calculated_VARMtlBur - COMBPREP.Calculated_DMRMtlBur
                                  ELSE
                                      0
                              END,2)) as [Calculated_VARMtlBur], 
	(ROUND(COMBPREP.Calculated_VARSub + CASE
                                  WHEN COMBPREP.JobHead_JobClosed = 1 THEN
                                      COMBPREP.Calculated_JobSubcontract - COMBPREP.Calculated_InvSub - COMBPREP.Calculated_COGSSub - COMBPREP.Calculated_VARSub - COMBPREP.Calculated_DMRSub
                                  ELSE
                                      0
                              END,2)) as [Calculated_VARSub], 
	[COMBPREP].[Calculated_DMRMtl] as [Calculated_DMRMtl], 
	[COMBPREP].[Calculated_DMRLbr] as [Calculated_DMRLbr], 
	[COMBPREP].[Calculated_DMRBur] as [Calculated_DMRBur], 
	[COMBPREP].[Calculated_DMRMtlBur] as [Calculated_DMRMtlBur], 
	[COMBPREP].[Calculated_DMRSub] as [Calculated_DMRSub] 

from  COMBPREP  as [COMBPREP])
 ,[FINAL] as 
(select  
	[COMB].[JobAsmbl_Company] as [JobAsmbl_Company], 
	[COMB].[JobAsmbl_Plant] as [JobAsmbl_Plant], 
	[COMB].[JobAsmbl_JobNum] as [JobAsmbl_JobNum], 
	[JobHead1].[JobClosed] as [JobHead1_JobClosed], 
	[JobHead1].[ClosedDate] as [JobHead1_ClosedDate], 
	[JobHead1].[WIPCleared] as [JobHead1_WIPCleared], 
	[COMB].[Calculated_JobCreatedOn] as [Calculated_JobCreatedOn], 
	[COMB].[Calculated_JobScheduledOn] as [Calculated_JobScheduledOn], 
	(ISNULL(PartPlant.PartNum,'')) as [Calculated_PartNum], 
	(ISNULL(part.ProdCode,'')) as [Calculated_ProdCode], 
	(PartPlant.PersonID) as [Calculated_Planner], 
	(ROUND(COMB.Calculated_JobLabor - COMB.Calculated_InvLbr - COMB.Calculated_COGSLbr - COMB.Calculated_VARLbr - COMB.Calculated_DMRLbr, 2)) as [Calculated_WIPLbr], 
	(ROUND(COMB.Calculated_JobBurden - COMB.Calculated_InvBur - COMB.Calculated_COGSBur - COMB.Calculated_VARBur - COMB.Calculated_DMRBur, 2)) as [Calculated_WIPBur], 
	(ROUND(COMB.Calculated_JobMaterial - COMB.Calculated_InvMtl - COMB.Calculated_COGSMtl - COMB.Calculated_VARMtl - COMB.Calculated_DMRMtl, 2)) as [Calculated_WIPMtl], 
	(ROUND(COMB.Calculated_JobSubcontract - COMB.Calculated_InvSub - COMB.Calculated_COGSSub - COMB.Calculated_VARSub - COMB.Calculated_DMRSub, 2)) as [Calculated_WIPSubcontract], 
	(ROUND(COMB.Calculated_JobMtlBur - COMB.Calculated_InvMtlBur - COMB.Calculated_COGSMtlBur - COMB.Calculated_VARMtlBur - COMB.Calculated_DMRMtlBur, 2)) as [Calculated_WIPMtlBur], 
	(CASE WHEN Constants.CompanyID = 'F001' AND COMB.JobAsmbl_Company <> 'F101' THEN 1 ELSE CASE WHEN COMB.JobAsmbl_Company = Constants.CompanyID THEN 1 ELSE 0 END END) as [Calculated_show] 

from  COMB  as [COMB]
inner join Erp.JobHead as [JobHead1] on 
	  COMB.JobAsmbl_Company = JobHead1.Company
	and  COMB.JobAsmbl_Plant = JobHead1.Plant
	and  COMB.JobAsmbl_JobNum = JobHead1.JobNum
left outer join Erp.PartPlant as [PartPlant] on 
	  JobHead1.Company = PartPlant.Company
	and  JobHead1.PartNum = PartPlant.PartNum
	and  JobHead1.Plant = PartPlant.Plant
left outer join Erp.Part as [Part] on 
	  PartPlant.Company = Part.Company
	and  PartPlant.PartNum = Part.PartNum)

select  
	[ElFin].[JobAsmbl_JobNum] as [JobAsmbl_JobNum], 
	[ElFin].[Calculated_PartNum] as [Calculated_PartNum], 
	[ElFin].[Calculated_WIPTotal] as [Calculated_WIPTotal], 
	(case 
        when JobStatus.Calculated_Status = 1 then '1. Material Issued'
        when JobStatus.Calculated_Status = 2 then '2. Back From HT'
        when JobStatus.Calculated_Status = 3 then '3. Farmout'
        when JobStatus.Calculated_Status = 4 then '4. In Process'
        when JobStatus.Calculated_Status = 5 then '5. OV Not Final'
        when JobStatus.Calculated_Status = 6 then '6. OV Final or Back'
        else '0. Unknown'
        end) as [Calculated_ActualStatus], 
	(sum(OpenValue1.Calculated_OpenValue)) as [Calculated_OpenValTot], 
	[JobStatus].[Calculated_Status] as [JobStatus_Calculated_Status] 

from  (select  
	[FINAL].[JobAsmbl_Company] as [JobAsmbl_Company], 
	[FINAL].[JobAsmbl_Plant] as [JobAsmbl_Plant], 
	[FINAL].[JobAsmbl_JobNum] as [JobAsmbl_JobNum], 
	[FINAL].[JobHead1_JobClosed] as [JobHead1_JobClosed], 
	[FINAL].[JobHead1_ClosedDate] as [JobHead1_ClosedDate], 
	[FINAL].[Calculated_JobCreatedOn] as [Calculated_JobCreatedOn], 
	[FINAL].[Calculated_JobScheduledOn] as [Calculated_JobScheduledOn], 
	[FINAL].[Calculated_PartNum] as [Calculated_PartNum], 
	[FINAL].[Calculated_ProdCode] as [Calculated_ProdCode], 
	[FINAL].[Calculated_Planner] as [Calculated_Planner], 
	[FINAL].[Calculated_WIPLbr] as [Calculated_WIPLbr], 
	[FINAL].[Calculated_WIPBur] as [Calculated_WIPBur], 
	[FINAL].[Calculated_WIPMtl] as [Calculated_WIPMtl], 
	[FINAL].[Calculated_WIPSubcontract] as [Calculated_WIPSubcontract], 
	[FINAL].[Calculated_WIPMtlBur] as [Calculated_WIPMtlBur], 
	(FINAL.Calculated_WIPLbr + FINAL.Calculated_WIPBur + FINAL.Calculated_WIPMtl + FINAL.Calculated_WIPSubcontract + FINAL.Calculated_WIPMtlBur) as [Calculated_WIPTotal] 

from  FINAL  as [FINAL]
where (FINAL.JobHead1_WIPCleared = 0  
and FINAL.Calculated_show = 1) and ( FINAL.Calculated_WIPLbr + FINAL.Calculated_WIPBur + FINAL.Calculated_WIPMtl + FINAL.Calculated_WIPSubcontract + FINAL.Calculated_WIPMtlBur <> 0  ))  as [ElFin]
left outer join  (select  
	[JobProd].[Company] as [JobProd_Company], 
	[JobProd].[JobNum] as [JobProd_JobNum], 
	(sum(OrderRel.OurReqQty-OrderRel.OurJobShippedQty-OrderRel.OurStockShippedQty)) as [Calculated_RemainQty], 
	((RemainQty * OrderDtl.UnitPrice)) as [Calculated_OpenValue], 
	[OrderDtl].[UnitPrice] as [OrderDtl_UnitPrice] 

from Erp.JobProd as [JobProd]
inner join Erp.OrderRel as [OrderRel] on 
	  JobProd.Company = OrderRel.Company
	and  JobProd.OrderNum = OrderRel.OrderNum
	and  JobProd.OrderLine = OrderRel.OrderLine
	and  JobProd.OrderRelNum = OrderRel.OrderRelNum
inner join Erp.OrderDtl as [OrderDtl] on 
	  OrderRel.Company = OrderDtl.Company
	and  OrderRel.OrderNum = OrderDtl.OrderNum
	and  OrderRel.OrderLine = OrderDtl.OrderLine
inner join Erp.OrderHed as [OrderHed] on 
	  OrderDtl.Company = OrderHed.Company
	and  OrderDtl.OrderNum = OrderHed.OrderNum
group by 
	[JobProd].[Company], 
	[JobProd].[JobNum], 
	[OrderDtl].[UnitPrice])  as [OpenValue1] on 
	  ElFin.JobAsmbl_Company = OpenValue1.JobProd_Company
	and  ElFin.JobAsmbl_JobNum = OpenValue1.JobProd_JobNum
left outer join JobStatus as [JobStatus] on 
	  ElFin.JobAsmbl_Company = JobStatus.JobHead6_Company
	and  ElFin.JobAsmbl_JobNum = JobStatus.Calculated_JobNum
group by 
	[ElFin].[JobAsmbl_JobNum], 
	[ElFin].[Calculated_PartNum], 
	[ElFin].[Calculated_WIPTotal], 
	(case 
        when JobStatus.Calculated_Status = 1 then '1. Material Issued'
        when JobStatus.Calculated_Status = 2 then '2. Back From HT'
        when JobStatus.Calculated_Status = 3 then '3. Farmout'
        when JobStatus.Calculated_Status = 4 then '4. In Process'
        when JobStatus.Calculated_Status = 5 then '5. OV Not Final'
        when JobStatus.Calculated_Status = 6 then '6. OV Final or Back'
        else '0. Unknown'
        end), 
	[JobStatus].[Calculated_Status]

[GThom (Gage Thompson)]

Some version differences can probably break this, but you can rename a .baq into .zip. From there it should contain a “BAQVersion” file that you can update with your current version to import.

[jbooker (Josh Booker)]

Wow. Complex indeed, my friend. This is why cloud users need a local db sample to work with. This is impossible to optimize in BAQ designer, IMO. Sorry.

I imported yours and it worked. Seems JobCost is the bottleneck, especially when joined into WIP.

First rule is always include Company in Join, Group By, and Where. heck why not Order By and Select for good measure. Then filter early - always, filter once - if you can.

For example, assuming you want only firm unclosed jobs, make sure any subquery that has JobHead (especially if it does grouping, aggregates) has WHERE [jh].[JobClosed] = 0 AND [jh].[JobFirm] = 1. There are very likely indexes on those flags so the cost of filtering is negligable compared to the cost of grouping and calculating aggregates on jobs you don’t want.

Perhaps those two changes alone will have significant impact, but there’s more room for improvement - duplication and stuff according to Claude.

No idea if this is helpful to you but I found it an interesting excerise:

https://github.com/joshbooker/jobcost-optimization

The optimised query still doesn’t import, but it may be not far off. (I’m cloud so without a db, I’ve no idea if this is legit sql or baq compatible)

I’ve spent more time forcing SQL-to-BAQ then I have in the designer myself so can’t help there, but yeah CTEs in JobStatus would be the next step.

FWIW, I have the most trouble with table and field aliases when it comes to SQL-to-BAQ imports. Sometimes they import and need some fixing to run, other times they’re unfixable or don’t import so dump n try again.

Let us know how it goes. Good luck.

[cpilinko (Christian Pilinko)]

How many plants do you have? I only have one and I’ve found that if I hardcode that as a filter on the PartPlant table in BAQs it makes a world of difference. It does some sort of security check in SQL that takes forever. I’ve gotten in the habit of hardcoding the Plant as a filter whenever I can. It helps a lot on PartTran also.

[NateS (Nate Schoonover)]

@cpilinko

I’ve found that if I hardcode that as a filter on the PartPlant table in BAQs

I only have one PartPlant table reference. Do I just add it to that using the BAQ special constant CurrentPlant, or do I need to hardcode the actual string?

@jbooker

Wow. Complex indeed,

I will dive into all the work you did this weekend soon. Thank you for looking and thinking about this with me!

[NateS (Nate Schoonover)]

Thank you both! I found a handful of places where I hadn’t included Company in my joins. Naughty me! I also filtered down the Job table earlier and gained a good 10 seconds! I’ll keep digging into the other suggestions. What I was really hoping for was a way to see how long each step takes for this kind of complex BAQ with a referenced BAQ.

[NateS (Nate Schoonover)]

GPT suggested creating indices in the BAQ. I don’t think that is possible with Epicor. At least not from my cloud position. Right? Otherwise the suggestions really helped! Thank you!

[jbooker (Josh Booker)]

Right. AFAIK, Indexes are what they are for cloud. You can see the execution time so you can run one subquery at a time and see exec time for that portion.

[cpilinko (Christian Pilinko)]

I’ve always hardcoded the string. I never tested using a BAQ constant. There’s a post somewhere on this website that I read a long time ago about it. If I come across it again I’ll tag it here.

[cpilinko (Christian Pilinko)]

BAQ Performance with Part Rev

So have a query that is checking the differences on PartMaterial between two companies. I two have subqueries that give a list for one company and one for the other that are join in the top level to give me the differences. As usual, running the phrase in SSMS is fast. I know Epicor doesn’t actually execute that query but I do somewhat expect that the difference in time to return should be minimal. I’m doing a Top 500 and SSMS executes in .01 where as the BAQ takes over 80 seconds. Digging int…

[jbooker (Josh Booker)]

Thanks for sharing your complex query. It was quite helpful in our continued effort to work with the SQL-to-BAQ feature.

We were able to come up with a solid set of rules which make the tool import a complex query successfully.

Here is the resulting SQL which imports successfully via SQL-to-BAQ.

No idea if it matches your orginal intent but it should be close and rather quick.

[jbooker (Josh Booker)]

Sorry for hijacking your thread. Took your example as a opportunity to learn whats available in BAQ designer.

@NateS

What I was really hoping for was a way to see how long each step takes for this kind of complex BAQ with a referenced BAQ

So you can see the execution time when you run and you can run individual subqueries and look for longer exec time to pinpoint the badies.

You can also analyze your query to get some good suggestions.

For example, these missing index warnings are those missing Company in the join criteria:

If you add company to the subquery then to the relationsions (join) the warning should go away and perf is better.

Regarding CTEs. Switching from inner-subquery to a CTE is pretty straight forward. In the Subqueries slider, simply drop down and choose CTE

Now the important part when it comes to CTEs is the order top to bottom. CTEs used in other CTEs must come first. In your example. Top is used in TopStatus and AsmStatus so Top must come before those.

If you’re familiar with MSAccess, think of CTEs as TEMP tables (like when you first run a Make Table Query and then use the temp table in subsequent queries). Sometimes you can get away with subqueries ( query in a query in a query, etc) But other times those inner queries are running so may times you take a perf hit so it pays to run a make table then join to the table instead. CTE is like the temp table. Especially beneficial for aggregates, calcs, and conditionals like CASE (IIF), COALESE (IsNull) - do them once then use them many times in subsequent queries.

Hope that helps. I know I learned a thing or two.

[NateS (Nate Schoonover)]

I didn’t realize the time was staring me in the face the whole time! I often run the sub queries and consider performance one at a time. I didn’t even notice the time in there! Thank you. And Thank you for all the other details. This is very helpful!

[jbooker (Josh Booker)]

FWIW, I have no idea if it’s db exec time or server round trip. I did notice that it drops significantly after a few execs which is common for SQL once it gets a plan and caches your data. When it drops to the bottom limit after a few execs, you’re no longer getting an accurature reading as if the query was run without cache so you prolly need to go get a coffee and come back to it.

PS- the normal way to ensure cold cache in sql server is OPTION RECOMPILE. There’s a hints setting in the BAQ designer which may work for this. Haven’t tried it.

[Randy (Randy Stulce)]

You can use recompile to flush the cache with option(recompile) in Execution Settings.

Previous thread about it: Some of our Epicor users experience extreme slow downs on loading a BAQ through a customization but when using a different user ID on the same computer, its fast! - #3 by josecgomez

[jbooker (Josh Booker)]

youre a mind reader. thanks for that.