Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
sandhya.pingale
sandhya.pingale
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 15
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.
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 898
Can you give some sample data and the CTE you are currently using.

CTEs should work for this scenario
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45434 Visits: 39942
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sandhya.pingale
sandhya.pingale
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 15
Hi Aaron,

Thanks for quick reply.

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

ContractDateID PredecessorContractDateID
570 NULL
572 NULL
574 NULL
576 NULL
578 NULL
580 NULL
582 NULL
584 NULL
586 NULL
588 NULL
596 NULL
604 NULL
605 NULL
606 NULL
607 NULL
608 NULL
609 NULL
610 NULL
611 NULL
612 NULL
613 NULL
614 NULL
615 NULL
616 NULL
617 NULL
618 NULL
619 NULL
620 NULL
621 NULL
622 NULL
623 NULL
624 NULL
625 NULL
626 NULL
627 NULL
630 NULL
601 NULL
590 NULL
597 570
598 570
592 570
571 570
573 572
592 572
599 574
575 574
577 576
592 576
595 576
602 578
579 578
581 580
602 580
602 582
583 582
585 584
592 584
592 586
587 586
589 588
599 588
592 590
591 590
593 592
594 592
599 592
600 599
602 599
603 602

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.

ContractDateID PredecessorContractDateID HLevel
570 NULL 0
572 NULL 0
574 NULL 0
576 NULL 0
578 NULL 0
580 NULL 0
582 NULL 0
584 NULL 0
586 NULL 0
588 NULL 0
590 NULL 0
596 NULL 0
601 NULL 0
604 NULL 0
605 NULL 0
606 NULL 0
607 NULL 0
608 NULL 0
609 NULL 0
610 NULL 0
611 NULL 0
612 NULL 0
613 NULL 0
614 NULL 0
615 NULL 0
616 NULL 0
617 NULL 0
618 NULL 0
619 NULL 0
620 NULL 0
621 NULL 0
622 NULL 0
623 NULL 0
624 NULL 0
625 NULL 0
626 NULL 0
627 NULL 0
630 NULL 0
571 570 1
573 572 1
575 574 1
577 576 1
579 578 1
581 580 1
583 582 1
585 584 1
587 586 1
589 588 1
591 590 1
592 570 1
592 572 1
592 576 1
592 584 1
592 586 1
592 590 1
595 576 1
597 570 1
598 570 1
599 574 1
599 588 1
602 578 1
602 580 1
602 582 1
593 592 2
594 592 2
599 592 2
600 599 2
602 599 2
603 602 2
600 599 3 --duplicate
602 599 3
603 602 3
603 602 4 --duplicate

How such data can be avoided in recursive heirachy.

Thanks & Regards,
Sandhya.
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 898
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)

Con Prev Lvl Path
603 602 3 0-578-602
603 602 3 0-580-602
603 602 3 0-582-602
603 602 4 0-574-599-602
603 602 4 0-588-599-602
603 602 5 0-570-592-599-602
603 602 5 0-572-592-599-602
603 602 5 0-576-592-599-602
603 602 5 0-584-592-599-602
603 602 5 0-586-592-599-602
603 602 5 0-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?
sandhya.pingale
sandhya.pingale
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 15
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search