Dbo.uspGetWhereUsedProductID (procedure) - Revision history

Bot at 22:15, 23 June 2010

2010-06-23T22:15:44Z

Bot at 21:59, 23 June 2010

2010-06-23T21:59:55Z

Bot at 22:07, 2 February 2010

2010-02-02T22:07:35Z

Bot at 21:56, 28 December 2009

2009-12-28T21:56:45Z

Bot at 06:02, 30 November 2009

2009-11-30T06:02:04Z

Bot at 05:40, 30 November 2009

2009-11-30T05:40:17Z

Bot at 05:26, 30 November 2009

2009-11-30T05:26:43Z

Bot at 23:00, 26 November 2009

2009-11-26T23:00:05Z

Bot: New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''procedure | dbo.uspGetWhereUsedProductID |- |}

 CREATE PROCEDURE [dbo].[uspGetWh...

2009-01-03T12:36:16Z

New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''procedure | dbo.uspGetWhereUsedProductID |- |} <pre> CREATE PROCEDURE [dbo].[uspGetWh...

New page

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''procedure
| dbo.uspGetWhereUsedProductID
|-
|}

<pre>
CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
@StartProductID [int],
@CheckDate [datetime]
AS
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 [BOM_cte]([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 [BOM_cte] 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.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
FROM [BOM_cte] 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 25)
END;
</pre>

@@ Line 58: / Line 58: @@
 | Select
 | Table
-| [[Production.BillOfMaterials_(_(table))|Production.BillOfMaterials]]
+| [[Production.BillOfMaterials_(table)|Production.BillOfMaterials]]
 |- valign="top"
 | Select
 | Table
-| [[Production.Product_(_(table))|Production.Product]]
+| [[Production.Product_(table)|Production.Product]]
 |}

@@ Line 2: / Line 2: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
-|-
+|- valign="top"
 | '''Procedure
 | dbo.uspGetWhereUsedProductID
@@ Line 8: / Line 8: @@
 | '''Description
 | Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.
 |}
 === Source ===
@@ Line 57: / Line 55: @@
 | '''Referenced Object
-|-
+|- valign="top"
 | Select
 | Table
-| [[Production.BillOfMaterials_(table)|Production.BillOfMaterials]]
+| [[Production.BillOfMaterials_(_(table))|Production.BillOfMaterials]]
-|-
+|- valign="top"
 | Select
 | Table
-| [[Production.Product_(table)|Production.Product]]
+| [[Production.Product_(_(table))|Production.Product]]
 |}

@@ Line 3: / Line 3: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |-
-| '''procedure
+| '''Procedure
 | dbo.uspGetWhereUsedProductID
 |- valign="top"
@@ Line 11: / Line 11: @@
 |}
 <pre>
 CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
@@ Line 48: / Line 50: @@
 </pre>
+=== References ===
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |- style="background:silver"
@@ Line 66: / Line 68: @@
 |}

@@ Line 5: / Line 5: @@
 | '''procedure
 | dbo.uspGetWhereUsedProductID
 |-
 |}
@@ Line 44: / Line 47: @@
 END;
 </pre>
 == automatically generated ==

@@ Line 51: / Line 51: @@
 | dbo.uspGetWhereUsedProductID
 |- valign="top"
 | '''Description
 | Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.
 |-
 |}

← Older revision		Revision as of 23:00, 26 November 2009
Line 44:		Line 44:
	END;		END;
	</pre>		</pre>
		+
		+	== automatically generated ==
		+
		+	{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
		+	\|-
		+	\| '''procedure
		+	\| dbo.uspGetWhereUsedProductID
		+	\|-
		+
		+	\| '''Description
		+	\| Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.
		+	\|-
		+
		+	\|}
		+
		+	<pre>
		+
		+	CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
		+	@StartProductID [int],
		+	@CheckDate [datetime]
		+	AS
		+	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 [BOM_cte]([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 [BOM_cte] 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.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
		+	FROM [BOM_cte] 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 25)
		+	END;
		+	</pre>
		+
		+
		+	{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
		+	\|- style="background:silver"
		+	\| '''Referenced Object
		+	\| '''Object Type
		+	\| '''Dependency Type
		+
		+	\|-
		+	\| [[Production.BillOfMaterials_(table)\|Production.BillOfMaterials]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Production.Product_(table)\|Production.Product]]
		+	\| Table
		+	\| Select
		+	\|}