0

I have a CTE recursive query that is looking for the root parent for each child. Under normal circumstances, the root parent's ParentID should be null. However if the "root parent" (see childID 4 below) has a ParentID that's supposed to be under the same hierarchy, it'd create an infinite loop.

Normal circumstances

ChildID   ParentID  StartDate  Root Parent
1         2         2014       4
2         3         2013       4
3         4         2012       4
4         NULL      2011       4
5         6         2012       6
6         NULL      2013       6

Infinite Loop

ChildID   ParentID  StartDate  Root Parent  Desired Root Parent
1         2         2014       4            1
2         3         2013       1            1
3         4         2012       2            1
4         1         2011       3            1
5         6         2012       6            6
6         6         2013       6            6

I've created the following query to stop the infinite loop but then the Root Parents for ChildID 1, 2, 3 and 4 were all different. My goal is to assign Root parent 1 to ChildIDs 1,2,3 and 4 based on StartDate (assign the latest).

I am not sure where to start with. Any help will be appreciated. Thanks.

With a as
(   select 1 as childID, 2 as ParentID, 2014 as startDate
    union all select 2 as childID, 3 as ParentID , 2013 as startDate
    union all select 3 as childID, 4 as ParentID, 2012 as StartDate
    --union all select 4 as childID, NULL as ParentID, 2011 as StartDate
    union all select 4 as childID, 1 as ParentID, 2011 as StartDate
    union all select 5 as childID, 6 as ParentID, 2012 as StartDate
    union all select 6 as childID, NULL as ParentID, 2013 as StartDate
)
, RCTE AS
(   SELECT childID
    , ParentID
    , 1 AS Lvl
    , '/' + convert(varchar(max), rtrim(childID)) + '/' [path]
    , startDate
    from a

    UNION ALL

    SELECT rc.childID
    , rh.ParentID
    , Lvl+1 AS Lvl
    , rc.[path] + convert(varchar(max),rtrim(rh.childID)) + '/'
    , rc.startDate
    FROM a rh 
        INNER JOIN RCTE rc ON
    rh.childID = rc.ParentID and 
    rh.childID<>rh.ParentID
    WHERE rc.[path] not like '%/' + convert(varchar(max),rtrim(rh.ParentID)) + '/%'
)
, CTE_RN AS
(    SELECT * 
         , ROW_NUMBER() OVER (PARTITION BY r.childID ORDER BY r.Lvl DESC) RN
     FROM RCTE r
)

SELECT r.childID, 
    case 
        when r.ParentID is null then childID 
        else ParentID 
    end as ParentID
    , rn,[path]
    , r.startDate
FROM CTE_RN r
WHERE RN=1
Order by ChildID, RN
Dmitriy Khaykin
  • 5,238
  • 1
  • 20
  • 32
user2793572
  • 3
  • 1
  • 4
  • [Here](http://stackoverflow.com/questions/15080922/infinite-loop-cte-with-option-maxrecursion-0/15081353#15081353) is an example of detecting loops and stopping the recursion. A handy technique is to just add a limit on depth (`where Lvl < 5 and ...`) until you sort out where things are going astray. – HABO Jan 10 '14 at 17:29
  • Let me understand this. The columns in your table are ChildID, ParentID, StartDate, [root Parent] with your parent id having same as child id for the top most one (4 and 6 in your example instead of null). However, in your Cte you want another column [DesiredRootParent] which will mimic values as your infinite loop table. Is this correct?? – Consult Yarla Jan 10 '14 at 17:32
  • I've done this a few times, and never had a [root Parent] column. If you get a number in ParentId which should be null, that's a bug which requires a bug fix and a data fix - although that's never happened to me. There's only so much you can do in a Database to build in resilience to bugs; at some point the code has to be responsible for data it chucks at the DB. – simon at rcl Jan 10 '14 at 18:10
  • @ConsultYarla, my table has 3 columns childid, parentid and startdate. I am trying to get a 4th column root parent. In my example because childids 1 2 3 4 are creating an infinite loop in the cte, I have to stop the loop somehow. But then I also want those 4 ids root parent to be 1 (based on latest startdate) -> this is what I am trying to figure out. Thanks. – user2793572 Jan 10 '14 at 19:21
  • @simonatrcl,currently we do not have this scenario in the data but most likely will happen in the future. Unfortunately we do not have control over the source data and we have to somehow deal with it. – user2793572 Jan 10 '14 at 19:23

0 Answers0