|
|
Posted by clare at snyder.on.ca on 03/19/06 08:27
On Sat, 18 Mar 2006 20:33:17 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:
>
>Oh well, if you don't know what is the key in your data, no one here
>will be able to help you with part.
Eric and others,
Please bear with me.
This SQL thing is one place where I am a real, raw neophyte - but I'm
learning.
Hard to teach an old dog new tricks.
I've figured out all the syntax, and it looks like I have it working,
with one glitch that is NOBODY'S FAULT but mine, because I forgot to
mention it.
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.
I need to filter THAT out, so the cancelled policies are not notified.
What I have working now is:
SELECT distinct c.lastname, c.email, p.polno, p.polexpdate
FROM afw_customer c
JOIN (SELECT custid, polexpdate=MAX(polexpdate)
FROM afw_basicpolinfo
WHERE polno like '1932700%'
GROUP BY custid)AS p1 ON p1.custid=c.custid
JOIN afw_basicpolinfo 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 4,1
If for instance Joe Blow is due within the specified time period, but
has cancelled his policy, Joe Blow will turn up twice in the output,
once as "1932700-1234s", and once as "1932700-123sCANC"
Putting the line in after the "Where polno like" line has no effect.
This has to be simpler than the max date problem.
Thanks in advance, guys.
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.
*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***
Navigation:
[Reply to this message]
|