Join on Closest Date - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Join on Closest Date

Author

Topic

Brittney10
Posting Yak Master

154 Posts

Posted - 2012-06-22 : 09:04:43

I have two tables: Invoice and Rates. I need to be able to join Invoice and Rates based on the Product and the Date. The only issue is that the Rates table does not contained every day (only a few days from any given month). so i can't do simple join based on the date (i.e. InvoiceDate = Date) What i need to do is join based on the closest date without going over.

For example:

Invoice:

Invoice....InvoiceDate.....Product
12345......01/10/2012......ASEAMN
67890......01/6/2012......JADMAE
09873......02/17/2012......ASEAMN

Rates:

Product.....Date..........Price
ASEAMN......01/01/2012.....10000
JADMAE......01/02/2012.....10231
ASEAMN......01/02/2012.....10050
JADMAE......02/01/2012....19999

How would i perform this join???

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-22 : 14:30:36

[code];WITH cte AS
(
SELECT i.*,rc.[Date] AS JoinDate
FROM Invoice i
CROSS APPLY
(
SELECT TOP (1) *
FROM Rates r
ORDER BY ABS(DATEDIFF(dd,i.[Date],r.[Date]))
) rc
)
-- Your query here. Join cte to Rates table
-- on JoinDate column of cte and Dates column of Rates table[/code]If you are simply trying to join the two tables, you can use the query inside the cte instead of making it a cte and joining again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources