COMPLICATED SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

COMPLICATED SELECT

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-09 : 08:04:06

I have a table that has policy information.. the policy is one year policy (example 1/1/12 thru 1/1/13). the fields that I am using is policy_number policy_eff_date, policy_exp date..

I have been asked to produce a report that will list all the policies that have not been renewed. SO the logic is

1) Find all the policies that have policy_exp_date is between 1/1/12 and 6/30/12
2) Of all the policies found list the policies that was not renewed for next year.

example:

Policy 1234 expired in 5/1/12 so when it is renewed it will have a policy_eff_date 5/1/12 and policy_exp_date of 5/1/13 hence this policy will not be listed.

policy 5678 expired in 3/1/12 but was not renewed so it will not have any record with policy_eff_date 3/1/12 and policy_exp_date 3/1/13. so this policy will be listed...

Hope this makes sense

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-09 : 08:39:07

How is your data stored? Will there be a new row each time when the policy is renewed? Or is the expiration date updated on the same row when the policy is renewed?

If it is the same row, then you can use this:

SELECT
	*
FROM
	Table1 a
WHERE
	policy_exp_date >= '20120101'
	AND policy_exp_date < '20120701'

If a new row is inserted when the policy is renewed then:

SELECT
	*
FROM
	Table1 a
WHERE
	policy_exp_date >= '20120101'
	AND policy_exp_date < '20120701'
	AND NOT EXISTS
	(
		SELECT * FROM Table1 b
		WHERE a.policy_number  =  b.policy_number 
		AND b.policy_exp_date >= '20120701'
	);

divan
Posting Yak Master

153 Posts

Posted - 2012-08-09 : 09:04:34

SUNITABECK your are a life saver.Your script worked perfect.. I apologize there is another thing we have to consider you see in the example below the third field tells me that the policy was terminated before the policy_exp_date so we would not want to include this policy..

2011-04-04 2012-04-04 NULL
2011-04-04 2012-04-04 2011-07-01 00:00:00.000

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-09 : 09:16:24

Glad to help :)

You can add another condition to your where clause:

WHERE
	policy_exp_date >= '20120101'
	AND policy_exp_date < '20120701'
	AND NOT EXISTS
	(
		SELECT * FROM Table1 b
		WHERE a.policy_number  =  b.policy_number 
		AND b.policy_exp_date >= '20120701'
		AND b.policy_cxl_date IS NULL
	)

If you want to include policies with effective cancel date later than today, you may want to modify that to:

WHERE
	policy_exp_date >= '20120101'
	AND policy_exp_date < '20120701'
	AND NOT EXISTS
	(
		SELECT * FROM Table1 b
		WHERE a.policy_number  =  b.policy_number 
		AND b.policy_exp_date >= '20120701'
		AND ( b.policy_cxl_date IS NULL OR b.policy_cxl_date > GETDATE())
	);

divan
Posting Yak Master

153 Posts

Posted - 2012-08-09 : 11:48:39

WE ARE STILL PICKING UP POLICY A

POLICY_NUMBER POL_EFF_DATE POL_EXP_DATE POL_TERM_DATE
A 2011-04-04 2012-04-04 NULL
A 2011-04-04 2012-04-04 2011-07-01

THESE TWO RECORDS ARE FOR THE SAME POLICY SO THIS POLICY SHOULD COMPLETELY BE IGNORED. SINCE THE POLICY WAS TERMINATED (07-01-2011) BEFORE THE THE POLICY EXPIRATION (04-04-12)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-09 : 13:29:00

I don't completely understand the logic of when rows get inserted - that could be why. In any case, based on my understanding, may be this is what you need.

SELECT
	*
FROM
	Table1 a
WHERE
	policy_exp_date >= '20120101'
	AND policy_exp_date < '20120701'
	AND NOT EXISTS
	(
		SELECT * FROM Table1 b
		WHERE a.policy_number  =  b.policy_number 
		AND b.policy_exp_date >= '20120701'
	)
	AND NOT EXISTS
	(
		SELECT * FROM Table1 b
		WHERE a.policy_number  =  b.policy_number 
		AND b.policy_exp_date = a.policy_exp_date
		AND b.policy_eff_date = a.policy_eff_date
	)

If that does not do it for you, examine the logic in the second NOT EXISTS clause to see if those are the actual requirements.

divan
Posting Yak Master

153 Posts

Posted - 2012-08-10 : 09:21:03

Well as usual, the user has thrown another wrench in the mix..hrrrrrrrrr!!!! The following script works fine and it gives me the result that they expected before but now they are asking that we do not list the clients that have transfered to another policy.. This is where the mpl_detail comes to play. Each policy record has a mpl_detail record with the same policy_date time and within the mpl_detail record there is a client number.. SO what I am thinking is that we should also check to make sure that the client number is not appearing in any of the policies in the NOT EXISTS clause..

SELECT M.CLIENT P.POLICY_NUMBER
FROM
POLICY P
INNER JOIN MPL_DETAIL M ON P.POLICY_NUMBER = M.POLICY_NUMBER AND P.POLICY_DATE_TIME = M.POLICY_DATE_TIME
WHERE
P.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM POLICY P2 WHERE P.POLICY_NUMBER = P2.POLICY_NUMBER) AND
P.POL_EXP_DATE >= '20120101'
AND P.POL_EXP_DATE < '20120701' AND P.POLICY_NUMBER NOT LIKE 'Q%'
AND NOT EXISTS
(
SELECT * FROM POLICY P1
WHERE P.POLICY_NUMBER = P1.POLICY_NUMBER
AND P1.POL_EXP_DATE >= '20120701'
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources