|
|
Posted by clare at snyder.on.ca on 03/20/06 03:22
On Sun, 19 Mar 2006 23:33:01 +0100, Hugo Kornelis
<hugo@perFact.REMOVETHIS.info.INVALID> wrote:
>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
This APPEARS to be working - I will have to "prove" the results at
the office tomorrow, as it still shows some outstanding policies from
Feb. Not sure it should.
>
>The one below might also work - it might be faster, though I personally
>find it less intuitive to understand.
It gives me an empty result for Feb, and gives me a list for April -
which is what I would expect, so this one may just be it!! Will keep
you updated.
Thanks Hugo.
Erlund's last attempt gives me the same result as Hugo's first
example.
Thanks for the help, Erlund.
Between you two fine fellows I THINK I've learned a lot.
>
>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]
|