Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 [Help Needed] Looking for a solution for this case Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, September 11, 2012 2:43 AM
 Valued Member Group: General Forum Members Last Login: Friday, May 24, 2013 3:49 AM Points: 69, Visits: 134
 Hi all, I have a task which required me to sum value of all children level from bottom to top as this imagehttps://www.dropbox.com/s/vrylso2vvxfaidz/groupsum.jpg`https://www.dropbox.com/s/vrylso2vvxfaidz/groupsum.jpg`I have come up with several approach but no help Anyone with any advice are welcome Regards, sol///EditData was created by @dwain.c`DECLARE @t TABLE (node VARCHAR(10), parent VARCHAR(10), value INT)INSERT INTO @tSELECT 'lv3a', 'lv2a', 15UNION ALL SELECT 'lv3b', 'lv2a', 4UNION ALL SELECT 'lv3c', 'lv2b', 0UNION ALL SELECT 'lv3d', 'lv2c', 10UNION ALL SELECT 'lv3e', 'lv2d', 2UNION ALL SELECT 'lv3f', 'lv2d', 15UNION ALL SELECT 'lv2a', 'lv1a', 3UNION ALL SELECT 'lv2b', 'lv1a', 5UNION ALL SELECT 'lv2c', 'lv1a', 7UNION ALL SELECT 'lv2d', 'lv1b', 30UNION ALL SELECT 'lv1a', 'root', 30UNION ALL SELECT 'lv1b', 'root', 10UNION ALL SELECT 'root', NULL, 100`///Edit 2This 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 @tSELECT 'lv3a', 'lv2a', 15,NULL,NULLUNION ALL SELECT 'lv3b', 'lv2a', 4,NULL,NULLUNION ALL SELECT 'lv3c', 'lv2b', 0,NULL,NULLUNION ALL SELECT 'lv3d', 'lv2c', 10,NULL,NULLUNION ALL SELECT 'lv3e', 'lv2d', 2,NULL,NULLUNION ALL SELECT 'lv3f', 'lv2d', 15,NULL,NULLUNION ALL SELECT 'lv2a', 'lv1a', 3,NULL,NULLUNION ALL SELECT 'lv2b', 'lv1a', 5,NULL,NULLUNION ALL SELECT 'lv2c', 'lv1a', 7,NULL,NULLUNION ALL SELECT 'lv2d', 'lv1b', 30,NULL,NULLUNION ALL SELECT 'lv1a', 'root', 30,NULL,NULLUNION ALL SELECT 'lv1b', 'root', 10,NULL,NULLUNION ALL SELECT 'root', NULL, 100,NULL,NULLSelect * 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 xSet levelno=y.levelnofrom @t x JOIN CTE_Level y on y.node=x.nodeDeclare @max int=(Select MAX(levelno) from @t)Declare @count intUpdate @t Set Total=valuewhere levelno=@maxSet @count=@max-1While @count>-1BeginUpdate xSet 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.nodewhere x.levelno=@countset @count=@count-1End Select * from @t order by levelno`/// Edit 3This 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 tooCREATE TYPE ty01 AS TABLE( node VARCHAR(10), parent VARCHAR(10), value INT , slevel INT )G -- function to do a recursive on the treeCreate 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 :DDECLARE @t TABLE (node VARCHAR(10), parent VARCHAR(10), value INT , slevel INT )INSERT INTO @tSELECT 'lv3a', 'lv2a', 15 , 4UNION ALL SELECT 'lv3b', 'lv2a', 4, 4UNION ALL SELECT 'lv3c', 'lv2b', 0, 4UNION ALL SELECT 'lv3d', 'lv2c', 10, 4UNION ALL SELECT 'lv3e', 'lv2d', 2, 4UNION ALL SELECT 'lv3f', 'lv2d', 15, 4UNION ALL SELECT 'lv2a', 'lv1a', 3, 3UNION ALL SELECT 'lv2b', 'lv1a', 5, 3UNION ALL SELECT 'lv2c', 'lv1a', 7, 3UNION ALL SELECT 'lv2d', 'lv1b', 30, 3UNION ALL SELECT 'lv1a', 'root', 30 , 2UNION ALL SELECT 'lv1b', 'root', 10 , 2UNION ALL SELECT 'root', NULL, 100 , 1DECLARE @ty ty01INSERT 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
Post #1357197
 Posted Tuesday, September 11, 2012 2:51 AM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, November 26, 2013 12:26 AM Points: 47, Visits: 180
 Hi there,The image you posted is not opopeningp dude.Regards,
Post #1357209
 Posted Tuesday, September 11, 2012 3:50 AM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 5:42 PM Points: 3,596, Visits: 5,112
 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 @tSELECT 'lv3a', 'lv2a', 15UNION ALL SELECT 'lv3b', 'lv2a', 4UNION ALL SELECT 'lv3c', 'lv2b', 0UNION ALL SELECT 'lv3d', 'lv2c', 10UNION ALL SELECT 'lv3e', 'lv2d', 2UNION ALL SELECT 'lv3f', 'lv2d', 15UNION ALL SELECT 'lv2a', 'lv1a', 3UNION ALL SELECT 'lv2b', 'lv1a', 5UNION ALL SELECT 'lv2c', 'lv1a', 7UNION ALL SELECT 'lv2d', 'lv1b', 30UNION ALL SELECT 'lv1a', 'root', 30UNION ALL SELECT 'lv1b', 'root', 10UNION 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!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!
