SQL Server 2019

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

CREATE TABLE AAATRANSPORTTABLE
    ([ProNumber] int, [BillDate] date, [CREATEDDATETIME] datetime2, [RECID] bigint);
    
INSERT INTO AAATRANSPORTTABLE
    ([ProNumber], [BillDate], [CREATEDDATETIME], [RECID])
VALUES
    (14521857, '2019-04-10', '2019-06-04 21:53:09.000', 5637146183);

CREATE TABLE AAALTLCHANGEREQUEST
    ([AAAREFNUMVALUE] int, [AAALTLCHANGEVALUE] varchar(16), [RECID] bigint, 
     [CREATEDDATETIME] datetime2, [AAATRANSPORTORDERRECID] bigint);
    
INSERT INTO AAALTLCHANGEREQUEST
    ([AAAREFNUMVALUE], [AAALTLCHANGEVALUE], [RECID], [CREATEDDATETIME], [AAATRANSPORTORDERRECID])
VALUES
    (14521857, 'Edit Cycle', 5637655326, '2020-01-21 14:26:31.000', 5637146183),
    (14521857, 'Ready to Invoice', 5637656076, '2020-01-21 14:29:24.000', 5637146183),
    (14521857, 'Invoiced', 5637656098, '2020-01-21 16:04:39.000', 5637146183);

SELECT

t.[ProNumber],
    t.CREATEDDATETIME,
    t.[BillDate],
    t.RECID,
    l.AAALTLCHANGEVALUE,
    l.CREATEDDATETIME as Status_Date

FROM [AAATRANSPORTTABLE] t
LEFT JOIN (
select t1.AAATRANSPORTORDERRECID, t1.AAALTLCHANGEVALUE, t1.CREATEDDATETIME 
from AAALTLCHANGEREQUEST t1
inner join (select AAATRANSPORTORDERRECID, max(CREATEDDATETIME) as maxDate 
from AAALTLCHANGEREQUEST
group by AAATRANSPORTORDERRECID) t2 
  on t1.AAATRANSPORTORDERRECID = t2.AAATRANSPORTORDERRECID 
    and t1.CREATEDDATETIME = t2.MaxDate) l
ON t.RECID = l.AAATRANSPORTORDERRECID

WHERE t.[ProNumber] = '14521857';

ProNumber	CREATEDDATETIME	BillDate	RECID	AAALTLCHANGEVALUE	Status_Date
14521857	2019-06-04 21:53:09.0000000	2019-04-10	5637146183	Invoiced	2020-01-21 16:04:39.0000000