x

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
more ▼

asked Sep 11, 2012 at 08:19 PM in Default

avatar image

muk
440 33 35 40

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Sep 12, 2012 at 01:06 PM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1069
x748
x660
x428

asked: Sep 11, 2012 at 08:19 PM

Seen: 1148 times

Last Updated: Sep 12, 2012 at 01:07 PM

Copyright 2016 Redgate Software. Privacy Policy