All,

I creating an ASP.Net with C# application with a Microsoft SQL 2012 database and one of the features needs to be a BOM compare. The BOM information is being pulled from our PDM System's database. I am able to pull the two separate BOM's using recursive queries but I'm not sure how to do the actual compare.

The compare needs to check to see if the quantity changed, if a part was completely removed, or if a new part was added. I was testing this with a series of merge statements but the issue I ran into was if a part had the same parent and the same level but it's parent was different.

Example - Part number 1003 is at the same level (starting at level 0) in two places even though it's parent is different.

1. 1000

	* 1001
	* 1002

		+ 1003
			- 1004
			- 1005
	* 1006

		+ 1003
			- 1004
			- 1005

[Results from the two BOM's](http://i.stack.imgur.com/A8EpU.png)

The structure of the results are ObjectId is the identifier for the part, ParentObjectId is the identifier for the part's parent, TopObjectId is the identifier for the top part of the assembly. Quantity is the number of that part number at that level of the assembly. TreeObjectId is the identifier for the Parent/Child relationship table. ObjectId does not change if the quantity changes, but it does change if the revision of the part changes. The TreeObjectId and TopObjectId changes if the revision of the assembly changes.

The results I want would basically flag the older assembly row when the quantity has been modified, insert a row if a part that didn't exist was added, flag the row if it has been removed.

I would prefer to do this on the database side but if there is a better way to do this on C# side that is fine with me.

Here is the code to run the recursive queries:

```
CREATE TABLE #LatestBOM (ObjectId int, PartNumber varchar(255), ParentObjectId int, ParentPartNumber varchar(255), TopObjectId int,
    TopPartNumber varchar(255), TreeLevel int, Quantity int, TreeObjectId int, ChangeFlag int);

CREATE TABLE #OrderedBOM (ObjectId int, PartNumber varchar(255), ParentObjectId int, ParentPartNumber varchar(255), TopObjectId int,
    TopPartNumber varchar(255), TreeLevel int, Quantity int, TreeObjectId int, ChangeFlag int);

DECLARE @LatestBOMObjectId int, @OrderedBOMObjectId int;
SET @LatestBOMObjectId = 149276;
SET @OrderedBOMObjectId = 149023;

WITH BOM (ObjectId, ParentObjectId, TopObjectId, TreeLevel, Quantity, TreeObjectId, ChangeFlag)
 AS
(
-- Anchor member definition
    SELECT TopLevelAssembly.[OBJECT_ID] as ObjectId, TopLevelAssembly.[OBJECT_ID] as ParentObjectId, @LatestBOMObjectId as TopObjectId,
        0 as TreeLevel, CAST(1.00 AS FLOAT) as Quantity, null as TreeObjectId, 1 as ChangeFlag
    FROM SMARTEAM.dbo.TN_DOCUMENTS as TopLevelAssembly
    WHERE TopLevelAssembly.[OBJECT_ID] = @LatestBOMObjectId
    UNION ALL
    SELECT Children.SON_OBJECT_ID as ObjectId, Children.OBJECT_ID1 as ParentObjectId, @LatestBOMObjectId as TopObjectId,
        BL.TreeLevel + 1, CN_QUANTITY as Quantity, Children.[OBJECT_ID] as TreeObjectId, 1 as ChangeFlag
    FROM SMARTEAM.dbo.DOCUME0_TREE as Children
    INNER JOIN BOM as BL
    on Children.OBJECT_ID1 = BL.ObjectId
    where Children.OBJECT_ID1 IS NOT NULL
)

INSERT INTO #LatestBOM (ObjectId, PartNumber, ParentObjectId, ParentPartNumber, TopObjectId, TopPartNumber, TreeLevel, Quantity, TreeObjectId, ChangeFlag)
select ObjectId, PartProduct.CN_PART_NUMBER as PartNumber, ParentObjectId, ParentPartProduct.CN_PART_NUMBER as ParentPartNumber, TopObjectId,
    TopPartProduct.CN_PART_NUMBER as TopPartNumber, TreeLevel, Quantity, TreeObjectId, ChangeFlag
from BOM
left join SMARTEAM.dbo.TN_DOCUMENTS PartProduct
on BOM.ObjectId = PartProduct.[OBJECT_ID]
left join SMARTEAM.dbo.TN_DOCUMENTS ParentPartProduct
on BOM.ParentObjectId = ParentPartProduct.[OBJECT_ID]
left join SMARTEAM.dbo.TN_DOCUMENTS TopPartProduct
on BOM.TopObjectId = TopPartProduct.[OBJECT_ID];

WITH BOM (ObjectId, ParentObjectId, TopObjectId, TreeLevel, Quantity, TreeObjectId, ChangeFlag)
 AS
(
-- Anchor member definition
    SELECT TopLevelAssembly.[OBJECT_ID] as ObjectId, TopLevelAssembly.[OBJECT_ID] as ParentObjectId, @OrderedBOMObjectId as TopObjectId,
        0 as TreeLevel, CAST(1.00 AS FLOAT) as Quantity, null as TreeObjectId, 1 as ChangeFlag
    FROM SMARTEAM.dbo.TN_DOCUMENTS as TopLevelAssembly
    WHERE TopLevelAssembly.[OBJECT_ID] = @OrderedBOMObjectId
    UNION ALL
    SELECT Children.SON_OBJECT_ID as ObjectId, Children.OBJECT_ID1 as ParentObjectId, @OrderedBOMObjectId as TopObjectId,
        BL.TreeLevel + 1, CN_QUANTITY as Quantity, Children.[OBJECT_ID] as TreeObjectId, 1 as ChangeFlag
    FROM SMARTEAM.dbo.DOCUME0_TREE as Children
    INNER JOIN BOM as BL
    on Children.OBJECT_ID1 = BL.ObjectId
    where Children.OBJECT_ID1 IS NOT NULL
)

INSERT INTO #OrderedBOM (ObjectId, PartNumber, ParentObjectId, ParentPartNumber, TopObjectId, TopPartNumber, TreeLevel, Quantity, TreeObjectId, ChangeFlag)
select ObjectId, PartProduct.CN_PART_NUMBER as PartNumber, ParentObjectId, ParentPartProduct.CN_PART_NUMBER as ParentPartNumber, TopObjectId,
    TopPartProduct.CN_PART_NUMBER as TopPartNumber, TreeLevel, Quantity, TreeObjectId, ChangeFlag
from BOM
left join SMARTEAM.dbo.TN_DOCUMENTS PartProduct
on BOM.ObjectId = PartProduct.[OBJECT_ID]
left join SMARTEAM.dbo.TN_DOCUMENTS ParentPartProduct
on BOM.ParentObjectId = ParentPartProduct.[OBJECT_ID]
left join SMARTEAM.dbo.TN_DOCUMENTS TopPartProduct
on BOM.TopObjectId = TopPartProduct.[OBJECT_ID];

select * from #LatestBOM;
select * from #OrderedBOM;
drop table #LatestBOM;
drop table #OrderedBOM;

```

Here is the code I tried to find the differences but ran into the issue with the same level under a different higher level parent:

```
--checks if a part is in the Order that isn't in the BOM (removed from BOM), if it is then add it to the #tempbomhistory with a changeflag of 2
WITH CTE AS (select OrderDetailId, OrderId, NeedQtyOld, NeedQtyNew, OrderQtyOld, OrderQtyNew, IsDeletedOld, IsDeletedNew, ChangeFlag, ObjectID, ParentProductObjectId, TopLevelProductObjectId, TreeLevel,
    MakeBuy
    from #TempBOMWithHistory)
MERGE CTE AS TARGET
    USING ( 
    Select OrderDetailId, OrderId, NeedQty, ObjectId, ParentProductObjectId, TopLevelProductObjectId, TreeLevel, MakeBuy
    from Order_Detail
    left JOIN SMARTEAM.dbo.TN_DOCUMENTS TopLevelPartNumber ON Order_Detail.TopLevelProductObjectId = TopLevelPartNumber.[OBJECT_ID]
    where TopLevelPartNumber.CN_PART_NUMBER = @TopLevelPartNumber AND OrderId = @OrderId)
     AS SOURCE
        ON (TARGET.OrderDetailId = SOURCE.OrderDetailId)
        WHEN NOT MATCHED THEN
            INSERT (OrderDetailId, OrderId, NeedQtyOld, NeedQtyNew, OrderQtyOld, OrderQtyNew, IsDeletedOld, IsDeletedNew,
                ChangeFlag, ObjectId, ParentProductObjectId, TopLevelProductObjectId, TreeLevel, MakeBuy)
            VALUES (SOURCE.OrderDetailId, SOURCE.OrderId, SOURCE.NeedQty, SOURCE.NeedQty, SOURCE.NeedQty, SOURCE.NeedQty, 0, 1,
                2, SOURCE.ObjectId, SOURCE.ParentProductObjectId, SOURCE.TopLevelProductObjectId, SOURCE.TreeLevel, SOURCE.MakeBuy);

--checks if a new part is in the BOM that isn't in the Order (new part in BOM), if it isn't in the order then set the changeflag in #tempBomWithHistory to 3
WITH CTE AS (select OrderDetailId, OrderId,NeedQtyOld, NeedQtyNew, OrderQtyOld, OrderQtyNew, IsDeletedOld, IsDeletedNew,
    ChangeFlag, ObjectId, ParentProductObjectId, TopLevelProductObjectId, TreeLevel, MakeBuy, ParentPartNumber 
    from #TempBOMWithHistory)
MERGE CTE AS TARGET
    USING (Select OrderDetailId, OrderId, NeedQty, OrderQty, LastModifiedTime, LastModifiedBy, ChangeFlag,
    ObjectId, ParentProductObjectId, TopLevelProductObjectId, TreeLevel, MakeBuy
    from Order_Detail
    WHERE OrderId = @OrderId) AS SOURCE
        ON (TARGET.OrderDetailId = SOURCE.OrderDetailId)
        WHEN NOT MATCHED BY SOURCE THEN
            UPDATE SET
                ChangeFlag = 3, NeedQtyOld = TARGET.NeedQtyNew, OrderQtyOld = TARGET.NeedQtyNew, OrderQtyNew = TARGET.NeedQtyNew, IsDeletedOld = 0, IsDeletedNew = 0;

--checks the part is in the Order and BOM but a attribute has changed, ignore anything that already has a changeflag pending (should only possibly be changeflag 2)
WITH CTE AS (Select * from #TempBOMWithHistory Where ChangeFlag = 0)
MERGE CTE AS TARGET
USING (select 
    OrderDetailId, NeedQty, OrderQty, MakeBuy
    from Order_Detail
    WHERE OrderId = @OrderId
    ) AS SOURCE
ON (TARGET.OrderDetailId = SOURCE.OrderDetailId)
WHEN MATCHED AND TARGET.NeedQtyNew = SOURCE.NeedQty
THEN
delete
WHEN MATCHED    
THEN
UPDATE SET
    TARGET.NeedQtyOld = SOURCE.NeedQty, TARGET.OrderQtyOld = SOURCE.OrderQty,TARGET.OrderQtyNew = SOURCE.OrderQty,
    TARGET.IsDeletedOld = 0, TARGET.IsDeletedNew = 0, TARGET.MakeBuy = SOURCE.MakeBuy, TARGET.ChangeFlag = 1;

```

Thanks!