I’m trying to make a CTE query that can add up all of the labor time needed to make an assembly. I used the link below to make the recursive query to get a BOM. That works fine. Then I pull in the PartOpr table on the top level to see all of the operations and prod standards in the BOM (from which I can create some calculated fields and sum and whatever)
The problem I have is getting the initializing part number into the list to be added in. There are operations on that part number too that have to be included. You can see that 205-2-12390 is my parameter for the first part to look at, and you can see all of the materials. I need one more row per top level operation that would essentially have the parent part information blank (because there won’t be one), and put the 205-2-12390 part and it’s related operations in the column with the material parts.
Any ideas on how I can get that last bit of information into the query?
Below is the copied out SQL for you matrix types that read code like a book.
With [CTE] AS
(select
[PartMtl].[Company] as [PartMtl_Company],
[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],
(cast ( substring('........',1,(hierarchy + 1) ) + Partmtl.MtlPartNum as nvarchar(25))) as [Calculated_Ind1]
from Erp.PartMtl as PartMtl
where (PartMtl.PartNum = @Part)
UNION ALL
select
[PartMtl1].[Company] as [PartMtl1_Company],
[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],
(CTE.Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(cast ( substring('........',1 ,(Hierarchy2 + 1) ) + PartMtl1.MtlPartNum
as nvarchar(25))) as [Calculated_ind2]
from Erp.PartMtl as PartMtl1
inner join CTE as CTE on
PartMtl1.PartNum = CTE.PartMtl_MtlPartNum
And
PartMtl1.Company = CTE.PartMtl_Company)
select
[CTE1].[PartMtl_Company] as [PartMtl_Company],
[CTE1].[PartMtl_PartNum] as [PartMtl_PartNum],
[CTE1].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[CTE1].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[CTE1].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[CTE1].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[CTE1].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[CTE1].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
[CTE1].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
[CTE1].[PartMtl_PlanAsAsm] as [PartMtl_PlanAsAsm],
[CTE1].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[CTE1].[Calculated_Ind1] as [Calculated_Ind1],
[PartOpr].[OprSeq] as [PartOpr_OprSeq],
[PartOpr].[OpCode] as [PartOpr_OpCode],
[PartOpr].[ProdStandard] as [PartOpr_ProdStandard]
from CTE as CTE1
inner join Erp.PartOpr as PartOpr on
CTE1.PartMtl_MtlPartNum = PartOpr.PartNum
where (CTE1.PartMtl_PullAsAsm = true)
