2

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
2
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.

1
  • Ok thanks for the link as well I had no idea that was a thing. I tried to narrow the results to try and get a known result. Which is 36 i'm hoping. Oct 31 '16 at 20:02
0

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.

2
  • Line 13 pos.personID cannot be bound. I'm not familiar with the OUTER APPLY. I added on line 12 FROM dbo.POSEligibility AS pos. And now it's saying per.personID not found. Oct 31 '16 at 19:52
  • Whoops used the entire APPLY alias rather than creating an internal alias for POSEligibility. Andy Jones' answer is slightly better though, because mine doesn't account for multiple eligibilities correctly. The OUTER APPLY is useful when you need to do something (like check a max value) against each row. Oct 31 '16 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.