Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » SQL Server 2008 » T-SQL (SS2K8) » [Help Needed] Looking for a solution for this...
Add to briefcase
12 posts, Page 1 of 2
12»»

[Help Needed] Looking for a solution for this case Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
sol.nt
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: Monday, May 13, 2013 3:59 AM
Points: 69, Visits: 133
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
shani19831
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: Today @ 4:21 AM
Points: 37, Visits: 125
Hi there,

The image you posted is not opopeningp dude.

Regards,
Post #1357209
dwain.c
Posted Tuesday, September 11, 2012 3:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 2,340, Visits: 3,167
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!

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1357247
Kingston Dhasian
Posted Tuesday, September 11, 2012 3:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 2,217, Visits: 4,171
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
dwain.c
Posted Tuesday, September 11, 2012 4:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 2,340, Visits: 3,167
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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1357265
sol.nt
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: Monday, May 13, 2013 3:59 AM
Points: 69, Visits: 133
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
Jason-299789
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: Yesterday @ 3:38 AM
Points: 803, Visits: 2,123
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
sol.nt
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: Monday, May 13, 2013 3:59 AM
Points: 69, Visits: 133
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
Jason-299789
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: Yesterday @ 3:38 AM
Points: 803, Visits: 2,123
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
Jason-299789
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: Yesterday @ 3:38 AM
Points: 803, Visits: 2,123
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 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.