Self joining next date in table as another date field

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

So another method, that doesn't use JOINS is:

with a CTE for a input data:

WITH fake_data(currency, start_date, rate) AS (
    SELECT column1, to_date(column2, 'dd/mm/yyyy'), column3 
    FROM VALUES 
        ('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)
)

then to get the end_time for each row (same as Caius's answer):

SELECT currency
    ,start_date
    ,rate
    ,lead(start_date,1,current_date) over (order by start_date) as end_date
FROM fake_data
ORDER BY 2;

gives:

But to now chain the results together we want to filer out streaks of same, which we can do with a LAG and QUALIFY

SELECT currency
    ,start_date
    ,rate
    ,lag(currency) over (order by start_date) as lag_currency
FROM fake_data
QUALIFY lag_currency IS NULL OR lag_currency != currency
ORDER BY 2;

and given the prior "end on the row is not useful in that context, we can drop it, but now we want the next entry time to be the end_time of the streak

SELECT currency
    ,start_date
    ,rate
    ,lead(start_date,1,current_date) over (order by start_date) as end_date
FROM (
    SELECT currency
        ,start_date
        ,rate
        ,lag(currency) over (order by start_date) as lag_currency
    FROM fake_data
)
WHERE lag_currency IS NULL OR lag_currency != currency
ORDER BY 2;

I swapped from QUALIFY to an outer WHERE clause, because it's just seems common usage when using nested SELECTs

Now there are some things the JOIN method allows, like aggregation of count of streak rows and values on those, but we can do that here also, we just do a couple of extra steps.

WITH fake_data(currency, start_date, rate) AS (
    SELECT column1, to_date(column2, 'dd/mm/yyyy'), column3 
    FROM VALUES 
        ('EUR','01/12/2021', 1.25),
        ('EUR','02/12/2021', 1.25),
        ('EUR','03/12/2021', 1.25),
        ('US','04/12/2021', 0.75),
        ('US','10/12/2021', 0.8),
        ('EUR','23/12/2021', 1.11),
        ('EUR','24/12/2021', 1.11),
        ('US','25/12/2021', 1.11)
)
SELECT a.*
    ,nvl(streak_start, lag(streak_start)ignore nulls over(order by start_date)) as streak_date
FROM (
    SELECT currency
        ,start_date
        ,rate
        ,lag(currency) over (order by start_date) as lag_currency
        ,lag_currency IS NULL OR lag_currency != currency as new_row
        ,iff(new_row, start_date, null) as streak_start
    FROM fake_data
) AS a
ORDER BY 2;

this gives a grouping token streak_date

thus you can then wrap that and do any aggregation math you like:

WITH fake_data(currency, start_date, rate) AS (
    SELECT column1, to_date(column2, 'dd/mm/yyyy'), column3 
    FROM VALUES 
        ('EUR','01/12/2021', 1.25),
        ('EUR','02/12/2021', 1.26),
        ('EUR','03/12/2021', 1.27),
        ('US','04/12/2021', 0.75),
        ('US','10/12/2021', 0.8),
        ('EUR','23/12/2021', 1.11),
        ('EUR','24/12/2021', 1.14),
        ('US','25/12/2021', 1.11)
)
SELECT b.currency
    ,min(b.start_date) as start_date
    ,any_value(streak_end) as end_date
    ,count(*) as streak_count
    ,avg(rate) as avg_rate
    ,min(rate) as min_rate
    ,max(rate) as max_rate
FROM (
    SELECT a.*
        ,nvl(streak_start, lag(streak_start)ignore nulls over(order by start_date)) as streak_date
        ,lead(streak_start,1,current_date) ignore nulls over (order by start_date) as streak_end
    FROM (
        SELECT currency
            ,start_date
            ,rate
            ,lag(currency) over (order by start_date) as lag_currency
            ,lag_currency IS NULL OR lag_currency != currency as new_row
            ,iff(new_row, start_date, null) as streak_start
        FROM fake_data
    ) AS a
) as b
GROUP BY b.streak_date,1
ORDER BY 2;