how to combine two records into one in query

0

Hello all!

I have this query to get the total enrollment credits for each department at my organization based on student type (matrix report in SSRS). The problem is that in STUDENT_ENROLLMENT_DETAIL_VIEW, the joint degree students are listed twice for each course (one in the undergraduate section and one in the graduate section. This identifiable by the SECTION_NAME: i.e.: a joint student is signed up for JD-880-JD and JD-880-ITP; The problem is that for my purposes I only need to count them one time. How can I modify this query to do so? Thanks in advance.

Select
STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID,
STUDENT_ACAD_PROGRAMS_VIEW.STP_DEPT,
Case
When STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_CURRENT_TYPE Is Null Then
SubString(STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM,
dbo.fnNthIndex(STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM, '.', 1) + 1,
Len(STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM))
Else STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_CURRENT_TYPE
End As STUDENT_CURRENT_TYPE,
STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_TERM,
SUM(STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CREDITS) as [Enroll Credits],
PERSON.FIRST_NAME,
PERSON.LAST_NAME
From
STUDENT_ACAD_PROGRAMS_VIEW Left Join
STUDENT_ENROLLMENT_DETAIL_VIEW On STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID =
STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_ID Left Join
PERSON On STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID = PERSON.ID
Where
STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS Like 'Active' And
STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM Not Like '%CERT%' And
STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_TERM = '2012FA' And
(STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CURRENT_STATUS = 'Add' Or
STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CURRENT_STATUS = 'New') And
STUDENT_ENROLLMENT_DETAIL_VIEW.SECTION_SUBJECT Like STUDENT_ACAD_PROGRAMS_VIEW.STP_DEPT
Group By
STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, STUDENT_ACAD_PROGRAMS_VIEW.STP_DEPT,
STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_TERM, PERSON.FIRST_NAME,
PERSON.LAST_NAME, STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_CURRENT_TYPE,
STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM
Order By
STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID