SQL query - duplicate records - different dates - how to get only latest information?

[clare at snyder.on.ca]

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

[figital]

There is no such thing as SQL Server 2003....do you mean 2000 or 2005?

You have a lot of questions here, I'll take a stab at a couple of them
and then we'll go from there.

Structure and data would have helped me give you a MUCH better answer.
I'm going to use a simpler model.

CREATE TABLE Policy (
PolID int,
PolNo nvarchar(30),
PolExpDate datetime,
TranType nvarchar(30)
)

CREATE TABLE Customer (
Lastname nvarchar(30),
Email nvarchar(30),
PolID int
)

INSERT INTO Customer VALUES ('Abby', '[e-mail]', 1)
INSERT INTO Customer VALUES ('Brad', '[e-mail]', 2)
INSERT INTO Customer VALUES ('Chris', NULL, 3)

INSERT INTO Policy VALUES (1, '1234', '20030101', 'nbs')
INSERT INTO Policy VALUES (1, '1234D', '20040101', 'rwl')
INSERT INTO Policy VALUES (1, '1234S', '20050101', 'rwl')
INSERT INTO Policy VALUES (1, '1234L', '20060101', 'rwl')

INSERT INTO Policy VALUES (2, '1234', '20030101', 'nbs')
INSERT INTO Policy VALUES (2, '1234D', '20040101', 'rwl')
INSERT INTO Policy VALUES (2, '1234S', '20050101', 'rwl')
INSERT INTO Policy VALUES (2, '1234L', '20060101', 'rwl')

INSERT INTO Policy VALUES (3, '1234', '20030101', 'nbs')
INSERT INTO Policy VALUES (3, '1234D', '20040101', 'rwl')
INSERT INTO Policy VALUES (3, '1234S', '20050101', 'rwl')
INSERT INTO Policy VALUES (3, '1234L', '20060101', 'rwl')

SELECT * FROM Policy P
JOIN Customer C ON C.PolID = P.PolID
WHERE P.PolExpDate =
(SELECT TOP 1 P2.PolExpDate
FROM Policy P2
WHERE P2.PolID = C.PolID
ORDER BY P2.PolExpDate DESC
)
AND NOT C.Email IS NULL
AND (P.TranType IN ('nbs', 'rwl'))

Note that you probably should have a TransTypes table so that you
aren't storing strings ('rwl', 'nbs') in your policy table over and
over.

You also have no need for unions here.

If you want specific types, you can use ORs or the like.

I'm not sure what you want with the dates in the where clause. Do you
want only records that have their most recent event between two dates?

Also note, there are probably a hundred ways to do this...this is
unlikely to be the fastest. Without really knowing what you want
though...

[clare at snyder.on.ca]

On 17 Mar 2006 12:49:52 -0800, "figital" <mh****@gmail.c om> wrote:

There is no such thing as SQL Server 2003....do you mean 2000 or 2005?

You have a lot of questions here, I'll take a stab at a couple of them
and then we'll go from there.

Structure and data would have helped me give you a MUCH better answer.
I'm going to use a simpler model.

CREATE TABLE Policy (
PolID int,
PolNo nvarchar(30),
PolExpDate datetime,
TranType nvarchar(30)
)

CREATE TABLE Customer (
Lastname nvarchar(30),
Email nvarchar(30),
PolID int
)

INSERT INTO Customer VALUES ('Abby', '[e-mail]', 1)
INSERT INTO Customer VALUES ('Brad', '[e-mail]', 2)
INSERT INTO Customer VALUES ('Chris', NULL, 3)

INSERT INTO Policy VALUES (1, '1234', '20030101', 'nbs')
INSERT INTO Policy VALUES (1, '1234D', '20040101', 'rwl')
INSERT INTO Policy VALUES (1, '1234S', '20050101', 'rwl')
INSERT INTO Policy VALUES (1, '1234L', '20060101', 'rwl')

