February 1, 2013 at 5:32 am
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
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply