Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Return Parent Level entry using cte Expand / Collapse
Author
Message
Posted Sunday, September 26, 2010 12:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:23 PM
Points: 31, Visits: 370
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
Posted Sunday, September 26, 2010 5:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:46 AM
Points: 6,933, Visits: 12,666
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
Posted Monday, September 27, 2010 12:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:23 PM
Points: 31, Visits: 370
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
Posted Tuesday, September 28, 2010 2:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:23 PM
Points: 31, Visits: 370
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
Posted Tuesday, September 28, 2010 5:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:23 PM
Points: 31, Visits: 370
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 »

Add to briefcase

Permissions Expand / Collapse