Previous posts have shown how to do a parts explosion and a where used query. Another common query needed for Bill of Materials is a list of quantities required for all components in an assembly. The indented parts explosion is easily modified to produce grand totals for all parts in an assembly.
;WITH BOMcte(ComponentID, Name, PerAssemblyQty, ProductAssemblyID)
AS
(
SELECT b.ComponentID,
p.Name,
b.PerAssemblyQty,
b.ProductAssemblyID
FROM Production.BillOfMaterials AS b
INNER JOIN Production.Product p
on b.ComponentID = p.ProductID
WHERE b.EndDate IS NULL -- only retrieve components still being used
and b.ComponentID = 775 -- specify a component to explode
UNION ALL
SELECT b.ComponentID,
p.Name,
b.PerAssemblyQty,
b.ProductAssemblyID
FROM Production.BillOfMaterials as b
INNER JOIN Production.Product p
on b.ComponentID = p.ProductID
INNER JOIN BOMcte AS cte
ON b.ProductAssemblyID = cte.ComponentID
WHERE b.EndDate IS NULL -- only retrieve components still being used
)
SELECT Name, sum(PerAssemblyQty) as Total
FROM BOMcte
group by Name;
Name Total
----------------------------- -----
Adjustable Race 1.00
BB Ball Bearing 10.00
Bearing Ball 10.00
Blade 2.00
Chain 1.00
Chain Stays 2.00
Chainring 3.00
Chainring Bolts 3.00
Chainring Nut 3.00
Cone-Shaped Race 2.00
Crown Race 1.00
Cup-Shaped Race 2.00
Decal 1 2.00
Decal 2 1.00
Down Tube 1.00
Fork Crown 1.00
Fork End 2.00
Freewheel 1.00
Front Brakes 1.00
Front Derailleur 1.00
Front Derailleur Cage 1.00
Front Derailleur Linkage 1.00
Guide Pulley 1.00
Handlebar Tube 1.00
Head Tube 1.00
Headset Ball Bearings 8.00
HL Bottom Bracket 1.00
HL Crankarm 2.00
HL Crankset 1.00
HL Fork 1.00
HL Grip Tape 40.00
HL Headset 1.00
HL Hub 2.00
HL Mountain Frame - Black, 38 1.00
HL Mountain Front Wheel 1.00
HL Mountain Handlebars 1.00
HL Mountain Pedal 1.00
HL Mountain Rear Wheel 1.00
HL Mountain Rim 2.00
HL Mountain Seat Assembly 1.00
HL Mountain Seat/Saddle 1.00
HL Mountain Tire 2.00
HL Nipple 72.00
HL Shell 3.00
HL Spindle/Axle 2.00
Keyed Washer 1.00
Lock Nut 19 1.00
Lock Ring 1.00
Lower Head Race 1.00
Metal Bar 1 1.00
Metal Bar 2 1.00
Metal Sheet 2 3.00
Metal Sheet 3 1.00
Metal Sheet 4 1.00
Metal Sheet 5 3.00
Metal Sheet 6 2.00
Metal Sheet 7 1.00
Mountain End Caps 2.00
Mountain Tire Tube 2.00
Mountain-100 Black, 38 1.00
Paint - Black 8.00
Pinch Bolt 4.00
Rear Brakes 1.00
Rear Derailleur 1.00
Rear Derailleur Cage 1.00
Reflector 4.00
Seat Lug 1.00
Seat Post 1.00
Seat Stays 4.00
Seat Tube 1.00
Spokes 72.00
Steerer 1.00
Stem 1.00
Tension Pulley 1.00
Top Tube 1.00