August 20, 2009 at 10:26 am
Hello,
I'm having quite a hard time writing a recursive CTE for summing values (standard ones are ok). The point I'm struggling with is when summing a group, if one row of that group has some child that parent AvCost2 value must be replaced with the sum of it's child and so on. If a child of that parent has some child we must sum the lowers child first, push the sum one level higher, redo the sum push the new value up to the parent then sum all the parent for the final value. (There will be more level then those describe here hence the appealing recursion)
Here's a demo:
The result set give the good value when ParentID IS NULL but does not sum the values from its child.
The expected result would be:
Total of 1.234234
If Total is 2.23 and some digits then the parent HeaderID = 51 AvCost2 value was used instead of the sum of it's child.
The Final sum should be 0.194131 + 0.179581 + 0.860522 (all parents AvCost2 without child + the sums of the child replacing the AvCost2 of that parent with the sum value)
DECLARE @Table TABLE (
HeaderID INT NOT NULL
, InQty DECIMAL (18,6)
, UsQty DECIMAL (18,6)
, AvCost DECIMAL (18,6)
, AvCost2 DECIMAL (18,6)
, Qty DECIMAL (18,6)
, ParentID INT
, ChildID INT
)
INSERT INTO @Table VALUES (49, 1.0, 350.0, 13.59, 0.194131, 5.0, NULL, NULL)
INSERT INTO @Table VALUES (51, 1.0, 1000.0, 107.569, 1.860552, 8.0, NULL, 10)
INSERT INTO @Table VALUES (34, 1.0, 350.0, 17.99, 3.747917, 10.0, 10, NULL)
INSERT INTO @Table VALUES (37, 1.0, 1000.0, 5.25, 0.010500, 20.0, 10, NULL)
INSERT INTO @Table VALUES (36, 1.0, 75.0, 10.0, 2.666667, 20.0, 10, NULL)
INSERT INTO @Table VALUES (35, 1.0, 75.0, 12.95, 3.453333, 20.0, 10, NULL)
INSERT INTO @Table VALUES (32, 1.0, 17.0, 28.99, 53.290441, 31.25, 10, NULL)
INSERT INTO @Table VALUES (50, 2.21, 16.0, 6.35, 0.179581, 1.0, NULL, NULL)
INSERT INTO @Table VALUES (33, 1.0, 16.0, 3.14, 44.400452, 500.0, 10, NULL)
SELECT * FROM @Table
;WITH GetCost (ParentID, AvCost2) AS
(
SELECT
ParentID
, SUM (CASE
WHEN ChildID IS NULL THEN AvCost2
ELSE 0
END
) AS 'AvCost2'
FROM
@Table
WHERE
ParentID IS NULL
GROUP BY ParentID
UNION ALL
SELECT
itmCost.ParentID
, itmCost.AvCost2 / tt.UsQty / tt.InQty * tt.Qty AS 'AvCost2'
FROM
@Table tt
INNER JOIN GetCost itmCost
ON tt.ChildID = itmCost.ParentID
)
SELECT * FROM GetCost
Thanks
August 20, 2009 at 1:02 pm
I've coded an iterative replacement but it lack the optimization of the CTE. Still if someone could get the CTE to work I would be very grateful.
Here's the working example of what I'm looking for:
(the LevelDepth column was added from the previous example only)
DECLARE @Table TABLE (
HeaderID INT NOT NULL
, InQty DECIMAL (18,6)
, UsQty DECIMAL (18,6)
, AvCost DECIMAL (18,6)
, AvCost2 DECIMAL (18,6)
, Qty DECIMAL (18,6)
, ParentID INT
, ChildID INT
, LevelDepth INT
)
INSERT INTO @Table VALUES (49, 1.0, 350.0, 13.59, 0.194131, 5.0, NULL, NULL, 1)
INSERT INTO @Table VALUES (51, 1.0, 1000.0, 107.569, 1.860552, 8.0, NULL, 10, 1)
INSERT INTO @Table VALUES (34, 1.0, 350.0, 17.99, 3.747917, 10.0, 10, NULL, 2)
INSERT INTO @Table VALUES (37, 1.0, 1000.0, 5.25, 0.010500, 20.0, 10, NULL, 2)
INSERT INTO @Table VALUES (36, 1.0, 75.0, 10.0, 2.666667, 20.0, 10, NULL, 2)
INSERT INTO @Table VALUES (35, 1.0, 75.0, 12.95, 3.453333, 20.0, 10, NULL, 2)
INSERT INTO @Table VALUES (32, 1.0, 17.0, 28.99, 53.290441, 31.25, 10, NULL, 2)
INSERT INTO @Table VALUES (50, 2.21, 16.0, 6.35, 0.179581, 1.0, NULL, NULL, 1)
INSERT INTO @Table VALUES (33, 1.0, 16.0, 3.14, 44.400452, 500.0, 10, NULL, 2)
--SELECT * FROM @Table
DECLARE @i AS INT = 0
DECLARE @MaxLoop AS INT
SELECT @MaxLoop = MAX(LevelDepth) FROM @Table
WHILE @MaxLoop >= 1
BEGIN
--SELECT ParentID, SUM(AvCost2) AS 'TheTotal' FROM @Table WHERE LevelDepth = @MaxLoop GROUP BY ParentID
UPDATE @Table SET AvCost2 = ChildSum.TheTotal / t.InQty / t.UsQty * t.Qty
FROM @Table t
INNER JOIN (SELECT ParentID, SUM(AvCost2) AS 'TheTotal' FROM @Table WHERE LevelDepth = @MaxLoop GROUP BY ParentID) ChildSum
ONt.ChildID = ChildSum.ParentID
SET @MaxLoop-=1
END
SELECT SUM(AvCost2) FROM @Table WHERE LevelDepth = 1
August 21, 2009 at 1:51 pm
This is one of the way you can do it.
DECLARE @Table TABLE (
HeaderID INT NOT NULL
, InQty DECIMAL (18,6)
, UsQty DECIMAL (18,6)
, AvCost DECIMAL (18,6)
, AvCost2 DECIMAL (18,6)
, Qty DECIMAL (18,6)
, ParentID INT
, ChildID INT
)
INSERT INTO @Table VALUES (49, 1.0, 350.0, 13.59, 0.194131, 5.0, NULL, NULL)
INSERT INTO @Table VALUES (51, 1.0, 1000.0, 107.569, 1.860552, 8.0, NULL, 10)
INSERT INTO @Table VALUES (34, 1.0, 350.0, 17.99, 3.747917, 10.0, 10, NULL)
INSERT INTO @Table VALUES (37, 1.0, 1000.0, 5.25, 0.010500, 20.0, 10, NULL)
INSERT INTO @Table VALUES (36, 1.0, 75.0, 10.0, 2.666667, 20.0, 10, NULL)
INSERT INTO @Table VALUES (35, 1.0, 75.0, 12.95, 3.453333, 20.0, 10, NULL)
INSERT INTO @Table VALUES (32, 1.0, 17.0, 28.99, 53.290441, 31.25, 10, NULL)
INSERT INTO @Table VALUES (50, 2.21, 16.0, 6.35, 0.179581, 1.0, NULL, NULL)
INSERT INTO @Table VALUES (33, 1.0, 16.0, 3.14, 44.400452, 500.0, 10, NULL)
SELECT * FROM @Table
;
WITH GetCost (ParentID, AvCost2,ChildID) AS
(
SELECT
ParentID,
SUM (AvCost2) AS 'AvCost2',
ChildID
FROM
@Table
GROUP BY ParentID,ChildID
UNION ALL
SELECT
tt.ParentID,
CAST(itmCost.AvCost2 / tt.InQty / tt.UsQty * tt.Qty as DECIMAL(38,6)) ,
itmCost.ChildID
FROM
@Table tt
INNER JOIN GetCost itmCost
ON tt.ChildID = itmCost.ParentID
)
SELECT SUM(AvCost2) FROM GetCost
WHERE ChildID IS NULL and ParentID IS NULL
August 21, 2009 at 2:02 pm
Thank you for taking time! I'll take a look at the solution and understand what I did wrong with mine.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply