Recursive CTE Alternative

Transact-SQL
1

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

2

you might want to read Adam Machanic's excellent article on parallelizing Recursive CTEs

http://sqlblog.com/blogs/adam_machanic/archive/2015/04/07/re-inventing-the-recursive-cte.aspx

3

hi

hope this helps

after 9 years ... :wink: :stuck_out_tongue_winking_eye:

create data script

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

;WITH PolicyHierarchy AS (  
    SELECT   
        Country,  
        AccountNum,  
        PolicyNum,  
        PreviousPolicyNum,  
        PolicyEffDt,  
        CONVERT(nvarchar(max), '/' + PolicyNum) AS Path,  
        ROW_NUMBER() OVER (PARTITION BY AccountNum ORDER BY PolicyEffDt DESC) AS Level  
    FROM   
        @PolicyRelationship  
),  
RootPolicies AS (  
    SELECT   
        Country,  
        AccountNum,  
        PolicyNum AS RootPolicyNum  
    FROM   
        PolicyHierarchy  
    WHERE   
        Level = 1  
)  
SELECT   
    p.Country,  
    p.AccountNum,  
    p.PolicyNum,  
    p.PreviousPolicyNum,  
    p.PolicyEffDt,  
    r.RootPolicyNum  
FROM   
    @PolicyRelationship p  
LEFT JOIN   
    RootPolicies r   
ON   
    p.Country = r.Country AND   
    p.AccountNum = r.AccountNum;

image
image696×350 8.28 KB