Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

[Help Needed] Looking for a solution for this case Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 2:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 image


https://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

///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 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
Post #1357197
Posted Tuesday, September 11, 2012 2:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
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!

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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
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 Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1357249
Posted Tuesday, September 11, 2012 4:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
Kingston Dhasian (9/11/2012)
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


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!

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

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 :D

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

Post #1357761
Posted Wednesday, September 12, 2012 8:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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 @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 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
Post #1358009
Posted Friday, September 14, 2012 1:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 @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 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
Post #1359082
Posted Friday, September 14, 2012 1:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse