how to combine two records into one in query

Question

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

Answer 1

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

how to combine two records into one in query

Follow this question

Related Questions