Posted by clare at snyder.on.ca on 10/01/91 11:42

I have a SQL query I need to design to select name and email addresses
for policies that are due and not renewed in a given time period. The
problem is, the database keeps the information for every renewal in
the history of the policyholder.
The information is in 2 tables, policy and customer, which share the
custid data. The polno changes with every renewal Renewals in 2004
would be D, 2005 S, and 2006 L. polexpdates for a given customer could
be 2007-03-21, 2006-03-21, 2005-03-21, and 2004-09-21, with polno of
1234 (original policy), 1234D (renewal in 2004), 1234S (renewal in
2005), and 1235L (renewed in 2006).
The policy is identified in trantype as either 'rwl' for renewal, or
'nbs' for new business.

The policies would have poleffdates of 2004-03-21 (original 6 month
policy) 2004-09-21 (first 6 month renewal) , 2005-03-21 (2nd renewal,
1 year), 2006-03-21(3rd renewal, 1 yr).

I want ONLY THE LATEST information, and keep getting early
information.
My current query structure is:
select c.lastname, c.email, p.polno, p.polexpdate
from policy p, customer c
where p.polid = c.polid
and p.polexpdate between '2006-03-01 and 2006-03-31
and p.polno like '1234%s'
and p.trantype like 'rwl'
and c.email is not null

union

select c.lastname, c.email, p.polno, p.polexpdate
from policy p, customer c
where p.polid = c.polid
and p.polexpdate between '2006-03-01 and 2006-03-31
and p.polno like '1234%'
and p.trantype like 'nbs'
and c.email is not null

How do I make this query give me ONLY the polno 123%, or 123%S
information, and not give me the information on policies that ALSO
have 123%L policies, and/ or renewal dates after 2006-03-31?

Adding a 'and not polexpdate > 2006-03-31' does not work.

I am working with SQL SERVER 2003. Was using SQL Server 7, but found
it was too restrictive, and I had a valid 2003 licence, so I upgraded,
and still could not do it (after updating the syntax - things like
using single quotes instead of double, etc)

I keep getting those policies that were due in the stated range and
HAVE been renewed as well as those which have not. I need to get only
those which have NOT been renewed, and I cannot modify the database in
any way.
*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***

[Back to original message]