• 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