SQL - get the value of the related update by date-CodePudding

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

CodePudding user response：

Use a join:

SELECT
    o.order_id,
    o.dollar_amount,
    r.dollar_to_pound AS pound_value
    o.dollar_amount * r.dollar_to_pound AS calculated
FROM Orders o
LEFT JOIN Rates r
    ON r.update_date = o.purchase_date
ORDER BY
    o.order_id;

The above query loosely assumes that the rates table would always be populated with all dates for which there were orders. If not, then a given order might report NULL for the pound converted value. You would need to define logic for how to handle this case.

CodePudding user response：

The point is find the latest dollar_to_pound value but its update_date is no newer than the order purchase_date

SELECT a.order_id, a.dollar_amount, b.pound_value, a.dollar_amount * b.pound_value calculated
FROM orders a 
JOIN dollar_to_pound b ON a.purchase_date >= b.update_date
WHERE NOT EXISTS (
    SELECT 1
    FROM dollar_to_pound c
    WHERE a.purchase_date >= c.update_date 
    AND c.update_date > b.update_date
)

CodePudding user response：

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

CodePudding user response：

This is script which calculate the closest rate date for order:

select t1.order_id,
t1.dollar_amount,
r.dollar_to_pound,
r.dollar_to_pound * t1.dollar_amount as 'pounds'
from (
select o.*,rates_to_date.max_date
from Orders o cross apply (select max(r.update_date) as max_date from Rates r where r.update_date <= o.purchase_date) rates_to_date) as t1 innner join Rates r on r.update_date = t1.max_date