Posted by clare at snyder.on.ca on 03/18/06 23:20

On Sat, 18 Mar 2006 20:33:17 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

> (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.
I am assuming the POLID is the common key. I was told it is. It does
not appear to ever get changed.It seems to work just fine as I had it
written with the exception I cannot get the LATEST date info.

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

[Back to original message]