Posted by Erland Sommarskog on 03/20/06 00:26

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

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))


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]