total inside cte

  • declare @hierarchy table(id int,name nvarchar(40),parentid int,cost int)

    insert into @hierarchy

    select 1 as id,'a' as name , null as parentid , null as cost union all

    select 2 as id,'b' as name , 1 as parentid , null as cost union all

    select 3 as id,'c' as name , 2 as parentid , null as cost union all

    select 4 as id,'d' as name , 3 as parentid , 100 as cost union all

    select 5 as id,'e' as name , 3 as parentid , 200 as cost union all

    select 6 as id,'f' as name , 3 as parentid , 400 as cost union all

    select 10 as id,'g' as name , 2 as parentid , null as cost union all

    select 11 as id,'h' as name , 10 as parentid , 50 as cost union all

    select 12 as id,'i' as name , 10 as parentid , 50 as cost

    declare @table table (id int,name nvarchar(40),parentid int,cost int,serial nvarchar(10))

    ;with cte

    as

    (

    select ID,name,parentid,cost,CAST(id as varchar(max)) as serial from @hierarchy where parentid is null

    union all

    select a.id,a.name,a.parentid,a.cost,cte.serial +'.'+ cast(a.ID as varchar(max)) as serial from @hierarchy a

    join cte on a.parentid=cte.id

    )

    insert into @table

    select * from cte

    --update @table

    --set cost=b.newcost

    --from @table a join dbo.total b

    --on a.id=b.id

    select * from @table

    -- i tried to create this view and run update against this but it won't give me result for record 1 ,2

    -- this need to recursively called.

    --alter view total

    --as

    --select parentid as id,SUM(cost) as newcost from hierarchy group by parentid

    -- required output

    select 1 as id,'a' as name , null as parentid , 800 as cost,'1' as serial union all

    select 2 as id,'b' as name , 1 as parentid , 800 as cost,'1.2' as serial union all

    select 3 as id,'c' as name , 2 as parentid , 700 as cost,'1.2.3' as serial union all

    select 10 as id,'g' as name , 2 as parentid , 100 as cost,'1.2.10' as serial union all

    select 11 as id,'h' as name , 10 as parentid , 50 as cost,'1.2.10.11' as serial union all

    select 12 as id,'i' as name , 10 as parentid , 50 as cost,'1.2.10.12' as serial union all

    select 4 as id,'d' as name , 3 as parentid , 100 as cost,'1.2.3.4' as serial union all

    select 5 as id,'e' as name , 3 as parentid , 200 as cost,'1.2.3.5' as serial union all

    select 6 as id,'f' as name , 3 as parentid , 400 as cost,'1.2.3.6' as serial

    please some one can it be solved with cte or do i need to use any other thing.

  • First, thank you for providing the data in a readily consumable format. It helps a lot. 🙂

    Here's your original data reformatted and put into a Temp Table just because I prefer working with Temp Tables instead of Table Variables. I didn't include any of the essential indexes because I don't know what you have for indexes already.

    --===== Conditionally drop Temp Tables to make reruns in SSMS easier

    IF OBJECT_ID('tempdb..#Hierarchy','U') IS NOT NULL DROP TABLE #Hierarchy;

    IF OBJECT_ID('tempdb..#HierarchyNew','U') IS NOT NULL DROP TABLE #HierarchyNew;

    GO

    --===== Create the test table

    CREATE TABLE #Hierarchy

    (

    ID INT,

    Name NVARCHAR(40),

    ParentID INT,

    Cost INT

    )

    ;

    --===== Populate the test table with data provide by the OP

    INSERT INTO #Hierarchy

    (ID, Name, ParentID, Cost)

    SELECT 1 AS ID, 'a' AS Name, NULL AS ParentID, NULL AS Cost UNION ALL

    SELECT 2 AS ID, 'b' AS Name, 1 AS ParentID, NULL AS Cost UNION ALL

    SELECT 3 AS ID, 'c' AS Name, 2 AS ParentID, NULL AS Cost UNION ALL

    SELECT 4 AS ID, 'd' AS Name, 3 AS ParentID, 100 AS Cost UNION ALL

    SELECT 5 AS ID, 'e' AS Name, 3 AS ParentID, 200 AS Cost UNION ALL

    SELECT 6 AS ID, 'f' AS Name, 3 AS ParentID, 400 AS Cost UNION ALL

    SELECT 10 AS ID, 'g' AS Name, 2 AS ParentID, NULL AS Cost UNION ALL

    SELECT 11 AS ID, 'h' AS Name, 10 AS ParentID, 50 AS Cost UNION ALL

    SELECT 12 AS ID, 'i' AS Name, 10 AS ParentID, 50 AS Cost

    ;

    Here's the solution to your problem. It assumes you know a bit about Hierarchies and both what a Tally Table is and how it works to replace certain WHILE loops. If you don't know, then please see the article at the following link:

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    Yes, the following code could be done all in one query... it's just not my nature to do so. I like to save the "interim" steps especially for troubleshooting.

    --===== Build a "combined" table that will hold both the "Adjacency List"

    -- and the near equivalent of the HierarchyID datatype but in 2005.

    WITH cteBuildPath AS

    ( --=== This "anchor" section finds the top dog with a NULL as a parent

    SELECT anchor.ID,

    anchor.ParentID,

    anchor.Name,

    anchor.Cost,

    CAST(CAST(anchor.ID AS BINARY(4)) AS VARBINARY(1000)) AS HierarchyPath,

    CAST(CAST(anchor.ID AS VARCHAR(10)) AS VARCHAR(1000)) AS HumanReadablePath,

    1 AS HierarchyLevel

    FROM #Hierarchy anchor

    WHERE ParentID IS NULL

    UNION ALL ---------------------------------------------------------------------------------------

    --=== This "recusive" section puts the rest of the hierarchy together.

    SELECT recur.ID,

    recur.ParentID,

    recur.Name,

    recur.Cost,

    CAST(rcte.HierarchyPath + CAST(recur.ID AS BINARY(4)) AS VARBINARY(1000)) AS HierarchyPath,

    CAST(rcte.HumanReadablePath + '.' + CAST(recur.ID AS VARCHAR(10)) AS VARCHAR(1000)) AS HumanReadablePath,

    rcte.HierarchyLevel + 1 AS HierarchyLevel

    FROM #Hierarchy recur

    INNER JOIN cteBuildPath rcte

    ON rcte.ID = recur.ParentID

    ) --=== This builds our new hierarchy table from the rCTE above.

    SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY HierarchyPath),0) AS SortOrder,

    ID,

    ParentID,

    Name,

    CAST(Cost AS BIGINT) AS Cost,

    HierarchyPath,

    HumanReadablePath,

    HierarchyLevel

    INTO #HierarchyNew

    FROM cteBuildPath

    ;

    --===== This little bit of computational heaven does what you want...

    -- and could really do so much more!

    WITH

    cteSplit AS

    ( --=== Splits the path into elements so we can aggregate costs by ID

    SELECT CAST(SUBSTRING(h.HierarchyPath,(t.N*4)-3,4) AS INT) AS ID,

    h.Cost AS Cost

    FROM dbo.Tally AS t WITH(NOLOCK)

    CROSS JOIN #HierarchyNew AS h WITH(TABLOCKX)

    WHERE t.N BETWEEN 1 AND DATALENGTH(h.HierarchyPath)/4

    )

    ,

    cteAggregate AS

    ( --=== Does the required totals by ID.

    SELECT ID,

    SUM(Cost) AS NodeDownLineTotalCost

    FROM cteSplit

    GROUP BY ID

    ) --=== Display the answer as requested.

    SELECT hn.ID,

    hn.Name,

    hn.ParentID,

    Cost = agg.NodeDownLineTotalCost,

    Serial = hn.HumanReadablePath

    FROM cteAggregate agg

    INNER JOIN #HierarchyNew hn

    ON agg.ID = hn.ID

    WHERE agg.ID > 0

    ORDER BY /*UGH!*/ Serial

    ;

    Here's the output I get... I'd rather see it in the proper sort order but I did it your way instead...

    ID Name ParentID Cost Serial

    -- ---- -------- ---- ---------

    1 a NULL 800 1

    2 b 1 800 1.2

    10 g 2 100 1.2.10

    11 h 10 50 1.2.10.11

    12 i 10 50 1.2.10.12

    3 c 2 700 1.2.3

    4 d 3 100 1.2.3.4

    5 e 3 200 1.2.3.5

    6 f 3 400 1.2.3.6

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks you very much Jeff Moden for solving this and keeping the community alive by helping others.

    regards,

  • My pleasure and thank you for the kudo.

    Of more immediate concern is, have you studied the code, know how it works, or have any questions about how it works? I ask because I didn't have time to write very much about it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I have a question. Why the TABLOCKX hint on the temp table? What does it give you that the QP/QO wouldn't do anyway?

  • Jack Corbett (10/24/2011)


    I have a question. Why the TABLOCKX hint on the temp table? What does it give you that the QP/QO wouldn't do anyway?

    It's a small "speed trick". Instead of the QP/QO doing the normal row to page to extent to table escalation, I just lock the whole table and prevent all of that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (10/24/2011)


    Jack Corbett (10/24/2011)


    I have a question. Why the TABLOCKX hint on the temp table? What does it give you that the QP/QO wouldn't do anyway?

    It's a small "speed trick". Instead of the QP/QO doing the normal row to page to extent to table escalation, I just lock the whole table and prevent all of that.

    That's what I thought, but I wanted some confirmation.

  • Just the bold part i didn't get does it depend on number of levels or any other thing and the conversion of hierarchypath to binary why it is required?

    WITH

    cteSplit AS

    ( --=== Splits the path into elements so we can aggregate costs by ID

    SELECT CAST(SUBSTRING(h.HierarchyPath,(t.N*4)-3,4) AS INT) AS ID,

    h.Cost AS Cost

    FROM dbo.Tally AS t WITH(NOLOCK)

    CROSS JOIN #HierarchyNew AS h WITH(TABLOCKX)

    WHERE t.N BETWEEN 1 AND DATALENGTH(h.HierarchyPath)/4

    )

    thanks in advance.

    regards,

  • It splits the Hierarchy path back to ID's. Each ID can be a part of several "paths" and the only way to accumulate the total "cost" associated with each of those ID's is to sum the cost from whatever path they appear in. By their very nature, they are assocated with costs in the "downlines". By position, they are "level sensitive".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Just want to add my thanks to Jeff.

    This article helped me solve my own, slightly similar problem (@ http://www.sqlservercentral.com/Forums/Topic1196820-391-1.aspx )

    Mine is a bill of materials problem, and involved adding up nutritional information in raw materials (fat, protein, sugar, vitamins, minerals, etc), to find out what is in the finished product (or intermediate 'blends').

    Once I'd fixed my problem, I decided to see if the technique I came up with would work for *this* problem; mostly just to satisfy my own curiosity.

    So I thought I'd share it to demonstrate that there usually more than one way to solve a problem, and in the off-chance that someone else has a similar-but-not-quite-the-same problem that might benefit from this. The code below produces the same output as Jeff's, at least when given the same input (I haven't tested it with any other data).

    --===== Conditionally drop Temp Tables to make reruns in SSMS easier

    IF OBJECT_ID('tempdb..#Hierarchy','U') IS NOT NULL DROP TABLE #Hierarchy;

    IF OBJECT_ID('tempdb..#HierarchyNew','U') IS NOT NULL DROP TABLE #HierarchyNew;

    GO

    --===== Create the test table

    CREATE TABLE #Hierarchy

    (

    ID INT,

    Name NVARCHAR(40),

    ParentID INT,

    Cost BIGINT

    )

    ;

    --===== Populate the test table with data provide by the OP

    INSERT INTO #Hierarchy

    (ID, Name, ParentID, Cost)

    SELECT 1 AS ID, 'a' AS Name, NULL AS ParentID, NULL AS Cost UNION ALL

    SELECT 2 AS ID, 'b' AS Name, 1 AS ParentID, NULL AS Cost UNION ALL

    SELECT 3 AS ID, 'c' AS Name, 2 AS ParentID, NULL AS Cost UNION ALL

    SELECT 4 AS ID, 'd' AS Name, 3 AS ParentID, 100 AS Cost UNION ALL

    SELECT 5 AS ID, 'e' AS Name, 3 AS ParentID, 200 AS Cost UNION ALL

    SELECT 6 AS ID, 'f' AS Name, 3 AS ParentID, 400 AS Cost UNION ALL

    SELECT 10 AS ID, 'g' AS Name, 2 AS ParentID, NULL AS Cost UNION ALL

    SELECT 11 AS ID, 'h' AS Name, 10 AS ParentID, 50 AS Cost UNION ALL

    SELECT 12 AS ID, 'i' AS Name, 10 AS ParentID, 50 AS Cost

    ;

    --===== Build a "combined" table that will hold both the "Adjacency List"

    -- and the near equivalent of the HierarchyID datatype but in 2005.

    WITH cteBuildPath AS

    ( --=== This "anchor" section finds the top dog with a NULL as a parent

    SELECT anchor.ID AS TopLevelID,

    anchor.Name AS TopLevelName,

    anchor.ID,

    anchor.ParentID,

    anchor.Name,

    COALESCE(anchor.Cost,0) AS Cost,

    CAST(CAST(anchor.ID AS BINARY(4)) AS VARBINARY(1000)) AS HierarchyPath,

    CAST(CAST(anchor.ID AS VARCHAR(10)) AS VARCHAR(1000)) AS HumanReadablePath,

    1 AS HierarchyLevel

    FROM #Hierarchy anchor

    --WHERE ID IN (SELECT DISTINCT ParentID FROM #Hierarchy)

    UNION ALL ---------------------------------------------------------------------------------------

    --=== This "recusive" section puts the rest of the hierarchy together.

    SELECT rcte.TopLevelID,

    rcte.TopLevelName,

    recur.ID,

    recur.ParentID,

    recur.Name,

    rcte.Cost + COALESCE(recur.Cost,0),

    CAST(rcte.HierarchyPath + CAST(recur.ID AS BINARY(4)) AS VARBINARY(1000)) AS HierarchyPath,

    CAST(rcte.HumanReadablePath + '.' + CAST(recur.ID AS VARCHAR(10)) AS VARCHAR(1000)) AS HumanReadablePath,

    rcte.HierarchyLevel + 1 AS HierarchyLevel

    FROM #Hierarchy recur

    INNER JOIN cteBuildPath rcte

    ON rcte.ID = recur.ParentID

    ) --=== This builds our new hierarchy table from the rCTE above.

    SELECT TopLevelID,

    TopLevelName,

    ID,

    ParentID,

    Name,

    Cost,

    HierarchyPath,

    HumanReadablePath,

    HierarchyLevel

    INTO #HierarchyNew

    FROM cteBuildPath

    ;

    SELECT * FROM #HierarchyNew;

    -- sum up and display the totals for each top-level bom item

    SELECT h.TopLevelID as id, h.TopLevelName as name, p.ParentID, SUM(h.Cost) AS Cost, p.HumanReadablePath AS Serial

    FROM #HierarchyNew AS h

    inner join #HierarchyNew p on h.TopLevelID = p.ID and p.TopLevelID = 1

    group by h.TopLevelID, h.TopLevelName, p.ParentID, p.HumanReadablePath, p.HierarchyPath

    order by p.HumanReadablePath

    ;

    -- name, parent, cost, serial

    select * from (

    select 1 as id,'a' as name , null as parentid , 800 as cost,'1' as serial union all

    select 2 as id,'b' as name , 1 as parentid , 800 as cost,'1.2' as serial union all

    select 3 as id,'c' as name , 2 as parentid , 700 as cost,'1.2.3' as serial union all

    select 10 as id,'g' as name , 2 as parentid , 100 as cost,'1.2.10' as serial union all

    select 11 as id,'h' as name , 10 as parentid , 50 as cost,'1.2.10.11' as serial union all

    select 12 as id,'i' as name , 10 as parentid , 50 as cost,'1.2.10.12' as serial union all

    select 4 as id,'d' as name , 3 as parentid , 100 as cost,'1.2.3.4' as serial union all

    select 5 as id,'e' as name , 3 as parentid , 200 as cost,'1.2.3.5' as serial union all

    select 6 as id,'f' as name , 3 as parentid , 400 as cost,'1.2.3.6' as serial) x

    order by serial

    It works by treating each item in the hierarchy as its own top-level item, and then recursing down each resultant path to add the costs up for each 'top-level' item.

    (Note: I COALESCE the costs to 0 in my query; since I have no SUM() that ignores NULL values, I have deal with them 'manually')

    The devilish detail is lurking in the intermediate query results. I used this to work out what Jeff's code was doing (basically, the guts of the cteSplit query, with extra info):

    SELECT CAST(SUBSTRING(h.HierarchyPath,(t.N*4)-3,4) AS INT) AS ApplyToID,

    h.Cost AS Cost, h.ID, h.ParentID, h.Name, CAST(h.HierarchyPath as varbinary(20)) as HierarchyPath

    FROM dbo.Tally AS t WITH(NOLOCK)

    CROSS JOIN #HierarchyNew AS h WITH(TABLOCKX)

    WHERE t.N BETWEEN 1 AND DATALENGTH(h.HierarchyPath)/4

    ORDER BY ApplyToID, ParentID, ID

    it produces this:

    ApplyToID Cost ID ParentID Name HierarchyPath

    ----------- -------------------- ----------- ----------- ---- ------------------------------------------

    1 NULL 1 NULL a 0x00000001

    1 NULL 2 1 b 0x0000000100000002

    1 NULL 3 2 c 0x000000010000000200000003

    1 NULL 10 2 g 0x00000001000000020000000A

    1 100 4 3 d 0x00000001000000020000000300000004

    1 200 5 3 e 0x00000001000000020000000300000005

    1 400 6 3 f 0x00000001000000020000000300000006

    1 50 11 10 h 0x00000001000000020000000A0000000B

    1 50 12 10 i 0x00000001000000020000000A0000000C

    2 NULL 2 1 b 0x0000000100000002

    2 NULL 3 2 c 0x000000010000000200000003

    2 NULL 10 2 g 0x00000001000000020000000A

    2 100 4 3 d 0x00000001000000020000000300000004

    2 200 5 3 e 0x00000001000000020000000300000005

    2 400 6 3 f 0x00000001000000020000000300000006

    2 50 11 10 h 0x00000001000000020000000A0000000B

    2 50 12 10 i 0x00000001000000020000000A0000000C

    3 NULL 3 2 c 0x000000010000000200000003

    3 100 4 3 d 0x00000001000000020000000300000004

    3 200 5 3 e 0x00000001000000020000000300000005

    3 400 6 3 f 0x00000001000000020000000300000006

    4 100 4 3 d 0x00000001000000020000000300000004

    5 200 5 3 e 0x00000001000000020000000300000005

    6 400 6 3 f 0x00000001000000020000000300000006

    10 NULL 10 2 g 0x00000001000000020000000A

    10 50 11 10 h 0x00000001000000020000000A0000000B

    10 50 12 10 i 0x00000001000000020000000A0000000C

    11 50 11 10 h 0x00000001000000020000000A0000000B

    12 50 12 10 i 0x00000001000000020000000A0000000C

    (29 row(s) affected)

    compare this with my intermediate table:

    SELECT h.TopLevelID AS ApplyToID, h.Cost AS Cost, h.ID, h.ParentID, h.Name, h.HierarchyPath

    FROM #HierarchyNew h

    ORDER BY h.TopLevelID, h.ParentID, h.ID

    ApplyToID Cost ID ParentID Name HierarchyPath

    ----------- -------------------- ----------- ----------- ---- ------------------------------------------

    1 0 1 NULL a 0x00000001

    1 0 2 1 b 0x0000000100000002

    1 0 3 2 c 0x000000010000000200000003

    1 0 10 2 g 0x00000001000000020000000A

    1 100 4 3 d 0x00000001000000020000000300000004

    1 200 5 3 e 0x00000001000000020000000300000005

    1 400 6 3 f 0x00000001000000020000000300000006

    1 50 11 10 h 0x00000001000000020000000A0000000B

    1 50 12 10 i 0x00000001000000020000000A0000000C

    2 0 2 1 b 0x00000002

    2 0 3 2 c 0x0000000200000003

    2 0 10 2 g 0x000000020000000A

    2 100 4 3 d 0x000000020000000300000004

    2 200 5 3 e 0x000000020000000300000005

    2 400 6 3 f 0x000000020000000300000006

    2 50 11 10 h 0x000000020000000A0000000B

    2 50 12 10 i 0x000000020000000A0000000C

    3 0 3 2 c 0x00000003

    3 100 4 3 d 0x0000000300000004

    3 200 5 3 e 0x0000000300000005

    3 400 6 3 f 0x0000000300000006

    4 100 4 3 d 0x00000004

    5 200 5 3 e 0x00000005

    6 400 6 3 f 0x00000006

    10 0 10 2 g 0x0000000A

    10 50 11 10 h 0x0000000A0000000B

    10 50 12 10 i 0x0000000A0000000C

    11 50 11 10 h 0x0000000B

    12 50 12 10 i 0x0000000C

    (29 row(s) affected)

    as a high school friend used to say: 'the same only different !'

    In addition, the technique I used will still produce possibly useful results if you have a 'child' that has more than one 'parent'. e.g. if you add another row to the hierarchy:

    INSERT INTO #Hierarchy (ID, Name, ParentID, Cost) VALUES (4, 'd', 10, 100);

    you get this out:

    ApplyToID name ParentID Cost Serial

    ----------- ---- ----------- -------------------- --------------------------------------------------

    1 a NULL 900 1

    2 b 1 900 1.2

    10 g 2 200 1.2.10

    11 h 10 50 1.2.10.11

    12 i 10 50 1.2.10.12

    4 d 10 200 1.2.10.4

    3 c 2 700 1.2.3

    4 d 3 200 1.2.3.4

    5 e 3 200 1.2.3.5

    6 f 3 400 1.2.3.6

    (10 row(s) affected)

    The upper-level 'summaries' are what I expect, but my query is coming 200 for both '4' items, instead of 100 (which is what I was expecting). Of course, depending on what you're reporting, that 200 may well be correct!

    I'd like to continue on this, but I need to go and do something that'll pay my bills.

    EDIT: (forgot to test before posting) that last result set is that same as you'd get from Jeff's code for the same input data.

    regards

    /Ryan

  • Apologies for the very late reply but thank you very much for posting all the work you did on this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply