Recursive sum in Parent-child hierarchy T-SQL

---tree (parent-child relationship Class) grading class statistics (parent-child statistics)--2014-08-14drop Table Bookkindlistcreate table bookkindlist (Bookkindid INT IDENTITY (P) Rimary KEY, Bookkindname nvarchar ($) not NULL, bookkindparent int null) godrop table bookcostsper---CREATE table Bo Okcostsper (ID INT IDENTITY (PRIMARY) KEY, NodeId int not NULL, [bookname] nvarchar (+) not NULL, [Costsvalue] DEC iMAL (18,6) not NULL, costdate datetime default (GETDATE ())) goselect * from Bookkindlistinsert to Bookkindlist (bookkindn ame,bookkindparent) VALUES (' Smudge document directory ', NULL) insert INTO bookkindlist (bookkindname,bookkindparent) VALUES (' literature ', 1) Insert into Bookkindlist (bookkindname,bookkindparent) VALUES (' Design art ', 1) insert into bookkindlist (Bookkindname, bookkindparent) VALUES (' Natural science ', 1) insert into bookkindlist (bookkindname,bookkindparent) VALUES (' novel ', 2) insert INTO Bookkindlist (bookkindname,bookkindparent) VALUES (' verse verse ', 2) insert INTO Bookcostsper (Nodeid,[bookname],[costsvalue] , costdate) VALUES (3, ' Design theory ', ' 2014-01-02 ') insert into Bookcostsper (NoDeid,[bookname],[costsvalue],costdate) VALUES (4, ' Computer science ', +, ' 2014-01-02 ') insert into Bookcostsper (nodeid,[ Bookname],[costsvalue],costdate) VALUES (5, ' arrogance and Prejudice ', 550, ' 2014-01-02 ') insert into Bookcostsper (nodeid,[bookname],[ Costsvalue],costdate) VALUES (6, ' Song Ci ', ' 2014-01-02 ') insert into Bookcostsper (Nodeid,[bookname],[costsvalue], Costdate) VALUES (3, ' Layout design ', ' 2013-05-02 ') insert into Bookcostsper (nodeid,[bookname],[costsvalue],costdate) VALUES (4, ' C language Design ', $, ' 2013-05-02 ') insert into Bookcostsper (nodeid,[bookname],[costsvalue],costdate) VALUES (5, ' Uncle Tom's Hut ', 530, ' 2013-05-02 ') insert into Bookcostsper (nodeid,[bookname],[costsvalue],costdate) VALUES (6, ' Tang Dynasty ', 110, ' 2013-05-02 ')--View CREATE View V_bookcostsperasselect *,year (costdate) as ' yearname ' from Bookcostspergo---system with Directreport (Bookkindparent, Bookkindid, [Bookkindname], level, struc) as (--Anchorselect a.bookkindparent, A. Bookkindid, A.bookkindname, 0 as level, cast (': ' + cast (a.bookkindid as varchar) + ': ' as varchar ') as Strucfrom Boo KKindlist awhere a.bookkindparent is nullunion all--recursiveselect a.bookkindparent, A.BookKindID, A.BookKindName, Level +1, CAST (D.struc + cast (a.bookkindid as varchar) + ': ' as varchar ') as Strucfrom bookkindlist a JOIN Directrepo RT D on d.bookkindid = a.bookkindparent) SELECT d.bookkindparent, D.bookkindid, D.bookkindname, D.level, d.Struc,sum (case When D.struc = SUBSTRING (dd. Struc, 1, Len (D.struc)) then C.costsvalue ELSE 0 END) as Totcostfrom directreport d,directreport ddjoin bookcostsper c O N C.nodeid = dd. Bookkindidgroup by D.bookkindparent,d.bookkindid, D.bookkindname, D.level, D.strucorder by D. BOOKKINDIDGO-----Annual Parent-child class totals with Directreport (bookkindparent, Bookkindid, [Bookkindname], level, Struc, [Yearname]) as (- -Anchor Select A.bookkindparent, A.bookkindid, A.bookkindname, 0 as level, cast (': ' + cast (a.bookkindid as varchar) + ':  ' as varchar ') as Struc, Y.[yearname] from Bookkindlist A, yearnames y where a.bookkindparent are null UNION ALL --Recursive SeleCT a.bookkindparent, A.bookkindid, a.bookkindname, Level +1, cast (D.struc + cast (a.bookkindid as varchar) + ': ' as varchar (+)) as Struc, D.[yearname] from Bookkindlist a joins Directreport d on d.bookkindid = a.bookkindparent) Select d.bo Okkindparent, D.[yearname], D.bookkindid, D.bookkindname, D.level, D.struc,--DD. Struc,sum (case when d.struc = SUBSTRING (dd). Struc, 1, Len (D.struc)) then C.costsvalue else 0 end) as Totcostfrom Directreport D left joins Directreport DD on D.[yearna Me] = Dd.[yearname] Join V_bookcostsper c on c.[yearname] = Dd.[yearname] and C.nodeid = dd.  Bookkindid GROUP by D.bookkindparent, D.[yearname], D.bookkindid, D.bookkindname, D.level, D.strucorder by D.[YearName], D.bookkindidgo