intl_orders (order_id, order_date, currency, amount) holds orders in several currencies. fx_rates (currency, rate_date, usd_per_unit) holds one daily rate per currency — but only for days that currency's market was open, so weekends and local holidays have no row, and the holidays differ between currencies. The table has no rows for 'USD'.
Convert every order to USD:
- •Use the order currency's rate for the order date; if that day has none, use the most recent earlier rate for the same currency. Never use a later rate, and never another currency's.
- •An order with no rate on or before its date gets NULL
rate_date and NULL amount_usd. - •USD orders need no rate:
amount_usd = amount, rate_date NULL.
Columns: order_id, currency, amount, rate_date (the date of the rate used), amount_usd (amount × usd_per_unit, rounded to 2 decimal places). Sort by order_id.