[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.png

    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-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 Dhasian

    How 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 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![/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