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