Ok, I always try and figure problems out on my own but I'm getting my butt kicked with this. I trying to pull data on the most current entry of people that have multiple entries and different dates. Example is PersonID and Startdate returns 3, 02/09/2016 and 3, 7/29/2016 instead of just the 7/29/2016 for the personID of 3 in this case.
I found out one reason I was not getting my desired result is from someone else's question here.
The short version is my query was using the GROUP BY clause on the personID column which has a unique value for both entries. As a result I was getting too many rows. It was suggested to use a subquery to apply the aggregate function to return the max(startDate) for each personID.
As I'm not sure how to do that. I looked at many examples and tried to copy them. The part that's getting me is where I did the SELECT MAX INNER JOIN. I'm assuming the pos2 is the alias for the Select I just did above.
But "ON pos1.personID = pos2.personID" warns that pos2.personID is an invalid column so my alias idea doesn't seem right. Below is my entire query.
SELECT per.personID, id.lastName, id.middleName, id.firstName, CONVERT(VARCHAR(10), pos1.startDate, 101) AS StartDate, cal.name, en.grade FROM dbo.POSEligibility pos1 INNER JOIN ( SELECT MAX(startDate) AS startDate, PersonID FROM dbo.POSEligibility GROUP BY personID ) pos2 ON pos1.personID = pos2.personID AND pos1.startDate = pos2.startDate INNER JOIN dbo.Person per ON ( pos1.personID = per.personID) INNER JOIN dbo.Enrollment en ON ( per.personID = en.personID) INNER JOIN dbo.[Identity] id ON ( per.personID = id.personID) INNER JOIN dbo.Calendar cal ON ( en.calendarID = cal.calendarID);