Need help with sub queries across multiple tables with conditions

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

Use OUTER APPLY instead of LEFT OUTER JOIN. Btw, don't worry about the "SELECT *" in the APPLY -- SQL will only actually read columns that are used in the outer SELECT:

SELECT PE.progname, PE.clientid, PEStartDate, PE.EndDate, 
    WA.StaffID, WA.WAStartDate, WA.WAEndDate, WA.SupervisorID
FROM dbo.ProgEnroll PE 
OUTER APPLY (
    SELECT TOP (1) *
    FROM dbo.WorkerAssignments WA2
    WHERE PE.ProgEnrollID = WA2.ProgEnrollID AND
         WA2.WAStartDate BETWEEN PE.StartDate AND PE.EndDate
    ORDER BY WA2.WAStartDate DESC
) AS WA