Exchange Rate 20170317

CREATE TABLE IF NOT EXISTS exchange_rate (
currency_code varchar(5) NOT NULL,
effective_date date NOT NULL,
exchange_rate decimal(5,0) NOT NULL
);

INSERT INTO exchange_rate (currency_code, effective_date, exchange_rate) VALUES
('USD', '2016-01-01', '13000'),
('USD', '2016-02-01', '13500'),
('USD', '2016-03-10', '13200'),
('SGD', '2016-01-01', '9500'),
('SGD', '2016-02-11', '9300'),
('SGD', '2016-03-20', '9250');

SELECT * FROM exchange_rate;

+---------------+----------------+---------------+
| currency_code | effective_date | exchange_rate |
+---------------+----------------+---------------+
| USD           | 2016-01-01     |         13000 |
| USD           | 2016-02-01     |         13500 |
| USD           | 2016-03-10     |         13200 |
| SGD           | 2016-01-01     |          9500 |
| SGD           | 2016-02-11     |          9300 |
| SGD           | 2016-03-20     |          9250 |
+---------------+----------------+---------------+

SELECT
    a.currency_code,
    a.effective_date,
    a.exchange_rate
FROM
    exchange_rate AS a
ORDER BY
    a.currency_code DESC,
    a.effective_date ASC;

+---------------+----------------+---------------+
| currency_code | effective_date | exchange_rate |
+---------------+----------------+---------------+
| USD           | 2016-01-01     |         13000 |
| USD           | 2016-02-01     |         13500 |
| USD           | 2016-03-10     |         13200 |
| SGD           | 2016-01-01     |          9500 |
| SGD           | 2016-02-11     |          9300 |
| SGD           | 2016-03-20     |          9250 |
+---------------+----------------+---------------+

SELECT
    a.currency_code,
    a.effective_date,
    (
        SELECT
            DATE_SUB(MIN(b.effective_date), INTERVAL 1 DAY)
        FROM exchange_rate AS b
        WHERE
            b.effective_date>a.effective_date AND a.currency_code=b.currency_code
    ) AS effective_end_date,
    a.exchange_rate
FROM
    exchange_rate AS a
ORDER BY
    a.currency_code DESC,
    a.effective_date ASC;

+---------------+----------------+--------------------+---------------+
| currency_code | effective_date | effective_end_date | exchange_rate |
+---------------+----------------+--------------------+---------------+
| USD           | 2016-01-01     | 2016-01-31         |         13000 |
| USD           | 2016-02-01     | 2016-03-09         |         13500 |
| USD           | 2016-03-10     | NULL               |         13200 |
| SGD           | 2016-01-01     | 2016-02-10         |          9500 |
| SGD           | 2016-02-11     | 2016-03-19         |          9300 |
| SGD           | 2016-03-20     | NULL               |          9250 |
+---------------+----------------+--------------------+---------------+

SELECT
    a.currency_code,
    DATE_FORMAT(a.effective_date,'%d-%b-%Y') AS effective_date,
    DATE_FORMAT(
        COALESCE(
            (
            SELECT
                DATE_SUB(MIN(b.effective_date), INTERVAL 1 DAY)
            FROM exchange_rate AS b
            WHERE
                b.effective_date>a.effective_date AND a.currency_code=b.currency_code
            )
            ,'2099-12-31'
        )
        ,'%d-%b-%Y'
    ) AS effective_end_date,
    a.exchange_rate
FROM
    exchange_rate AS a
ORDER BY
    a.currency_code DESC,
    a.effective_date ASC;

+---------------+----------------+--------------------+---------------+
| currency_code | effective_date | effective_end_date | exchange_rate |
+---------------+----------------+--------------------+---------------+
| USD           | 01-Jan-2016    | 31-Jan-2016        |         13000 |
| USD           | 01-Feb-2016    | 09-Mar-2016        |         13500 |
| USD           | 10-Mar-2016    | 31-Dec-2099        |         13200 |
| SGD           | 01-Jan-2016    | 10-Feb-2016        |          9500 |
| SGD           | 11-Feb-2016    | 19-Mar-2016        |          9300 |
| SGD           | 20-Mar-2016    | 31-Dec-2099        |          9250 |
+---------------+----------------+--------------------+---------------+