I am having trouble understanding sub queries across multiple tables with conditions and how to form them correctly
I have 4 tables
ProgEnroll - TABLE
ProgEnrollID - PKey
StartDate
EndDate
ClientID
...
WorkerAssignments - TABLE
WorkerAssignID - PKey
ProgEnrollID - ties to progenroll
StaffID
WAStartDate - Worker Start Date
WAEndDate - Worker End date
SupervisorID
...
Client - TABLE
ClientID - PKey
...
Staff - TABLE
StaffID - PKey
...
I have my progenroll table filtered down the way I need it with other table attached, that is ok, the problem I am having is that there could have been More than one worker assigned to an enrollment over time, I need to get the worker that has the most recent WAStartDate that is between ProgEnroll.StartDate and ProgEnroll.EndDate.
Select PE.progname, PE.clientid, PEStartDate, PE.EndDate, WA.StaffID, WA.WAStartDate, WA.WAEndDate, WA.SupervisorID
From ProgEnroll PE left outer join
WorkerAssignments WA on PE.ProgEnrollID = WA.ProgEnrollID
Thank you,
Steve