InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 21:29:56 GMT20http://www.sqlservercentral.com/Forums/Topic987109-391-1.aspxHere's a simple solution which assumes only three levels in the hierarchy, as in your sample data. Note how the sample data has been set up using a script:[code="sql"]DROP TABLE #SampleCREATE TABLE #Sample (DepartmentId INT, ParentId INT, DeptName VARCHAR(20), Incoming DECIMAL(5,2))INSERT INTO #Sample (DepartmentId, ParentId, DeptName, Incoming)SELECT 1, 0, 'PhnDeprt', 14.00 UNION ALLSELECT 3, 1, 'Facilities', 01.00 UNION ALLSELECT 2, 0, 'Calse', 5.00 UNION ALL SELECT 4, 2, 'CalFacitity', 1.00 UNION ALLSELECT 5, 4, 'CalFF', 2.00SELECT d.DepartmentId, d.ParentId, d.DeptName, Level0Incoming = d.Incoming, Level1Incoming = c1.Incoming, Level2Incoming = c2.Incoming FROM #Sample d LEFT JOIN #Sample c1 ON c1.ParentId = d.DepartmentId LEFT JOIN #Sample c2 ON c2.ParentId = c1.DepartmentId [/code]Mon, 27 Sep 2010 05:12:08 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic987109-391-1.aspxHi,Try the following code:with Incoming as(select DepartmentId, ParentId, Incoming as Incoming from #Departmentwhere ParentId in (select DepartmentId from #department)Union Allselect d.DepartmentId, i.ParentId, d.Incoming as Incomingfrom #Department d Inner Join Incoming iONi.DepartmentId = d.ParentId)select d.DepartmentId, d.ParentId, (d.incoming+isnull(i.incoming,0)) as incomingfrom#Department d Left Outer Join (Select ParentId, Sum(Incoming) as incoming from Incoming group by ParentId) iON d.DepartmentId = i.ParentIdSrinivas Reddy.SMon, 27 Sep 2010 03:57:55 GMTsinureddihttp://www.sqlservercentral.com/Forums/Topic987109-391-1.aspxHaiIncoming Is the Decimal type field. This field have any type of values in decimal.Thu, 16 Sep 2010 05:55:58 GMTmohanarajhttp://www.sqlservercentral.com/Forums/Topic987109-391-1.aspxis facilities always 1? What type of field is storing value '1' or '15'? Is it int or varchar?Thu, 16 Sep 2010 05:48:35 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic987109-391-1.aspxHai,I want to sum up the child department values and display in the parent recordBelow is the Table.[u]DepartmentId[/u] [u]ParentId[/u] [u]DeptName[/u] [u]Incoming[/u] 1 ------------0------ PhnDeprt ------14.00 3 ------------1-------Facilities ------01.00 2 ------------0-------Calse ---------- 5.00 4 ------------2-------CalFacitity ------1.00 5 ----------- 4 -------CalFF -----------2.00I want the result as [u]DepartmentId[/u] [u]ParentId[/u] [u]DeptName[/u] [u]Incoming[/u] 1 ------------0------ PhnDeprt ------15.00 3 ------------1-------Facilities ------01.00 2 ------------0-------Calse ---------- 8.00 4 ------------2-------CalFacitity ------3.00 5 ----------- 4 -------CalFF -----------2.00Here [b]'PhnDeprt'[/b] department have child [b]'Facilities'[/b] so in the result 1.00 + 14.00 = 15.00 this result will be displayed in the Parent record i.e. in the [b]'PhnDeprt'[/b]'s Incoming columnPlease advise and help meThanks in advance.Thu, 16 Sep 2010 05:07:48 GMTmohanaraj