Posted by clare at snyder.on.ca on 03/20/06 02:09

On Sun, 19 Mar 2006 22:26:37 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

> (clare at snyder.on.ca) writes:
>> On Sun, 19 Mar 2006 11:06:27 +0000 (UTC), Erland Sommarskog
>><esquel@sommarskog.se> wrote:
>>
>>> (clare at snyder.on.ca) writes:
>>>> There is ONE condition that I forgot to mention in the polno field.
>>>> Not only do they add a different suffix for every year's renewal(which
>>>> now appears to be a non-issue) but if a policy is cancelled, it can
>>>> have CANC at the end, or NOTRENEWED.
>>>
>>>AND polno NOT LIKE '%CANC'
>>>AND polno NOT LIKE '%NOTRENEWED'
>>
>>
>> In my last post I said:
>> I have tried putting "and polno not like '1932700%CANC'" in the line
>> before "AND c.email IS NOT NULL"and I still get the cancelled policy,
>> but only once - without the CANC on the polno.
>> The cancelled policy still shows up, butas due for renewal, and not
>> cancelled.
>>
>> Also said "Putting the line in after the "Where polno like" line has
>> no effect.", in other words, putting it there does not remove either
>> the duplicate OR both incidents of the record.
>
>There is a common recommendation for posts to the SQL Server newsgroups
>and that is that you include:
>
>o CREATE TABLE statements for your tables.
>o INSERT statemetns with sample data.
>o The desired output given the sample.
>
>If you put that effort into your post, it is likely that someone will
>make the simple effort of copying and pasting the script into a
>query tool to develop a tested solution.
>
>If you make less effort, the person who answer will also make less effort
>in his posting, and the answers you get be less accurate.
>
>I would have guessed the NOT LIKE clauses would make it, because if
>their latest policy does not have CANC, then I don't understand what
>is going on. If there is an earlier cancellation, would that not mean
>that they have come back.

The problem is the cancellation is processed on the renewal date. If
the cancellation was entered the day after the renewal/expiry date it
would not be a problem.
As for the create and insert statements, I have never had to do them
before -so am not sure I would get them right.
>
>Nevertheless, you can add a WHERE NOT EXISTS:
>
> SELECT c.lastname, c.email, p.polno, p.polexpdate
> FROM customer c
> JOIN (SELECT polid, polexpdate = MAX(polexpdate)
> FROM policies
> WHERE polno like '1234%'
> GROUP BY polid) AS p1 ON p1.polid = c.polid
> JOIN policies p ON p.polid = p1.polid
> AND p.polexpdate = p1.polexpdate
> WHERE p.polexpdate BETWEEN '20060301' AND '20060331'
> AND c.email IS NOT NULL
> AND NOT EXISTS (SELECT *
> FROM policies p2
> WHERE p.polid = p2.polid
> AND (p.polno LIKE '123%CANC' OR
> p.polno LIKE '123%NOTRENEWD))

Thanks Erland. That is what I suspected would be required but I did
not know how to accomplish it. I'm learning!!!!
The p1 and p2 are temporary tables, right?

*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***

[Back to original message]