SQLTeam.com | Weblogs | Forums

Parent/child relationships


#1

have the following 2 tables:

declare @Policy table
(
AccountNum nvarchar(5),
PolicyNum nvarchar(20),
CurrentStatus nvarchar(50)
)

declare @PolicyRelationship table
(
AccountNum nvarchar(5),
PolicyNum nvarchar(20),
PolicyRelationshipType nvarchar(5),
RelatedPolicyNum nvarchar(20),
PolicyRelationshipEffDt datetime,
PolicyRelationshipExpDt datetime
)

insert into @policy values ('31515', '1000124', 'Approved')
insert into @policy values ('31515', '1000203', 'Renewed')
insert into @policy values ('31515', '1007440', 'Renewed')
insert into @policy values ('31515', '1007441', 'Renewed')
insert into @policy values ('31515', '1009999', 'New')

insert into @PolicyRelationship values ('31515', '1000124', 'REN', 'UK120241', '2014-11-01 00:00:00.000', '2015-10-31 00:00:00.000')
insert into @PolicyRelationship values ('31515', '1000203', 'REN', 'UK120210', '2014-11-01 00:00:00.000', '2016-10-31 00:00:00.000')
insert into @PolicyRelationship values ('31515', '1007440', 'REN', '1000124', '2015-11-01 00:00:00.000', '2016-10-31 00:00:00.000')
insert into @PolicyRelationship values ('31515', '1007441', 'REN', '1005123', '2015-11-01 00:00:00.000', '2016-10-31 00:00:00.000')

What I need to do is join the tables on account and Policy Number. I will need fields from both tables, along with a group ranking field to group parent/child relationships.

for example, in the tables above you will see that Policy 100024 was a renewal of Policy UK120241 effective 11-1-2014. Policy 100024 was also renewed and became Policy 1007441 effective 11-1-2015. I will need to create a group rank showing policy 1007441 with Renewal 100024 as group rank 1 and Polciy 100024 with Renewal UK120241 with group rank 2. The other policies will have group rank 1 as they do not have more than 1 parent/child relationship.


#2

there is no Policy 100024 do you mean Policy 1000124
also "and became Policy 1007441" do you mean 1007440 ?


#3

sorry, yes that was a typo. Policy 1000124 became policy 1007440