asked
how to combine two records into one in query
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
A simple way to do this would be to use 2 SELECT statements that query for graduate students and undergraduates and then use UNION - not UNION ALL. The UNION will have the effect of removing duplicates for you. This may not be the fastest way to filter the records but I havent time to study your query in details and then mock up schemas and data to test other options currently. eg IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL DROP TABLE #Temp1 GO CREATE TABLE #Temp1 ( ID INT , StuName VARCHAR(10) , GradStatus BIT ) go INSERT [#Temp1] ( [ID], [StuName], [GradStatus] ) VALUES ( 1, -- ID - int 'Bill', -- StuName - varchar(10) 1-- GradStatus - bit ), ( 2, -- ID - int 'Bill', -- StuName - varchar(10) 0 -- GradStatus - bit ), ( 3, -- ID - int 'Barry', -- StuName - varchar(10) 1 -- GradStatus - bit ), ( 4, -- ID - int 'Gary', -- StuName - varchar(10) 1 -- GradStatus - bit ) SELECT [t].[StuName] FROM [#Temp1] AS t WHERE [t].[GradStatus] = 0 UNION SELECT [t].[StuName] FROM [#Temp1] AS t WHERE [t].[GradStatus] = 1 SELECT [t].[StuName] FROM [#Temp1] AS t WHERE [t].[GradStatus] = 0 UNION ALL SELECT [t].[StuName] FROM [#Temp1] AS t WHERE [t].[GradStatus] = 1
8 People are following this question.
