September 26, 2010 at 12:30 am
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 stones2600
Building of Rooms2400
i want to return
Education 2600
Private Sector2400
can some one help me to solve this.
regards,
September 26, 2010 at 5:33 am
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
September 27, 2010 at 12:15 am
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
September 28, 2010 at 2:56 am
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
September 28, 2010 at 5:12 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply