Mapping multiple dates to one date

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

Mapping multiple dates to one date

Author

Topic

JustinM
Starting Member

17 Posts

Posted - 2010-03-30 : 13:13:08

Searched online for about an hour and decided to just post...

I have a table with policy effective dates and another table with dates of price changes and the prices. I need to map the policy effective date to the date of the price change to determine the price paid by the policyholder. So basically I need to map the effective date to the latest date of price change that is not after the effective date.

The only idea I found online was some between functions. However, I would need to repeat these between functions about 500 times because of different effective dates and prices in different states. I was hoping to do something with a floor but didn't see anything that looked like it would work.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 14:20:30

can you post some sample data ? also are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JustinM
Starting Member

17 Posts

Posted - 2010-03-30 : 14:50:19

Yes, using sql 2005

Here is an example of what I need done

Table 1
State Price_Change_Date Price
NC 3/1/2009 13.52
NC 1/15/2009 11.47
NC 6/1/2008 12.52
NC 12/15/2007 12.17
CO 7/1/2009 21.17
CO 2/20/2009 22.45
CO 6/1/2008 19.55
CO 11/25/2007 19.1

Table 2
State Policy_Number Effective_Date
NC 1 1/2/2008
CO 2 1/5/2008
CO 3 2/3/2008
NC 4 3/15/2008
CO 5 3/22/2008
NC 6 4/14/2008
NC 7 5/13/2008
NC 8 7/17/2008
CO 9 8/22/2008
NC 10 9/1/2008
NC 11 11/1/2008
NC 12 1/17/2009
CO 13 1/17/2009
NC 14 2/23/2009
CO 15 3/3/2009
NC 16 3/17/2009
CO 17 4/22/2009
CO 18 6/13/2009
CO 19 7/2/2009
CO 20 7/12/2009

Output should look like this
State Policy_Number Effective_Date Price
NC 1 1/2/2008 12.17
CO 2 1/5/2008 19.1
CO 3 2/3/2008 19.1
NC 4 3/15/2008 12.17
CO 5 3/22/2008 19.1
NC 6 4/14/2008 12.17
NC 7 5/13/2008 12.17
NC 8 7/17/2008 12.52
CO 9 8/22/2008 19.55
NC 10 9/1/2008 12.52
NC 11 11/1/2008 12.52
NC 12 1/17/2009 11.47
CO 13 1/17/2009 19.55
NC 14 2/23/2009 11.47
CO 15 3/3/2009 22.45
NC 16 3/17/2009 13.52
CO 17 4/22/2009 22.45
CO 18 6/13/2009 22.45
CO 19 7/2/2009 21.17
CO 20 7/12/2009 21.17

byrdzeye
Starting Member

14 Posts

Posted - 2010-03-31 : 11:50:02

You don't need to be concerned with date ranges, only start date.

select state, policy, ...
(select top 1 price from table1 t1
where t1.state = t2.state
and t1.ratedate < t2.ratedate order by t1.ratedate desc) as price
from table2 t2

might need to change < to <= it depends...

JustinM
Starting Member

17 Posts

Posted - 2010-03-31 : 12:40:15

Perfect! I figured it would be something relatively straightforward. My actual data is much more complicated than the example I gave, I'll put it together in the next week or so and let you know the result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:17:17

[code]select t2.state, t2.policy, ...
t.price
from table2 t2
cross apply (select top 1 price from table1 t1
where t1.state = t2.state
and t1.ratedate < t2.ratedate order by t1.ratedate desc) t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JustinM
Starting Member

17 Posts

Posted - 2010-04-22 : 10:15:08

Data was a bit more annoying than the example I gave, but after playing around with your suggestions I got it to work. Thank you byrdzeye and visakh16 for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:16:12

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources