-2

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);
2

I always break queries down first.

  1. I want the max date for each personid
  2. join other tables off step 1 subquery, otherwise you'll get duplicates for multiple dates.

Step 1:

select personid, max(startdate)
from dbo.posEligibility group by personid 

Step 2: now join the remaining tables for detailed data on the above subquery and get rid of the group by

SELECT
per.personID,
id.lastName,
id.middleName,
id.firstName,
CONVERT(VARCHAR(10), pos1.startDate, 101) AS StartDate,
cal.name,
en.grade,
pos1.personID   
FROM
    dbo.POSEligibility pos1
INNER JOIN
(
    select personid, max(startdate) as startdate
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);
  • After posting, I realize, this is probably the correct answer assuming it meets the OP requirements. The only thing that might need to be added is in the main select statement, add pos1.eligibility. That should give you the latest eligibility for each personId/max(startdate) combination. – Shooter McGavin Oct 28 '16 at 20:28
  • Ok I see now, I wasn't selecting on the personID but instead the whole startdate. I'm getting a few odd duplicates. One shows up 3 times with the same startdate of 7/26/2016. But looking directly at the dbo.posEligibility table the only two dates are 7/25/2015 and 7/26/2016. Also not sure if I conveyed that the dbo.person table is what has the unique non duplicate personIDs our phonebook of people if you will. – Joshua Barrett Oct 28 '16 at 22:03
  • Sorry my mistake the eligibility is a yearly event. Each year people will be added to it with their same personID in addition a new startdate there is an enddate that is simple set to the day before the new startdate for that year. Ex. ID3, Date 7/26/2015 Then ID3, Date 7/26/2016. So if someone stayed 10 years there could be 10 entries but im only after the latest one. – Joshua Barrett Oct 28 '16 at 22:16
  • Thanks for all the help so far, sorry I don't have enough rep to change your guys number but I did click up vote on everyones help. Still fighting that duplicate issue I added a WHERE pos1.personID = '13' seeing how that is my duplicate person. I'm going to read up more on SQL. I know that this is probably being caused one of my join statements. – Joshua Barrett Oct 31 '16 at 13:02
0

in your subquery change the [Date] alias to [StartDate] otherwise what you have there won't compile when it gets to the following ON Statement:

INNER JOIN
(
    SELECT MAX(startDate) AS [DATE], eligibility
    FROM dbo.POSEligibility
    GROUP BY startDate, eligibility
) pos2
    ON pos1.personID = pos2.personID
    AND pos1.startDate = pos2.startDate

Change to this:

INNER JOIN
(
    SELECT MAX(startDate) AS [startDate], eligibility
    FROM dbo.POSEligibility
    GROUP BY startDate, eligibility
) pos2
    ON pos1.personID = pos2.personID
    AND pos1.startDate = pos2.startDate
  • This doesn't address the error where the sub-query is missing the PersonID. The subquery should have the personID in both the select and the group by clause. – Shooter McGavin Oct 28 '16 at 20:21
  • @ShooterMcGavin You're right I missed that bit. – Mitchell Crimi Oct 28 '16 at 20:22
  • No worries my friend. We're all on the same team here. :) – Shooter McGavin Oct 28 '16 at 20:23
0

One problem I see with your sub-query is that you are trying to join using the personID but you aren't providing that value in the sub-query. Also, I would assume the purpose of the subquery is to reduce the number of results from the table to only the latest entry for each PersonID and eligibility. That being the case, you should really add that to the join as well.

INNER JOIN
(
    SELECT MAX(startDate) AS [DATE], PersonID
    FROM dbo.POSEligibility
    GROUP BY PersonID
) pos2
    ON pos1.personID = pos2.personID
    AND pos1.startDate = pos2.Date  
  • BTW, I'm not clear on what you are actually trying to return from the primary table. I see that you are trying to use the INNER JOIN sub-query to help limit(filter) the results of the main table. If you are trying to return the latest eligibility value from the table for each personID, you will need to remove eligibilty from the sub-query(pos2) and instead get it from pos1. – Shooter McGavin Oct 28 '16 at 20:22
  • You are correct, i'm trying the pull the latest startdate value for each personID even when there are duplicate personIDs. Each year their personIDs are added with a new startdate as the old ones are enddated to the day before the new startdate. – Joshua Barrett Oct 28 '16 at 22:47
  • Ok. So take out the references to eligibility in the sub-query then as well as the join for the subquery. Then add it back in to the top select using pos1 as the source. Try and remember, the subquery with an inner join is used to filter the results of the source table. By only returning a max date and the personID and then joining back to it, you are saying you only want a single record for each personID. I will edit my post to make it more clear. – Shooter McGavin Oct 28 '16 at 22:57

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.