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

On Sat, 18 Mar 2006 16:05:54 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.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 ***

[Back to original message]