|
|
Posted by figital on 03/17/06 22:49
There is no such thing as SQL Server 2003....do you mean 2000 or 2005?
You have a lot of questions here, I'll take a stab at a couple of them
and then we'll go from there.
Structure and data would have helped me give you a MUCH better answer.
I'm going to use a simpler model.
CREATE TABLE Policy (
PolID int,
PolNo nvarchar(30),
PolExpDate datetime,
TranType nvarchar(30)
)
CREATE TABLE Customer (
Lastname nvarchar(30),
Email nvarchar(30),
PolID int
)
INSERT INTO Customer VALUES ('Abby', 'a@a.com', 1)
INSERT INTO Customer VALUES ('Brad', 'b@b.com.com', 2)
INSERT INTO Customer VALUES ('Chris', NULL, 3)
INSERT INTO Policy VALUES (1, '1234', '20030101', 'nbs')
INSERT INTO Policy VALUES (1, '1234D', '20040101', 'rwl')
INSERT INTO Policy VALUES (1, '1234S', '20050101', 'rwl')
INSERT INTO Policy VALUES (1, '1234L', '20060101', 'rwl')
INSERT INTO Policy VALUES (2, '1234', '20030101', 'nbs')
INSERT INTO Policy VALUES (2, '1234D', '20040101', 'rwl')
INSERT INTO Policy VALUES (2, '1234S', '20050101', 'rwl')
INSERT INTO Policy VALUES (2, '1234L', '20060101', 'rwl')
INSERT INTO Policy VALUES (3, '1234', '20030101', 'nbs')
INSERT INTO Policy VALUES (3, '1234D', '20040101', 'rwl')
INSERT INTO Policy VALUES (3, '1234S', '20050101', 'rwl')
INSERT INTO Policy VALUES (3, '1234L', '20060101', 'rwl')
SELECT * FROM Policy P
JOIN Customer C ON C.PolID = P.PolID
WHERE P.PolExpDate =
(SELECT TOP 1 P2.PolExpDate
FROM Policy P2
WHERE P2.PolID = C.PolID
ORDER BY P2.PolExpDate DESC
)
AND NOT C.Email IS NULL
AND (P.TranType IN ('nbs', 'rwl'))
Note that you probably should have a TransTypes table so that you
aren't storing strings ('rwl', 'nbs') in your policy table over and
over.
You also have no need for unions here.
If you want specific types, you can use ORs or the like.
I'm not sure what you want with the dates in the where clause. Do you
want only records that have their most recent event between two dates?
Also note, there are probably a hundred ways to do this...this is
unlikely to be the fastest. Without really knowing what you want
though...
[Back to original message]
|