I'm tring to list every personID in the person table while left joining it to the POSEligibility table that may not have a personID entry or if it does may have multiple entries as a personID is created for each startdate entry. New startdates are added each year for those eligible. Which is why i'm using the MAX Aggergate on startdate. I only want the most recent date. If I remove the subquery I get all my personIDs from the person table with NULL entries for those personIDs that don't have a startdate as expected. But I also get all the startdates that each personID has, since of course the Aggergate is missing. Any ideas?
SELECT per.personID ,id.lastName ,id.firstName ,id.middleName ,CONVERT(VARCHAR(10), pos1.startDate, 101) AS StartDate ,cal.name ,en.grade ,pos1.eligibility FROM dbo.Person per LEFT OUTER JOIN dbo.POSEligibility pos1 ON per.personID = pos1.personID INNER JOIN dbo.[Identity] id ON per.personID = id.personID INNER JOIN dbo.Enrollment en ON per.personID = en.personID INNER JOIN dbo.Calendar cal ON en.calendarID = cal.calendarID INNER JOIN (SELECT pos2.personID ,MAX(pos2.startDate) AS startdate FROM dbo.POSEligibility pos2 GROUP BY pos2.personID) pos2 ON pos2.personID = pos1.personID AND pos2.startdate = pos1.startDate WHERE en.grade = '01' AND cal.name LIKE '%BES' AND en.active = '1' AND en.endYear = '2017' GROUP BY per.personID ,pos1.startDate ,id.lastName ,id.firstName ,id.middleName ,cal.name ,en.grade ,pos1.eligibility