Would using jobhead rather than parttran resolve this? When you moved it to main assembly did it return all results?
I’m getting a strange duplication problem, when I update the select lists to make it clearer to me, the duplication goes away. But yes, looking at one indented BOM example, it seems like everything is there.
JobHead may be a little faster since you’d probably be looking at 10k-100k jobs instead of like 100M, but a good index would probably get your results to be about equal. I think the recursion for many assemblies is going to be your biggest issue.
So if I’m understanding you moved parttran and it’s filtering criteria to main assembly and joined to partmtl. What did the join consist of? Company to company, part to ?, And rev to rev? If so, should part be to calc. Main part or partrev?
I may have overhauled nearly all of the query… Anyway, here it is, and most shockingly to me is that it only takes 7 seconds in SSMS. I tried my best to make sure you could reimplement it as a BAQ and I tried to match the naming but gave up at some points.
with [MaxRev] AS (
SELECT
[PartRev].[Company] AS [PartRev_Company],
[PartRev].[PartNum] AS [PartRev_PartNum],
(MAX([PartRev].[RevisionNum])) AS [Calculated_MaxRevisionNum]
FROM [Erp].[PartRev] AS [PartRev]
WHERE
[PartRev].[Company] = '<YourCompanyID>' AND
[PartRev].[Approved] = 1
GROUP BY
[PartRev].[Company],
[PartRev].[PartNum]
),
[PartTranWithJobs] AS (
SELECT
[PartTran].[Company] AS [PartTran_Company],
[PartTran].[PartNum] AS [PartTran_PartNum],
[PartTran].[RevisionNum] AS [PartTran_RevisionNum],
[PartTran].[JobNum] AS [PartTran_JobNum],
[PartTran].[WareHouseCode],
[PartTran].[BinNum],
SUM([PartTran].[TranQty]) AS [Calculated_SumTranQty]
FROM [Erp].[PartTran] AS [PartTran]
WHERE
[PartTran].[Company] = '<YourCompanyID>' AND
[PartTran].[TranType] = 'mfg-stk' AND
[PartTran].[Plant] = 'MfgSys' AND
[PartTran].[TranDate] >= @StartDate AND
[PartTran].[TranDate] <= @EndDate
GROUP BY
[PartTran].[Company],
[PartTran].[PartNum],
[PartTran].[RevisionNum],
[PartTran].[JobNum],
[PartTran].[WareHouseCode],
[PartTran].[BinNum]
),
[PartTranPartsOnly] AS (
SELECT DISTINCT
[PartTranWithJobs].[PartTran_Company],
[PartTranWithJobs].[PartTran_PartNum],
[PartTranWithJobs].[PartTran_RevisionNum]
FROM [PartTranWithJobs]
),
[MainAssembly] AS (
SELECT
(0) AS [Calculated_Level],
([PartMtl].[PartNum]) AS [Calculated_MainPart],
[PartMtl].[Company] AS [PartMtl_Company],
[PartMtl].[PartNum] AS [PartMtl_PartNum],
[PartMtl].[MtlPartNum] AS [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] AS [PartMtl_QtyPer],
CAST([PartMtl].[QtyPer] AS decimal(18, 8)) AS [Calculated_ExtQtyPer]
FROM [Erp].[PartMtl] AS PartMtl
INNER JOIN [MaxRev] AS [MaxRev] on
PartMtl.[Company] = [MaxRev].[PartRev_Company] AND
PartMtl.[PartNum] = [MaxRev].[PartRev_PartNum] AND
PartMtl.[RevisionNum] = [MaxRev].Calculated_MaxRevisionNum
INNER JOIN [PartTranPartsOnly] AS [PartTran] ON
[PartTran].[PartTran_Company] = [MaxRev].[PartRev_Company] AND
[PartTran].[PartTran_PartNum] = [MaxRev].[PartRev_PartNum] AND
[PartTran].[PartTran_RevisionNum] = [MaxRev].[Calculated_MaxRevisionNum]
union all
SELECT
([MainAssembly].Calculated_Level+1) AS [Calculated_Level],
[MainAssembly].[Calculated_MainPart] AS [Calculated_MainPart],
[PartMtl1].[Company] AS [PartMtl1_Company],
[PartMtl1].[PartNum] AS [PartMtl1_PartNum],
[PartMtl1].[MtlPartNum] AS [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] AS [PartMtl1_QtyPer],
CAST([MainAssembly].[Calculated_ExtQtyPer] * [PartMtl1].[QtyPer] AS decimal(18, 8)) AS [Calculated_ExtQtyPer]
FROM [Erp].[PartMtl] AS PartMtl1
INNER JOIN [MaxRev] AS [MaxRev1] on
PartMtl1.[Company] = [MaxRev1].PartRev_Company
AND [PartMtl1].[PartNum] = [MaxRev1].PartRev_PartNum
AND [PartMtl1].[RevisionNum] = [MaxRev1].Calculated_MaxRevisionNum
INNER JOIN [MainAssembly] AS [MainAssembly] on
PartMtl1.[Company] = [MainAssembly].PartMtl_Company
AND [PartMtl1].[PartNum] = [MainAssembly].PartMtl_MtlPartNum
where (([MainASsembly].Calculated_Level+1) <= 12)
),
[PartOprSums] AS (
SELECT
[PartOpr].[Company],
[PartOpr].[PartNum],
[PartOpr].[RevisionNum],
[PartOpDtl].[ResourceGrpID],
SUM([PartOpr].[ProdStandard]) AS [SumProdStandard]
FROM [Erp].[PartOpr]
inner join [Erp].[PartOpDtl] as [PartOpDtl] on
PartOpr.[Company] = [PartOpDtl].Company
and [PartOpr].[PartNum] = [PartOpDtl].PartNum
and [PartOpr].[RevisionNum] = [PartOpDtl].RevisionNum
and [PartOpr].[AltMethod] = [PartOpDtl].AltMethod
and [PartOpr].[OprSeq] = [PartOpDtl].OprSeq
GROUP BY
[PartOpr].[Company],
[PartOpr].[PartNum],
[PartOpr].[RevisionNum],
[PartOpDtl].[ResourceGrpID]
)
select
[MainAssembly1].[Calculated_MainPart] as [Calculated_MainPart],
[Part].[PartDescription],
[MainAssembly1].[PartMtl_PartNum] as [PartMtl_PartNum],
[MainAssembly1].[PartMtl_MtlPartNum],
[MainAssembly1].[Calculated_ExtQtyPer] * [PartTran1].[Calculated_SumTranQty] AS [Calculated_TranQty],
ISNULL([PartOpr].[ResourceGrpID], '') as [PartOpDtl_ResourceGrpID],
ISNULL([PartOpr].[SumProdStandard], 0.0) as [PartOpr_ProdStandard],
[PartTran1].[PartTran_JobNum],
[MainAssembly1].[Calculated_ExtQtyPer]
/ IIF([MainAssembly1].[PartMtl_QtyPer] > 0, [MainAssembly1].[PartMtl_QtyPer], 1) -- Back out last level
* [PartTran1].[Calculated_SumTranQty]
* ISNULL([PartOpr].[SumProdStandard], 0.0) AS [Calculated_EarnedHours],
[PartTran1].[WareHouseCode],
[PartTran1].[BinNum]
from [MainAssembly] as MainAssembly1
INNER JOIN [PartTranWithJobs] AS [PartTran1] ON
[PartTran1].[PartTran_Company] = [MainAssembly1].[PartMtl_Company] AND
[PartTran1].[PartTran_PartNum] = [MainAssembly1].[Calculated_MainPart]
inner join [MaxRev] as [MaxRev1] on
[MaxRev1].[PartRev_Company] = [MainAssembly1].PartMtl_Company
and [MaxRev1].[PartRev_PartNum] = [MainAssembly1].[PartMtl_PartNum]
LEFT join [PartOprSums] [PartOpr] ON
[PartOpr].[Company] = [MaxRev1].[PartRev_Company] AND
[PartOpr].[PartNum] = [MaxRev1].[PartRev_PartNum] AND
[PartOpr].[RevisionNum] = [MaxRev1].[Calculated_MaxRevisionNum]
inner join [Erp].[Part] as [Part] on
[Part].[Company] = [MainAssembly1].[PartMtl_Company] AND
[Part].[PartNum] = [MainAssembly1].[Calculated_MainPart]
Wow. Thank you very much! Moving parttran to main assembly worked!
Glad I could help!