Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    8

    Unanswered: GET MIN TERM in SQL CODE

    Please help....I have the below code. I'm trying to remove the duplicates by getting the min term for each student.

    Code:
    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
    FROM         dbo.STUDENT_TERMS 
                  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,
              dbo.TERMS.TERM_START_DATE, dbo.TERMS.TERM_END_DATE
    order by dbo.STUDENT_TERMS.STTR_STUDENT
    Basically, for each person, their min term is 15/FA
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  2. #2
    Join Date
    Jan 2013
    Posts
    353
    Provided Answers: 1
    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?

  3. #3
    Join Date
    Nov 2012
    Posts
    8
    Thank you for your assistance, I fixed my code and it's now returning the results expected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •