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

SQL query to get all predecessors and successors for given node. Expand / Collapse
Author
Message
Posted Monday, September 03, 2012 4:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 05, 2012 7:01 AM
Points: 3, Visits: 14
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.
Post #1353440
Posted Monday, September 03, 2012 3:01 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:49 PM
Points: 366, Visits: 577
Can you give some sample data and the CTE you are currently using.

CTEs should work for this scenario
Post #1353636
Posted Monday, September 03, 2012 8:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353674
Posted Tuesday, September 04, 2012 3:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 05, 2012 7:01 AM
Points: 3, Visits: 14
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.
Post #1353787
Posted Tuesday, September 04, 2012 3:38 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:49 PM
Points: 366, Visits: 577
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?
Post #1354224
Posted Tuesday, September 04, 2012 11:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 05, 2012 7:01 AM
Points: 3, Visits: 14
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.
Post #1354326
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse