MySQL – Sum revenue while correcting by daily exchange rates

I have two tables

Orders table

id       | total_price (EUR)| order_date |
---------|------------------|------------|
1        | 400              | 2021-05-01 |
2        | 1350             | 2021-10-04 |
3        | 760              | 2021-10-05 |

Exchange rates table (cron-job does the job for this)

id       | day              | base_currency | currency | exchange |
---------|------------------|---------------|----------|----------|
1        | 2021-10-03       | EUR           | USD      | 1.19     |
2        | 2021-10-04       | EUR           | USD      | 1.17     |
3        | 2021-10-05       | EUR           | USD      | 1.16     |

I have to sum the orders total in USD and with the correct exchange rate!

How could I achieve that? Is there a common practice?

What I have so far is a basic sum:

sum(total_price) as salesRevenue,

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The link you need is that based on the date. For that purpose I’d first run a check to see whether I do have all the necessary information.

So I’ll ask, given all the order dates, what rates are available for that day; requiring that the rate is NULL, meaning that there is no rate. The desired result is that the query returns nothing (no days have no rates, so all days have at least one rate).

SELECT o.order_date 
   FROM orders AS o 
   LEFT JOIN rates AS r 
       ON (o.order_date = r.day AND base_currency='EUR' AND currency='USD')
   WHERE r.exchange IS NULL;

Another acceptance test I’d run is to verify that there are no double entries for the exchange rate (each day has exactly one rate):

SELECT day, COUNT(*) AS n FROM rates
    GROUP BY day
    HAVING COUNT(*) > 1;

Again the desired result is that the query returns nothing.

Should the tests report anything, you wouldn’t be able to run the queries below, because you’d get wrong results.

If the tests both report nothing, then you can repeat almost the same JOIN and each tuple will have the exchange column available for use:

SELECT SUM(o.order_amount * usd.exchange) AS TotalUSD
   FROM orders AS o 
   -- Now we know a link is always available, we can use a JOIN
   JOIN rates AS usd
       ON (o.order_date = usd.day AND usd.base_currency='EUR' AND usd.currency='USD')
   ;

If you wanted to add a further currency column you would need to repeat the JOIN with a different alias:

SELECT SUM(o.order_amount * usd.exchange) AS TotalUSD,
       SUM(o.order_amount * sek.exchange) AS TotalSEK,
   FROM orders AS o 
   JOIN rates AS usd
       ON (o.order_date = usd.day AND base_currency='EUR' AND currency='USD')
   JOIN rates AS sek
       ON (o.order_date = sek.day AND sek.base_currency='EUR' AND sek.currency='SEK')
   ;

Method 2

With MySql >= 8.0

I assume you don’t have every day exchange rate, so I use the previous last exchange rate available. If some days you have more than one exchange rate, you should save hour and minutes too.

First you need to get the previous last exchange rate for each order, then you have to convert to dollars, and finally sum all orders total in dollars.

Be careful: in your example data, first order don’t have a exchange rate, because it has to be previous to the order date (this order will be omit in my query).

WITH cte AS (SELECT o.id, o.order_date, o.total_price, 
                    FIRST_VALUE(er.exchange) OVER (PARTITION BY o.id ORDER BY er.day DESC) AS exchange
             FROM Orders o
             INNER JOIN ExchangeRates er ON o.order_date >= er.day
             WHERE base_currency = 'EUR' AND currency = 'USD'),
             
     cte1 AS (SELECT id, order_date, total_price * MIN(exchange) AS total_price_dolar
              FROM cte
              GROUP BY id, order_date, total_price)
              
SELECT SUM(total_price_dolar) AS total
FROM cte1;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0