|
|
Posted by clare at snyder.on.ca on 03/18/06 06:39
On 17 Mar 2006 12:49:52 -0800, "figital" <mharen@gmail.com> wrote:
>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...
OK, here is the scoop.
The program that creates and uses the database does not allow us to
select only the policies that are due and nor yet renewed in a given
time period and return the e-mail address.
We need this functionality to email policy reminders with a link to
the e-commerce page that allows them to renew online. This will save
mailing notices to those policyholders that have e-mail and deal with
the company in that manner.
There are two tables involved - and they can NOT be modified in any
way - we are stuck with them.
The customer table has all kinds of customer information fields - over
half of which are not used at all. Among them are the lastname (which
has first and last name in it), email address,policy-id, and a lot of
stuff we don't need to know.
The policy information table has the policy-id, the policy number, the
renewal date, and the effective date, as well as the transaction type.
The policy number starts with a particular numeric pattern, which
identifies the particular policies we are interested in. The rest of
the policy number identifies the actual policy, and starts out as a
numeric string, like 3215.
When it is renewed, it is saved with a suffix letter. Might be d for
2005, s for 2005, and l for 2006 (I think that is correct - but you
get the idea). Nothing is EVER deleted from this database.
SO - John Doe has policy 12345-123 issued Mar 1 2003, due Mar 1 2004.
John Doe's policy is renewed in Feb 2004, and becomes 12345-123d, due
Mar 1, 2005.
It is renewed and becomes 12345-123s, due Mar 2006
It is renewed Mar 2006 and becomes 12345-123L, due Mar 1 2007.
Jane Smith has a policy #12345-923 issued Mar 9, 2005, due Mar 9,
2006.
IT is renewed and becomes 12345-923s, due Mar 9, 2006
I need to do a search by date and policy number to find Jane Smith,
and others like her, while not finding John Doe, and others like him,
in Feb 2006 to remind them to renew their policies.
Don't want to bother those who have already looked after theirs. Don't
want to miss the one that's going to forget without a reminder.
I WAS using the union because I was breaking it down looking for only
renewals with a S at the end of the policy number, eliminating the
1993, 1994, and 1995 renewals - and specifying poltype "rwl" so we
were only looking at renewals.
Then I was adding the policies that were new business "nbs", and had
not yet had a renewal, so had no 'S' on the end.
That was the theory behind the construct.
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.
*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***
Navigation:
[Reply to this message]
|