I’m having trouble trying to figure out how to get a total quantity on a CTE BOM query. Basically if there are 2 of part A, which goes into assembly B, and assembly B has a quantity 2 which goes into TopAssembly C, the total quantity of part A needs to be 4. What I have right now is just grabbing the quantity per and making a table, then grouping and summing in that table. That gets me close, but like in the situation listed above, won’t multiply like it should. I tried replacing the QtyPer with a calculated field that multiplies the lower level Qtyper by the next level up Qtyper, but I get an error when I try to do that. I can use one, or the other with no error, but when I try to multiply them, it pukes. I’m not any good at CTE queries, so I’m sure that I am doing something wrong, but I’m not sure where to go from here. Anyone have an example I can look at for this?
(If your looking at the code I pasted and wondering what’s going on with all of the extra stuff, it’s a UBaq that interacts with a UDField on the part master to denote whether it’s a part eligible to be sold as a spare part. Also, this query doesn’t run in the the BAQ because I didn’t want parameters in the dashboard, it understandably times out. It does work with filters set up in the tracker on the dashboard though)
with [SubQuery1] as
(select
(PartMtl.PartNum) as [Calculated_IntialPart],
(Part1.PartDescription) as [Calculated_InitialPartDesc],
[PartMtl].[Company] as [PartMtl_Company],
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part1 on
PartMtl.Company = Part1.Company
and PartMtl.PartNum = Part1.PartNum
union all
select
(SubQuery1.Calculated_IntialPart) as [Calculated_intialpart2],
(SubQuery1.Calculated_InitialPartDesc) as [Calculated_InitialPartDesc2],
[PartMtl1].[Company] as [PartMtl1_Company],
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
PartMtl1.Company = SubQuery1.PartMtl_Company
and PartMtl1.PartNum = SubQuery1.PartMtl_MtlPartNum)
select
[BOMqty].[Calculated_IntialPart] as [Calculated_IntialPart],
[BOMqty].[Calculated_InitialPartDesc] as [Calculated_InitialPartDesc],
[BOMqty].[PartMtl_Company] as [PartMtl_Company],
[BOMqty].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[Part].[PartDescription] as [Part_PartDescription],
[BOMqty].[Calculated_TotalQty] as [Calculated_TotalQty],
[Part].[IUM] as [Part_IUM],
[Part].[ClassID] as [Part_ClassID],
[AverageCost].[Calculated_AverageCost] as [Calculated_AverageCost],
[PartCosts].[Calculated_StandardCost] as [Calculated_StandardCost],
(case
when Part.TypeCode = 'p' then round(AverageCost.Calculated_AverageCost,2)
else round(PartCosts.Calculated_StandardCost,2 )
end) as [Calculated_QuoteCost],
(case
when Part.TypeCode = 'p' then 'Ave' else 'Std'
end) as [Calculated_PriceType],
(BOMqty.Calculated_TotalQty * QuoteCost) as [Calculated_TotalBomCost],
(case
when (QuoteCost * (1+@Markup/100)) < 1.00 then (QuoteCost * (1+@Markup/100))
else convert(decimal,ceiling(QuoteCost * (1+@Markup/100)))
end) as [Calculated_EaPrice],
(EaPrice * BOMqty.Calculated_TotalQty) as [Calculated_TotalBomPrice],
(ceiling(BOMqty.Calculated_TotalQty*.1)) as [Calculated_OneYearQty],
(OneYearQty * EaPrice) as [Calculated_OneYearPrice],
(ceiling(BOMqty.Calculated_TotalQty*.5)) as [Calculated_FiveYearQty],
(FiveYearQty * EaPrice) as [Calculated_FiveYearPrice],
((case when Part.SparePart_c = 1 then 'SpareBoth' else 'NotSpareBoth' end)) as [Calculated_SparePartFilter],
(convert(varchar, @Markup)+'%') as [Calculated_Markup],
[Part].[SparePart_c] as [Part_SparePart_c],
[Part].[PartNum] as [Part_PartNum]
from (select
[SubQuery12].[Calculated_IntialPart] as [Calculated_IntialPart],
[SubQuery12].[Calculated_InitialPartDesc] as [Calculated_InitialPartDesc],
[SubQuery12].[PartMtl_Company] as [PartMtl_Company],
[SubQuery12].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
(sum( SubQuery12.PartMtl_QtyPer )) as [Calculated_TotalQty]
from SubQuery1 as SubQuery12
group by [SubQuery12].[Calculated_IntialPart],
[SubQuery12].[Calculated_InitialPartDesc],
[SubQuery12].[PartMtl_Company],
[SubQuery12].[PartMtl_MtlPartNum]) as BOMqty
left outer join (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
(avg( PartTran.MtlUnitCost )) as [Calculated_AverageCost]
from Erp.PartTran as PartTran
where (PartTran.TranType like 'pur')
group by [PartTran].[Company],
[PartTran].[PartNum]) as AverageCost on
BOMqty.PartMtl_Company = AverageCost.PartTran_Company
and BOMqty.PartMtl_MtlPartNum = AverageCost.PartTran_PartNum
left outer join (select
[PartCost].[Company] as [PartCost_Company],
[PartCost].[PartNum] as [PartCost_PartNum],
(PartCost.StdLaborCost+PartCost.StdBurdenCost+PartCost.StdMaterialCost+PartCost.StdSubContCost+PartCost.StdMtlBurCost) as [Calculated_StandardCost]
from Erp.PartCost as PartCost) as PartCosts on
BOMqty.PartMtl_Company = PartCosts.PartCost_Company
and BOMqty.PartMtl_MtlPartNum = PartCosts.PartCost_PartNum
inner join Erp.Part as Part on
BOMqty.PartMtl_Company = Part.Company
and BOMqty.PartMtl_MtlPartNum = Part.PartNum
and ( not Part.ClassID like '7' )
