0

I need to JOIN two tables to correct the price with exchange rates as

SELECT date, price/rate AS adjusted FROM
table1 a JOIN table2 b ON a.date=b.date

The problem is that table2 does not have the exchange rate for all dates.

How can I get a nearest exchange rate for missing dates in table2?

EXAMPLE

Table 1

date          price
2019-01-01    2.3
2019-01-03    3.4
2019-01-04    2.6
2019-01-05    2.2
2019-01-06    2.5
2019-01-07    5.1
2019-01-08    4.6
2019-01-09    4.2
2019-01-10    3.3

Table 2

date          rate
2019-01-04    1.1
2019-01-06    1.3
2019-01-09    1.0

Alternatively, an approach to fill the missing dates in table2 works too.

  • 1
    What version of mysql are you using? Oh, and define "nearest" - The previous available rate for a given date (would make sense to me), or something else? – Philᵀᴹ Apr 19 at 14:11
  • @Philᵀᴹ it's not that sensitive, any rate from previous or next available dates. Your suggestion for previous available rate perfectly works. I have mysql 5.7.25 and the table is innoDB – Googlebot Apr 19 at 14:21
1

To perform the join without matching rows on the dates you need to use an outer join. The challenge here is going to be propagating down the nearest rates as you are missing various rates in table2 both ordered ascending and descending. I don't have much experience with MySQL so I'm not sure if Window Functions are available? I believe this will help you accomplish your goal.

ISNULL(ISNULL(rate, (SELECT TOP (1) rate FROM table2 AS t2 
WHERE a.date <= t2.date)), 
(SELECT TOP (1) rate FROM table2 AS t2 
 WHERE a.date >= t2.date 
 ORDER BY t2.date DESC))

SELECT a.[date],
       b.[date],
       a. price, 
       b.rate, 
       price/rate AS adjusted,
       ISNULL(ISNULL(rate, 
(SELECT TOP (1) rate FROM table2 AS t2 
 WHERE a.date <= t2.date)), 
(SELECT TOP (1) rate FROM table2 AS t2 
 WHERE a.date >= t2.date ORDER BY t2.date DESC))
FROM table1 AS a
LEFT OUTER JOIN table2 AS b
    ON a.[date] = b.[date];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.