I need to consolidate two tables specifically by associating task milestones (here being registration and completion) with their statuses at that time.
Example data is below.
Customers table:
Location Registration Completion
AZ 01/15/22 01/30/22
Locations table:
Location Status_Date Status
AZ 01/05/22 Pending
AZ 01/20/22 Approved
The desired result using sample data would be:
Location Status_Registration Status_Completion
AZ Pending Approved
My initial logic got me as far as linking status dates and milestones by year & month but stuck on how to specifically tie a milestone status to the timeframe that status was valid, i.e. until the status changed in that same location (join along location).