Skip to content
Advertisement

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!

Advertisement

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

1 People found this is helpful
Advertisement