How to select and join field with max date?

I have two tables joined on RECID and AAATRANSPORTORDERRECID :

AAATRANSPORTTABLE

Pro Number  Bill Date   CREATEDDATETIME         RECID
14521857    2019-04-10  2019-06-04 21:53:09.000 5637146183

AAALTLCHANGEREQUEST

AAAREFNUMVALUE  AAALTLCHANGEVALUE   RECID        CREATEDDATETIME              AAATRANSPORTORDERRECID
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

I need to select the record shown from AAATRANSPORTTABLE and join the AAALTLCHANGEVALUE value for the most recent CREATEDDATETIME from AAALTLCHANGEREQUEST. My query is as below:

SELECT

    t.[Pro Number],
    t.CREATEDDATETIME,
    t.[Bill Date],
    t.RECID,
    l.AAALTLCHANGEVALUE,
    max(l.CREATEDDATETIME) as Status_Date


FROM [AX2cTestStage].[dbo].[AAATRANSPORTTABLE_V] t
LEFT JOIN AX2cTestAdapter_dbo_AAALTLCHANGEREQUEST_V l 
ON t.RECID = l.AAATRANSPORTORDERRECID

WHERE t.[Pro Number] = '14521857'

GROUP by l.CREATEDDATETIME,t.[Pro Number],t.CREATEDDATETIME,t.[Bill Date],t.RECID,l.AAALTLCHANGEVALUE  

It produces these results:

Pro Number  Bill Date     CREATEDDATETIME              RECID        AAALTLCHANGEVALUE   Status_Date
14521857    2019-04-10    2019-06-04 21:53:09.000     5637146183    Edit Cycle          2020-03-24 11:42:52.000
14521857    2019-04-10    2019-06-04 21:53:09.000     5637146183    Ready to Invoice    2020-03-24 11:51:00.000
14521857    2019-04-10    2019-06-04 21:53:09.000     5637146183    Invoiced            2020-03-24 11:52:08.000

My desired output is

Pro Number  Bill Date   CREATEDDATETIME         RECID       AAALTLCHANGEVALUE  Status_Date
14521857    2019-04-10  2019-06-04 21:53:09.000 5637146183  Invoiced           2020-03-24 11:52:08.000

Answer

The problem is you are selecting max(l.CREATEDDATETIME) in your select query but you are not doing anything to select the max date in your where clause. You have to put a where clause to select the row with max date out of your 3 rows. I have tried to modify your query, you might need to modify the table and column name but you will get the context once you go through it –

SELECT

    t.[Pro Number],
    t.CREATEDDATETIME,
    t.[Bill Date],
    t.RECID,
    l.AAALTLCHANGEVALUE,
    l.CREATEDDATETIME as Status_Date


FROM [AX2cTestStage].[dbo].[AAATRANSPORTTABLE_V] t
LEFT JOIN AX2cTestAdapter_dbo_AAALTLCHANGEREQUEST_V l 
ON t.RECID = l.AAATRANSPORTORDERRECID

WHERE t.[Pro Number] = '14521857'
AND l.CREATEDDATETIME = (select max(V.CREATEDDATETIME) from AX2cTestAdapter_dbo_AAALTLCHANGEREQUEST_V V where V.AAAREFNUMVALUE = '14521857')

GROUP by l.CREATEDDATETIME,t.[Pro Number],t.CREATEDDATETIME,t.[Bill Date],t.RECID,l.AAALTLCHANGEVALUE