1

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
  • This looks like a job for a hierarchical query. – Michael Kutz Aug 20 '18 at 21:27
3

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;
  • Ahh.....makes sense that it is referencing itself. Only problem with getting the data in check is that it is inconsistent. Appreciate your help on this. I have a good idea on how to get the data into a staging table to perform the recursion. – jdids Aug 21 '18 at 14:17
  • Yeap, inconsistent data usually returns undesired results, no matter how good is the query. Anyway, I wish you good luck and success! – AMG Aug 21 '18 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.