Return Parent Level entry using cte

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Return Parent Level entry using cte

Return Parent Level entry using cte

Rate Topic

Display Mode

Topic Options

Author

Message

sayedkhalid99

Posted Sunday, September 26, 2010 12:30 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 07, 2013 12:28 AM
Points: 23, Visits: 332

CREATE TABLE [dbo].[Asic]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[NAme] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentID] [int] NULL,
[LevelID] [nchar])
CREATE TABLE [dbo].[Project](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AsicId] [int] NULL,
[Cost] [numeric](18, 0) NULL)
insert into dbo.asic(name,parentid,levelid) values('Education',NULL,1)

insert into dbo.asic(name,parentid,levelid) values('Construction of School',1,2)

insert into dbo.asic(name,parentid,levelid) values('Building of Rooms',2,3)

insert into dbo.asic(name,parentid,levelid) values('Private Sector',NULL,1)

insert into dbo.asic(name,parentid,levelid) values('Construction of Road',4,2)

insert into dbo.asic(name,parentid,levelid) values('pooring of stones',5,3)

insert into dbo.project(projectname,asicid,cost) values('Construction of building',3,100)
insert into dbo.project(projectname,asicid,cost) values('Pooring of rock by attaturk co',3,100)
insert into dbo.project(projectname,asicid,cost) values('Construction of building 2',6,100)
insert into dbo.project(projectname,asicid,cost) values('Pooring of rock 2 by attaturk co',6,100)

2- ;with cte
as
(
select id,Name,parentid,levelid from dbo.asic where parentid is null
union all
select a.id,a.name,a.parentid,a.levelid from dbo.asic a inner join cte
on a.parentid=cte.id
)
, cte1
as
(
select count(projectname) TotalProject,sum(cost)TotalCost,AsicId from project
group by asicid
)

--select * from cte order by id
select c.name,TotalProject,TotalCost from cte1
inner join cte c on c.id=cte1.asicid

the problem here is that asic with levelid =3 is entered into project table,i want the output to return asic at Main level which is at levelid= 1 instead of asic at levelid=3
now it returns
pooring of stones 2 600
Building of Rooms 2 400
i want to return
Education 2 600
Private Sector 2 400

can some one help me to solve this.
regards,

Post #993340

LutzM

Posted Sunday, September 26, 2010 5:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

I added the top level name as an addtl. column (Level1Name) and moved the calculation to the final select (therewith removing the cte1 subquery).

;WITH 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
)
SELECT 
    MAX(Level1Name) AS name, 
    COUNT(projectname) TotalProject,
    SUM(cost)TotalCost
FROM @project p
INNER JOIN cte ON cte.id=p.asicid
GROUP BY p.asicid

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #993355

sayedkhalid99

Posted Monday, September 27, 2010 12:15 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 07, 2013 12:28 AM
Points: 23, Visits: 332

thanks alot LutzM the problem is solved . I have one another problem in here how can i parameterized this cte incase i want to pass locationid at any level.
if i pass ID =1 it shall pass take all the locations under 1, if 2 it shall look for locationid 3 and 4.
means if i pass parent it shall look for all it's childs entry location in project page.
do i need to create another cte,or is it possible to update this.

CREATE TABLE [dbo].[LocationTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LocationName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentID] [int] NULL,
[LevelID] [int] NULL)

insert into [LocationTable](LocationName,ParentId,Levelid) values('A',NULL,1)
insert into [LocationTable](LocationName,ParentId,Levelid) values('B',1,2)
insert into [LocationTable](LocationName,ParentId,Levelid) values('C',2,3)
insert into [LocationTable](LocationName,ParentId,Levelid) values('D',2,3)
insert into [LocationTable](LocationName,ParentId,Levelid) values('E',1,2)
insert into [LocationTable](LocationName,ParentId,Levelid) values('F',5,3)
go
alter table project
add locationid int
go
update project set locationid=3 where id=1
update project set locationid=4 where id=3

Post #993448

sayedkhalid99

Posted Tuesday, September 28, 2010 2:56 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 07, 2013 12:28 AM
Points: 23, Visits: 332

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

Post #994255

sayedkhalid99

Posted Tuesday, September 28, 2010 5:12 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 07, 2013 12:28 AM
Points: 23, Visits: 332

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

Post #994328

« Prev Topic | Next Topic »

Permissions