Background:
I need to run a report that totals how many of each sub-item (child) I will need based on 2 things:
I currently have a SQL script using a recursive CTE that will provide me the correct totals for ONLY level 1 of the BOM. After level 1, the extended quantity calculation is incorrect as you will see below. So I need help to make the correct calculation.
My Example Code:
declare @multiplier int
set @multiplier = '8';
WITH CTE (ProdBOMNo, ItemDescription, ItemNo, QtyPer, ExtendedQty, PBLevel)
AS
(
SELECT BL.[ProdBOMNo], BL.[Descript], BL.[ItemNo], BL.[QtyPer], BL.[QtyPer] * @multiplier, 1 AS LVL
FROM [dbName$BOMLine] BL
WHERE BL.[ProdBOMNo] = '008722'
UNION ALL
SELECT BL2.[ProdBOMNo], BL2.[Descript], BL2.[ItemNo], BL2.[QtyPer], BL2.[QtyPer] * @multiplier, PBLevel + 1
FROM [dbName$BOMLine] BL2
INNER JOIN CTE ON CTE.[ItemNo] = BL2.[ProdBOMNo]
)
SELECT
CTE.[PBLevel],CTE.[ProdBOMNo],CTE.[ItemNo],CTE.[ItemDescription],CTE.[QtyPer],CTE.[ExtendedQty]
FROM CTE
LEFT JOIN [dbName$BOM] BOM ON BOM.[No_] = CTE.[ProdBOMNo]
The code above produces following results:
Level | BOM No. | Item No. | Item Description | Qty. Per | Extended Qty. Required |
---|---|---|---|---|---|
1 | 008722 | 007327 | Pump Assembly | 2 | 16 |
2 | 007327 | 007448 | Pump Body | 3 | 24 |
3 | 007448 | 007392 | Flange for Pump | 4 | 32 |
4 | 007392 | 007395 | Flapper for Pump | 1 | 8 |
What I want to see is:
Level | BOM No. | Item No. | Item Description | Qty. Per | Extended Qty. Required |
---|---|---|---|---|---|
1 | 008722 | 007327 | Pump Assembly | 2 | 16 |
2 | 007327 | 007448 | Pump Body | 3 | 48 |
3 | 007448 | 007392 | Flange for Pump | 4 | 192 |
4 | 007392 | 007395 | Flapper for Pump | 1 | 192 |
I am not sure how to adjust the code to get the results I'm looking for but would appreciate any help here. Please let me know if more data or information is needed and I will provide it. Thanks.
You are very close, you just need to replace your multiplication by the Multiplier with multiplication by the ExtendedQuantity from the recursion
declare @multiplier int
set @multiplier = '8';
WITH cteTab as ( SELECT * FROM ( VALUES
('1', '008722', '007327', 'Pump Assembly', '2', '16')
, ('2', '007327', '007448', 'Pump Body', '3', '24')
, ('3', '007448', '007392', 'Flange for Pump', '4', '32')
, ('4', '007392', '007395', 'Flapper for Pump', '1', '8')
) as TabA(Lvl, BOMNo, ItemNo, ItemDescription, QtyPer, ExtQtyReq)
)
,CTE (ProdBOMNo, ItemDescription, ItemNo, QtyPer, ExtendedQty, PBLevel)
AS
(
SELECT BL.BOMNo, BL.ItemDescription, BL.[ItemNo], BL.[QtyPer]
, CONVERT(INT, BL.[QtyPer] * @multiplier) as ExtendedQty
, 1 AS LVL
FROM cteTab as BL
WHERE BL.BOMNo = '008722'
UNION ALL
--SELECT BL2.BOMNo, BL2.ItemDescription, BL2.[ItemNo], BL2.[QtyPer], BL2.[QtyPer] * @multiplier, PBLevel + 1
SELECT BL2.BOMNo, BL2.ItemDescription, BL2.[ItemNo], BL2.[QtyPer]
, CONVERT(INT, CTE.ExtendedQty * BL2.[QtyPer]) as ExtendedQty --This one field changed
, PBLevel + 1
FROM cteTab as BL2
INNER JOIN CTE ON CTE.[ItemNo] = BL2.BOMNo
)
SELECT
CTE.[PBLevel],CTE.[ProdBOMNo],CTE.[ItemNo],CTE.[ItemDescription],CTE.[QtyPer],CTE.[ExtendedQty]
FROM CTE
LEFT JOIN cteTab as BOM ON BOM.BOMNo = CTE.[ProdBOMNo]
Anyway, make that one small change and you get the results you expected
PBLevel | ProdBOMNo | ItemNo | ItemDescription | QtyPer | ExtendedQty |
---|---|---|---|---|---|
1 | 008722 | 007327 | Pump Assembly | 2 | 16 |
2 | 007327 | 007448 | Pump Body | 3 | 48 |
3 | 007448 | 007392 | Flange for Pump | 4 | 192 |
4 | 007392 | 007395 | Flapper for Pump | 1 | 192 |