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.