Rekursive Summe in der Eltern-Kind-Hierarchie T-SQL

Ich habe eine database, die Cost-Informationen für eine Organisation speichert, durch eine Division Pause und zeit-schrittweise nach Jahr. Die Kostenstruktur beinhaltet eine Eltern-Kind-Beziehung; können die Benutzer die Kostenwerte auf jeder Ebene in der Struktur angeben, die einzige Einschränkung ist, dass alle Werte auf höheren Ebenen in der Hierarchie als Summe der Kinderknoten berechnet werden, wenn ein untergeordneter Knoten einen Wert hat; übergeordnete Knotenwerte, die als Summe von Kinderknoten resultieren, werden nicht in der database gespeichert.

Ich benötige eine Abfrage, die rekursiv die Werte der Eltern anhand ihrer Kinder berechnet und für Kinder ohne Werte Null gesetzt wird (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); 

Angesichts der Struktur oben und der Beispieldaten, so sieht es aus wie:

  | 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 | 

Dies sollte ein korrektes Ergebnis geben:

 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 

Hier ist die Geige Link: http://sqlfiddle.com/#!3/cd98d/22/0

Beachten Sie die linke Verbindung zwischen den beiden DirectReport-Teil, um auch die Abteilungen ohne Kosten zu halten.

 WITH DirectReport (ParentNodeId, Id, Name, LEVEL, Struc) AS ( -- anchor SELECT a.ParentNodeId, a.Id, a.Name, 0 AS LEVEL, cast(':' + cast(a.Id AS varchar) + ':' AS varchar (100)) AS Struc FROM CostStructureNodes a 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 FROM CostStructureNodes a JOIN DirectReport d ON d.Id = a.ParentNodeId ) SELECT d.ParentNodeId, d.Id, d.Name, d.level, d.Struc, sum(CASE WHEN d.Struc = SUBSTRING(dd.Struc, 1, len(d.Struc))THEN c.Value ELSE 0 END) AS TotCost FROM DirectReport d,DirectReport dd JOIN CostsPerYear c ON c.NodeId = dd.Id GROUP BY d.ParentNodeId,d.Id, d.Name, d.level, d.Struc ORDER BY d.id