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
hierarchical query
. – Michael Kutz Aug 20 '18 at 21:27