Browse by Tags

• Bill of Materials – Computing Component Totals

  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... Posted Saturday, November 07, 2009 4:48 PM by John Paul Cook | 1 Comments Filed under: Bill of Materials

• Bill of Materials – The Basics

  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... Posted Friday, November 06, 2009 6:23 PM by John Paul Cook | 5 Comments Filed under: Bill of Materials

• Hierarchyid data type and AdventureWorks2008 BillOfMaterials table

  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... Posted Sunday, November 01, 2009 12:07 AM by John Paul Cook | 0 Comments Filed under: Bill of Materials Attachment(s): CreateHierBillOfMaterials.zip

• Bill of Materials - Where Used Query

  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... Posted Sunday, October 11, 2009 7:50 PM by John Paul Cook | 0 Comments Filed under: Bill of Materials

• Displaying Hierarchical Data - Indenting the Output

  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... Posted Saturday, October 03, 2009 11:49 PM by John Paul Cook | 0 Comments Filed under: Bill of Materials