title: Sum of child levels total in a Hierarchy
body: <p>I need to have each level be the sum of all children (in the hierarchy) in addition to any values set against that value itself for the Budget and Revised Budget columns.</p>
  
  <p>I've included a simplified version of my table structure and some sample data to illustrate what is currently being produced and what I'd like to produce.</p>
  
  <p>Sample table:</p>
  
  <pre><code>CREATE TABLE Item (ID INT, ParentItemID INT NULL, ItemNo nvarchar(10), ItemName nvarchar(max), Budget decimal(18, 4), RevisedBudget decimal(18, 4));
  </code></pre>
  
  <p>Sample data:</p>
  
  <pre><code>INSERT INTO Item (ID, ParentItemID, ItemNo, ItemName, Budget, RevisedBudget) VALUES (1, NULL, N'10.01', N'Master Bob', 0.00, 17.00);
  INSERT INTO Item (ID, ParentItemID, ItemNo, ItemName, Budget, RevisedBudget) VALUES (2, 1, N'10.01.01', N'Bob 1', 0.00, 0.00);
  INSERT INTO Item (ID, ParentItemID, ItemNo, ItemName, Budget, RevisedBudget) VALUES (3, 2, N'10.01.02', N'Bob 2', 2.00, 2.00);
  INSERT INTO Item (ID, ParentItemID, ItemNo, ItemName, Budget, RevisedBudget) VALUES (4, 2, N'10.02.01', N'Bob 1.1', 1.00, 1.00);
  </code></pre>
  
  <p>CTE SQL to generate Hierarchy: </p>
  
  <pre><code>WITH HierarchicalCTE
  AS
  (
      SELECT ID, ParentItemID, ItemNo, ItemName, Budget, RevisedBudget, 0 AS LEVEL
      FROM Item
      WHERE Item.ParentItemID IS NULL
  
      UNION ALL
  
      SELECT i.ID, i.ParentItemID, i.ItemNo, i.ItemName, i.Budget, i.RevisedBudget, cte.LEVEL + 1
      FROM HierarchicalCTE cte
      INNER JOIN Item i ON i.ParentItemID = cte.ID
  )
  </code></pre>
  
  <p>So, currently my CTE produces (simplified):</p>
  
  <pre><code>ID: 1, Level: 0, Budget: 0, RevisedBudget: 17
  ID: 2, Level: 1, Budget: 0, RevisedBudget: 0
  ID: 3, Level: 2, Budget: 2, RevisedBudget: 2
  ID: 4, Level: 2, Budget: 1, RevisedBudget: 1
  </code></pre>
  
  <p>And I want the results to produce:</p>
  
  <pre><code>ID: 1, Level: 0, Budget: 3, RevisedBudget: 20
  ID: 2, Level: 1, Budget: 3, RevisedBudget: 3
  ID: 3, Level: 2, Budget: 2, RevisedBudget: 2
  ID: 4, Level: 2, Budget: 1, RevisedBudget: 1
  </code></pre>
  
  <p>Hopefully that is easy enough to understand. </p>
  
  <p>Link to SQLFiddle with table and initial CTE: <a href="http://sqlfiddle.com/#!3/66f8b/4/0" rel="nofollow">http://sqlfiddle.com/#!3/66f8b/4/0</a></p>
  
  <p>Please note, any proposed solution will need to work in SQL Server 2008R2.</p>