Background:

I need to run a report that totals how many of each sub-item (child) I will need based on 2 things:

• A bill of materials (BOM). This comes from a BOM table in the database.
• How many of the parent items are required.

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 parent BOM is 008722 in this example
• The user of the report inputs a parameter (multiplier) in SSRS for how many units they need, in this example I've set the parameter to '8'

The code above produces following results:

What I want to see is:

• So currently Level 1 is correct. Quantity 2 of item # 007327 are required per 008722. (2 * 8 (the multiplier) = 16)
• The SQL script currently just simply multiplies the qty. per by the multiplier/parameter but this becomes incorrect after level 1.
• At level 2 (and lower), it should instead multiply the qty. per by the extended qty. required of its parent. So for level 2 in the first example above its currently calculating 3 * 8, when the calculation should actually be 3 * 16.

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.

CodePudding user response：

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