------------------------------------------------------------------------
--This example inserts two top level assemblies into a temporary table along
--with the number of each model required for 12 months in advance, and by
--and by doing so it calculates the budgeted quantity of each bought out
--component required by an assembly type factory or process pivoted for a
--12 month look ahead period.
------------------------------------------------------------------------
--The technique used here is much more efficient that using a recursive
--common table expression (CTE), but it cannot list a bill of materials
--in hierarchical order. It could be possible to list in this order with a few
--modifications.
--It calculates the requirements for components on a level-by-level basis. The
--initial level is set to zero, and the top level items are inserted at this level --into a temporary table. Each successive level is grabbed from a BOM structure
-- table where each line represents a parent/child pair. Each successive level
--looks at the previously appended level and multiplies the requirement for the
--parent (from the previous level) with the new items found in the structure
--table until no more items are returned from the structure table.
------------------------------------------------------------------------
DECLARE
@level int;
--create working table
SELECT ParentPart as StockCode, CAST(0 as int) As Lev, CAST(10 as float) as Reqd, CAST(1 as int) as Mnth
INTO #tmp
FROM BomStructure
WHERE 1=0;
--insert all the models and requirements
SET @level = 0;
INSERT INTO #tmp VALUES('DAD261',@level,1860,1);
INSERT INTO #tmp VALUES('DAD261',@level,1860,2);
INSERT INTO #tmp VALUES('DAD261',@level,4000,3);
INSERT INTO #tmp VALUES('DAD261',@level,2142,4);
INSERT INTO #tmp VALUES('DAD261',@level,2289,5);
INSERT INTO #tmp VALUES('DAD261',@level,2289,6);
INSERT INTO #tmp VALUES('DAD261',@level,4000,7);
INSERT INTO #tmp VALUES('DAD261',@level,3000,8);
INSERT INTO #tmp VALUES('DAD261',@level,3969,9);
INSERT INTO #tmp VALUES('DAD261',@level,3780,10);
INSERT INTO #tmp VALUES('DAD261',@level,3806,11);
INSERT INTO #tmp VALUES('DAD261',@level,826,12);
INSERT INTO #tmp VALUES('DAD262',@level,820,1);
INSERT INTO #tmp VALUES('DAD262',@level,820,2);
INSERT INTO #tmp VALUES('DAD262',@level,820,3);
INSERT INTO #tmp VALUES('DAD262',@level,2000,4);
INSERT INTO #tmp VALUES('DAD262',@level,2289,5);
INSERT INTO #tmp VALUES('DAD262',@level,2289,6);
INSERT INTO #tmp VALUES('DAD262',@level,820,7);
INSERT INTO #tmp VALUES('DAD262',@level,1578,8);
INSERT INTO #tmp VALUES('DAD262',@level,600,9);
INSERT INTO #tmp VALUES('DAD262',@level,800,10);
INSERT INTO #tmp VALUES('DAD262',@level,1000,11);
INSERT INTO #tmp VALUES('DAD262',@level,700,12);
--loop through each matching level in the BOM table
WHILE (@@RowCount > 0) AND (@level < 15)
BEGIN
SET @level = @level + 1;
INSERT INTO #tmp
SELECT B.Component, @level , A.Reqd * B.QtyPer, A.Mnth
FROM BomStructure B JOIN #tmp A
ON A.StockCode = B.ParentPart
JOIN InvMaster I
ON I.StockCode = B.ParentPart
WHERE A.Lev = @level - 1
AND ((B.StructureOffDate IS NULL) OR (B.StructureOffDate >=GetDate()))
AND ((B.StructureOnDate IS NULL) OR (B.StructureOnDate <= GetDate()))
AND B.Route='0'
AND I.PartCategory <> 'B'
END;
-- This example uses a pivot inside a CTE, so we can process the CTE further
WITH MyCTE
AS
(
SELECT *
FROM #tmp AS Result
PIVOT
(
SUM(Reqd)
FOR Mnth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PVT
)
SELECT * FROM MyCTE;
DROP TABLE #tmp
GO