MySQL perform subquery if count is zero

Question

I have three tables: monthly_revenue, currencies and foreign_exchange.

monthly_revenue table

|------------------------------------------------------|
| id | product_id | currency_id | value | month | year |
|------------------------------------------------------|
| 1  | 1          | 1           | 100   | 1     | 2015 |
| 2  | 1          | 2           | 125   | 1     | 2015 |
| 3  | 1          | 3           | 115   | 1     | 2015 |
| 4  | 1          | 1           | 100   | 2     | 2015 |
| 5  | 1          | 2           | 125   | 2     | 2015 |
| 6  | 1          | 3           | 115   | 2     | 2015 |
|------------------------------------------------------|

foreign_exchange table

|---------------------------------------|
| id | base | target | rate | rate_date |
|---------------------------------------|
| 1  | GBP  | USD    | 1.6  |2015-01-01 |
| 2  | GBP  | USD    | 1.62 |2015-01-15 |
| 3  | GBP  | USD    | 1.61 |2015-01-31 |
| 4  | EUR  | USD    | 1.2  |2015-01-01 |
| 5  | EUR  | USD    | 1.4  |2015-01-15 |
| 6  | EUR  | USD    | 1.4  |2015-01-31 |
| 7  | GBP  | EUR    | 1.4  |2015-01-01 |
| 8  | GBP  | EUR    | 1.45 |2015-01-15 |
| 9  | GBP  | EUR    | 1.44 |2015-01-31 |
|---------------------------------------|

From this, we can see the average fx rates:

• GBP > USD in January is 1.61
• EUR > USD in January is 1.33
• GBP > EUR in January is 1.43

No rates are available for USD as a base currency, and no rates are available for February.

currencies table

|-----------|
| id | name |
|-----------|
| 1  | GBP  |
| 2  | USD  |
| 3  | EUR  |
|-----------|

What i'm trying to achieve

Each row within the monthly_revenue table can have a different currency_id, as orders are placed is different currencies. I want to see all revenue for a given month, in a common currency. So, rather than looking at all revenue in January in GBP, and then separately looking at all revenue in January in USD, I'd like to get one value for all revenue in January - converted to USD (for example).

This can be calculated for each row, using the following (using January for this example):

revenue value x average fx rate for January between base and target currency

If I have 50 orders in January, in 4 different currencies, this let's me see all revenue in any single currency.

Example - get all revenue in January, in USD

This should return:

|------------------------------------------------------|
| id | product_id | currency_id | value | month | year |
|------------------------------------------------------|
| 1  | 1          | 1           | 100   | 1     | 2015 |
| 2  | 1          | 2           | 125   | 1     | 2015 |
| 3  | 1          | 3           | 115   | 1     | 2015 |
|------------------------------------------------------|

However, rows 1 and 3 are not in USD (these are GBP, and EUR respectively).

What I'd like to see is each row returned with the average FX rate that is being converted to, and a converted column. For example:

|-------------------------------------------------------------------------|
| id | prod_id | currency_id | value | month | year | fx_avg | converted  |
|-------------------------------------------------------------------------|
| 1  | 1       | 1           | 100   | 1     | 2015 | 1.61   | 161        |
| 2  | 1       | 2           | 125   | 1     | 2015 | 1      | 125        |
| 3  | 1       | 3           | 115   | 1     | 2015 | 1.33   | 152.95     |
|-------------------------------------------------------------------------|

Where I'm at

I can currently get the basic calculation done using the query below, but a couple of key features are lacking:

• If there is no FX rate available (for example for future dates where of course an FX rate isn't available) then the entire row is ignored. What I'd like in this instance is for the latest month's average to be used.

• If the calculation is being performed where the target currency is the same as the base currency, the entire row is ignored (as there is no record in the FX table where the base equals the target). In this instance, the rate should be hard defined as 1.

Query so far

SELECT 
    r.value * IFNULL(AVG(fx.rate),1) as converted, AVG(fx.rate) as averageFx, 
    r.*, fx.*
FROM 
    foreign_exchange fx, monthly_revenue r, order_headers h
WHERE 
    fx.base IN (SELECT name FROM currencies WHERE id = r.currency_id) AND 
    r.order_header_id = h.id AND 
    fx.target = 'USD' AND 
    MONTH(fx.rate_date) = r.month AND
    YEAR(fx.rate_date) = r.year AND
    r.year = 2015
GROUP BY r.id
ORDER BY month ASC

If there are no records available for FX, it looks like a separate subquery should be performed to get the average of the latest month's rates.

Any input would be appreciated. If any further info is required, please post a comment.

Thanks.

Edit Here is a SQFiddle which has the example schemas and the code which highlights the issue.

Answer 1

Here is an approximation of a function that computes your exchange for a given currency and start of month:

DELIMITER //
CREATE FUNCTION MonthRate(IN _curr CHAR(3) CHARACTER SET ascii,
                          IN _date DATE)
    RETURNS FLOAT
    DETERMINISTIC
BEGIN
    -- Note:  _date must be the first of some month, such as '2015-02-01'
    DECLARE _avg FLOAT;
    DECLARE _prev FLOAT;
    -- First, try to get the average for the month:
    SELECT AVG(rate) INTO _avg FROM foreign_exchange
        WHERE base = _curr
          AND target = 'USD'
          AND rate_date >= _date
          AND rate_date  < _date + INTERVAL 1 MONTH;
    IF _avg IS NOT NULL THEN
        RETURN _avg;
    END;
    -- Fall back onto the last rate before the month:
    SELECT rate INTO _prev
        FROM foreign_exchange
        WHERE base = _curr
          AND target = 'USD'
          AND rate_date < _date
        ORDER BY _date
        LIMIT 1;
    IF _prev IS NOT NULL THEN
        RETURN _prev;
    END;
    SELECT "Could not get value -- ran off start of Rates table";
END;
DELIMITER ;

There are probably syntax errors, etc. But hopefully you can work with it.

It should be easy to call the function from the rest of the code.

For performance, this would be beneficial:

INDEX(base, target, rate_date, rate)