got the reply from other forum.

declare @id int

declare @levelid int

set @id=2;

set @levelid=2;

;with cteloc

as

(

select ID,LocationName,ParentID,LevelID,ID as ParentId2 from locationtable where id=@id

union all

select a.Id,a.LocationName,a.ParentID,a.LevelID,cteloc.id from locationtable

a inner join cteloc on a.parentid=cteloc.id

)

,cte

AS

(

SELECT id,Name,parentid,levelid, name AS Level1Name FROM asic b WHERE parentid IS NULL

UNION ALL

SELECT a.id,a.name,a.parentid,a.levelid, cte.Level1Name FROM asic a INNER JOIN cte

ON a.parentid=cte.id

)

,cte3

as

(

select p.ProjectName,p.AsicId,p.Cost,p.LocationId,cteloc.id,cteloc.parentid2 from project p

inner join cteloc on cteloc.id=p.LocationId

)

select min(Level1Name)as Name,COUNT(projectname) TotalProject ,sum(cost)as TotalProjectCost from cte3

INNER JOIN cte ON cte.id=cte3.asicid

where (cte3.ParentId2=@id and @LevelId <> 3) or (@LevelId = 3 and cte3.Id = @ID) group by asicid