Post #1357247
 Posted Tuesday, September 11, 2012 3:52 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 3:57 AM Points: 2,570, Visits: 4,655
 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 Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Modenhttp://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1357249
 Posted Tuesday, September 11, 2012 4:07 AM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 5:42 PM Points: 3,596, Visits: 5,112
 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 thisYou 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!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!
Post #1357265
 Posted Tuesday, September 11, 2012 8:00 PM
 Valued Member Group: General Forum Members Last Login: Friday, May 24, 2013 3:49 AM Points: 69, Visits: 134
 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 :DAbout your solution, yes, as you said i need a solution which come up with n level of parent-child tree. Thanks and Regards,Sol
Post #1357761
 Posted Wednesday, September 12, 2012 8:28 AM
 SSC Eights! Group: General Forum Members Last Login: Thursday, April 17, 2014 7:37 AM Points: 860, Visits: 2,323
 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 @tSELECT 'lv3a', 'lv2a', 15,NULL,NULLUNION ALL SELECT 'lv3b', 'lv2a', 4,NULL,NULLUNION ALL SELECT 'lv3c', 'lv2b', 0,NULL,NULLUNION ALL SELECT 'lv3d', 'lv2c', 10,NULL,NULLUNION ALL SELECT 'lv3e', 'lv2d', 2,NULL,NULLUNION ALL SELECT 'lv3f', 'lv2d', 15,NULL,NULLUNION ALL SELECT 'lv2a', 'lv1a', 3,NULL,NULLUNION ALL SELECT 'lv2b', 'lv1a', 5,NULL,NULLUNION ALL SELECT 'lv2c', 'lv1a', 7,NULL,NULLUNION ALL SELECT 'lv2d', 'lv1b', 30,NULL,NULLUNION ALL SELECT 'lv1a', 'root', 30,NULL,NULLUNION ALL SELECT 'lv1b', 'root', 10,NULL,NULLUNION ALL SELECT 'root', NULL, 100,NULL,NULLSelect * 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 xSet levelno=y.levelnofrom @t x JOIN CTE_Level y on y.node=x.nodeDeclare @max int=(Select MAX(levelno) from @t)Declare @count intUpdate @t Set Total=valuewhere levelno=@maxSet @count=@max-1While @count>-1BeginUpdate xSet 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.nodewhere x.levelno=@countset @count=@count-1End 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
Post #1358009
 Posted Friday, September 14, 2012 1:25 AM
 Valued Member Group: General Forum Members Last Login: Friday, May 24, 2013 3:49 AM Points: 69, Visits: 134
 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 @tSELECT 'lv3a', 'lv2a', 15,NULL,NULLUNION ALL SELECT 'lv3b', 'lv2a', 4,NULL,NULLUNION ALL SELECT 'lv3c', 'lv2b', 0,NULL,NULLUNION ALL SELECT 'lv3d', 'lv2c', 10,NULL,NULLUNION ALL SELECT 'lv3e', 'lv2d', 2,NULL,NULLUNION ALL SELECT 'lv3f', 'lv2d', 15,NULL,NULLUNION ALL SELECT 'lv2a', 'lv1a', 3,NULL,NULLUNION ALL SELECT 'lv2b', 'lv1a', 5,NULL,NULLUNION ALL SELECT 'lv2c', 'lv1a', 7,NULL,NULLUNION ALL SELECT 'lv2d', 'lv1b', 30,NULL,NULLUNION ALL SELECT 'lv1a', 'root', 30,NULL,NULLUNION ALL SELECT 'lv1b', 'root', 10,NULL,NULLUNION ALL SELECT 'root', NULL, 100,NULL,NULLSelect * 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 xSet levelno=y.levelnofrom @t x JOIN CTE_Level y on y.node=x.nodeDeclare @max int=(Select MAX(levelno) from @t)Declare @count intUpdate @t Set Total=valuewhere levelno=@maxSet @count=@max-1While @count>-1BeginUpdate xSet 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.nodewhere x.levelno=@countset @count=@count-1End 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 tableThis will solve the "idea" of my question but not as i expected in my caseI 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 asapThanks and regards, sol
Post #1359082
 Posted Friday, September 14, 2012 1:38 AM
 SSC Eights! Group: General Forum Members Last Login: Thursday, April 17, 2014 7:37 AM Points: 860, Visits: 2,323
 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
Post #1359085
 Posted Friday, September 14, 2012 1:50 AM
 SSC Eights! Group: General Forum Members Last Login: Thursday, April 17, 2014 7:37 AM Points: 860, Visits: 2,323
 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
Post #1359097

 Permissions