SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Rate (
    CR NVARCHAR(50),
    Rate DECIMAL(10, 2),
    Operator NVARCHAR(50)
);

CREATE TABLE RateHistory (
    CR NVARCHAR(50),
    Rate DECIMAL(10, 2),
    Operator NVARCHAR(50),
    FROMSOURCEDATE DATETIME
);
INSERT INTO Rate (CR, Rate, Operator) VALUES ('USD', 3.65, '*');

INSERT INTO RateHistory (CR, Rate, Operator, FROMSOURCEDATE) VALUES ('USD', 3.60, '*', '2023-10-27T22:57:22.820');
INSERT INTO RateHistory (CR, Rate, Operator, FROMSOURCEDATE) VALUES ('USD', 3.75, '*', '2023-10-29T22:57:22.820');
INSERT INTO RateHistory (CR, Rate, Operator, FROMSOURCEDATE) VALUES ('USD', 3.70, '*', '2023-10-28T22:57:22.820');
4 rows affected

SELECT
    r.CR,
    r.Rate AS NEWRATE,
    rh.Rate AS OLDRATE,
    r.Operator
FROM
    Rate r
OUTER APPLY (
    SELECT TOP 1 Rate
    FROM RateHistory
    WHERE CR = r.CR
    ORDER BY FROMSOURCEDATE DESC
) rh
WHERE r.CR = 'USD';


CR
NEWRATE
OLDRATE
Operator


USD
3.65
3.75
*