Recursive sum in parent-child hierarchy T-SQL

Question

I have a database that stores Cost information for an organization, by a division break-down and time-phased by year. The cost structure involves a parent-child relationship; the users can specify the cost values on any level in the structure, the only restriction is that all the values at higher levels in the hierarchy are computed as the sum of the children nodes, if any child node has a value; parent node values that result as sum of children nodes are not stored in the database.

I need a query which will recursively compute the values of the parents based on their children and for children with no values zero will be set (T-SQL, SQL 2008R2)

[SQL Fiddle] MS SQL Server 2008 Schema Setup:

CREATE TABLE CostStructureNodes (
  Id INT NOT NULL PRIMARY KEY,
  Name NVARCHAR(250) NOT NULL,
  ParentNodeId INT,
  FOREIGN KEY(ParentNodeId) REFERENCES CostStructureNodes(Id)
); 

CREATE TABLE Years (
  Year INT NOT NULL PRIMARY KEY
);

CREATE TABLE CostsPerYear (
  NodeId INT NOT NULL,
  Year INT NOT NULL,
  Value DECIMAL(18,6) NOT NULL,
  PRIMARY KEY(NodeId, Year),
  FOREIGN KEY(NodeId) REFERENCES CostStructureNodes(Id),
  FOREIGN KEY(Year) REFERENCES Years(Year)
);

INSERT INTO CostStructureNodes VALUES ('1', 'Total Costs', NULL);
INSERT INTO CostStructureNodes VALUES ('2', 'R&D', 1);
INSERT INTO CostStructureNodes VALUES ('3', 'Legal', 1);
INSERT INTO CostStructureNodes VALUES ('4', 'HR', 1);
INSERT INTO CostStructureNodes VALUES ('5', 'IT', 1);
INSERT INTO CostStructureNodes VALUES ('6', 'Software', 5);
INSERT INTO CostStructureNodes VALUES ('7', 'Hardware', 5);

INSERT INTO Years VALUES (2010);
INSERT INTO Years VALUES (2011);
INSERT INTO Years VALUES (2012);

INSERT INTO CostsPerYear VALUES (1, 2010, 100000);
INSERT INTO CostsPerYear VALUES (2, 2011, 50000);
INSERT INTO CostsPerYear VALUES (5, 2011, 20000);
INSERT INTO CostsPerYear VALUES (6, 2012, 22000);
INSERT INTO CostsPerYear VALUES (7, 2012, 13000);
INSERT INTO CostsPerYear VALUES (2, 2012, 76000);

Given the structure above and the sample data, this is how things would look like:

    |        NAME | YEAR |  VALUE |
    -------------------------------
    | Total Costs | 2010 | 100000 |
    |         R&D | 2010 |      0 |
    |          IT | 2010 |      0 |
    |    Software | 2010 |      0 |
    |    Hardware | 2010 |      0 |
    |          HR | 2010 |      0 |
    | Total Costs | 2011 |  70000 |
    |         R&D | 2011 |  50000 |
    |          IT | 2011 |  20000 |
    |    Software | 2011 |      0 |
    |    Hardware | 2011 |      0 |
    |          HR | 2011 |      0 |
    | Total Costs | 2012 | 111000 |
    |         R&D | 2012 |  76000 |
    |          IT | 2012 |  35000 |
    |    Software | 2012 |  22000 |
    |    Hardware | 2012 |  13000 |
    |          HR | 2012 |      0 |

Answer 1

This should give a correct result:

with DirectReport (ParentNodeId, Id, Name, Level, Struc, year)
as
(
  -- anchor
  select a.ParentNodeId, a.Id, a.Name, 0 as Level, cast(':' + cast(a.Id as varchar) + ':' as varchar (100))  as Struc, y.year
  from CostStructureNodes a, Years y
  where a.ParentNodeId is null
  union all
  -- recursive
  Select a.ParentNodeId, a.Id, a.Name, Level +1, cast(d.Struc + cast(a.Id as varchar)+ ':'  as varchar(100)) as Struc, d.year
  from CostStructureNodes a
    join DirectReport d on d.Id = a.ParentNodeId
  )

Select d.ParentNodeId, d.year, d.Id, d.Name, d.level, d.Struc,-- dd.Struc, 
sum(case when d.Struc = SUBSTRING(dd.Struc, 1, len(d.Struc))then c.Value else 0 end) as TotCost
from DirectReport d
  left join DirectReport dd on d.year = dd.year 
  join CostsPerYear c on c.Year = dd.year and c.NodeId = dd.Id
 group by d.ParentNodeId, d.year, d.Id, d.Name, d.level, d.Struc
order by  d.year, d.id

Here is the fiddle link: http://sqlfiddle.com/#!3/cd98d/22/0

Note the left join between the two DirectReport part, for keeping also the departments without costs.