Hi,
Try the following code:
with Incoming as(
select DepartmentId, ParentId, Incoming as Incoming from #Department
where ParentId in (select DepartmentId from #department)
Union All
select d.DepartmentId, i.ParentId, d.Incoming as Incoming
from #Department d Inner Join Incoming i
ON
i.DepartmentId = d.ParentId
)
select d.DepartmentId, d.ParentId, (d.incoming+isnull(i.incoming,0)) as incoming
from
#Department d Left Outer Join (Select ParentId, Sum(Incoming) as incoming from Incoming group by ParentId) i
ON d.DepartmentId = i.ParentId
Srinivas Reddy.S