hi,

can some of make this thing concise becouse the same cte is repeated twice i need to check the levelid only, now it accept locationid as parameter.

select * from project

--- i will pass @id and @Levelid from front end

---only thing here is that i need to make where dynamic such that if @levelid=3

--- then @id=@id else parentid2=@id

declare @id int

declare @levelid int

set @id=4;

set @levelid=3

if @LevelID=3

begin

;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 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.id=@id group by asicid

end

else

begin

;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.ParentId2,cteloc.id 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 group by asicid

end