Please help....I have the below code. I'm trying to remove the duplicates by getting the min term for each student.
SELECT dbo.STUDENT_TERMS.STTR_STUDENT AS ID,
dbo.PERSON.LAST_NAME, dbo.PERSON.FIRST_NAME, min(STUDENT_TERMS.STTR_TERM) as term,
min(TERMS.TERM_START_DATE) as MEMBERSHIP_START_DATE, min(TERMS.TERM_END_DATE) AS MEMBERSHIP_END_DATE
LEFT OUTER JOIN dbo.PERSON ON dbo.STUDENT_TERMS.STTR_STUDENT = dbo.PERSON.ID
LEFT OUTER JOIN dbo.STUDENT_TERMS_STOREDCC ON dbo.STUDENT_TERMS.STUDENT_TERMS_ID = dbo.STUDENT_TERMS_STOREDCC.STUDENT_TERMS_ID
LEFT OUTER JOIN dbo.TERMS ON dbo.STUDENT_TERMS.STTR_TERM = dbo.TERMS.TERMS_ID
WHERE STUDENT_TERMS.STTR_STUDENT NOT IN (SELECT STH_STUDENT FROM STUDENT_HIATUS
WHERE STH_CODE = 'W')
AND dbo.STUDENT_TERMS.STTR_TERM IN ('15/FA','16/SP','16/SU')
and (dbo.STUDENT_TERMS_STOREDCC.L27_STTR_ACTIVE_CRED >= 12) AND (dbo.STUDENT_TERMS.STTR_ACAD_LEVEL = 'UG')
GROUP BY dbo.STUDENT_TERMS.STTR_STUDENT, dbo.PERSON.LAST_NAME, dbo.PERSON.FIRST_NAME,
order by dbo.STUDENT_TERMS.STTR_STUDENT
Basically, for each person, their min term is 15/FA
We really need to see some DDL here. What you did post has very fundamental errors in it. For example, columns change names. This does not happen in a valid data model. Sometimes you have a "sttr_student" which violates ISO 11179 rules. The prefix is called a role, and it tells what duplicate copies the same data element do in a given statement. But "student" is the attribute and it needs to have an attribute property after it. I also have been able to figure out what a "sttr_" is.
Since there is no generic, magical, universal "id" in RDBMS, my guess would be that you meant to have a "student_id" that does follow ISO rules.
A valid schema design would not have a single Person table. This is wrong in two ways. A table models a set, therefore its name is a plural or collective noun. A table has to be a set of specific kinds of things; that means we do not have vague generic things like "persons", "things", etc.
LEFT OUTER JOINs are not automatically wrong, but they have a bad code smell. They say that the DDL (which you never showed us!) might lack DRI actions and references.
We never begin a column or table name with digits. Please read any of the ISO standards on this matter; they require the data element names begin with a Latin letter, followed by more letters or digits or a limited set of punctuation marks. This is so that we can have data dictionaries.
How many different kinds of terms do you have? That is, why is there a student term that is special and totally different from a near term which you use later. It is interesting that you seem to be concerned about students, but there is no table that models this set of entities. My guess is that what you are calling student terms is really a relationship between terms (a calendar concept) and students (a set of entities).
Finally, you group by on the term start date and term end dates, then want to get their minimum. Just think about what this means for a second.
Would you would you like to try again and post usable DDL?