title: CTE in SQL Server 2008: how to calculate subtotals recursively
body: <p>I've got a table where some parts of a car are related hierarchically and I also have the cost of manufacturing those parts in each one of the rows. This is a simplification of the table:</p>
  
  <pre><code>parentId  Id description qty manufacturingCost costDescripcion
  -------- --- ----------- --- ----------------- ---------------
  NULL      1  Car          1  100               Assembly the car
  NULL      2  Motorcycle   1  100               Assembly the motrocycle
   1       11  Wheel        4   20               Assembly the wheel
  11      111  Rim          1   50               Manufacture the rim
  11      112  Tire         1   60               Manufacture the tire
   1       12  Door+Window  4   30               Assembly the door and the window
  12      121  Door         1   30               Manufacture the door
  12      122  Window       2   10               Manufacture the window
   2       11  Wheel        2   15               Assembly the wheel
  </code></pre>
  
  <p>I need to get the whole family tree starting in 'Car' and showing the total quantities and the total costs for each branch. Better explained: a car has 4 wheels and each wheel has 1 rim and 1 tire, so I should get 1 Car, 4 Wheels, 4 Tires, 4 Rims. A little bit more complicated for the costs: Assembling a car costs 100$, but I have to add to this cost, the one of assembling the 4 wheels (4x20) and the cost of manufacturing the 4 rims (4x50) and the 4 tires (4x60), and the same for the doors and windows.</p>
  
  <p>This is the expected result:</p>
  
  <pre><code>parentId  Id description qty manufacturingCost   recLevel
  -------- --- ----------- --- -----------------   ---------------
  NULL       1  Car          1 940 (100+4*130+4*80) 0
   1        11  Wheel        4 130 (20+50+60)       1
   1        12  Door+Window  4 80  (30+30+2*10)     1
  12       121  Door         4 30                   2
  12       122  Window       8 10                   2
  11       111  Rim          4 50                   2
  11       112  Tire         4 60                   2
  </code></pre>
  
  <p>I can easily reach this one using a recursive Function or Stored Procedure but it is very slow with more complex structures, so I am trying to do it using Common Table Expressions. But I didn´t find the way to sum the costs. I use a recursive CTE starting in the top level and going down and I get the sum of the quantities, but I should go from inside to outside in the structure to sum the costs, how can I do that?</p>
  
  <p>This is the code to create the table:</p>
  
  <pre><code>CREATE TABLE #Costs 
  (
    parentId int, 
    Id int, 
    description varchar(50),
    qty int, 
    manufacturingCost int,
    costDescripcion varchar(150)
  )
  
  INSERT INTO #Costs VALUES (NULL , 1, 'Car', 1, 100, 'Assembly the car')
  INSERT INTO #Costs VALUES (NULL , 2, 'Motorcycle', 1, 100, 'Assembly the motrocycle')
  INSERT INTO #Costs VALUES (1 , 11, 'Wheel', 4, 20, 'Assembly the wheel')
  INSERT INTO #Costs VALUES (11 , 111, 'Rim', 1, 50, 'Manufacture the rim')
  INSERT INTO #Costs VALUES (11 , 112, 'Tire', 1, 60, 'Manufacture the tire')
  INSERT INTO #Costs VALUES (1 , 12, 'Door+Window', 4, 30, 'Assembly the door and the window')
  INSERT INTO #Costs VALUES (12 , 121, 'Door', 1, 30, 'Manufacture the door')
  INSERT INTO #Costs VALUES (12 , 122, 'Window', 2, 10, 'Manufacture the window')
  INSERT INTO #Costs VALUES (2 , 11, 'Wheel', 2, 15, 'Assembly the wheel')
  </code></pre>
  
  <p>And this is the CTE I wrote:</p>
  
  <pre><code>with CTE(parentId, id, description, totalQty, manufacturingCost, recLevel)
  as
  (
    select c.parentId, c.id, c.description, c.qty, c.manufacturingCost, 0
    from #Costs c
    where c.id = 1
  
    union all
  
    select c.parentId, c.id, c.description, c.qty * ct.totalQty, c.manufacturingCost, ct.recLevel + 1
    from #Costs c
    inner join CTE ct on ct.id = c.parentId 
  )
  select * from CTE
  </code></pre>
  
  <p>And this is the result I get that, as you can see, is not the expected one (the costs are not being added):</p>
  
  <pre><code>parentId  Id description qty manufacturingCost recLevel
  -------- --- ----------- --- ----------------- ---------------
  NULL       1  Car          1 100                0
   1        11  Wheel        4 20                 1
   1        12  Door+Window  4 30                 1
  12       121  Door         4 30                 2
  12       122  Window       8 10                 2
  11       111  Rim          4 50                 2
  11       112  Tire         4 60                 2
  </code></pre>
  
  <p>Is it possible to do what I want using CTE? If so, how can I do it?</p>
  
  <p>Thank you very much,</p>
  
  <p>Antuan</p>