Post reply

parent/child relationships

  • 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.

  • March 23, 2016 at 5:33 am

    #1867037

    Looks like a case for a recursive CTE

  • March 23, 2016 at 6:34 am

    #1867056

    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.

    LinkedIn

  • March 23, 2016 at 8:56 am

    #1867115

    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)
    Intro to Tally Tables and Functions

  • March 23, 2016 at 12:32 pm

    #1867200

    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.

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

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