;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
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.
Join on Closest Date
Posted - 06/22/2012 : 09:04:43