INSERT INTO Policy VALUES (2, '1234', '20030101', 'nbs')
INSERT INTO Policy VALUES (2, '1234D', '20040101', 'rwl')
INSERT INTO Policy VALUES (2, '1234S', '20050101', 'rwl')
INSERT INTO Policy VALUES (2, '1234L', '20060101', 'rwl')

INSERT INTO Policy VALUES (3, '1234', '20030101', 'nbs')
INSERT INTO Policy VALUES (3, '1234D', '20040101', 'rwl')
INSERT INTO Policy VALUES (3, '1234S', '20050101', 'rwl')
INSERT INTO Policy VALUES (3, '1234L', '20060101', 'rwl')

SELECT * FROM Policy P
JOIN Customer C ON C.PolID = P.PolID
WHERE P.PolExpDate =
(SELECT TOP 1 P2.PolExpDate
FROM Policy P2
WHERE P2.PolID = C.PolID
ORDER BY P2.PolExpDate DESC
)
AND NOT C.Email IS NULL
AND (P.TranType IN ('nbs', 'rwl'))

Note that you probably should have a TransTypes table so that you
aren't storing strings ('rwl', 'nbs') in your policy table over and
over.

You also have no need for unions here.

If you want specific types, you can use ORs or the like.

I'm not sure what you want with the dates in the where clause. Do you
want only records that have their most recent event between two dates?

Also note, there are probably a hundred ways to do this...this is
unlikely to be the fastest. Without really knowing what you want
though...

OK, here is the scoop.
The program that creates and uses the database does not allow us to
select only the policies that are due and nor yet renewed in a given
time period and return the e-mail address.

We need this functionality to email policy reminders with a link to
the e-commerce page that allows them to renew online. This will save
mailing notices to those policyholders that have e-mail and deal with
the company in that manner.

There are two tables involved - and they can NOT be modified in any
way - we are stuck with them.

The customer table has all kinds of customer information fields - over
half of which are not used at all. Among them are the lastname (which
has first and last name in it), email address,policy-id, and a lot of
stuff we don't need to know.

The policy information table has the policy-id, the policy number, the
renewal date, and the effective date, as well as the transaction type.
The policy number starts with a particular numeric pattern, which
identifies the particular policies we are interested in. The rest of
the policy number identifies the actual policy, and starts out as a
numeric string, like 3215.
When it is renewed, it is saved with a suffix letter. Might be d for
2005, s for 2005, and l for 2006 (I think that is correct - but you
get the idea). Nothing is EVER deleted from this database.

SO - John Doe has policy 12345-123 issued Mar 1 2003, due Mar 1 2004.
John Doe's policy is renewed in Feb 2004, and becomes 12345-123d, due
Mar 1, 2005.
It is renewed and becomes 12345-123s, due Mar 2006
It is renewed Mar 2006 and becomes 12345-123L, due Mar 1 2007.

Jane Smith has a policy #12345-923 issued Mar 9, 2005, due Mar 9,
2006.
IT is renewed and becomes 12345-923s, due Mar 9, 2006

I need to do a search by date and policy number to find Jane Smith,
and others like her, while not finding John Doe, and others like him,
in Feb 2006 to remind them to renew their policies.
Don't want to bother those who have already looked after theirs. Don't
want to miss the one that's going to forget without a reminder.

I WAS using the union because I was breaking it down looking for only
renewals with a S at the end of the policy number, eliminating the
1993, 1994, and 1995 renewals - and specifying poltype "rwl" so we
were only looking at renewals.
Then I was adding the policies that were new business "nbs", and had
not yet had a renewal, so had no 'S' on the end.

That was the theory behind the construct.

I know we can likely get rid of that complication IF we can figure out
how to get only the LATEST renewal date - using the capabilities of
SQL Server 2003. Have not seen any documentation on "top", so am
unaware of if and how it works.
*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***

[Erland Sommarskog]

