InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 02:29:46 GMT20http://www.sqlservercentral.com/Forums/Topic1353440-391-1.aspxHi 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.Tue, 04 Sep 2012 23:31:30 GMTsandhya.pingalehttp://www.sqlservercentral.com/Forums/Topic1353440-391-1.aspxWith 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[code="sql"]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) [/code]CTE query[code="sql"]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[/code]you will get the following results set (partial shown)[code="sql"]Con Prev Lvl Path603 602 3 0-578-602603 602 3 0-580-602603 602 3 0-582-602603 602 4 0-574-599-602603 602 4 0-588-599-602603 602 5 0-570-592-599-602603 602 5 0-572-592-599-602603 602 5 0-576-592-599-602603 602 5 0-584-592-599-602603 602 5 0-586-592-599-602603 602 5 0-590-592-599-602[/code]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?Tue, 04 Sep 2012 15:38:08 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1353440-391-1.aspxHi 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 HLevelWhich gives duplicate contractdateid and predecessorscontractID at different level.ContractDateID PredecessorContractDateID HLevel570 NULL 0572 NULL 0574 NULL 0576 NULL 0578 NULL 0580 NULL 0582 NULL 0584 NULL 0586 NULL 0588 NULL 0590 NULL 0596 NULL 0601 NULL 0604 NULL 0605 NULL 0606 NULL 0607 NULL 0608 NULL 0609 NULL 0610 NULL 0611 NULL 0612 NULL 0613 NULL 0614 NULL 0615 NULL 0616 NULL 0617 NULL 0618 NULL 0619 NULL 0620 NULL 0621 NULL 0622 NULL 0623 NULL 0624 NULL 0625 NULL 0626 NULL 0627 NULL 0630 NULL 0571 570 1573 572 1575 574 1577 576 1579 578 1581 580 1583 582 1585 584 1587 586 1589 588 1591 590 1592 570 1592 572 1592 576 1592 584 1592 586 1592 590 1595 576 1597 570 1598 570 1599 574 1599 588 1602 578 1602 580 1602 582 1593 592 2594 592 2599 592 2600 599 2602 599 2603 602 2600 599 3 --duplicate602 599 3603 602 3603 602 4 --duplicateHow such data can be avoided in recursive heirachy.Thanks & Regards,Sandhya.Tue, 04 Sep 2012 03:45:20 GMTsandhya.pingalehttp://www.sqlservercentral.com/Forums/Topic1353440-391-1.aspx[quote][b]sandhya.pingale (9/3/2012)[/b][hr]How can i get the correct output without duplicate hierarchical records.[/quote]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.Mon, 03 Sep 2012 20:06:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353440-391-1.aspxCan you give some sample data and the CTE you are currently using.CTEs should work for this scenarioMon, 03 Sep 2012 15:01:21 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1353440-391-1.aspxHi 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.Mon, 03 Sep 2012 04:41:37 GMTsandhya.pingale