How to return lowest level parts from BOM

Question

How to return lowest level parts from BOM

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

use AdventureWorks2017;
go

DECLARE @SalesOrderID INT = 43659;

SELECT SalesOrderID
-- , ProductID
-- , bom.AssemblyName
, bom.Component
, bom.ComponentID
-- , bom.PerAssemblyQty
, TotalPartQty = CAST(SUM(OrderQty * bom.PerAssemblyQty) AS INT)
FROM Sales.SalesOrderDetail sod
CROSS APPLY dbo.udfProductBOM(sod.ProductID) bom
WHERE sod.SalesOrderID = @SalesOrderID
AND NOT EXISTS (SELECT 1
                FROM dbo.udfProductBOM(sod.ProductID) bom2
                WHERE bom2.ComponentID = sod.ProductID)
GROUP BY SalesOrderID
-- , ProductID
   , bom.AssemblyName
   , bom.Component
   , bom.ComponentID
   -- , bom.PerAssemblyQty

First off, sorry for scattering the conversation a little bit...

This was my try at an answer ... gotta re-read Eddie's answer and try to apply it to my situation. I think this is right:

use AdventureWorks2017;
go

DECLARE @SalesOrderID INT = 43659;

SELECT SalesOrderID
-- , ProductID
-- , bom.AssemblyName
, bom.Component
, bom.ComponentID
-- , bom.PerAssemblyQty
, TotalPartQty = CAST(SUM(OrderQty * bom.PerAssemblyQty) AS INT)
FROM Sales.SalesOrderDetail sod
CROSS APPLY dbo.udfProductBOM(sod.ProductID) bom
WHERE sod.SalesOrderID = @SalesOrderIDAND NOT EXISTS (SELECT 1
FROM dbo.udfProductBOM(sod.ProductID) bom2
WHERE bom2.ComponentID = sod.ProductID)
GROUP BY SalesOrderID
-- , ProductID
, bom.AssemblyName
, bom.Component
, bom.ComponentID
-- , bom.PerAssemblyQty

This reply was modified 4 years, 5 months ago by pietlinden.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

I would take a completely different approach to this problem. Please Google articles on "nested sets model" or get a copy of my book on Trees and Hierarchies in SQL. You remember the basic design principle that we do not mix entities and relationships in one table? This is why we can have a table for personnel (entities) and a separate table for the organizational chart (a relationship) which will reference the personnel for each of the positions.

Without explaining the whole model, since you can look it up easily, the query to get the leaf nodes in a tree structure is simply:

SELECT *

FROM Tree

WHERE lft + 1 = rgt;

This skeleton should give you a good start and should run quite a bit faster than what you're trying to do

Please post DDL and follow ANSI/ISO standards when asking for help.

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können