SQL - Coalesce until next available date

Question

Hopefully simple enough but cannot find the answer. Trying to run:

SQL Server

left outer join exchanges fx (nolock) on 
v1.ccy = fx.ccy and v1.date = fx.date

The problem is that fx.ccy may not exist on fx.date. I would therefore like to join on the next available date.

Many thanks,

José

Answer 1

Possible solution:

....
left outer join exchanges fx (nolock) on 
v1.ccy = fx.ccy and fx.date = (
    select min(date) from exchanges where date >= v1.date and ccy = v1.ccy)

Answer 2

left outer join exchanges fx (nolock) on 
v1.ccy = fx.ccy and fx.date = (select min(date) 
                                 from exchanges x 
                                where v1.ccy = x.ccy 
                                  and x.date >= v1.date)

Answer 3

Found a solution.

left outer join exchanges fx (nolock) on
v1.ccy = fx.ccy and 
(select min(date) from exchanges (nolock) where date >= v1.date and ccy = v1.ccy) = fx.date

Thank you for your help.

José