One option is to create a recursive cte to generate a sort of calendar of all the months from min to max date in sales table. If you are dealing with dates then it is most efficient to use date data types (not number or string). In the ansver below all date columns are truncated to a month. Join sales and rates table to calendar cte taking care of null values and get your expected result: WITH -- recursive cte generating months for period of sales cal ( m_id, a_month, month_untill) AS ( Select 1 as m_id, month_from as a_month, month_untill From ( Select Min(TRUNC(To_Date(dat, yyyyMMdd ), MM )) as month_from, Max(TRUNC(To_Date(dat, yyyyMMdd ), MM )) as month_untill From sales ) UNION ALL Select m_id + 1, ADD_MONTHS(a_month, 1), month_untill From cal Where a_month < month_untill ) ... sales table here was Inner Joined - but if you want to see all the months of period (having sales or not - Jun 2023 no sales ) change it to Left Join and use Coalesce() and type coonversion to get date from calendar instead of sales date. Rates table was Left Joined to the calendar and if there is no current period date the previous one is taken using Coalesce() function... -- S Q L : Select s.dat, s.val, Coalesce(cr.rate, LAG(cr.rate) Over(Order By cal.a_month)) as rate From cal Inner Join sales s ON( Trunc(To_Date(SubStr(s.dat, 1, 10), yyyyMMdd ), MM ) = cal.a_month ) Left Join currency_rate cr ON( Coalesce(Trunc(cr.currentperiod, MM ), Trunc(cr.previousperiod, MM )) = cal.a_month ) Order By cal.a_month, s.dat /* R e s u l t : DAT VAL RATE ---------- ---------- ---------- 20230404 1226.55 0.894166 20230506 8 0.893192 20230506 102.14 0.893192 20230526 12813.4 0.893192 20230717 13834.73 0.887729 20230805 779.9 0.887729 20230904 8 0.898093 20230912 8 0.898093 20230916 10120.71 0.898093 */

Quick Answer Look below for a quick comment on your query. Note: To make the example easy I already assume all dates are equally formatted as dates, you may have to adapt to your table schemas. You have a good approach where you first try to clean up the rates table and then join on a date range, but you re just missing a few details when cleaning up the table, here is a possible solution: WITH FilledRates AS ( SELECT CASE WHEN CurrentPeriod IS NOT NULL THEN CurrentPeriod ELSE LEAD(PreviousPeriod, 1) OVER (ORDER BY (SELECT NULL)) END AS current_period, PreviousPeriod as previous_period, CurrRate as curr_rate FROM currency_rate ), RateRange AS ( SELECT start_period, CASE WHEN end_period IS NULL THEN DATEADD(month, 1, start_period) ELSE end_period END AS end_period, curr_rate FROM ( SELECT current_period AS start_period, LEAD(current_period, 1) OVER (ORDER BY current_period ASC) AS end_period, curr_rate FROM FilledRates ) AS RateRangeWithNullEnd ) SELECT s.* rr.curr_rate FROM v_sales s LEFT OUTER JOIN RateRange rr ON s.sale_date > rr.start_period AND s.sale_date < rr.end_period; This is actually similar to your approach, the main differences are: FilledRates: Use LEAD to look to the next previous_period to fill in current_period when it is null, this ensures no null current_period RateRange: Change the rates table to a table where each record has the start and end date for the given range with no null values, this ensures an easy join moving forward. And also helps join the dates on the last period. Note: I am assuming empty dates can be filled with a DATEADD statement where we add a month. You didn t indicate any specific rule here, but I will assume intervals are consistent in length. Result: The join is quite similar to what you already have, but we now the table we join in has the start and end period for each rate. Notes In the statement: SELECT CurrentPeriod, LAG(CurrentPeriod) OVER (PARTITION BY curr_rate_from, curr_rate_to ORDER BY CurrentRate) AS PreviousPeriod, curr_rate_from, curr_rate_to, CurrRate FROM schema.currency_rate By using LAG you are looking one record ahead, but based on your example, a record ahead can be null (line 5 from the currency_rate table). Because you are ordering by CurrentPeriod, this will result in your currency rates for any period after the first null value to not be linked to the correct periods.