(clare at snyder.on.ca) writes:

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.
It appears that the data you have is very messy, with various pieces
of information built into the policy number. Since I don't know the
tables in detail, and only have your narrative to work from, this
is a bit of guesswork:

SELECT c.lastname, c.email, p.polno, p.polexpdate
FROM customer c
JOIN (SELECT polid, polexpdate = MAX(polexpdate)
FROM policies
WHERE polno like '1234%'
GROUP BY polid) AS p1 ON p1.polid = c.polid
JOIN policies p ON p.polid = p1.polid
AND p.polexpdate = p1.polexpdate
WHERE p.polexpdate BETWEEN '20060301' AND '20060331'
AND c.email IS NOT NULL

First, I have assumed that the polid does not change over renewals, but
that is plain guess on my part.

The inner SELECT gives the most recent expiration date for each polid,
and then I filter to see only those with expiration in March 2006.

If this does not address your problem, I would suggest that you post:

o CREATE TABLE statements for your tables. Don't forget to include
defintion of primary keys and foreign keys.
o INSERT statemetns with sample data.
o The desired result given the sample.
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)
Please clarify if you are working with SQL 2000 or SQL 2005. There is
no such thing as SQL 2003.
I know we can likely get rid of that complication IF we can figure out
how to get only the LATEST renewal date - using the capabilities of
SQL Server 2003. Have not seen any documentation on "top", so am
unaware of if and how it works.

You can read about TOP in Books Online. By the way, it was available in
SQL 7 as well.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[clare at snyder.on.ca]

On Sat, 18 Mar 2006 16:05:54 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.se> wrote:

(clare at snyder.on.ca) writes:

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.

It appears that the data you have is very messy, with various pieces
of information built into the policy number. Since I don't know the
tables in detail, and only have your narrative to work from, this
is a bit of guesswork:

SELECT c.lastname, c.email, p.polno, p.polexpdate
FROM customer c
JOIN (SELECT polid, polexpdate = MAX(polexpdate)
FROM policies
WHERE polno like '1234%'
GROUP BY polid) AS p1 ON p1.polid = c.polid
JOIN policies p ON p.polid = p1.polid
AND p.polexpdate = p1.polexpdate
WHERE p.polexpdate BETWEEN '20060301' AND '20060331'
AND c.email IS NOT NULL

First, I have assumed that the polid does not change over renewals, but
that is plain guess on my part.

The inner SELECT gives the most recent expiration date for each polid,
and then I filter to see only those with expiration in March 2006.

If this does not address your problem, I would suggest that you post:

o CREATE TABLE statements for your tables. Don't forget to include
defintion of primary keys and foreign keys.
o INSERT statemetns with sample data.
o The desired result given the sample.

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)

Please clarify if you are working with SQL 2000 or SQL 2005. There is
no such thing as SQL 2003.

I know we can likely get rid of that complication IF we can figure out
how to get only the LATEST renewal date - using the capabilities of
SQL Server 2003. Have not seen any documentation on "top", so am
unaware of if and how it works.

You can read about TOP in Books Online. By the way, it was available in
SQL 7 as well.

It is SQL 2000 - just checked.

I will try your code and see what happens.
Thanks.
*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***

[clare at snyder.on.ca]

On Sat, 18 Mar 2006 16:05:54 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.se> wrote:
I have a few syntax questions. I will insert them inline.

It appears that the data you have is very messy, with various pieces
of information built into the policy number. Since I don't know the
tables in detail, and only have your narrative to work from, this
is a bit of guesswork:

SELECT c.lastname, c.email, p.polno, p.polexpdate
FROM customer c
Don't I need 'policies p' in here too? JOIN (SELECT polid, polexpdate = MAX(polexpdate)
FROM policies
WHERE polno like '1234%'
and p.polno insted of polno? GROUP BY polid) AS p1 ON p1.polid = c.polid
And what is the 'BY polid)' and the 'AS p1 ON p1.polid' JOIN policies p ON p.polid = p1.polid
AND p.polexpdate = p1.polexpdate
WHERE p.polexpdate BETWEEN '20060301' AND '20060331'
Actual date info is " between '2006-03-01 00:00:00.000 and 2006-03-31
00:00:00.000' " AND c.email IS NOT NULL

