Posted by Hugo Kornelis on 03/20/06 00:33

On Sun, 19 Mar 2006 01:27:19 -0500, clare at snyder.on.ca wrote:

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

Hi Clare,

So if I understand correctly - if there is ONE occurence of a policy
that ends in CANC or NOTRENEWED, then NO occurence of that policy should
be returned by the query?

Did someone already mention elsewhere in this thread how terribly bad
practice it is to combine policy numbers with status codes into one
single column?

Based on the query you posted, this modification will probably do what
you need:

SELECT DISTINCT c.lastname, c.email, p.polno, p.polexpdate
FROM afw_customer AS c
INNER JOIN (SELECT custid, MAX(polexpdate) AS polexpdate
FROM afw_basicpolinfo
WHERE polno LIKE '1932700%'
GROUP BY custid) AS p1
ON p1.custid = c.custid
INNER JOIN afw_basicpolinfo AS p
ON p.custid = p1.custid
AND p.polexpdate = p1.polexpdate
WHERE p.polexpdate BETWEEN '20060201' AND '20060228'
AND c.email IS NOT NULL
AND NOT EXISTS (SELECT *
FROM afw_basicpolinfo
WHERE (polno LIKE '1932700%CANC'
OR polno LIKE '1932700%NOTRENEWED')
AND custid = c.custid)
ORDER BY p.polexpdate, c.lastname

The one below might also work - it might be faster, though I personally
find it less intuitive to understand.

SELECT DISTINCT c.lastname, c.email, p.polno, p.polexpdate
FROM afw_customer AS c
INNER JOIN (SELECT custid, MAX(polexpdate) AS polexpdate
FROM afw_basicpolinfo
WHERE polno LIKE '1932700%'
GROUP BY custid
HAVING MAX(CASE WHEN polno LIKE '%CANC'
OR polno LIKE '%NOTRENEWED'
THEN 1
ELSE 0
END) = 0) AS p1
ON p1.custid = c.custid
INNER JOIN afw_basicpolinfo AS p
ON p.custid = p1.custid
AND p.polexpdate = p1.polexpdate
WHERE p.polexpdate BETWEEN '20060201' AND '20060228'
AND c.email IS NOT NULL
ORDER BY p.polexpdate, c.lastname

(Both queries are untested - see www.aspfaq.com/5006 if you prefer a
tested reply)

--
Hugo Kornelis, SQL Server MVP

[Back to original message]