Q&A for materials modelers and data scientists
declare @product table(id int, price_date date) insert @product values(1, '2011-01-02') declare @price table(id int, fid int, price_date date) insert @price values(1, 1, '2010-01-01') insert @price values(1, 1, '2011-01-01') insert @price values(2, 1, '2012-01-01') select product.id, price.id from @product product cross apply (select top 1 id from @price where id = product.id and price_date < product.price_date order by price_date desc ) price