THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

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 query. The Sort column is shown so you can see how the query does its job. There's a lot of output. Highlighting was added to the output to help you understand the groupings as the hierarchyid is walked upwards.

 

;WITH BOMcte(ComponentID, Name, PerAssemblyQty, ComputedLevel, ProductAssemblyID, Sort)
AS
(
    SELECT b.ComponentID,
    CAST(p.Name as nvarchar(100)),
    b.PerAssemblyQty,
    0
,
    b.ProductAssemblyID,
    CAST('\' + p.Name as nvarchar(254))
    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 = 523 -- specify a component to find where it is used
    UNION ALL
    SELECT b.ComponentID,
    CAST(REPLICATE ('| ' , ComputedLevel) + p.Name as nvarchar(100)),
    b.PerAssemblyQty,
    ComputedLevel
+ 1,
    b.ProductAssemblyID,
    CAST(cte.Sort + '\' + p.Name as nvarchar(254))
    FROM Production.BillOfMaterials as b
    INNER JOIN Production.Product p
    on b.ComponentID = p.ProductID
    INNER JOIN BOMcte AS cte
    ON b.ComponentID = cte.ProductAssemblyID --b.ProductAssemblyID = cte.ComponentID in parts explosion
    WHERE b.EndDate IS NULL -- only retrieve components still being used
)
SELECT ProductAssemblyID, ComponentID, Name, PerAssemblyQty, Sort
FROM BOMcte
ORDER BY Sort;

 

ProductAssemblyID
 |   ComponentID                             PerAssemblyQty
 |    |  Name                                 |   Sort
---- --- ----------------------------------- ---- ------------------------------------------------------------------------
400  523 LL Spindle/Axle                     1.00 \LL Spindle/Axle
815  400 LL Hub                              1.00 \LL Spindle/Axle\LL Hub
818  400 LL Hub                              1.00 \LL Spindle/Axle\LL Hub
823  400 LL Hub                              1.00 \LL Spindle/Axle\LL Hub
826  400 LL Hub                              1.00 \LL Spindle/Axle\LL Hub

980  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

981  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

982  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

983  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

984  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

985  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

986  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

987  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

988  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

989  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

990  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

991  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

992  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

993  815 |    LL Mountain Front Wheel        1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel

NULL 980 |    |    Mountain-400-W Silver, 38 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-400-W Silver, 38

NULL 981 |    |    Mountain-400-W Silver, 40 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-400-W Silver, 40

NULL 982 |    |    Mountain-400-W Silver, 42 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-400-W Silver, 42

NULL 983 |    |    Mountain-400-W Silver, 46 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-400-W Silver, 46

NULL 989 |    |    Mountain-500 Black, 40    1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Black, 40

NULL 990 |    |    Mountain-500 Black, 42    1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Black, 42

NULL 991 |    |    Mountain-500 Black, 44    1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Black, 44

NULL 992 |    |    Mountain-500 Black, 48    1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Black, 48

NULL 993 |    |    Mountain-500 Black, 52    1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Black, 52

NULL 984 |    |    Mountain-500 Silver, 40   1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Silver, 40

NULL 985 |    |    Mountain-500 Silver, 42   1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Silver, 42

NULL 986 |    |    Mountain-500 Silver, 44   1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Silver, 44

NULL 987 |    |    Mountain-500 Silver, 48   1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Silver, 48

NULL 988 |    |    Mountain-500 Silver, 52   1.00 \LL Spindle/Axle\LL Hub\LL Mountain Front Wheel\Mountain-500 Silver, 52

980  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

981  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

982  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

983  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

984  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

985  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

986  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

987  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

988  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

989  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

990  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

991  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

992  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

993  823 |    LL Mountain Rear Wheel         1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel

NULL 980 |    |    Mountain-400-W Silver, 38 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-400-W Silver, 38

NULL 981 |    |    Mountain-400-W Silver, 40 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-400-W Silver, 40

NULL 982 |    |    Mountain-400-W Silver, 42 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-400-W Silver, 42

NULL 983 |    |    Mountain-400-W Silver, 46 1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-400-W Silver, 46

NULL 989 |    |    Mountain-500 Black, 40    1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Black, 40

NULL 990 |    |    Mountain-500 Black, 42    1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Black, 42

NULL 991 |    |    Mountain-500 Black, 44    1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Black, 44

NULL 992 |    |    Mountain-500 Black, 48    1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Black, 48

NULL 993 |    |    Mountain-500 Black, 52    1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Black, 52

NULL 984 |    |    Mountain-500 Silver, 40   1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Silver, 40

NULL 985 |    |    Mountain-500 Silver, 42   1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Silver, 42

NULL 986 |    |    Mountain-500 Silver, 44   1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Silver, 44

NULL 987 |    |    Mountain-500 Silver, 48   1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Silver, 48

NULL 988 |    |    Mountain-500 Silver, 52   1.00 \LL Spindle/Axle\LL Hub\LL Mountain Rear Wheel\Mountain-500 Silver, 52

759  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

760  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

761  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

762  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

763  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

764  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

765  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

766  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

767  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

768  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

769  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

770  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

977  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

997  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

998  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

999  818 |    LL Road Front Wheel            1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel

NULL 768 |    |    Road-650 Black, 44        1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 44
NULL 769 |    |    Road-650 Black, 48        1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 48

NULL 770 |    |    Road-650 Black, 52        1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 52

NULL 765 |    |    Road-650 Black, 58        1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 58

NULL 766 |    |    Road-650 Black, 60        1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 60

NULL 767 |    |    Road-650 Black, 62        1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Black, 62

NULL 762 |    |    Road-650 Red, 44          1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 44

NULL 763 |    |    Road-650 Red, 48          1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 48

NULL 764 |    |    Road-650 Red, 52          1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 52

NULL 759 |    |    Road-650 Red, 58          1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 58

NULL 760 |    |    Road-650 Red, 60          1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 60

NULL 761 |    |    Road-650 Red, 62          1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-650 Red, 62

NULL 997 |    |    Road-750 Black, 44        1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-750 Black, 44

NULL 998 |    |    Road-750 Black, 48        1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-750 Black, 48

NULL 999 |    |    Road-750 Black, 52        1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-750 Black, 52

NULL 977 |    |    Road-750 Black, 58        1.00 \LL Spindle/Axle\LL Hub\LL Road Front Wheel\Road-750 Black, 58

759  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

760  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

761  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

762  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

763  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

764  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

765  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

766  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

767  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

768  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

769  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

770  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

977  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

997  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

998  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

999  826 |    LL Road Rear Wheel             1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel

NULL 768 |    |    Road-650 Black, 44        1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 44

NULL 769 |    |    Road-650 Black, 48        1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 48

NULL 770 |    |    Road-650 Black, 52        1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 52

NULL 765 |    |    Road-650 Black, 58        1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 58

NULL 766 |    |    Road-650 Black, 60        1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 60

NULL 767 |    |    Road-650 Black, 62        1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Black, 62

NULL 762 |    |    Road-650 Red, 44          1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 44

NULL 763 |    |    Road-650 Red, 48          1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 48

NULL 764 |    |    Road-650 Red, 52          1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 52

NULL 759 |    |    Road-650 Red, 58          1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 58

NULL 760 |    |    Road-650 Red, 60          1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 60

NULL 761 |    |    Road-650 Red, 62          1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-650 Red, 62

NULL 997 |    |    Road-750 Black, 44        1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-750 Black, 44

NULL 998 |    |    Road-750 Black, 48        1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-750 Black, 48

NULL 999 |    |    Road-750 Black, 52        1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-750 Black, 52

NULL 977 |    |    Road-750 Black, 58        1.00 \LL Spindle/Axle\LL Hub\LL Road Rear Wheel\Road-750 Black, 58

 

The quantity of the results can easily be a bit overwhelming in a where used query, particularly if the component (i.e., part) is a very common and standard sized item such as a bolt or nut. You may want to restrict the output to only show the top level assemblies that use a particular part as shown in this query:

 

;WITH BOMcte(ComponentID, Name, PerAssemblyQty, ComputedLevel, ProductAssemblyID, Sort)
AS
(
    SELECT b.ComponentID,
    CAST(p.Name as nvarchar(100)),
    b.PerAssemblyQty,
    0
,
    b.ProductAssemblyID,
    CAST('\' + p.Name as nvarchar(254))
    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 = 523 -- specify a component to find where it is used
    UNION ALL
    SELECT b.ComponentID,
    CAST(REPLICATE ('| ' , ComputedLevel) + p.Name as nvarchar(100)),
    b.PerAssemblyQty,
    ComputedLevel
+ 1,
    b.ProductAssemblyID,
    CAST(cte.Sort + '\' + p.Name as nvarchar(254))
    FROM Production.BillOfMaterials as b
    INNER JOIN Production.Product p
    on b.ComponentID = p.ProductID
    INNER JOIN BOMcte AS cte
    ON b.ComponentID = cte.ProductAssemblyID --b.ProductAssemblyID = cte.ComponentID in parts explosion
    WHERE b.EndDate IS NULL -- only retrieve components still being used
)
SELECT ComponentID, Name
FROM BOMcte
WHERE ProductAssemblyID is NULL -- only show the top level assemblies
ORDER BY Sort;

 

ComponentID Name
----------- -----------------------------------
980         |    |    Mountain-400-W Silver, 38

981         |    |    Mountain-400-W Silver, 40

982         |    |    Mountain-400-W Silver, 42

983         |    |    Mountain-400-W Silver, 46

989         |    |    Mountain-500 Black, 40

990         |    |    Mountain-500 Black, 42

991         |    |    Mountain-500 Black, 44

992         |    |    Mountain-500 Black, 48

993         |    |    Mountain-500 Black, 52

984         |    |    Mountain-500 Silver, 40

985         |    |    Mountain-500 Silver, 42

986         |    |    Mountain-500 Silver, 44

987         |    |    Mountain-500 Silver, 48

988         |    |    Mountain-500 Silver, 52

980         |    |    Mountain-400-W Silver, 38

981         |    |    Mountain-400-W Silver, 40

982         |    |    Mountain-400-W Silver, 42

983         |    |    Mountain-400-W Silver, 46

989         |    |    Mountain-500 Black, 40

990         |    |    Mountain-500 Black, 42

991         |    |    Mountain-500 Black, 44

992         |    |    Mountain-500 Black, 48

993         |    |    Mountain-500 Black, 52

984         |    |    Mountain-500 Silver, 40

985         |    |    Mountain-500 Silver, 42

986         |    |    Mountain-500 Silver, 44

987         |    |    Mountain-500 Silver, 48

988         |    |    Mountain-500 Silver, 52

768         |    |    Road-650 Black, 44

769         |    |    Road-650 Black, 48

770         |    |    Road-650 Black, 52

765         |    |    Road-650 Black, 58

766         |    |    Road-650 Black, 60

767         |    |    Road-650 Black, 62

762         |    |    Road-650 Red, 44

763         |    |    Road-650 Red, 48

764         |    |    Road-650 Red, 52

759         |    |    Road-650 Red, 58

760         |    |    Road-650 Red, 60

761         |    |    Road-650 Red, 62

997         |    |    Road-750 Black, 44

998         |    |    Road-750 Black, 48

999         |    |    Road-750 Black, 52

977         |    |    Road-750 Black, 58

768         |    |    Road-650 Black, 44

769         |    |    Road-650 Black, 48

770         |    |    Road-650 Black, 52

765         |    |    Road-650 Black, 58

766         |    |    Road-650 Black, 60

767         |    |    Road-650 Black, 62

762         |    |    Road-650 Red, 44

763         |    |    Road-650 Red, 48

764         |    |    Road-650 Red, 52

759         |    |    Road-650 Red, 58

760         |    |    Road-650 Red, 60

761         |    |    Road-650 Red, 62

997         |    |    Road-750 Black, 44

998         |    |    Road-750 Black, 48

999         |    |    Road-750 Black, 52

977         |    |    Road-750 Black, 58

 

The logic from the parts explosion for indenting the output is no longer relevant, so you can remove it from the output to clean up the results.

 

;WITH BOMcte(ComponentID, Name, PerAssemblyQty, ComputedLevel, ProductAssemblyID, Sort)
AS
(
    SELECT b.ComponentID,
    p.Name,
    b.PerAssemblyQty,
    0
,
    b.ProductAssemblyID,
    CAST('\' + p.Name as nvarchar(254))
    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 = 523 -- specify a component to find where it is used
    UNION ALL
    SELECT b.ComponentID,
    p.Name,
    b.PerAssemblyQty,
    ComputedLevel
+ 1,
    b.ProductAssemblyID,
    CAST(cte.Sort + '\' + p.Name as nvarchar(254))
    FROM Production.BillOfMaterials as b
    INNER JOIN Production.Product p
    on b.ComponentID = p.ProductID
    INNER JOIN BOMcte AS cte
    ON b.ComponentID = cte.ProductAssemblyID --b.ProductAssemblyID = cte.ComponentID in parts explosion
    WHERE b.EndDate IS NULL -- only retrieve components still being used
)
SELECT ComponentID, Name
FROM BOMcte
WHERE ProductAssemblyID is NULL -- only show the top level assemblies
ORDER BY Sort;

 

to get this (output truncated for brevity):

ComponentID Name
----------  -------------------------
980         Mountain-400-W Silver, 38
981         Mountain-400-W Silver, 40
982         Mountain-400-W Silver, 42
983         Mountain-400-W Silver, 46
989         Mountain-500 Black, 40  

 

Published Sunday, October 11, 2009 7:50 PM by John Paul Cook
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement