John Paul Cook : Bill of Materials

Bill of Materials – Computing Component Totals

John Paul Cook — Sat, 07 Nov 2009 21:48:00 GMT

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...(read more)

Bill of Materials – The Basics

John Paul Cook — Fri, 06 Nov 2009 23:23:00 GMT

After having several people at SQL PASS ask me to explain what a Bill of Materials is, it seemed like a good idea for a blog post. A Bill of Materials, otherwise known as a BOM (don’t read this aloud in an airport lounge), is a listing of all of the components...(read more)

Hierarchyid data type and AdventureWorks2008 BillOfMaterials table

John Paul Cook — Sun, 01 Nov 2009 04:07:00 GMT

AdventureWorks2008 uses the hierarchyid data type in a updated version of the HumanResources.Employee table, but not in the Production.BillOfMaterials table. Michael Coles provides the code for a clever CTE to create Production.HierBillOfMaterials from...(read more)

Bill of Materials - Where Used Query

John Paul Cook — Sun, 11 Oct 2009 23:50:00 GMT

My post on indenting output from hierarchical queries showed how to do a parts explosion on a Bill of Materials (BOM) that was implemented with an adjacency list pattern. One very simple change converts the query from a parts explosion to a where used...(read more)

Displaying Hierarchical Data - Indenting the Output

John Paul Cook — Sun, 04 Oct 2009 03:49:00 GMT

When working with hierarchical data, people commonly request the output be indented for each level of the hierarchy. There are two key pieces to solving this query puzzle – knowing the level in the hierarchy and coming up with a sorting key for ordering...(read more)