It's probably easier for me to explain this using sample tables.

So I have two tables, (not sure how to convert this into formatted text)

1. Product EOM Table

2. Transaction Table

And the result I want to get is to find out the latest transaction date before each EOM date, like below.

CodePudding user response：

Update: I've achieved this using the code below, just wondering if there's any better idea?

SELECT 
    PE.[Product ID],
    PE.[EOM Date],
    MAX(TR.[Transaction Date]) AS [Last Transaction Date]
FROM [TD_External_Data].[dbo].[Product EOM] AS PE
LEFT JOIN [TD_External_Data].[dbo].[Transaction] AS TR
    ON PE.[Product ID] = TR.[Product ID]
    AND PE.[EOM Date] >= TR.[Transaction Date]
GROUP BY
    PE.[Product ID],
    PE.[EOM Date]
ORDER BY
    PE.[Product ID],
    PE.[EOM Date]