Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


[Help Needed] Looking for a solution for this case


[Help Needed] Looking for a solution for this case

Author
Message
sol.nt
sol.nt
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 136
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.png


I have come up with several approach but no help Sad

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 BigGrin

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
shani19831
shani19831
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 215
Hi there,

The image you posted is not opopeningp dude.

Regards,
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3003 Visits: 5007
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/
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
sol.nt
sol.nt
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 136
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 BigGrin

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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
sol.nt
sol.nt
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 136
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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search