[SQL] SQL Case Statement Evaluating Same Column Twice

I need assistance with a case statement. I am trying assign a code depending on courses students take. The problem comes when students take two courses. In this case, they should be assigned a code and only one record should be returned for them, not two. I explain what the results should look like below.

--Create table:
CREATE TABLE [dbo].[Courses](
[TermCode] [varchar](5) NULL,
[StudentID] [varchar](9) NULL,
[CourseCode] [varchar](6) NULL
) ON [PRIMARY]
GO

--Insert Records:
Insert into Courses (TermCode, StudentID, CourseCode)
values ('20211', '123456789', '100001');

Insert into Courses (TermCode, StudentID, CourseCode)
values ('20211', '234567890', '400001');

Insert into Courses (TermCode, StudentID, CourseCode)
values ('20211', '345678901', 'BH0001');

Insert into Courses (TermCode, StudentID, CourseCode)
values ('20211', '456789012', 'BH0002');

Insert into Courses (TermCode, StudentID, CourseCode)
values ('20211', '567890123', '100001');

Insert into Courses (TermCode, StudentID, CourseCode)
values ('20211', '567890123', 'BH0001');

Insert into Courses (TermCode, StudentID, CourseCode)
values ('20211', '678901234', '400001');

Insert into Courses (TermCode, StudentID, CourseCode)
values ('20211', '678901234', 'BH0002');


-- What I have so far:

select StudentID,
case
when CourseCode = '100001' then 'AAN1'
when CourseCode = '400001' then 'AAN4'
when CourseCode = 'BH0001' then 'BABH'
when CourseCode = 'BH0002' then 'BPBH'
when (CourseCode = '100001' and CourseCode = 'BH0001') then 'BH01'
when (CourseCode = '400001' and CourseCode = 'BH0002') then 'BH04'
end as CustomCode
from Courses


-- Results I'm getting:

| StudentID | CustomCode |
| --------- | ---------- |
| 123456789 | AAN1 |
| 234567890 | AAN4 |
| 345678901 | BABH |
| 456789012 | BPBH |
| 567890123 | AAN1 |
| 567890123 | BABH |
| 678901234 | AAN4 |
| 678901234 | BPBH |


Results that I need:

| StudentID | CustomCode |
| --------- | ---------- |
| 123456789 | AAN1 |
| 234567890 | AAN4 |
| 345678901 | BABH |
| 456789012 | BPBH |
| 567890123 | BH01 |
| 678901234 | BH04 |


The problem with this is that it evaluates the single courses just fine. The lines where it is checking for two courses isn't working. StudentID 567890123 and 678901234 should return one record, not two. I'm not sure what I am doing wrong.

Any assistance will be greatly appreciated. Thank you in advance!

Continue reading...