I moved away from using TLA/LLA costs. They didn’t have the level of detail that I needed. It seems that JobOper.ActLab and ActBur contain the correct costs I need to sum.
However, I can’t get this CTE to roll everythign up properly. Granted, AI helped me start it, but I have heavily modified it. Now for some jobs it shows the correct data. But if the job contains a subassmebly as a material resource for an operation, then those costs are blowing up the whole CTE.
I think the first part is correct, where the costs are rolled up in AccumulatedCost. At least until the calculated “Mtl” fields I added. If the job has assemblies as materials on an op, then I have to add up the costs on this side.
I think I need a two-step CTE. The first would roll up the costs for operations less than or equal to the operation where the scrap was generated. The second, would review those operations, and if any of them contain another assembly, then add up the costs the same way for those assemblies.
I am terrible at CTEs and don’t really understand how it works. I have had some of the brightest folks here try to teach me. But I still don’t get it. I get the concept, but the structure and syntax are so unintuitive.
I’d really appreciate any help from you CTE experts out there. I think I need to break off the second half of the query in AccumulatedCosts, and make a CTE out of that. Then later combine it with the other accumulated costs.
Here is the SQL and BAQ:
/*
* 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.
*/
with [AssemblyHierarchy] as
(select
[ja].[Company] as [Company],
[ja].[JobNum] as [JobNum],
[ja].[AssemblySeq] as [AssemblySeq],
[ja].[Parent] as [Parent],
[JobHead].[PartNum] as [JobHead_PartNum],
[ja].[PartNum] as [ja_PartNum],
(0) as [Calculated_LevelDepth],
(ja.AssemblySeq) as [Calculated_RootAssembly]
from Erp.JobAsmbl as [ja]
inner join Erp.LaborDtl as [ld] on
ja.Company = ld.Company
and ja.JobNum = ld.JobNum
and ja.AssemblySeq = ld.AssemblySeq
inner join Erp.JobHead as [JobHead] on
ja.Company = JobHead.Company
and ja.JobNum = JobHead.JobNum
where ( ld.ScrapQty > 0
and ld.PayrollDate >= @FirstDayOfPrevWeek
and ld.PayrollDate <= @LastDayOfPrevWeek ))
,[Scrap] as
(select
[ld1].[Company] as [ld1_Company],
[ld1].[JobNum] as [ld1_JobNum],
[ld1].[AssemblySeq] as [ld1_AssemblySeq],
[ld1].[OprSeq] as [ld1_OprSeq],
(sum(ld1.ScrapQty)) as [Calculated_ScrapQty]
from Erp.LaborDtl as [ld1]
where ( ld1.ScrapQty > 0
and ld1.PayrollDate >= @FirstDayOfPrevWeek
and ld1.PayrollDate <= @LastDayOfPrevWeek )
group by
[ld1].[Company],
[ld1].[JobNum],
[ld1].[AssemblySeq],
[ld1].[OprSeq])
,[AccumulatedCost] as
(select
[s].[ld1_Company] as [ld1_Company],
[s].[ld1_JobNum] as [ld1_JobNum],
[h].[JobHead_PartNum] as [JobHead_PartNum],
[s].[ld1_AssemblySeq] as [ld1_AssemblySeq],
[h].[ja_PartNum] as [ja_PartNum],
[s].[ld1_OprSeq] as [ld1_OprSeq],
[s].[Calculated_ScrapQty] as [Calculated_ScrapQty],
(isnull(jo.QtyCompleted, 0)) as [Calculated_QtyCompleted],
[jo].[OprSeq] as [jo_OprSeq],
[jo].[SubContract] as [jo_SubContract],
(jo.ActLabCost) as [Calculated_OpActLab],
(jo.ActBurCost) as [Calculated_OpActBur],
[jm].[TotalCost] as [jm_TotalCost],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOper].[ActBurCost] as [JobOper_ActBurCost],
[JobOper].[ActLabCost] as [JobOper_ActLabCost],
[JobOper].[SubContract] as [JobOper_SubContract],
[JobMtl].[TotalCost] as [JobMtl_TotalCost],
(isnull(JobOper.QtyCompleted, 0)) as [Calculated_MtlQtyCompl],
[JobAsmbl].[RelatedOperation] as [JobAsmbl_RelatedOperation]
from Scrap as [s]
inner join AssemblyHierarchy as [h] on
s.ld1_Company = h.Company
and s.ld1_JobNum = h.JobNum
and s.ld1_AssemblySeq = h.AssemblySeq
left outer join Erp.JobOper as [jo] on
h.Company = jo.Company
and h.JobNum = jo.JobNum
and h.AssemblySeq = jo.AssemblySeq
and ( jo.OprSeq <= s.ld1_OprSeq )
left outer join Erp.JobMtl as [jm] on
jo.Company = jm.Company
and jo.JobNum = jm.JobNum
and jo.AssemblySeq = jm.AssemblySeq
and jo.OprSeq = jm.RelatedOperation
left outer join Erp.JobAsmbl as [JobAsmbl] on
jo.Company = JobAsmbl.Company
and jo.JobNum = JobAsmbl.JobNum
and jo.OprSeq = JobAsmbl.RelatedOperation
left outer join Erp.JobOper as [JobOper] on
JobAsmbl.Company = JobOper.Company
and JobAsmbl.JobNum = JobOper.JobNum
and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
left outer join Erp.JobMtl as [JobMtl] on
JobOper.Company = JobMtl.Company
and JobOper.JobNum = JobMtl.JobNum
and JobOper.AssemblySeq = JobMtl.AssemblySeq
and JobOper.OprSeq = JobMtl.RelatedOperation)
select
[MtlTotal].[ld1_JobNum] as [ld1_JobNum],
[MtlTotal].[ld1_AssemblySeq] as [ld1_AssemblySeq],
[MtlTotal].[ld1_OprSeq] as [ld1_OprSeq],
[MtlTotal].[JobHead_PartNum] as [JobHead_PartNum],
[MtlTotal].[ja_PartNum] as [ja_PartNum],
[MtlTotal].[Calculated_ScrapQty] as [Calculated_ScrapQty],
[MtlTotal].[jo_OprSeq] as [jo_OprSeq],
[MtlTotal].[Calculated_LabTot] as [Calculated_LabTot],
[MtlTotal].[Calculated_AllMatTot] as [Calculated_AllMatTot],
[MtlTotal].[Calculated_SCTot] as [Calculated_SCTot],
[MtlTotal].[Calculated_BurTot] as [Calculated_BurTot],
[MtlTotal].[jo_SubContract] as [jo_SubContract],
(isnull(MtlTotal.Calculated_LabTot,0) +
isnull(MtlTotal.Calculated_AllMatTot,0)+
isnull(MtlTotal.Calculated_SCTot,0)+
isnull(MtlTotal.Calculated_BurTot,0)) as [Calculated_TotalCost],
[MtlTotal].[Calculated_MtlMatTot] as [Calculated_MtlMatTot],
[MtlTotal].[Calculated_MLabTot] as [Calculated_MLabTot],
[MtlTotal].[Calculated_MMatTot] as [Calculated_MMatTot],
[MtlTotal].[Calculated_MSCTot] as [Calculated_MSCTot],
[MtlTotal].[Calculated_MBurTot] as [Calculated_MBurTot],
[MtlTotal].[JobOper_AssemblySeq] as [JobOper_AssemblySeq],
[MtlTotal].[JobOper_OprSeq] as [JobOper_OprSeq]
from (select
[Main].[ld1_JobNum] as [ld1_JobNum],
[Main].[ld1_AssemblySeq] as [ld1_AssemblySeq],
[Main].[ld1_OprSeq] as [ld1_OprSeq],
[Main].[JobHead_PartNum] as [JobHead_PartNum],
[Main].[ja_PartNum] as [ja_PartNum],
[Main].[Calculated_ScrapQty] as [Calculated_ScrapQty],
[Main].[jo_OprSeq] as [jo_OprSeq],
[Main].[Calculated_LabTot] as [Calculated_LabTot],
(sum(Main.Calculated_MatTot)) as [Calculated_AllMatTot],
[Main].[Calculated_SCTot] as [Calculated_SCTot],
[Main].[Calculated_BurTot] as [Calculated_BurTot],
[Main].[jo_SubContract] as [jo_SubContract],
[Main].[JobOper_AssemblySeq] as [JobOper_AssemblySeq],
[Main].[JobOper_OprSeq] as [JobOper_OprSeq],
[Main].[Calculated_MtlMatTot] as [Calculated_MtlMatTot],
[Main].[Calculated_MLabTot] as [Calculated_MLabTot],
[Main].[Calculated_MMatTot] as [Calculated_MMatTot],
[Main].[Calculated_MSCTot] as [Calculated_MSCTot],
[Main].[Calculated_MBurTot] as [Calculated_MBurTot]
from (select distinct
[AccumulatedCost].[ld1_Company] as [ld1_Company],
[AccumulatedCost].[ld1_JobNum] as [ld1_JobNum],
[AccumulatedCost].[ld1_AssemblySeq] as [ld1_AssemblySeq],
[AccumulatedCost].[ld1_OprSeq] as [ld1_OprSeq],
[AccumulatedCost].[JobHead_PartNum] as [JobHead_PartNum],
[AccumulatedCost].[ja_PartNum] as [ja_PartNum],
[AccumulatedCost].[Calculated_ScrapQty] as [Calculated_ScrapQty],
[AccumulatedCost].[jo_OprSeq] as [jo_OprSeq],
(isnull(
iif(AccumulatedCost.jo_SubContract = 0,
AccumulatedCost.Calculated_OpActLab /
nullif(AccumulatedCost.Calculated_QtyCompleted,0),
0
),0
)) as [Calculated_LabTot],
(isnull(
iif(
AccumulatedCost.Calculated_QtyCompleted = 0,
AccumulatedCost.jm_TotalCost,
AccumulatedCost.jm_TotalCost /
nullif(AccumulatedCost.Calculated_QtyCompleted,0)
),
0
)) as [Calculated_MatTot],
(isnull(
iif(
AccumulatedCost.jo_SubContract = 1,
AccumulatedCost.Calculated_OpActLab /
nullif(AccumulatedCost.Calculated_QtyCompleted,0),
0
),
0)) as [Calculated_SCTot],
(isnull(
AccumulatedCost.Calculated_OpActBur /
nullif(AccumulatedCost.Calculated_QtyCompleted,0),
0)) as [Calculated_BurTot],
[AccumulatedCost].[Calculated_MtlQtyCompl] as [Calculated_MtlQtyCompl],
[AccumulatedCost].[Calculated_QtyCompleted] as [Calculated_QtyCompleted],
[AccumulatedCost].[jo_SubContract] as [jo_SubContract],
[AccumulatedCost].[Calculated_OpActLab] as [Calculated_OpActLab],
[AccumulatedCost].[Calculated_OpActBur] as [Calculated_OpActBur],
[AccumulatedCost].[jm_TotalCost] as [jm_TotalCost],
[AccumulatedCost].[JobOper_AssemblySeq] as [JobOper_AssemblySeq],
[AccumulatedCost].[JobOper_OprSeq] as [JobOper_OprSeq],
[AccumulatedCost].[JobOper_ActLabCost] as [JobOper_ActLabCost],
[AccumulatedCost].[JobOper_ActBurCost] as [JobOper_ActBurCost],
[AccumulatedCost].[JobOper_SubContract] as [JobOper_SubContract],
(sum(AccumulatedCost.JobMtl_TotalCost)) as [Calculated_MtlMatTot],
(isnull(
iif(AccumulatedCost.JobOper_SubContract = 0,
AccumulatedCost.JobOper_ActLabCost /
nullif(AccumulatedCost.Calculated_MtlQtyCompl,0),
0
),0
)) as [Calculated_MLabTot],
(isnull(
iif(
AccumulatedCost.Calculated_MtlQtyCompl = 0,
AccumulatedCost.JobMtl_TotalCost,
AccumulatedCost.JobMtl_TotalCost /
nullif(AccumulatedCost.Calculated_MtlQtyCompl,0)
),
0
)) as [Calculated_MMatTot],
(isnull(
iif(
AccumulatedCost.JobOper_SubContract = 1,
AccumulatedCost.JobOper_ActLabCost /
nullif(AccumulatedCost.Calculated_MtlQtyCompl,0),
0
),
0)) as [Calculated_MSCTot],
(isnull(
AccumulatedCost.JobOper_ActBurCost /
nullif(AccumulatedCost.Calculated_MtlQtyCompl,0),
0)) as [Calculated_MBurTot]
from AccumulatedCost as [AccumulatedCost]
group by
[AccumulatedCost].[ld1_Company],
[AccumulatedCost].[ld1_JobNum],
[AccumulatedCost].[ld1_AssemblySeq],
[AccumulatedCost].[ld1_OprSeq],
[AccumulatedCost].[JobHead_PartNum],
[AccumulatedCost].[ja_PartNum],
[AccumulatedCost].[Calculated_ScrapQty],
[AccumulatedCost].[jo_OprSeq],
(isnull(
iif(AccumulatedCost.jo_SubContract = 0,
AccumulatedCost.Calculated_OpActLab /
nullif(AccumulatedCost.Calculated_QtyCompleted,0),
0
),0
)),
(isnull(
iif(
AccumulatedCost.Calculated_QtyCompleted = 0,
AccumulatedCost.jm_TotalCost,
AccumulatedCost.jm_TotalCost /
nullif(AccumulatedCost.Calculated_QtyCompleted,0)
),
0
)),
(isnull(
iif(
AccumulatedCost.jo_SubContract = 1,
AccumulatedCost.Calculated_OpActLab /
nullif(AccumulatedCost.Calculated_QtyCompleted,0),
0
),
0)),
(isnull(
AccumulatedCost.Calculated_OpActBur /
nullif(AccumulatedCost.Calculated_QtyCompleted,0),
0)),
[AccumulatedCost].[Calculated_MtlQtyCompl],
[AccumulatedCost].[Calculated_QtyCompleted],
[AccumulatedCost].[jo_SubContract],
[AccumulatedCost].[Calculated_OpActLab],
[AccumulatedCost].[Calculated_OpActBur],
[AccumulatedCost].[jm_TotalCost],
[AccumulatedCost].[JobOper_AssemblySeq],
[AccumulatedCost].[JobOper_OprSeq],
[AccumulatedCost].[JobOper_ActLabCost],
[AccumulatedCost].[JobOper_ActBurCost],
[AccumulatedCost].[JobOper_SubContract],
(isnull(
iif(AccumulatedCost.JobOper_SubContract = 0,
AccumulatedCost.JobOper_ActLabCost /
nullif(AccumulatedCost.Calculated_MtlQtyCompl,0),
0
),0
)),
(isnull(
iif(
AccumulatedCost.Calculated_MtlQtyCompl = 0,
AccumulatedCost.JobMtl_TotalCost,
AccumulatedCost.JobMtl_TotalCost /
nullif(AccumulatedCost.Calculated_MtlQtyCompl,0)
),
0
)),
(isnull(
iif(
AccumulatedCost.JobOper_SubContract = 1,
AccumulatedCost.JobOper_ActLabCost /
nullif(AccumulatedCost.Calculated_MtlQtyCompl,0),
0
),
0)),
(isnull(
AccumulatedCost.JobOper_ActBurCost /
nullif(AccumulatedCost.Calculated_MtlQtyCompl,0),
0))) as [Main]
where ( Main.ld1_JobNum is not null )
group by
[Main].[ld1_JobNum],
[Main].[ld1_AssemblySeq],
[Main].[ld1_OprSeq],
[Main].[JobHead_PartNum],
[Main].[ja_PartNum],
[Main].[Calculated_ScrapQty],
[Main].[jo_OprSeq],
[Main].[Calculated_LabTot],
[Main].[Calculated_SCTot],
[Main].[Calculated_BurTot],
[Main].[jo_SubContract],
[Main].[JobOper_AssemblySeq],
[Main].[JobOper_OprSeq],
[Main].[Calculated_MtlMatTot],
[Main].[Calculated_MLabTot],
[Main].[Calculated_MMatTot],
[Main].[Calculated_MSCTot],
[Main].[Calculated_MBurTot]) as [MtlTotal]
where ( not isnull(MtlTotal.Calculated_LabTot,0) +
isnull(MtlTotal.Calculated_AllMatTot,0)+
isnull(MtlTotal.Calculated_SCTot,0)+
isnull(MtlTotal.Calculated_BurTot,0) = 0 )
LastWeekScrapCost2.baq (26.4 KB)
EDIT: I just tried doing the SQL import on this SQL, and it generates syntax errors. This is the code right from my BAQ screen. My BAQ runs without errors. ugghh… 
