Recursive CTE Alternative

Question

Recursive CTE Alternative

scottcabral

Default port

Points: 1495
More actions
July 18, 2016 at 6:28 pm

#309213

Hi,
here is a subset of data i have in a Policy Relationship Table:
DECLARE @PolicyRelationship TABLE
(
Country nvarchar(50),
AccountNum nvarchar(20),
PolicyNum nvarchar(20),
PreviousPolicyNum nvarchar(20),
PolicyEffDt datetime
)
insert into @PolicyRelationship
select 'California' as Country,
'1234' as AccountNum,
'A' as PolicyNum,
'B' as PreviousPolicyNum,
'1-1-2016' as PolicyEffDt
UNION ALL
select 'California' as Country,
'1234' as AccountNum,
'B' as PolicyNum,
'C' as PreviousPolicyNum,
'1-1-2015' as PolicyEffDt
UNION ALL
select 'California' as Country,
'1234' as AccountNum,
'C' as PolicyNum,
'D' as PreviousPolicyNum,
'1-1-2014' as PolicyEffDt
UNION ALL
select 'California' as Country,
'5678' as AccountNum,
'E' as PolicyNum,
'F' as PreviousPolicyNum,
'1-1-2016' as PolicyEffDt
UNION ALL
select 'California' as Country,
'5678' as AccountNum,
'F' as PolicyNum,
null as PreviousPolicyNum,
'1-1-2015' as PolicyEffDt
UNION ALL
select 'Texas' as Country,
'9999' as AccountNum,
'G' as PolicyNum,
null as PreviousPolicyNum,
'1-1-2016' as PolicyEffDt
select *
from @PolicyRelationship
what i need to do is create a new field called RootPolicyNumber that will store the most recent policy for each Country, Account, Policy relationship. For example, for California Account 1234, Policy A is related to Policy B, Policy B is Related to Policy C and Policy C is related to Policy. If i order by Policy Eff Date descending, the root policy number for A, B, C, D is Policy A because it is the top level policy by Policy Eff Date.
so in the end the data would need to look like this:
select 'California' as Country,
'1234' as AccountNum,
'A' as PolicyNum,
'B' as PreviousPolicyNum,
'1-1-2016' as PolicyEffDt,
'1234' as RootPolicyNum
UNION ALL
select 'California' as Country,
'1234' as AccountNum,
'B' as PolicyNum,
'C' as PreviousPolicyNum,
'1-1-2015' as PolicyEffDt,
'1234' as RootPolicyNum
UNION ALL
select 'California' as Country,
'1234' as AccountNum,
'C' as PolicyNum,
'D' as PreviousPolicyNum,
'1-1-2014' as PolicyEffDt,
'1234' as RootPolicyNum
UNION ALL
select 'California' as Country,
'5678' as AccountNum,
'E' as PolicyNum,
'F' as PreviousPolicyNum,
'1-1-2016' as PolicyEffDt,
'5678' as RootPolicyNum
UNION ALL
select 'California' as Country,
'5678' as AccountNum,
'F' as PolicyNum,
null as PreviousPolicyNum,
'1-1-2015' as PolicyEffDt,
'5678' as RootPolicyNum
UNION ALL
select 'Texas' as Country,
'9999' as AccountNum,
'G' as PolicyNum,
null as PreviousPolicyNum,
'1-1-2016' as PolicyEffDt,
'9999' as RootPolicyNum
I tried using Recursive CTE and it works with a small number of accounts, but the table has thousands of policies and relationships and i get the 'exceeds allowed limit' error. If i try to use the Option(Maxrecursion) with 0 it runs for hours, so I don't think it is the best option for my data set.
Is there another way to perform this task without using Recursive CTE?
Scott

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 1

Hi Scott,

A properly formed rCTE will traverse a well formed million node hierarchy in less than a minute on most machines. If it doesn't, with the code is incorrect or there is a hierarchical loop in one of the downlines.

While I'm taking a look at a couple of things for you, do me a favor... post the rCTE code the resolves your example (well done, BTW) and then run the following code on your real data. Although the absence of rows from that isn't conclusive proof that a loop doesn't exist, it's the first step to making sure this type of "Adjacency List" (Child/Parent) list is properly formed.

SELECT PolicyNum, Occurences = COUNT(*)

FROM dbo.YourTableNameHere

GROUP BY PolicyNum

HAVING COUNT(*) > 1

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63434 More actions · Answer 2

After taking Jeff's advice...

maybe this works?

SELECT Country

, AccountNum

, PolicyNum As CurrentPolicy

, LAG(PolicyNum,1) OVER (PARTITION BY Country, AccountNum ORDER BY PolicyEffDt) AS PrevPolicy

FROM @PolicyRelationship

ORDER BY Country

,AccountNum;