Somewhat OT: Sql server and "parent" rows

Somewhat 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.  Smile [:)]

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