Join the main Table with history table to get the current rate of the main table and the last and previous rate from the history table

Question

As per the part of the avoiding two different tables is there any way to combine it to a single query to get the result? Here in the history table having more than 100 rows against the currency 'USD' I need only the last value which is move to the history to pick out

Select  M.CR CR, M.Rate RATE, M.Operator OPERATOR
From Rate M
Where M.CR = 'USD' 


Select M.CR CR, M.Rate RATE, M.Operator OPERATOR
    , max(M.FROMSOURCEDATE) As MovedDate
From RateHistory M
Where M.CR = 'USD' 
Group by M.CR, M.Rate, M.Operator

Here only FROMSOURCEDATE is the only field I can which is updating with date when it is moved to history table.

Current Result is

CR	RATE	OPERATOR
USD	3.65	*

CR	RATE	OPERATOR	MOVEDDATE
USD	3.75	*	2023-10-29 22:57:22.820
USD	3.73	*	2023-10-29 22:56:22.810
USD	3.67	*	2023-10-29 22:56:21.820
USD	3.77	*	2023-10-29 22:57:22.803
USD	3.55	*	2023-9-29 21:57:22.820
USD	3.45	*	2023-9-29 21:56:22.810
USD	3.71	*	2023-8-29 20:55:25.505
USD	3.75	*	2023-8-29 20:55:25.410
USD	3.66	*	2023-7-29 22:57:22.820
USD	3.68	*	2023-7-29 22:57:22.820
USD	3.75	*	2023-7-29 22:57:22.820

the needed result is as follows

CR	NEWRATE	OLDRATE	OPERATOR
USD	3.65	3.75	*

Answer 1

You can use TOP 1 within the OUTER APPLY to get only the most recent historical rate based on the latest FROMSOURCEDATE

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'),
('USD', 3.75, '*', '2023-10-29T22:57:22.820'),
('USD', 3.70, '*', '2023-10-28T22:57:22.820');

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	*

fiddle