Search code examples
sqljoincoalesce

SQL - Coalesce until next available date

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é

Solution

  • 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)