SQL – get the value of the related update by date

Say I have 2 tables

Orders:

> order_id, dollar_amount, purchase_date 
>
> 1         10             10-10-2020 
>
> 2         30             10-10-2020 
>
> 3         10             12-10-2020 
>
> 4         40             19-10-2020

And I have a table that contains updates of the dollar to pound values on various dates

> dollar_to_pound update_date
> 
> 1.3             10-10-2020
> 
> 1.5             11-10-2020
> 
> 0.9             15-10-2020
> 
> 1.2             20-10-2020

My goal is to query for the pound price, using the updated price for the time, so I get something like this

order_id    dollar_price pound_value calculated
> 1         10           1.3         13
>
> 2         30           1.3         39
>
> 3         10           1.5         15
>
> 4         40           0.9         36

How can I connect the 2 tables? Using equals won’t work, as

Answer

You could use this:

SELECT o.order_id, 
    o.dollar_amount,
    d.dollar_to_pound,
    o.dollar_amount * d.dollar_to_pound AS calculated
FROM Orders o
INNER JOIN dollar_to_pound_table d
ON d.update_date = 
    (
        SELECT MAX(update_date)
        FROM dollar_to_pound_table
        WHERE update_date <= o.purchase_date 
    );