Multilevel BOM with Inventory

Question

I'm looking to build a query that is able to go through a BOM recursively with inventory and tell me what pieces I need to purchase in order to create the product.

create table item (
    item_sku varchar(10),
    item_description nvarchar(255)
);

create table bom (
    item_sku varchar(10),
    parent_item_sku varchar(10),
    quantity float
);

create table inventory (
    item_sku varchar(10),
    quantity float
);

-- ITEM DATA
insert into item (item_sku, item_description) 
    values ('10', 'Bicycle'),
            ('101', 'Wheels'),('102', 'Frame'),
            ('201','Spokes'),('202', 'Tyre'), ('203', 'Rim'), ('204', 'Hub'),   ('205', 'Seat'), ('206', 'Handle'), ('207', 'Main Frame'),
            ('301', 'Trye Valve'),
            ('401', 'Bolt'),('402','Nut'),('403','Cap')


-- BILL OF MATERIALS DATA
insert into bom (item_sku, parent_item_sku, quantity) 
    values ('101', '10', 2),('102', '10', 1),
        ('201', '101', 48),('202', '101', 1),('203', '101', 1),('204', '101', 1), 
        ('205', '102', 48),('206', '102', 1),('207', '102', 1),
        ('301', '202', 1), 
        ('401', '301', 48),('402', '301', 1),('403', '301', 1)


-- INVENTORY DATA
insert into inventory (item_sku, quantity) values ('10', 3), ('101', 5), ('205', 10), ('301', 2), ('402', 15), ('401', 20)




-- Produce 15 bikes
-- Produce 3 wheels
-- Produce 2 Tyre Valves
-- Produce/Purchase 10 Nuts

Answer 1

When you start doing BOMs of BOMs, this is extremely complex to do in a single query. Most inventory systems loop over the BOMs to produce the order list.

Can this be a stored proc?

Answer 2

Here is a query that unwinds the recursive structure. It may not show the correct result since you never said what output you expected. I'm also uncertain of what the quantity columns are supposed to signify, so I just included them as-is. It is always a good idea to give a short description of the business rules when you post a question. Don't assume that everyone is familiar with your business domain.

; WITH rekurs AS (
   SELECT i.item_description, bom.item_sku, bom.quantity
   FROM   bom
   JOIN   item i ON bom.item_sku = i.item_sku
   WHERE  bom.parent_item_sku = 10
   UNION ALL
   SELECT i.item_description, bom.item_sku, bom.quantity
   FROM   rekurs r
   JOIN   bom ON r.item_sku = parent_item_sku
   JOIN   item i ON bom.item_sku = i.item_sku
)
SELECT r.item_description, r.item_sku, r.quantity, inv.quantity
FROM   rekurs r
LEFT   JOIN  inventory inv ON inv.item_sku = r.item_sku