0

I'm extracting student data who have completed a list of courses for degree requirements. One of the courses on the list is equivalent to another course, so if a student completes both equivalent courses, it can only be counted once towards a degree. I need to extract data on students who completed the list of courses, while filtering for just one of the equivalent courses.

Where am I going wrong?

I've tried different OR and AND NOT clauses but I can't seem to get the result that I need

use coll18_live

select ENR_STUDENT_ID, ENR_TERM, CRS_NAME, ENR_GRADE
from dbo.CA320_ENROLLMENT_VIEW_N03
WHERE ENR_CENSUS_REG_FLAG = 'Y'
and ENR_TERM in ('14/FA', '15/SP')
and not (CRS_NAME = 'BUSI-105' and CRS_NAME = 'ENGL-120')
and CRS_NAME in ('ACCT-120', 'ACCT-125', 'BUSI-100', 'BUSI-103', 'BUSI-105', 'ENGL-120')

I expect the output to show students who completed ACCT-120, ACCT-12, BUSI-100, BUSI-103, and BUSI-105 or ENGL-120 (but not both BUSI-105 or ENGL-120)

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
kroniclesking
  • 1
  • 1

1 Answers1

0

I think you want aggregating with a having clause. You cannot do this with a WHERE, because the information you want is (apparently) in different rows:

select ENR_STUDENT_ID
from dbo.CA320_ENROLLMENT_VIEW_N03
where ENR_CENSUS_REG_FLAG = 'Y' AND
      ENR_TERM in ('14/FA', '15/SP')
group by ENR_STUDENT_ID
having sum(case when CRS_NAME in ('ACCT-120', 'ACCT-125', 'BUSI-100', 'BUSI-103') then 1 else 0 end) = 4 and
       sum(case when CRS_NAME in ('BUSI-105', 'ENGL-120') then 1 else 0 end) > 0;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the help Gordon. The data are in different rows. ENR_STUDENT_ID, ENR_TERM, and CRS_NAME are columns. I'm hoping for the output to show students who completed any number of these courses. In the end I'll determine who completed all of the course requirements, and those who are missing one, two, or three of the courses. – kroniclesking Apr 09 '19 at 18:04
  • Forgive my answers as I'm still relatively new at SQL and coding – kroniclesking Apr 09 '19 at 18:07
  • @kroniclesking . . . Your question includes "I need to extract data on students who completed the list of courses." To me, at least, this suggests that you want all the courses (subject to the duplication requirement, of course). – Gordon Linoff Apr 09 '19 at 18:15
  • Exactly. I would like to know what students (ENR_STUDENT_ID) have completed any number or combination of the courses as a list of rows, then I'll paste it into Excel and run a pivot to see what students have completed all of the courses or have completed all minus 1 or 2. Would I need to include a HAVING clause for BUSI-105 and ENGL-120, and WHERE clause for the rest? – kroniclesking Apr 09 '19 at 18:22