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

On Sun, 19 Mar 2006 23:33:01 +0100, Hugo Kornelis
<hugo@perFact.REMOVETHIS.info.INVALID> wrote:

Thanks Hugo, I'll try these as well!!!

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

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

[Back to original message]