Post reply

problems flattening off and then normalising

  • February 1, 2013 at 5:32 am

    #288410

    This has been driving me nuts for the last few days, I just can't get my head around it on a Friday.

    I have a table with records that look like this (data simplified for example)

    [font="Courier New"]

    PolicySeq# CustID Pol# polType elements

    1 123 111 AA A,B,C,D,E,F,G

    2 123 112 BB B,C,D

    2 123 444 CC A,E,F

    1 789 321 FA A,B,D,E,G,H,J

    2 789 555 DS A,B,C

    2 789 558 JH A,G,H,K

    [/font]

    What I am trying to do is find out which elements exist on policyseq# 1 that do not exist in the policySeq#2 for each customer.

    E.g. for Cust 123

    A,B,C,D,E,F were existing elements on policyseq#2 (The sequence# is duplicated because it indicates that both policies were ended at the same time: the elements were then transferred to the new policy)

    Element G is a new element on this policy

    For Cust 789

    A,B,C,G,H were transferred to the new policy: A was a duplicated element but I only need to know it existed, not how many times it existed.

    E and J were new elements

    K was dropped

    The challenge is to flatten off the seq#2 records (there may be up to 5 of them) and concatenate the element strings together, removing duplicate values THEN compare each element of the string in seq#1 elements with the combined string of seq#2 elements

    If it helps the list of elements is available against each policy in a normalised form

    [font="Courier New"]Poltype Element

    AA A

    AA B

    ...

    JH H

    JH K[/font]

    TIA

    Obiron

  • February 1, 2013 at 5:53 am

    #1583396

    Table definition and easily usable data would be a great help, but this may be a start...

    SELECT <stuff> FROM Policies p INNER JOIN PolicyDetails pd ON p.PolType = pd.PolType WHERE PolicySeq = 1 AND NOT EXISTS (

    SELECT 1 FROM Policies p_inner INNER JOIN PolicyDetails pd_inner ON p.PolType = pd.PolType WHERE PolicySeq = 2 AND p_inner.CustID = p.CustID AND pd_inner.Element = pd.Element

    If that's not what you want, please post table definitions and easily usable data as per http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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