I’m working in Microsoft SQL Management Studio.

I want to run a code to return the most recent invoice date on a purchase order in the query, but the code currently returns all invoice dates per line and not just the most recent date.

I’ve tried nesting in the max(date) function in the where clause instead, but that took out my NULL values which I need to retain.

I need to join in all 4 tables as the invoice date is only stored on the Invoice Header table and that only relates to the Invoice Detail page, which only relates to the PO detail page.

select distinct
po.PO_Number as 'PO #'
,pd.Item_Number as 'Item #'
,invoicedate


FROM PO_Header as PO
INNER JOIN PO_Detail as pd
ON po.PO_Header_ID = pd.PO_Header_ID
LEFT JOIN Invoice_Detail as vd on pd.PO_Detail_ID = vd.PO_Detail_ID
LEFT JOIN Invoice_Header as vh on vh.Invoice_Header_ID =  vd.Vendor_Invoice_Header_ID
LEFT JOIN (SELECT Invoice_Header_ID, max(invoice_date) AS invoicedate
FROM  Invoice_Header
GROUP BY Invoice_Header_ID) md
ON md.Invoice_Header_ID = vh.Invoice_Header_ID  

GROUP BY PO.PO_Number 
,pd.Item_Number
,pd.Part_Number
,invoicedate

Expected Output:

PO #    Item #  invoicedate
1111    1   04/07/2021
1112    1   05/07/2022
1113    1   04/21/2021
1114    1   04/19/2023

Current Output:

PO #    Item #  invoicedate
1111    1   04/07/2021
1111    1   02/18/2022
1111    1   05/25/2021
1111    1   02/18/2022
1111    1   05/25/2021