Post reply

SQL query to get all predecessors and successors for given node.

  • September 3, 2012 at 4:41 am

    #266152

    Hi All,

    I have table which holds ID and predecessors ID. One ID can have multiple predecessors. In such scenario I want list of all the possible predecessors and successors IDs for given ID. I have tried to get all the possible IDs using recursive hierarchical CTE but it gives duplicate ID and Predecessor ID combination at different level which is not correct. How can i get the correct output without duplicate hierarchical records. I am using MSSQL2008 server.

    Thanks.

    Sandhya.

  • September 3, 2012 at 3:01 pm

    #1533065

    Can you give some sample data and the CTE you are currently using.

    CTEs should work for this scenario

  • September 3, 2012 at 8:06 pm

    #1533093

    sandhya.pingale (9/3/2012)

    How can i get the correct output without duplicate hierarchical records.

    Create a "Hierarchical Path" column and check to make sure the next recursion isn't going to already appear in the "Hieracrical Path" column. It's the same "trick" used to find all paths from one node to another in a node net or undirected cyclic graph.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 4, 2012 at 3:45 am

    #1533182

    Hi Aaron,

    Thanks for quick reply.

    I have data as follows which is related to tasks schedule.

    ContractDateIDPredecessorContractDateID

    570NULL

    572NULL

    574NULL

    576NULL

    578NULL

    580NULL

    582NULL

    584NULL

    586NULL

    588NULL

    596NULL

    604NULL

    605NULL

    606NULL

    607NULL

    608NULL

    609NULL

    610NULL

    611NULL

    612NULL

    613NULL

    614NULL

    615NULL

    616NULL

    617NULL

    618NULL

    619NULL

    620NULL

    621NULL

    622NULL

    623NULL

    624NULL

    625NULL

    626NULL

    627NULL

    630NULL

    601NULL

    590NULL

    597570

    598570

    592570

    571570

    573572

    592572

    599574

    575574

    577576

    592576

    595576

    602578

    579578

    581580

    602580

    602582

    583582

    585584

    592584

    592586

    587586

    589588

    599588

    592590

    591590

    593592

    594592

    599592

    600599

    602599

    603602

    hierarchical recursive query is as follows

    ;WITH DatesHierarchy(ContractDateID, PredecessorContractDateID, HLevel) AS

    (SELECT

    ContractDateID,

    PredecessorContractDateID,

    0 AS Expr1

    FROM dbo.vw_PrjContrDtsPrdcsr

    AS PRDC1 WITH

    (NOLOCK)

    WHERE (PredecessorContractDateID IS NULL)

    UNION ALL

    SELECT

    PRDC2.ContractDateID,

    PRDC2.PredecessorContractDateID,

    DH.HLevel + 1 AS Expr1

    FROM dbo.vw_PrjContrDtsPrdcsr

    AS PRDC2 WITH

    (NOLOCK)

    INNER JOIN DatesHierarchy AS DH ON

    PRDC2.PredecessorContractDateID = DH.ContractDateID)

    SELECT DISTINCT TOP (100) PERCENT ContractDateID, PredecessorContractDateID, HLevel

    FROM DatesHierarchy AS DatesHierarchy_1

    ORDER BY HLevel

    Which gives duplicate contractdateid and predecessorscontractID at different level.

    ContractDateIDPredecessorContractDateIDHLevel

    570NULL0

    572NULL0

    574NULL0

    576NULL0

    578NULL0

    580NULL0

    582NULL0

    584NULL0

    586NULL0

    588NULL0

    590NULL0

    596NULL0

    601NULL0

    604NULL0

    605NULL0

    606NULL0

    607NULL0

    608NULL0

    609NULL0

    610NULL0

    611NULL0

    612NULL0

    613NULL0

    614NULL0

    615NULL0

    616NULL0

    617NULL0

    618NULL0

    619NULL0

    620NULL0

    621NULL0

    622NULL0

    623NULL0

    624NULL0

    625NULL0

    626NULL0

    627NULL0

    630NULL0

    5715701

    5735721

    5755741

    5775761

    5795781

    5815801

    5835821

    5855841

    5875861

    5895881

    5915901

    5925701

    5925721

    5925761

    5925841

    5925861

    5925901

    5955761

    5975701

    5985701

    5995741

    5995881

    6025781

    6025801

    6025821

    5935922

    5945922

    5995922

    6005992

    6025992

    6036022

    6005993 --duplicate

    6025993

    6036023

    6036024 --duplicate

    How such data can be avoided in recursive heirachy.

    Thanks & Regards,

    Sandhya.

  • September 4, 2012 at 3:38 pm

    #1533554

    With the data you have provided, you will get 'duplicates' because there are different ways to end up at 603 from 602. Some paths use 2 steps, some 3 and some 4, so it is a valid path at all three hierachies.

    Try the following:

    DML and data seed

    create table sandyha

    (

    ContractDateID int,

    PredecessorContractDateID int

    )

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (570 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (572 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (574 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (576 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (578 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (580 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (582 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (584 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (586 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (588 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (596 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (604 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (605 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (606 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (607 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (608 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (609 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (610 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (611 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (612 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (613 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (614 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (615 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (616 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (617 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (618 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (619 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (620 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (621 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (622 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (623 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (624 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (625 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (626 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (627 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (630 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (601 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (590 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (597 ,570)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (598 ,570)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,570)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (571 ,570)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (573 ,572)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,572)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (599 ,574)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (575 ,574)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (577 ,576)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,576)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (595 ,576)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (602 ,578)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (579 ,578)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (581 ,580)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (602 ,580)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (602 ,582)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (583 ,582)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (585 ,584)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,584)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,586)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (587 ,586)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (589 ,588)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (599 ,588)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,590)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (591 ,590)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (593 ,592)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (594 ,592)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (599 ,592)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (600 ,599)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (602 ,599)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (603 ,602)

    CTE query

    with CTE as

    (

    SELECT

    s.ContractDateID,

    s.PredecessorContractDateID,

    1 as level,

    cast(cast(coalesce(s.PredecessorContractDateID,'') as nvarchar(5)) as nvarchar(255)) as DHPath

    FROM

    sandyha s

    WHERE

    s.PredecessorContractDateID is null

    UNION ALL

    SELECT

    s.ContractDateID,

    s.PredecessorContractDateID,

    (x.level +1) as level,

    cast(x.DHPath + '-' + cast(coalesce(s.PredecessorContractDateID,'') as nvarchar(5)) as nvarchar(255)) as DHPath

    FROM

    sandyha s

    join

    cte x on x.ContractDateID = s.PredecessorContractDateID

    WHERE

    s.PredecessorContractDateID is not null

    )

    select distinct * from CTE order by contractdateID,level

    you will get the following results set (partial shown)

    ConPrevLvlPath

    60360230-578-602

    60360230-580-602

    60360230-582-602

    60360240-574-599-602

    60360240-588-599-602

    60360250-570-592-599-602

    60360250-572-592-599-602

    60360250-576-592-599-602

    60360250-584-592-599-602

    60360250-586-592-599-602

    60360250-590-592-599-602

    So you can get to 603 through 3, 4 or 5 predecessors, depending on where you start.

    What is the business problem you are trying to solve?

  • September 4, 2012 at 11:31 pm

    #1533628

    Hi Aaron,

    Thanks for the solution. Actuly depending upon this heirarchy i am going to calculate the start and end dates of tasks when end date is closed. If the entry is going to be duplicate it will calculate result as many time as record is appearing.

    Thanks & Regards,

    Sandhya.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply