3

I have a table like this:

COMPOSANT COMPOSE QTE PA COST LEVEL
PARENT1 CHILD1 24 0
PARENT1 CHILD2 2 0
CHILD1 CHILD11 10 1
CHILD1 CHILD12 4 3 12 1
CHILD11 CHILD111 100 1 100 2
CHILD2 CHILD21 5 10 50 1

And I try to make a query to sum all children costs to parents

DECLARE @tmp TABLE ( [COMPOSANT] [varchar](25) NULL, [COMPOSE] [varchar](25) NULL, [QTE] [numeric](38, 9) NULL, [PA] [numeric](38, 9) NULL, [COST] [numeric](38, 9) NULL, [LEVEL] [int] NULL ) INSERT INTO @tmp VALUES ('PARENT1', 'CHILD1', 24, NULL, NULL, 0); INSERT INTO @tmp VALUES ('PARENT1', 'CHILD2', 2, NULL, NULL, 0); INSERT INTO @tmp VALUES ('CHILD1', 'CHILD11', 10, NULL, NULL, 1); INSERT INTO @tmp VALUES ('CHILD2', 'CHILD21', 4, 3, 12, 1); INSERT INTO @tmp VALUES ('CHILD11', 'CHILD111', 100, 1, 100, 2); SELECT * FROM @tmp

My expected result is something like this:

COMPOSANT COMPOSE QTE PA COST LEVEL
PARENT1 CHILD1 24 1012 24288 0
PARENT1 CHILD2 2 50 100 0
CHILD1 CHILD11 10 100 1000 1
CHILD1 CHILD12 4 3 12 1
CHILD2 CHILD21 5 10 50 1
CHILD11 CHILD111 100 1 100 2

And so TOTAL COST is 24288+100

I tried using a recursive CTE without success.

Here are my tests :

WITH CTE_HIERARCHIE AS ( -- Feuilles avec PA connus SELECT COMPOSE AS COMPOSE, COMPOSANT, QTE, PA, cast(PA * QTE as [numeric](38, 9)) AS COST, 0 AS INVLEVEL ,LEVEL FROM @tmp WHERE PA IS NOT NULL UNION ALL -- Propagation vers les parents SELECT parent.COMPOSE AS COMPOSE, parent.COMPOSANT, parent.QTE, cast(child.COST as numeric (38, 9)) as PA, cast( child.PA*child.QTE as [numeric](38, 9)) AS COST, child.INVLEVEL + 1,parent.LEVEL FROM @tmp parent INNER JOIN CTE_HIERARCHIE child ON parent.COMPOSE = child.COMPOSANT ) -- Résumé des coûts par composant parent SELECT COMPOSANT,COMPOSE,QTE,SUM(PA) as PA, SUM(COST) as COST,LEVEL FROM CTE_HIERARCHIE GROUP BY COMPOSANT,COMPOSE,LEVEL,QTE ORDER BY LEVEL,COMPOSANT,COMPOSE;

https://dbfiddle.uk/vuN9Yo3G

But some cost are wrong I on level 0 and 1. I don't know how to solve this.

Here some more about how it works:

here

Improve this question
8
  • 1
    By what logic would such small numbers produce 1012 or 24288 ? Those aren't a hierarchical sum of either the QTE or PA columns. Commented May 8 at 16:03
  • CHILD1 PA is CHILD11+CHILD12 = 1000 + 12 Commented May 8 at 16:08
  • I think I solved my probleme changing calculation in the second part of the union all query in the CTE here : dbfiddle.uk/pP2_RyrX Any advice ? Commented May 8 at 16:10
  • There's no 1000 in the data. Post the code and logic in the question itself. The total cost should be 162. Commented May 8 at 16:18
  • "CHILD1 PA is CHILD11+CHILD12 = 1000 + 12" Where does this 1000 come from in your sample data (both the table you posted and the SQL don't have this). Your sample data and desired results should be related otherwise we are having to guess. Commented May 8 at 16:18

2 Answers 2

Reset to default
4

You have to SUM with analytic when navigating bottom-up:

WITH CTE_HIERARCHIE AS ( select d.composant, d.compose, d.qte, d.pa, d.cost, d.LEVEL from @tmp d WHERE PA IS NOT NULL union all select d.composant, d.compose, d.qte, sum(r.cost) over(partition by r.composant), cast(d.qte * sum(r.cost) over(partition by r.composant) as [numeric](38, 9)), d.LEVEL from CTE_HIERARCHIE r join @tmp d on d.compose = r.composant ) select composant, compose, qte, sum(pa) as pa, sum(cost) as cost, LEVEL from CTE_HIERARCHIE c group by composant, compose, qte, LEVEL order by level, composant, compose ;

https://dbfiddle.uk/qOXltddq

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

thanks @p3consulting ! It works very well ;)
be careful to test it on more complex data sets, you may need to add some CASE logic in the SUM to avoid counting several times the same node when having more than 1 child.
Your analytic functions partition by the child node's name. As a child node can only ever belong to one parent node, this means that only one row will ever be included in the window. Which is the same as not using an analytic function at all. dbfiddle.uk/fHtC9y_S
Yes indeed but the reason is different: I tested with another RDBMS and converted to SQLServer syntax afterwards, BUT the behavior of the recursive query is different in both: in the UNION ALL part SQLServer sees only the branch from here it comes while the other sees all the branches... that of course changes a lot the behavior of the SUM() OVER() when going bottom-up.
I've never seen the second behaviour, which DBMS was that!? That would be completely against the ANSI standard definition. Also, the non-analytic approach is the correct approach in both cases.
|
0

Your only mistake was the definition of a parent node's cost.

You had :

  • child.PA*child.QTE

It should be :

  • parent.qte * child.cost

See the following amendment to your fiddle...

There's no need for analytic functions, case expressions, etc.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.