Skip to content
Advertisement

MySQL – Sum revenue while correcting by daily exchange rates

I have two tables

Orders table

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

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:

Advertisement

Answer

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

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

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:

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

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement