SQL Previous currency rate

1
votes

I would like to ask you for help with getting previous currency rate. Eg. I have two tables (Orders and CurrencyRates) which I want to join. First table encloses orders in EUR, and second encloses currency rate from EUR to USD.

enter image description here

I would like to join it by Order Date. The problem is with order date "10.11.2018" which has null rate from CurrencyRates table. I would like to have here value from previous day rate (in this case "09.11.2018). And for the future date ("18.11.2018") I would like to have value having max(Date) from CurrencyRates table. Is there any way to join it to have expected values?

Thank you for your help!

1
sqlsql-servertsql

1 Answers

0
votes

You can use cross apply:

select o.*, cr.date, cr.rate, cr.currency
from orders o cross apply
     (select top (1) cr.*
      from currencyrates cr
      where cr.date <= o.orderdate
      order by cr.date desc
     ) cr;