Simple recursive CTE stuck in infinite loop

Question

I have a need for a recursive query to find daisy chained related data. I have the following query

CREATE TABLE #source
(Parent varchar(10), Child varchar(10), Date datetime)


INSERT INTO #source
(Parent, Child, Date)
VALUES
('05529744', '05395782', '9999-12-31'),
('05395782', '05529744', '2017-12-31'),
('05147762', '05395782', '2016-12-31'),
('04564728', '05147762', '2016-09-30'),
('04299824', '04564728', '2013-07-31'),
('05224920', '04999841', '2017-03-31'),
('04999841', '05224920', '2016-10-31'),
('04824720', '04999841', '2015-12-31'),
('02917870', '04824720', '2015-06-30')

;with 
descendants as
  ( select parent, child as descendant, Date, 1 as level
    from #source
  union all
   select d.parent, s.child, d.Date, d.level + 1
     from descendants as d
  join #source s
    on d.descendant = s.parent
) 
select *
from descendants 

--order by parent, level, descendant
option (maxrecursion 0) ;

DROP TABLE #source

As you can see from the test data, the parent and child fields daisy-chain back and forth. Unfortunately, it's getting stuck in an infinite loop when I run the above query and I'm scratching my head as to why.

The final goal is to get the data to look like this:

Parent      Child       Date          Rank
05529744    05529744    9999-12-31    1
05529744    05395782    2017-12-31    2
05529744    05147762    2016-12-31    3
05529744    04564728    2016-09-30    4
05529744    04299824    2013-07-31    5
05224920    05224920    2017-03-31    1
05224920    04999841    2016-10-31    2
05224920    04824720    2015-12-31    3
05224920    02917870    2015-06-30    4

Answer 1

I think you are missing the anchor WHERE...

select parent, child as descendant, Date, 1 as level
    from #source where <<SOMETHING TO SELECT JUST FEW RECORDS>>

Otherwise, every time you will be selecting all the records.

Moreover, check the first 2 records...

('05529744', '05395782', '9999-12-31'),
('05395782', '05529744', '2017-12-31'),

They are cyclical... This will never finish...
Fix the data, give it another try and let me know

Other thing that will throw error is the date convertion... Your date fields should look like this:

convert(date,'9999-12-31',120)

Fixing some of the data. The following CTE will work:

CREATE TABLE #source
(Parent varchar(10), Child varchar(10), Date datetime)

INSERT INTO #source
(Parent, Child, Date)
VALUES
('05529744', '05529744', convert(date,'9999-12-31',120)),
('05395782', '05395782', convert(date,'2017-12-31',120)),
('05395782', '05147762', convert(date,'2016-12-31',120)),
('05147762', '04564728', convert(date,'2016-09-30',120)),
('04564728', '04299824', convert(date,'2013-07-31',120)),
('05224920', '05224920', convert(date,'2017-03-31',120)),
('04999841', '04999841', convert(date,'2016-10-31',120)),
('04999841', '04824720', convert(date,'2015-12-31',120)),
('04824720', '02917870', convert(date,'2015-06-30',120))

select * from #source order by 1

;with 
descendants as
  ( select parent, child as descendant, Date, 1 as level
    from #source where Parent = Child
  union all
   select d.parent, s.child, d.Date, d.level + 1
     from descendants as d
  join #source s
    on d.descendant = s.Parent
    where s.Child <> s.Parent
) 
select *
from descendants 

order by parent, level, descendant
option (maxrecursion 0) ;

DROP TABLE #source;