Execute the following script in Query Editor to create and apply a lookup table:

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE [dbo].[procGetWhereUsedbyProductID]
GO

CREATE PROCEDURE [dbo].[procGetWhereUsedbyProductID]
@StartProductID [int],
@CheckDate [datetime]
AS

/*
This stored procedure using a recursive query to return all components or assemblies
that directly or indirectly use the specified ProductID (@ProductID)
at a given date (@CheckDate).

Author: Bill Gates
Create Date: 2005.06.01
Last Update: 2006.07.02
*/

BEGIN
SET NOCOUNT ON;

--Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly)
WITH [cteBOM]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
AS (
SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
FROM [Production].[BillOfMaterials] b
INNER JOIN [Production].[Product] p
ON b.[ProductAssemblyID] = p.[ProductID]
WHERE b.[ComponentID] = @StartProductID
AND @CheckDate >= b.[StartDate]
AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
UNION ALL
SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [cteBOM] cte
INNER JOIN [Production].[BillOfMaterials] b
ON cte.[ProductAssemblyID] = b.[ComponentID]
INNER JOIN [Production].[Product] p
ON b.[ProductAssemblyID] = p.[ProductID]
WHERE @CheckDate >= b.[StartDate]
AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
)
-- Outer select from the CTE
SELECT b.[ProductAssemblyID],
b.[ComponentDesc],
b.[ComponentID],
convert(int,SUM(b.[PerAssemblyQty])) AS [TotalQuantity] ,
'$'+convert(varchar,b.[StandardCost],1) AS StandardCost,
'$'+ convert(varchar,b.[ListPrice],1) AS ListPrice,
b.[BOMLevel], b.[RecursionLevel]
FROM [cteBOM] b
GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
OPTION (MAXRECURSION 35)
END;

GO
EXEC sys.sp_addextendedproperty @name=N'Sproc Description', @value=N'Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'procGetWhereUsedbyProductID'
GO
EXEC sys.sp_addextendedproperty @name=N'Sproc Description', @value=N'Input parameter for the stored procedure procGetWhereUsedbyProductID. Enter a valid ProductID from the Production.Product table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'procGetWhereUsedbyProductID', @level2type=N'PARAMETER',@level2name=N'@StartProductID'
GO
EXEC sys.sp_addextendedproperty @name=N'Sproc Description', @value=N'Input parameter for the stored procedure procGetWhereUsedbyProductID used to eliminate components not used after that date. Enter a valid date.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'procGetWhereUsedbyProductID', @level2type=N'PARAMETER',@level2name=N'@CheckDate'
GO

-- Test
EXEC [dbo].[procGetWhereUsedbyProductID] 316, '2002-01-15 00:00:00.000'
GO