parent/child relationships

Question

Post reply

parent/child relationships

scottcabral

Default port

Points: 1495
More actions
March 22, 2016 at 7:14 pm

#314230

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.

Viewing 5 posts - 1 through 4 (of 4 total)

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

xanthos SSCrazy Points: 2510 More actions · Answer 1

xanthos

SSCrazy

Points: 2510

March 23, 2016 at 5:33 am

#1867037

Looks like a case for a recursive CTE

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 2

Could you post a clear representation of the output you'd like please? I gave it a go but got a bit confused with the description of what you'd like to see.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

scottcabral (3/22/2016)
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.

First, thanks for posting readily consumable data. However, something is wrong with the data you posted. According to the data you provided, the following is incorrect.

Policy 100024 was also renewed and became Policy 1007441 effective 11-1-2015.

--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)

scottcabral Default port Points: 1495 More actions · Answer 4

Hi,

yes that was a typo on my part. The policy should be 1000124.

I'm trying to join the tables together and at the same time capture that there is a parent child relationship between policy 1007440 and policy 1000124 (1002440 is parent of 1000124) and also between 1000124 and UK120241 (1000124 is parent of UK120241).

therefore policy 1007440 is parent of both relationships.