I want to join two tables and one is missing a row for each currency group.
Tables:
Transactions in local currency aggregated by month.(transactions table)
Date Currency spend
2019-01-01 EUR 100
2019-02-01 EUR 200
2019-03-01 EUR 500
2019-04-01 EUR 214
2019-01-01 JYP 3200
2019-01-01 JYP 1534
2019-02-01 JYP 1534
2019-03-01 JYP 1534
2019-04-01 JYP 1534
Exchange Rate by month(exchange_data table)
Month Currency Average Monthly rate
2019-01-01 EUR 1.2
2019-02-01 EUR 1.3
2019-03-01 EUR 1.4
2019-01-01 JYP 101
2019-02-01 JYP 102
2019-03-01 JYP 103
2019-01-01 USA 1
2019-02-01 USA 1
2019-03-01 USA 1
I want to perform a join to get all the transactions in USD. The problem is that the rate for the current month(2019-04-01) is not available. So all the transactions for the current month return NULL after the join.
I've managed to solve it in R but is there a way to solve it with SQL? I've been trying to use a window function but without success
LAG(rate,1) OVER (PARTITION BY currency ORDER BY month)
Solution in R: Assuming that the rate remains constant.
library(lubridate)
library(dplyr)
exchange_previous <- exchange_data[exchange_data$month == floor_date(today(),"month") %m-% months(1),]
exchange_previous$month <- exchange_previous$month %m+% months(1)
exchange_data<-rbind(exchange_data,exchange_previous)
final <- transactions %>%
left_join(exchange_data, by = c("currency" = "name", "floor_date" = "month"))
Then simply multiply
Use a lateral join but it should look like this:
select t.*, ed.average_monthly_rate,
from transactions t left join lateral
(select ed.*
from exchange_data ed
where ed.currency = t.currency and
ed.month <= t.date
order by ed.month desc
fetch first 1 row only
) ed
on 1=1;
I'm not sure if you want to divide or multiply by the rate.