SQL Server: For each record in table 1, find the closest match in table 2

Question

I have 2 tables.

table1

date1, closingprice

table2

 date2, strikeprice, ask

For each record in table 1, I want to match on table1.date1 = table2.date2 - 4 to find table2.strikeprice that is the closest to table1.closingprice but where table1.closingprice > table2.strikeprice

For each record in table1, where the date matches on the date in table2, there are multiple records in table2. But I am interested in only 1 record in table2 based on the constraint above.

I want table2.date2, strikeprice, and ask from table2 in the output results.

I match on the dates using table1.date1 = DATEADD(DD, -4, table2.date2) and that works fine...

Any ideas on how to do this as simple as possible? Explanations on what is happening would be appreciated. I have seen an example with CROSS APPLY, but open to any example that is easily understood and why it works....

Thanks,

Dan

Answer 1

You can join and use a correlated subquery for filtering:

select t1.*, t2.strikeprice
from table1 t1
inner join table2 t2 
    on t1.date1 = dateadd(DD, -4, t2.date2)
    and t2.strikeprice = (
        select max(strikeprice)
        from table2 t22
        where t22.date2 = t2.date2
        and t22.strikeprice < t1.closingprice
    )

This assumes that there are no duplicates on table2(date2, strikeprice); otherwise, you might get duplicates in the resultset. This assumption seems consistant with your question though.