Selecting the current active policy

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

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting the current active policy

Author

Topic

toddhd
Yak Posting Veteran

55 Posts

Posted - 2006-02-26 : 17:03:49

I think I'm just having a very bad logic day, but I really need some help. Let's say I have table that contains three columns - PolicyNumber, BenefitAmount, and EffectiveDate. So it might look like this:

PolicyNumber BenefitAmount EffectiveDate
123 $20 1/1/2001
123 $30 6/1/2001
123 $40 1/1/2003
321 $20 1/1/2007

Note that one policy will have different benefit amounts that take place on different dates. And some policies may not yet be effective.

Ok, so this is theorectically simple. All I want to do is list the effective benefit amounts (per policy) for a given date. So if the date I supply is 6/1/2001, then I want to return the Policy# 123 and a Benefit Amount of $30.

What is the SQL query I need to accomplish this?????

I keep trying to do something like using MAX() and a subquery, but for the life of me I can't just figure out how to pull a given record that is equal or less to today's date, but not return expired dates.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-26 : 18:21:44

If I understand correctly, this should do it:


set nocount on
declare @date datetime
set @date = '6/1/2001'

declare @tb table (PolicyNumber int, BenefitAmount money, EffectiveDate datetime)
insert @tb
select 123, 20, '1/1/2001' union all
select 123, 30, '6/1/2001' union all
select 123, 40, '1/1/2003' union all
select 321, 20, '1/1/2007'

select	b.policyNumber
	,b.BenefitAmount
	,b.EffectiveDate
from	(
	select	policyNumber
		,max(EffectiveDate) EffectiveDate
	from	@tb
	where	EffectiveDate <= @date
	group by policyNumber
	) a
join	@tb b
		on b.policyNumber = a.PolicyNumber
		and b.EffectiveDate = a.Effectivedate

output:

policyNumber BenefitAmount         EffectiveDate                                          
------------ --------------------- -------------------------
123          30.0000               2001-06-01 00:00:00.000

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources