Posted by Erland Sommarskog on 03/18/06 22:33

(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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]