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

muk gravatar image

muk
400 30 33 35

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x985
x581
x547
x369

asked: Sep 11, 2012 at 08:19 PM

Seen: 868 times

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