sql server - Recursive sum in parent-child hierarchy T-SQL -

I have a database that stores cost information for a partition according to the partition on an annual basis for an organization Is and is time-phased. Cost structure includes parents' relationships; Users can specify value values ​​at any level in the structure, the only restriction is that at the higher levels in the hierarchy, all values ​​are counted as the sum of children's nodes, if any child has the value of the node; Parent node values ​​which are not stored in the results database as the sum of children's nodes.

I need a query that will calculate the values ​​of parents on the basis of their children and there will be no set with zero for children (T-SQL, SQL 2008 R2) [SQL Fuel] MS SQL Server 2008 Schema Setup :

  Make Table Constructor Nodes (ID INT Not Null Primary Key, Name NVHAR ( 250) is not called, parent nodeID int, vs Esh key (parent Nodaidi) references Koststrccrnod (id)); Create the whole year (year INT not original primary key); Create abstract values ​​(Nine INT INT, not year INT, not zero, value decimal (18,6) No primary key (node ​​ID, year), foreign key (node ​​ID) reference cost structure (ID), foreign key ( Year) Reference year (years)); INSERT ('1', 'Total Costs', NULL) in CostStructure Nodes Value; INSERT in coststructure nodes value ('2', 'R & amp; D', 1); INSERT in Coststroke Nodes Value ('3', 'Legal', 1); INSERT in Coststroke Nodes Value ('4', 'HR', 1); INSERT in cost structure nodes value ('5', 'IT', 1); INSERT ('6', 'software', 5) in the coststructure nodes value; INSERT in coststructure nodes value ('7', 'hardware', 5); Include Year Values ​​(2010); Include Year Values ​​(2011); Include Year's Values ​​(2012); Include Value Value Works (1, 2010, 100000); Value added values ​​(2, 2011, 50000); Include Value Value Works (5, 2011, 20000); Value Added Works (6, 2012, 22000); Value values ​​include value (7, 2012, 13000); Value Added Works (2, 2012, 76000);   
 Looking at the above structure and sample data, how do things look like this:  
  | NAME | Year | VALUE ------------------------------- | Total cost. 2010 | 100000 | | R & D | 2010 | 0 | | IT | 2010 | 0 | | Software | 2010 | 0 | | Hardware | 2010 | 0 | | Human Resources | 2010 | 0 | | Total cost. 2011 | 70000 | R & D | 2011 | 50000 | IT | 2011 | 20000 | | Software | 2011 | 0 | | Hardware | 2011 | 0 | | Human Resources | 2011 | 0 | | Total cost. 2012 | 111000 | | R & D | 2012 | 76000 | | IT | 2012 | 35000 | Software | 2012 | 22000 | | Hardware | 2012 | 13000 | | Human Resources | 2012 | 0 |    
 
 
 It should give the correct result:  
  direct As with the Report (ParentNode ID, ID, Name, Level, Struct, Year), select (- Anchor a.ParentNodeId, as a.Id, a.Name, Level 0, Cast (':' + cast (As a.Id varchar)) + ':' formality (100)) as in the form of a stroke, from y.year to coaststructure nodes A, ear y where a.ParentNodeId is a blank union - recursive. Choose A.ParentNodeId, a.Id, a.Name, Level +1, Dust (D. Stuck + Cast (AID Varchar) + ':' Vertical (100)) as Struct, D.E.E.S. Constructor nodes include DirectReport D at d.Id = a.ParentNodeId) d.ParentNodeId, D. Yure, D.ID, D. Noam, D. Level, d. Stuck, - DD. DirectReport dt from DirectReport Dot to TotCost d.year = dd.year as Streak, Zodiac (case when D. Stuck = Sbustring (D.D. Stack, 1, Lane (D. Stuck)) then c.value else 0 end. Join cs.Year = dd.year at cs.Year = dd.year and c.NodeId = dd.Id to join the group d.ParentNodeId, D.year, DIID, D. Name, D. Level, d. No. Order by d.year, d.id   
 Here is the fleet link:  
 Note that to keep departments without costs, join the left between both DirectReports part .