Somewhat OT: Sql server and "parent" rowsSomewhat OT: Sql server and "parent" rows
Old forum URL: forums.lhotka.net/forums/t/4364.aspx
ajj3085 posted on Monday, February 18, 2008
I have a hierarchy stored in Sql with the simply ParentId column method. I'm at a point now where I want to take the current rows quantity, and multiplie it by its parent row quantity, if it has a parent. This multipling should continue all the way up until we get to the row which has no parent.
These are line items.. so they have quantity, price ,etc. here's a sample row
LineItemId Qty PartNumber Parent
1 2 GP-001 NULL
2 4 BND-001 1
3 7 PART-001 2
What I want is to find the actual quantity of line item 3, which is 56. Anyone have a good way to do this in Sql / Linq directly?
Thanks
Andy
SomeGuy replied on Monday, February 18, 2008
I think you can do it with a CTE like:
DECLARE
@AssemblyID INT
SELECT
@AssemblyID = 800;
SELECT
* FROM Production.Product WHERE ProductID = @AssemblyID;
WITH
Parts(AssemblyID, ComponentID, PerAssemblyQty, TotalQty, UnitMeasureCode) AS
(
SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, b.PerAssemblyQty, b.UnitMeasureCode
FROM Production.BillOfMaterials AS b
WHERE b.ProductAssemblyID = @AssemblyID
AND b.EndDate IS NULL
UNION ALL
SELECT bom.ProductAssemblyID, bom.ComponentID, bom.PerAssemblyQty, CAST(p.PerAssemblyQty * bom.PerAssemblyQty AS DECIMAL(8,2)), bom.UnitMeasureCode
FROM Production.BillOfMaterials AS bom
INNER JOIN Parts AS p
ON bom.ProductAssemblyID = p.ComponentID
AND bom.EndDate IS NULL
)
SELECT
p.ComponentID, pr.Name, SUM(p.TotalQty), p.UnitMeasureCode
FROM
Parts AS p
INNER JOIN Production.Product AS pr
ON p.ComponentID = pr.ProductID
GROUP
BY p.ComponentID, pr.Name, p.UnitMeasureCode;ajj3085 replied on Monday, February 18, 2008
Ahh, good idea. I was able to get this working..I think.
rasupit replied on Monday, February 18, 2008
Andy, to take products of these rows, you can use EXP(SUM(LOG(Qty))).
The only catch is the qty must be positive value.
HTH,
Ricky.
Copyright (c) Marimer LLC