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

On Sat, 18 Mar 2006 16:05:54 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.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_basicpolinfo p WHERE p.polno like '1932700%'
GROUP BY polid AS p1 ON p1.polid-p1 p1 polid
Join afw_basicpolinfo 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 ***

[Back to original message]