A Scenario to Ponder #15

I guess, by now, most of you are familiar with the SQL Server 2005 sample database - AdventureWorks. We will use one of the tables from this database to create our scenario - Production.BillOfMaterials. To simplify our requirements, we will be selecting only those records in this table, that has the EndDate as NULL.

Before I go ahead and explain the scenario, lets see how this table is built. Its a hierarchical table. It shows relationship of each component (Column: ComponentID) with its parent component (Column: ProductAssemblyID). There can be mutiple levels of hierarchy.

For example, take ComponentID 749. It is the ultimate parent, since the ProductAssemblyID for this component is NULL. Finding the components which has the parent (ProductAssemblyID) as 749, we see that we need 14 other components to build 749. One such child component is 519 which inturn needs 4 other components for it to be built and so on.

Now, here is the scenario:
Our company uses this table Production.BillOfMaterials to identify the bill of materials for any component. Lets say that the company has decided to discontinue production/use of a particular Component A.

To optimize the inventory, we need to come up with a query using this table that will generate the list of components that can also be discontinued along with this component A based on the following rules:

• All parent components (at any level) which uses component A can be discontinued.
• All child components (at any level) which is used to build component A but is not used to build any other component can be discontinued.
• Child component (at any level) which is used to build a parent (dentified in rule 1) but is not used to build any other component can also be discontinued.

Can you come up with a query to achieve this? The query will accept one input - the discontinued ComponentID.

Please post your answer in the comments section.