First, I have assumed that the polid does not change over renewals, but
that is plain guess on my part.
I think your guess is correct, from what I've seen.
The inner SELECT gives the most recent expiration date for each polid,
and then I filter to see only those with expiration in March 2006.

If this does not address your problem, I would suggest that you post:
I think your methodology may work, if we get the syntax straightened
out.
o CREATE TABLE statements for your tables. Don't forget to include
defintion of primary keys and foreign keys.
o INSERT statemetns with sample data.
o The desired result given the sample.

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)

Please clarify if you are working with SQL 2000 or SQL 2005. There is
no such thing as SQL 2003.

I know we can likely get rid of that complication IF we can figure out
how to get only the LATEST renewal date - using the capabilities of
SQL Server 2003. Have not seen any documentation on "top", so am
unaware of if and how it works.

You can read about TOP in Books Online. By the way, it was available in
SQL 7 as well.

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

[clare at snyder.on.ca]

On Sat, 18 Mar 2006 16:05:54 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.se> wrote:

(clare at snyder.on.ca) writes:

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.

It appears that the data you have is very messy, with various pieces
of information built into the policy number. Since I don't know the
tables in detail, and only have your narrative to work from, this
is a bit of guesswork:

SELECT c.lastname, c.email, p.polno, p.polexpdate
FROM customer c
JOIN (SELECT polid, polexpdate = MAX(polexpdate)
FROM policies
WHERE polno like '1234%'
GROUP BY polid) AS p1 ON p1.polid = c.polid
JOIN policies p ON p.polid = p1.polid
AND p.polexpdate = p1.polexpdate
WHERE p.polexpdate BETWEEN '20060301' AND '20060331'
AND c.email IS NOT NULL

First, I have assumed that the polid does not change over renewals, but
that is plain guess on my part.

The inner SELECT gives the most recent expiration date for each polid,
and then I filter to see only those with expiration in March 2006.

If this does not address your problem, I would suggest that you post:

o CREATE TABLE statements for your tables. Don't forget to include
defintion of primary keys and foreign keys.
o INSERT statemetns with sample data.
o The desired result given the sample.

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)

Please clarify if you are working with SQL 2000 or SQL 2005. There is
no such thing as SQL 2003.

I know we can likely get rid of that complication IF we can figure out
how to get only the LATEST renewal date - using the capabilities of
SQL Server 2003. Have not seen any documentation on "top", so am
unaware of if and how it works.

You can read about TOP in Books Online. By the way, it was available in
SQL 7 as well.

Using just the inner SELECT, written as :
SELECT polid. polexpdate =MAX (polexpdate)
FROM afw_basicpolinf o p WHERE p.polno like '1932700%'
GROUP BY polid AS p1 ON p1.polid-p1 p1 polid
Join afw_basicpolinf o p on p.polid = p1.polid
AND p.polexpdate=p1 .polexpdate
WHERE p.polexpdate between '2006-04-15 00:00:00.000 and 2006-04-31
00:00:00.000'
and c.email IS NOT NULL
I get an error:

SERVER: Msg 156, Level 15, Stare 1, Line 3
Incorrect syntax near the keyword "AS".
*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***

[clare at snyder.on.ca]

On Sat, 18 Mar 2006 16:05:54 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.se> wrote:

It appears that the data you have is very messy, with various pieces
of information built into the policy number. Since I don't know the
tables in detail, and only have your narrative to work from, this
is a bit of guesswork:

