title: How to Multiple node weight with its value then compute sum of all child nodes in parent child hierarchy using sql server 2008
body: <p>I have a scenario, where i have to find percentage of each child node by multiplying node value with node weight, (e.g NodeVal * (NodeWeight/100)), then sum these computed values of all child nodes and add it to its parent, now at one level up this newly computed parent also has its weight, which will be multiplied with its value and then we will compute sum of all child nodes at this level and add it to its parent one level above, and so on.</p>
  
  <p>Queries found on internet work fine if we simply compute sum of all child nodes, but not helpful in my case.</p>
  
  <p>I am using following query, but it does not return accurate result.</p>
  
  <pre><code>    CREATE TABLE TEMP(
        Id INT
        ,ParentNodeId INT
        ,Weight FLOAT
        ,Score FLOAT    
        );
  
        INSERT INTO TEMP
              VALUES (2, 1, 0, 0),
              (3, 2, 100, 0),
              (4, 2, 100, 0),
              (5, 2, 100, 0),
              (6, 3, 40, 100),
              (7, 3, 30, 0),
              (8, 3, 30, 100),
              (9, 7, 10, 100),
              (10, 7, 40, 100),
              (11, 7, 50, 0),
              (12, 11, 30, 100),
              (13, 11, 70, 0),
              (14, 13, 50, 100),
              (15, 13, 50, 100);
  
  
  WITH cte(Id, ParentNodeId, LEVEL, Struc, Weight, Score)
              AS
              (
              SELECT t.Id, t.ParentNodeId, 0 AS LEVEL, CAST(':' + CAST(t.Id AS VARCHAR) + ':' AS VARCHAR(MAX)) AS Struc, t.Weight, t.Score 
              FROM TEMP t
              WHERE t.ParentNodeId = 2 
              UNION ALL
              SELECT t.Id, t.ParentNodeId, LEVEL + 1 AS LEVEL, CAST(e.Struc + CAST(t.Id AS VARCHAR) + ':' AS VARCHAR(MAX)) AS Struc, t.Weight, t.Score 
              FROM TEMP t
              INNER JOIN cte e ON e.Id = t.ParentNodeId           
              )
  
  select c1.Id, c1.ParentnodeId, c1.weight, c1.score,c1.struc,
  SUM(c2.score  * (c2.weight/100 )) ProductCountIncludingChildren
  from cte c1
  left outer join cte c2 on c1.struc &lt;&gt; c2.struc and left(c2.struc, LEN(c1.struc)) = c1.struc
  group by c1.Id, c1.ParentnodeId, c1.weight, c1.score, c1.struc
  order by c1.Id
  </code></pre>
  
  <p>This query works only at leave nodes, but not at above levels. <a href="http://sqlfiddle.com/#!6/958e3/15/0" rel="nofollow noreferrer">SQL Fiddle Link</a>.</p>
  
  <p>Please let me know, how this can be solved. Thanks for help in advance -:)</p>