Help with a complex join query

Question

Keep in mind I am using SQL 2000

I have two tables.

tblAutoPolicyList contains a field called PolicyIDList.

tblLossClaims contains two fields called LossPolicyID & PolicyReview.

I am writing a stored proc that will get the distinct PolicyID from PolicyIDList field, and loop through LossPolicyID field (if match is found, set PolicyReview to 'Y').

Sample table layout:

PolicyIDList        LossPolicyID 
9651XVB19       5021WWA85, 4421WWA20, 3314WWA31, 1121WAW11, 2221WLL99       Y
5021WWA85       3326WAC35, 1221AXA10, 9863AAA44, 5541RTY33, 9651XVB19       Y
0151ZVB19       4004WMN63, 1001WGA42, 8587ABA56, 8541RWW12, 9329KKB08       N

How would I go about writing the stored proc (looking for logic more than syntax)?

Keep in mind I am using SQL 2000.

So you are writing a stored procedure to de-normalise your data model? — diagonalbatman, Feb 03 '11 at 17:42
Is the PolicyIDList field a single field with a delimited list of Policy IDs? (AAAA BBBB CCCC or AAAA,BBBB,CCCC) ? — jbehren, Feb 03 '11 at 17:47
PolicyIDList is a field that contains only one ID per row; LossPolicyID is a field with comma seperated values; — user279521, Feb 03 '11 at 18:19
It's ironical how a single item column is named 'ItemList' while at the same time a list column is essentially simply 'Item'. :) Sorry if it's a thousandth time you hear that. Actually I'd like some details about these IDs and about `LossPolicyID` arrangement. Is the length of a single item fixed? Is the list format in `LossPolicyID` fixed (item, comma, space, item, comma, space...) Is the item quantity in a single `LossPolicyID` value fixed (it's always 5 in your example)? Or is there some maximum quantity per row? — Andriy M, Feb 10 '11 at 10:27
length of the single item is fixed; it is comma delimited (I use trim to take off the space); quantity can be 1 policyID or 10 or 15; Its a crazy setup, but I do what I am told to do :-) — user279521, Feb 10 '11 at 12:25

score 2 · Answer 1 · answered Apr 03 '11 at 19:15

2

Select LossPolicyID, * from tableName where charindex('PolicyID',LossPolicyID,1)>0

answered Apr 03 '11 at 19:15

Csharp

2,916
16
50
77

score 1 · Answer 2 · edited May 23 '17 at 11:47

Basically, the idea is this:

'Unroll' tblLossClaims and return two columns: a tblLossClaims key (you didn't mention any, so I guess it's going to be LossPolicyID) and Item = a single item from LossPolicyID.
Find matches of unrolled.Item in tblAutoPolicyList.PolicyIDList.
Find matches of distinct matched.LossPolicyID in tblLossClaims.LossPolicyID.
Update tblLossClaims.PolicyReview accordingly.

The main UPDATE can look like this:

UPDATE claims
   SET PolicyReview = 'Y'
  FROM tblLossClaims claims
  JOIN (
       SELECT DISTINCT unrolled.LossPolicyID
         FROM (
              SELECT LossPolicyID, Item = itemof(LossPolicyID)
                FROM unrolling_join
              ) unrolled
         JOIN tblAutoPolicyList
           ON unrolled.ID = tblAutoPolicyList.PolicyIDList
       ) matched
    ON matched.LossPolicyID = claims.LossPolicyID

You can take advantage of the fixed item width and the fixed list format and thus easily split LossPolicyID without a UDF. I can see this done with the help of a number table and SUBSTRING(). unrolling_join in the above query is actually tblLossClaims joined with the number table.

Here's the definition of unrolled 'zoomed in':

...
(
SELECT LossPolicyID,
       Item = SUBSTRING(LossPolicyID,
                        (v.number - 1) * @ItemLength + 1,
                        @ItemLength)
  FROM tblLossClaims c
  JOIN master..spt_values v ON v.type = 'P'
   AND v.number BETWEEN 1 AND (LEN(c.LossPolicyID) + 2) / (@ItemLength + 2)
) unrolled
...

master..spt_values is a system table that is used here as the number table. Filter v.type = 'P' gives us a rowset with number values from 0 to 2047, which is narrowed down to the list of numbers from 1 to the number of items in LossPolicyID. Eventually v.number serves as an array index and is used to cut out single items.

@ItemLength is of course simply LEN(tblAutoPolicyList.PolicyIDList). I would probably also declared @ItemLength2 = @ItemLength + 2 so it wasn't calculated every time when applying the filter.

Basically, that's it, if I haven't missed anything.

score 0 · Answer 3 · answered Feb 03 '11 at 17:51

0

If the PolicyIDList field is a delimited list, you have to first separate the individual policy IDs and create a temporary table with all of the results. Next up, use an update query on the tblLossClaims with 'where exists (select * from #temptable tt where tt.PolicyID = LossPolicyID).

Depending on the size of the table/data, you might wish to add an index to your temporary table.

answered Feb 03 '11 at 17:51

jbehren

780
6
11

Also, check out http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648 for help on creating a split function – jbehren Feb 03 '11 at 17:52

Help with a complex join query

3 Answers3