SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Dim_Calendar([CalendarID] int);
INSERT INTO Dim_Calendar([CalendarID])
VALUES
    (20131001),
    (20131002),
    (20131003),
    (20131004),
    (20131005),
    (20131006),
    (20131007)
;
7 rows affected

CREATE TABLE Dim_Rates([RateDate] int, [RateID] varchar(4));
INSERT INTO Dim_Rates  
    ([RateDate], [RateID])
VALUES
    (20131001, '2'),
    (20131002, '3'),
    (20131003, '4'),
    (20131004, '5'),
    (20131007, '6')
;
5 rows affected

SELECT c.CalendarID,
  COALESCE(
    r.RateID,
    LAG(r.RateID, 1) OVER (ORDER BY c.CalendarID),
    LAG(r.RateID, 2) OVER (ORDER BY c.CalendarID)
  ) RateID
FROM Dim_Calendar c LEFT JOIN Dim_Rates r 
ON r.RateDate = c.CalendarID
ORDER BY c.CalendarID


CalendarID
RateID


20131001
2

20131002
3

20131003
4

20131004
5

20131005
5

20131006
5

20131007
6

CalendarID	RateID
20131001	2
20131002	3
20131003	4
20131004	5
20131005	5
20131006	5
20131007	6