0

I have a table that looks like this:

Product  Currency  Cost    date
A        JPY       25.22   20211015
B        TRY       32.25   20200805
.
.

And another table that has the currency exchanges:

Currency   ExchangeRate   date
0.22       JPY            20211025 
0.22       JPY            20211022  
0.21       JPY            20211018 
0.20       JPY            20211014
0.31       TRY            20200802
0.30       TRY            20200805

The result should be a join between these two tables where the data in the product table matches the date between the currency table where the exchange rate and currency match. It should look something like this:

Product  Currency  Cost    date         currencyDate   currencyCurrency
A        JPY       25.22   20211015     20211014        0.20
B        TRY       32.25   20200805     20200805        0.30

I have a query like this:

> select *, (select Currency from currency inner join product on
> currency.ExchangeRate = product.Currency where product.date >=
> currency.date order by currency.date desc limit 1) currencyCurrency,
> (select currency.date from currency inner join product on
> currency.ExchangeRate = product.Currency where product.date >=
> currency.date order by currency.date desc limit 1) currencyDate from
> product

However, when I do this it just uses the maximum date in currency and the exchange rate for all of them. What am I doing wrong? I am using Spark SQL

user2896120
  • 3,180
  • 4
  • 40
  • 100

1 Answers1

1

This answers the original version of the question which was tagged SQL Server.

You can use outer apply. Using table names in your query:

select p.*, c.*
from product p outer apply
     (select top (1) c.*
      from currency c
      where c.currency = p.currency and
            c.date <= p.date
      order by c.date desc
     ) c;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786