Self joining next date in table as another date field

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.

For example:

Currency	Start Date	Rate
EUR	01/12/2021	1.25
US	01/12/2021	0.75
EUR	25/12/2021	1.11
US	10/12/2021	0.8
EUR	01/12/2021	1.25
US	25/12/2021	1.11

Should become:

Currency	Start Date	Rate	End Date
EUR	01/12/2021	1.25	24/12/2021
US	01/12/2021	0.75	09/12/2021
EUR	25/12/2021	1.11	today
US	10/12/2021	0.8	today

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!

Answer

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 >= and < pair ensures no overlap