SELECT c.lastname, c.email, p.polno, p.polexpdate
FROM customer c
JOIN (SELECT polid, polexpdate = MAX(polexpdate)
FROM policies
WHERE polno like '1234%'
GROUP BY polid) AS p1 ON p1.polid = c.polid
JOIN policies p ON p.polid = p1.polid
AND p.polexpdate = p1.polexpdate
WHERE p.polexpdate BETWEEN '20060301' AND '20060331'
AND c.email IS NOT NULL

First, I have assumed that the polid does not change over renewals, but
that is plain guess on my part.

The inner SELECT gives the most recent expiration date for each polid,
and then I filter to see only those with expiration in March 2006.

The "outer select" works if written:

Select c.lastname, c.email, p.polno, p.polexpdate
FROM afw_customer c, afw_basicpolinf o p

If we get the "inner select" working, the JOIN will likely work as
well.

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

[clare at snyder.on.ca]

On Sat, 18 Mar 2006 16:05:54 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.se> wrote:

(clare at snyder.on.ca) writes:

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.

It appears that the data you have is very messy, with various pieces
of information built into the policy number. Since I don't know the
tables in detail, and only have your narrative to work from, this
is a bit of guesswork:

SELECT c.lastname, c.email, p.polno, p.polexpdate
FROM customer c
JOIN (SELECT polid, polexpdate = MAX(polexpdate)
FROM policies
WHERE polno like '1234%'
GROUP BY polid) AS p1 ON p1.polid = c.polid
JOIN policies p ON p.polid = p1.polid
AND p.polexpdate = p1.polexpdate
WHERE p.polexpdate BETWEEN '20060301' AND '20060331'
AND c.email IS NOT NULL

First, I have assumed that the polid does not change over renewals, but
that is plain guess on my part.

The inner SELECT gives the most recent expiration date for each polid,
and then I filter to see only those with expiration in March 2006.

If this does not address your problem, I would suggest that you post:

o CREATE TABLE statements for your tables. Don't forget to include
defintion of primary keys and foreign keys.
o INSERT statemetns with sample data.
o The desired result given the sample.

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)

Please clarify if you are working with SQL 2000 or SQL 2005. There is
no such thing as SQL 2003.

I know we can likely get rid of that complication IF we can figure out
how to get only the LATEST renewal date - using the capabilities of
SQL Server 2003. Have not seen any documentation on "top", so am
unaware of if and how it works.

You can read about TOP in Books Online. By the way, it was available in
SQL 7 as well.

And polid needs to be custid. I've remoted into the server today to
get the proper field names and to attempt to run the queries.
The policies table is afw_basicpolinf o, and the customers is
afw_customer.

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

[Erland Sommarskog]

(clare at snyder.on.ca) writes:

SELECT c.lastname, c.email, p.polno, p.polexpdate
FROM customer c
Don't I need 'policies p' in here too?

It's further below. Note that I am using the newer ANSI syntax for
joins,

JOIN (SELECT polid, polexpdate = MAX(polexpdate)
FROM policies
WHERE polno like '1234%'

and p.polno insted of polno?

We're inside a derived table. A derived table is a temp table within the
query, but only conceptually. SQL Server may recast the comuptation
order, and often do. The purpose of the derived table is find the
latest expiration day for each policy holder.

GROUP BY polid) AS p1 ON p1.polid = c.polid

And what is the 'BY polid)'

The end of a GROUP BY clause.
and the 'AS p1 ON p1.polid'

AS p1 establishes an alias for the derived table. The ON clause specifies
on which columns the derived table is to be join with customers.

JOIN policies p ON p.polid = p1.polid
AND p.polexpdate = p1.polexpdate
WHERE p.polexpdate BETWEEN '20060301' AND '20060331'

Actual date info is " between '2006-03-01 00:00:00.000 and 2006-03-31
00:00:00.000' "

Always use the format YYYYMMDD for date literals, as this date format
is always interpreted the same. The format YYYY-MM-DD fails when the
current language is set to for instance German or British.

First, I have assumed that the polid does not change over renewals, but
that is plain guess on my part.

I think your guess is correct, from what I've seen.

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.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx