[Help Needed] Looking for a solution for this case
-
Hi all,
I have a task which required me to sum value of all children level from bottom to top as this image
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.pngI have come up with several approach but no help 🙁
Anyone with any advice are welcome
Regards,
sol
///Edit
Data was created by @dwain.c
DECLARE @t TABLE
(node VARCHAR(10), parent VARCHAR(10), value INT)
INSERT INTO @t
SELECT 'lv3a', 'lv2a', 15
UNION ALL SELECT 'lv3b', 'lv2a', 4
UNION ALL SELECT 'lv3c', 'lv2b', 0
UNION ALL SELECT 'lv3d', 'lv2c', 10
UNION ALL SELECT 'lv3e', 'lv2d', 2
UNION ALL SELECT 'lv3f', 'lv2d', 15
UNION ALL SELECT 'lv2a', 'lv1a', 3
UNION ALL SELECT 'lv2b', 'lv1a', 5
UNION ALL SELECT 'lv2c', 'lv1a', 7
UNION ALL SELECT 'lv2d', 'lv1b', 30
UNION ALL SELECT 'lv1a', 'root', 30
UNION ALL SELECT 'lv1b', 'root', 10
UNION ALL SELECT 'root', NULL, 100
///Edit 2
This is the solution of @Jason-299789, i put this in here in case someone needed
DECLARE @t TABLE
(node VARCHAR(10), parent VARCHAR(10), value INT, levelno smallint,Total int)
INSERT INTO @t
SELECT 'lv3a', 'lv2a', 15,NULL,NULL
UNION ALL SELECT 'lv3b', 'lv2a', 4,NULL,NULL
UNION ALL SELECT 'lv3c', 'lv2b', 0,NULL,NULL
UNION ALL SELECT 'lv3d', 'lv2c', 10,NULL,NULL
UNION ALL SELECT 'lv3e', 'lv2d', 2,NULL,NULL
UNION ALL SELECT 'lv3f', 'lv2d', 15,NULL,NULL
UNION ALL SELECT 'lv2a', 'lv1a', 3,NULL,NULL
UNION ALL SELECT 'lv2b', 'lv1a', 5,NULL,NULL
UNION ALL SELECT 'lv2c', 'lv1a', 7,NULL,NULL
UNION ALL SELECT 'lv2d', 'lv1b', 30,NULL,NULL
UNION ALL SELECT 'lv1a', 'root', 30,NULL,NULL
UNION ALL SELECT 'lv1b', 'root', 10,NULL,NULL
UNION ALL SELECT 'root', NULL, 100,NULL,NULL
Select * from @t
;WITH CTE_Level (node,parent,value, levelno)
AS
(
Select node,parent, value,0
From @t
Where parent is NULL
UNION ALL
Select a.node,a.parent, a.value,x.levelno+1
From @t a
JOIN CTE_Level x on x.node=a.parent
)
Update
x
Set
levelno=y.levelno
from @t x
JOIN CTE_Level y on y.node=x.node
Declare @max-2 int=(Select MAX(levelno) from @t)
Declare @count int
Update
@t
Set
Total=value
where
levelno=@max
Set @count=@max-1
While @count>-1
Begin
Update
x
Set Total=value-ABS(y.Total)
From @t x
JOIN
(Select a.node,SUM(ABS(b.Total)) Total
from @t a
JOIN @t b on b.parent=a.node
Group by a.node) y on x.node=y.node
where x.levelno=@count
set @count=@count-1
End
Select * from @t order by levelno
/// Edit 3
This is my solution without modify anything
-- Create table Type to hold the tree, this make sense when the dataset is small but i think it will be fine with a
-- large dataset too
CREATE TYPE ty01 AS TABLE
(
node VARCHAR(10), parent VARCHAR(10), value INT , slevel INT
)
G
-- function to do a recursive on the tree
Create FUNCTION sumtotal
(
@iparent VARCHAR(10),
@ipvalue INT ,
@ity01 ty01 READONLY
)
RETURNS @t TABLE (parent VARCHAR(10), a INT)
AS
BEGIN
DECLARE @a INT
SELECT @a = @ipvalue - (SUM(ISNULL(ABS(tt.a),0 ))) FROM @ity01 t CROSS APPLY dbo.sumtotal(t.node,t.VALUE, @ity01) tt
WHERE t.parent = @iparent
INSERT INTO @t VALUES (@iparent,ISNULL(@a,-@ipvalue) )
RETURN
END
GO
-- And profit :D
DECLARE @t TABLE
(node VARCHAR(10), parent VARCHAR(10), value INT , slevel INT )
INSERT INTO @t
SELECT 'lv3a', 'lv2a', 15 , 4
UNION ALL SELECT 'lv3b', 'lv2a', 4, 4
UNION ALL SELECT 'lv3c', 'lv2b', 0, 4
UNION ALL SELECT 'lv3d', 'lv2c', 10, 4
UNION ALL SELECT 'lv3e', 'lv2d', 2, 4
UNION ALL SELECT 'lv3f', 'lv2d', 15, 4
UNION ALL SELECT 'lv2a', 'lv1a', 3, 3
UNION ALL SELECT 'lv2b', 'lv1a', 5, 3
UNION ALL SELECT 'lv2c', 'lv1a', 7, 3
UNION ALL SELECT 'lv2d', 'lv1b', 30, 3
UNION ALL SELECT 'lv1a', 'root', 30 , 2
UNION ALL SELECT 'lv1b', 'root', 10 , 2
UNION ALL SELECT 'root', NULL, 100 , 1
DECLARE @ty ty01
INSERT INTO @ty
SELECT a.node, a.parent
,a.value AS svalue, a.slevel AS childlevel
FROM @t a INNER JOIN @t b
ON a.parent = b.node
SELECT * FROM @ty t CROSS APPLY dbo.sumtotal(t.node,t.value,@ty)
Hope this is usefully
Regards,
Sol
-
Hi there,
The image you posted is not opopeningp dude.
Regards,
-
shani19831 (9/11/2012)
Hi there,The image you posted is not opopeningp dude.
Regards,
The image does show when you access the link. Interesting problem but it won't be easy to come up with a general solution for n levels. However I can give you a start by solving only the 4 levels shown.
First though, when posting on this forum you really do need to try to give us some DDL and sample data in consumable form as follows:
DECLARE @t TABLE
(node VARCHAR(10), parent VARCHAR(10), value INT)
INSERT INTO @t
SELECT 'lv3a', 'lv2a', 15
UNION ALL SELECT 'lv3b', 'lv2a', 4
UNION ALL SELECT 'lv3c', 'lv2b', 0
UNION ALL SELECT 'lv3d', 'lv2c', 10
UNION ALL SELECT 'lv3e', 'lv2d', 2
UNION ALL SELECT 'lv3f', 'lv2d', 15
UNION ALL SELECT 'lv2a', 'lv1a', 3
UNION ALL SELECT 'lv2b', 'lv1a', 5
UNION ALL SELECT 'lv2c', 'lv1a', 7
UNION ALL SELECT 'lv2d', 'lv1b', 30
UNION ALL SELECT 'lv1a', 'root', 30
UNION ALL SELECT 'lv1b', 'root', 10
UNION ALL SELECT 'root', NULL, 100
Since, as I said the problem looked mighty interesting, I decided to take care of this for you, so I could try to come up with what follows:
;WITH rCTE AS (
SELECT lvl=1, node, parent, value
FROM @t
WHERE parent IS NULL
UNION ALL
SELECT lvl+1, a.node, a.parent, a.value
FROM @t a
INNER JOIN rCTE b ON b.node = a.parent
),
SumsLvl4 AS (
SELECT parent, value=SUM(value)
FROM rCTE
WHERE lvl = 4
GROUP BY parent
),
SumsLvl3 AS (
SELECT a.parent, value=ABS(a.value - b.value)
FROM rCTE a
INNER JOIN SumsLvl4 b ON a.node = b.parent
),
SumsLvl2 AS (
SELECT a.parent,value=ABS(a.value - b.value)
FROM rCTE a
INNER JOIN (
SELECT parent, value=SUM(value)
FROM SumsLvl3
GROUP BY parent
) b ON a.node = b.parent
)
SELECT a.parent,value=a.value - b.value
FROM rCTE a
INNER JOIN (
SELECT parent, value=SUM(value)
FROM SumsLvl2
GROUP BY parent
) b ON a.node = b.parent
The next thing you'll say is that you want to see all the intermediate results and/or handle more than 4 levels, at which point I'll probably have to say ... best of luck to you mate!
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Please describe the problem you have along with DDL, some sample data and the expected results
Check the link in my signature if don't know how to do this
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Kingston Dhasian (9/11/2012)
Please describe the problem you have along with DDL, some sample data and the expected resultsCheck the link in my signature if don't know how to do this
You might want to try with my sample data.
The 10 minute version of a solution I posted simply wasn't generalized. That would have taken much more time.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
First of all,
Sorry that i don't follow the rule of forum, i will do that when create next threads 🙂
@dwain.c: thanks for your data and solution. I will edit the first post to add your data, hope you don't mind 😀
About your solution, yes, as you said i need a solution which come up with n level of parent-child tree.
Thanks and Regards,
Sol
-
This seems like a piece of course work or a test thats been set internally by a senior.
However this code will work for any number of levels required though technically not elegant it does work
DECLARE @t TABLE
(node VARCHAR(10), parent VARCHAR(10), value INT, levelno smallint,Total int)
INSERT INTO @t
SELECT 'lv3a', 'lv2a', 15,NULL,NULL
UNION ALL SELECT 'lv3b', 'lv2a', 4,NULL,NULL
UNION ALL SELECT 'lv3c', 'lv2b', 0,NULL,NULL
UNION ALL SELECT 'lv3d', 'lv2c', 10,NULL,NULL
UNION ALL SELECT 'lv3e', 'lv2d', 2,NULL,NULL
UNION ALL SELECT 'lv3f', 'lv2d', 15,NULL,NULL
UNION ALL SELECT 'lv2a', 'lv1a', 3,NULL,NULL
UNION ALL SELECT 'lv2b', 'lv1a', 5,NULL,NULL
UNION ALL SELECT 'lv2c', 'lv1a', 7,NULL,NULL
UNION ALL SELECT 'lv2d', 'lv1b', 30,NULL,NULL
UNION ALL SELECT 'lv1a', 'root', 30,NULL,NULL
UNION ALL SELECT 'lv1b', 'root', 10,NULL,NULL
UNION ALL SELECT 'root', NULL, 100,NULL,NULL
Select * from @t
;WITH CTE_Level (node,parent,value, levelno)
AS
(
Select node,parent, value,0
From @t
Where parent is NULL
UNION ALL
Select a.node,a.parent, a.value,x.levelno+1
From @t a
JOIN CTE_Level x on x.node=a.parent
)
Update
x
Set
levelno=y.levelno
from @t x
JOIN CTE_Level y on y.node=x.node
Declare @max-2 int=(Select MAX(levelno) from @t)
Declare @count int
Update
@t
Set
Total=value
where
levelno=@max
Set @count=@max-1
While @count>-1
Begin
Update
x
Set Total=value-ABS(y.Total)
From @t x
JOIN
(Select a.node,SUM(ABS(b.Total)) Total
from @t a
JOIN @t b on b.parent=a.node
Group by a.node) y on x.node=y.node
where x.levelno=@count
set @count=@count-1
End
Select * from @t order by levelno
You might be able to get away with a second CTE rather than using the While loop, as on a large data set it wont perform.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Jason-299789 (9/12/2012)
This seems like a piece of course work or a test thats been set internally by a senior.However this code will work for any number of levels required though technically not elegant it does work
DECLARE @t TABLE
(node VARCHAR(10), parent VARCHAR(10), value INT, levelno smallint,Total int)
INSERT INTO @t
SELECT 'lv3a', 'lv2a', 15,NULL,NULL
UNION ALL SELECT 'lv3b', 'lv2a', 4,NULL,NULL
UNION ALL SELECT 'lv3c', 'lv2b', 0,NULL,NULL
UNION ALL SELECT 'lv3d', 'lv2c', 10,NULL,NULL
UNION ALL SELECT 'lv3e', 'lv2d', 2,NULL,NULL
UNION ALL SELECT 'lv3f', 'lv2d', 15,NULL,NULL
UNION ALL SELECT 'lv2a', 'lv1a', 3,NULL,NULL
UNION ALL SELECT 'lv2b', 'lv1a', 5,NULL,NULL
UNION ALL SELECT 'lv2c', 'lv1a', 7,NULL,NULL
UNION ALL SELECT 'lv2d', 'lv1b', 30,NULL,NULL
UNION ALL SELECT 'lv1a', 'root', 30,NULL,NULL
UNION ALL SELECT 'lv1b', 'root', 10,NULL,NULL
UNION ALL SELECT 'root', NULL, 100,NULL,NULL
Select * from @t
;WITH CTE_Level (node,parent,value, levelno)
AS
(
Select node,parent, value,0
From @t
Where parent is NULL
UNION ALL
Select a.node,a.parent, a.value,x.levelno+1
From @t a
JOIN CTE_Level x on x.node=a.parent
)
Update
x
Set
levelno=y.levelno
from @t x
JOIN CTE_Level y on y.node=x.node
Declare @max-2 int=(Select MAX(levelno) from @t)
Declare @count int
Update
@t
Set
Total=value
where
levelno=@max
Set @count=@max-1
While @count>-1
Begin
Update
x
Set Total=value-ABS(y.Total)
From @t x
JOIN
(Select a.node,SUM(ABS(b.Total)) Total
from @t a
JOIN @t b on b.parent=a.node
Group by a.node) y on x.node=y.node
where x.levelno=@count
set @count=@count-1
End
Select * from @t order by levelno
You might be able to get away with a second CTE rather than using the While loop, as on a large data set it wont perform.
Thanks for your reply 🙂
Your code will work but which modified design of table and update the result of every node to that table
This will solve the "idea" of my question but not as i expected in my case
I have solved this myself without modified anything and can using with any level, i put my code in the OP so that everyone will find the answer quickly asap
Thanks and regards,
sol
-
hi sol,
You can put the data into an alternate temp table or table variable to do the calcs, but thought it was a bit messy so revisited it before submitting so that it used the base code.
Edit :cross post.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Sol, Just looking at your solution It works well except that it doesnt return the Root node value, so I would suggest a Left Outer join on the Insert into @ty and it should all be good.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Yes, left join is fixed better than inner join in this case 🙂
In my case, i don't need to calc to root node so inner join is find for me
Regards,
sol
-
:-D.
looking at the supplied image it implied you also needed to calculate the root node value as well. 😉
I do like the code especially the nested Table function call, its an interesting solution, but there is a limit on the number of recursions that T-SQL can do in functions and SP's which is 32.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply