|
|
Posted by Erland Sommarskog on 03/18/06 18:05
(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, 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
Navigation:
[Reply to this message]
|