Closest available date query - Pl help

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 2005 Forums

Transact-SQL (2005)

Closest available date query - Pl help

Author

Topic

sqlserv001
Starting Member

2 Posts

Posted - 2010-01-26 : 16:32:51

I've data in two tables, one table has trade dates and another has fx rates with fx_date. There may not be a FX rate available for every trade date. If an exact match between trade-date and Fx_DAte doesn't exists, the FX rate should be picked up for the latest available date before trade date.

The sample table structure is like

Table1:

Trade date:
----------
01/12/2010
01/15/2010

Table2:

FX_Date FXRte
------- ------
1/11/2010 1.40
1/15/2010 1.42

Desired result set as:

Trade date FX_Date FXRte
---------- -------- ------
01/12/2010 1/11/2010 1.40
01/15/2010 01/15/2010 1.42

Thanks
K

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-01-26 : 17:02:44

Select *
from
(
Select Row_Number() over (Partition by TradeDate order by abs(datediff(day,a.[trade Date],b.fx_date)) asc) as RowID,* from
Table1 a
join
Table2 b
) aa
where aa.RowID =1

--Depending on how large of a table, you might want to consider using
--a cross apply and only factoring dates withing a certain period,
--or try to left join on a date range.
--Properly written both of those methods will probably
--have better performance as long as you properly index.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

sqlserv001
Starting Member

2 Posts

Posted - 2010-01-26 : 17:14:47

thank you. It worked like charm. I will be running against tables each with about 100,000 records. do you see any performance hit?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-01-26 : 17:18:01

see edited notes above.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 04:22:20

My (limited) tests for Row_Number() where a condition is applied to the RowID it generates (e.g. "Get the first N records for each Group") indicated that a CROSS APPLY was faster than a nested SELECT ... OVER

If I have a moment I'll run some tests on a larger data set.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources