In case it helps, this is my BAQ:
with [SubQuery1] as
(select
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
(0) as [Calculated_Hierarchy],
(case
when PartMtl.MtlSeq < 10 then cast(concat('000', PartMtl.MtlSeq) as nvarchar(25))
when PartMtl.MtlSeq < 100 then cast(concat('00', PartMtl.MtlSeq) as nvarchar(25))
when PartMtl.MtlSeq < 1000 then cast(concat('0', PartMtl.MtlSeq) as nvarchar(25))
else cast(PartMtl.MtlSeq as nvarchar(25))
end) as [Calculated_Ind1],
[PartMtl].[Company] as [PartMtl_Company],
[PartRev2].[Plant] as [PartRev2_Plant],
(cast(1 as decimal)) as [Calculated_ParentQty],
[PartRev2].[AltMethod] as [PartRev2_AltMethod],
(PartMtl.PartNum) as [Calculated_TopPartNum],
(PartMtl.RevisionNum) as [Calculated_TopRevNum]
from Erp.PartMtl as PartMtl
inner join Erp.PartRev as PartRev2 on
PartMtl.Company = PartRev2.Company
and PartMtl.PartNum = PartRev2.PartNum
and PartMtl.RevisionNum = PartRev2.RevisionNum
and PartMtl.AltMethod = PartRev2.AltMethod
and ( PartRev2.AltMethod = '' )
inner join (select
[PartRev3].[Company] as [PartRev3_Company],
[PartRev3].[PartNum] as [PartRev3_PartNum],
(max(PartRev3.RevisionNum)) as [Calculated_MaxRev]
from Erp.PartRev as PartRev3
where (PartRev3.Approved = true)
group by [PartRev3].[Company],
[PartRev3].[PartNum]) as GetMaxRev on
PartRev2.Company = GetMaxRev.PartRev3_Company
and PartRev2.PartNum = GetMaxRev.PartRev3_PartNum
and PartRev2.RevisionNum = GetMaxRev.Calculated_MaxRev
where PartMtl.PartNum = @PartNum
union all
select
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
[PartMtl1].[PlanAsAsm] as [PartMtl1_PlanAsAsm],
(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(case
when PartMtl1.MtlSeq < 10 then cast(concat(Calculated_Ind1, '-', '000', PartMtl1.MtlSeq) as nvarchar(25))
when PartMtl1.MtlSeq < 100 then cast(concat(Calculated_Ind1, '-', '00', PartMtl1.MtlSeq) as nvarchar(25))
when PartMtl1.MtlSeq < 1000 then cast(concat(Calculated_Ind1, '-', '0', PartMtl1.MtlSeq) as nvarchar(25))
else cast(PartMtl1.MtlSeq as nvarchar(25))
end) as [Calculated_Ind2],
[PartMtl1].[Company] as [PartMtl1_Company],
[PartRev].[Plant] as [PartRev_Plant],
(cast(PartMtl_QtyPer as decimal)) as [Calculated_ParentQty],
[SubQuery1].[PartRev2_AltMethod] as [PartRev2_AltMethod],
[SubQuery1].[Calculated_TopPartNum] as [Calculated_TopPartNum],
[SubQuery1].[Calculated_TopRevNum] as [Calculated_TopRevNum]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
PartMtl1.Company = SubQuery1.PartMtl_Company
and PartMtl1.PartNum = SubQuery1.PartMtl_MtlPartNum
and PartMtl1.AltMethod = SubQuery1.PartRev2_AltMethod
inner join Erp.PartRev as PartRev on
PartMtl1.Company = PartRev.Company
and PartMtl1.PartNum = PartRev.PartNum
and PartMtl1.RevisionNum = PartRev.RevisionNum
and PartMtl1.AltMethod = PartRev.AltMethod
and ( PartRev.AltMethod = '' and PartRev.Approved = true ))
select
[SubQuery11].[Calculated_Ind1] as [Calculated_Ind1],
[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[SubQuery11].[PartRev2_Plant] as [PartRev2_Plant],
[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[ClassID] as [Part_ClassID],
[SubQuery11].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
(SubQuery11.Calculated_ParentQty * SubQuery11.PartMtl_QtyPer) as [Calculated_ExtQty],
[SubQuery11].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
[SubQuery11].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
(case
when PartPlant.SourceType <> 'M'
then PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost + PartCost.StdMtlBurCost
when PartPlant.SourceType = 'M' and PartPlant.PhantomBOM = 0
then PartCost.StdLaborCost
else 0
end) as [Calculated_StdCost],
(ExtQty * StdCost) as [Calculated_ExtStdCost],
(case
when PartPlant.SourceType <> 'M'
then PartCost.LastLaborCost + PartCost.LastBurdenCost + PartCost.LastMaterialCost + PartCost.LastSubContCost + PartCost.LastMtlBurCost
when PartPlant.SourceType = 'M' and PartPlant.PhantomBOM = 0
then PartCost.LastLaborCost
else 0
end) as [Calculated_LastCost],
(ExtQty * LastCost) as [Calculated_ExtLastCost],
(case
when PartPlant.SourceType <> 'M'
then PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost
when PartPlant.SourceType = 'M' and PartPlant.PhantomBOM = 0
then PartCost.AvgLaborCost
else 0
end) as [Calculated_AvgCost],
(ExtQty * AvgCost) as [Calculated_ExtAvgCost],
[Vendor].[Name] as [Vendor_Name],
[PartClass].[BuyerID] as [PartClass_BuyerID],
[PartPlant].[LeadTime] as [PartPlant_LeadTime],
[PartPlant].[MfgLotMultiple] as [PartPlant_MfgLotMultiple],
[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
[SubQuery11].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
(case
when StdCost <> 0
then abs((LastCost - StdCost) / StdCost)
else 0
end) as [Calculated_LastCostVar],
(case
when StdCost <> 0
then abs((AvgCost - StdCost) / StdCost)
else 0
end) as [Calculated_AvgCostVar],
[CostUpdateRelevance].[Calculated_DaysSinceUpdate] as [Calculated_DaysSinceUpdate],
[SubQuery11].[Calculated_TopPartNum] as [Calculated_TopPartNum],
[SubQuery11].[Calculated_TopRevNum] as [Calculated_TopRevNum]
from SubQuery1 as SubQuery11
left outer join Erp.PartCost as PartCost on
SubQuery11.PartMtl_Company = PartCost.Company
and SubQuery11.PartMtl_MtlPartNum = PartCost.PartNum
left outer join Erp.Part as Part on
SubQuery11.PartMtl_Company = Part.Company
and SubQuery11.PartMtl_MtlPartNum = Part.PartNum
left outer join Erp.PartClass as PartClass on
Part.Company = PartClass.Company
and Part.ClassID = PartClass.ClassID
left outer join Erp.PartPlant as PartPlant on
SubQuery11.PartMtl_Company = PartPlant.Company
and SubQuery11.PartRev2_Plant = PartPlant.Plant
and SubQuery11.PartMtl_MtlPartNum = PartPlant.PartNum
left outer join Erp.Vendor as Vendor on
PartPlant.Company = Vendor.Company
and PartPlant.VendorNum = Vendor.VendorNum
left outer join (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
(datediff(day, max(PartTran.TranDate), Constants.Today)) as [Calculated_DaysSinceUpdate]
from Erp.PartTran as PartTran
where (PartTran.TranType = 'ADJ-CST')
group by [PartTran].[Company],
[PartTran].[PartNum]) as CostUpdateRelevance on
SubQuery11.PartMtl_MtlPartNum = CostUpdateRelevance.PartTran_PartNum
order by SubQuery11.Calculated_Ind1