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

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


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

You have it right
>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?

Yes, but that is totally beyond my control. This program was written
ages ago by who-knows-who, and became a commercial product that I and
many others now have to live with.
There is a new version out now that we will be updating to this
summer, but it still uses the old data structure, to the best of my
knowledge. It's another one of these "unplanned" products that just
grew like flopsy. It took me over 2 months just to figure out what the
darn thing was doing, before I could even start figuring out what to
ask for, much less how to ask for it. I hate this part of the job, but
somebody's got to do it!

Thanks for all the help!!!!!!!!

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