Left Joining Tables with tables that have subquery aggregate

Question

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

Answer 1

SELECT per.personID,
       id.lastName,
       id.firstName,
       id.middleName,
       pos.StartDate,
       cal.[name],
       en.grade,
       pos.eligibility
FROM dbo.Person per
     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
     OUTER APPLY
(
    SELECT TOP 1 p.eligibility,
                 StartDate = CONVERT( VARCHAR(10), p.startDate, 101)
    FROM dbo.POSEligibility AS p
    WHERE p.personID = per.personID
    ORDER BY p.startDate DESC
) AS pos
WHERE en.grade = '01'
      AND cal.[name] LIKE '%BES'
      AND en.active = '1'
      AND en.endYear = '2017';

Note that the filter on cal.[name] is not sargable. This means that even if that column is indexed, the index will not be used for a seek due to the leading "%" in the predicate.

Answer 2

When you are inner joining the subquery, you're eliminating all records that don't match that subquery. Simply changing it to a left join might work; however, here's what I would do:

SELECT DISTINCT
per.personID
,id.lastName
,id.firstName
,id.middleName
,CONVERT(VARCHAR(10), pos.startDate, 101) AS StartDate
,cal.name
,en.grade
,pos.eligibility
FROM dbo.Person per
OUTER APPLY(
SELECT MAX(startdate) as startdate, p.eligibility FROM dbo.POSEligibility p
WHERE per.personID = p.personID --fixed this
) pos
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
WHERE en.grade = '01'
AND cal.name LIKE '%BES'
AND en.active = '1'
AND en.endYear = '2017'

This way you only query the records you need and it should give you what you're looking for, I think.