1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[SQL] SQL Case Statement Evaluating Same Column Twice

Discussão em 'Outras Linguagens' iniciado por Stack, Setembro 10, 2021.

  1. Stack

    Stack Membro Participativo

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

Compartilhe esta Página