Trying to work with an exchange rate table and a transactions table, and how the transactions are joined to the exchange table depends on when the most recent exchange rate for that currency was relative to the transaction. The table contains many duplicates and many types of currencies and other fields not relevant to this issue.
I plan on joining the transactions by using BETWEEN for the start date and end date for each currency rate and the date of the transaction, however I don’t have an end date field. The exchange rate for a currency ends when the next one starts.
|Currency||Start Date||Rate||End Date|
I was thinking of:
with ordered_currency as ( select distinct Currency, Start_date from exchange_rate_table order by currency, start_date asc )
This would produce a table of all currencies in order of date, removing any duplicates.
The next step would be to check if the next line is the same currency, if it is then take it’s start date, as the end date of the current line. If it’s not the same currency then simply put current_date() as the end_date, and could then join this End_date field back onto the original table.
However, I’m not sure of the syntax which would be used to evaluate whether the line below has the same field (in this case currency)
Any help would be appreciated!
however I don’t have an end date field.
It’s a lot easier if you do. You can “get the one from the next row” with LEAD:
WITH exchg_from_to AS( SELECT Currency, StartDate, LEAD(StartDate, 1, TO_DATE('9999-12-31')) OVER(PARTITION BY Currency ORDER BY StartDate), CAST('9999-12-31' as date) EndDate, Rate FROM exchange_rate_table ) SELECT * FROM tran t JOIN exchg_from_to e ON t.TranDate >= e.StartDate AND t.TranDate < e.EndDate AND t.Currency = e.Currency
You might have to tweak that TO_DATE a bit.. (I’ve never used SnowFlake, but the docs say LEAD is supported))
I don’t recmmend using BETWEEN because it’s inclusive both ends; if you have a tran that is bang on the date it’ll match twice, once with the start date of the N row and once with the N-1 end date. Using a
< pair ensures no overlap