Return Parent Level entry using cte

  • 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,

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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

  • 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

  • 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