Mysql – How to translate one column in SQL with a given condition

There are certainly ways to write this more concisely, without the redundancy, but when rules get complex I’m partial to breaking them up so clear names can be given. That way the query reads more like natural language.

Assumptions:

1.) Table_A will always have only the current term (2024). Logic will still work otherwise, but the DISTINCT() might need fiddled with.

2.) Table_A.credit will always be the same per student year. Gist would still work otherwise, but plain old DISTINCT() won’t.

3.) A non passing grade is indicated by Null, ”, D, or F.

Rationale : ClassMarks contains all the data you need to assign the rules. So get the lastest year they were enrolled in each class, latest year they completed each class, then once that is at Student grain you can simply join and derive the rule based on whatever the Student.Term_Year is at a given point.

Query:

Fiddle: https://dbfiddle.uk/XHscF2Qn

CREATE TABLE IF NOT EXISTS Students (
    TERM INT,
    ID INT,
    LAST_NAME VARCHAR(255),
    FIRST_NAME VARCHAR(255),
    DEGREE VARCHAR(50),
    COURSE INT,
    COURSE_CODE VARCHAR(50),
    CREDIT VARCHAR(1),
    YEAR VARCHAR(1)
);

INSERT INTO Students (TERM, ID, LAST_NAME, FIRST_NAME, DEGREE, COURSE, COURSE_CODE, CREDIT, YEAR) VALUES
(2024, 0011, 'Richards', 'Diana', 'PHD', 3000, '030011', 'S', 'N'),
(2024, 0011, 'Richards', 'Diana', 'PHD', 3011, '030111', 'S', 'N'),
(2024, 0011, 'Richards', 'Diana', 'PHD', 3015, '030015', 'S', 'N'),
(2024, 0012, 'Smith', 'Alex', 'PHD', 2011, '020012', 'S', 'N'),
(2024, 0012, 'Smith', 'Alex', 'PHD', 2001, '020013', 'S', 'N'),
(2024, 0012, 'Smith', 'Alex', 'PHD', 2021, '020014', 'S', 'N'),
(2024, 0012, 'Smith', 'Alex', 'PHD', 2023, '020015', 'S', 'N'),
(2024, 0013, 'Thompson', 'Robert', 'PHD', 1009, '010011', 'S', 'N'),
(2024, 0013, 'Thompson', 'Robert', 'PHD', 1029, '010020', 'S', 'N');

CREATE TABLE IF NOT EXISTS ClassMarks (
    TERM_YEAR INT,
    STUDENT_ID INT,
    CLASS_CODE VARCHAR(50),
    CLASS VARCHAR(255),
    CLASS_MARK VARCHAR(2)
);

INSERT INTO ClassMarks (TERM_YEAR, STUDENT_ID, CLASS_CODE, CLASS, CLASS_MARK) VALUES
(2020, 0012, '010011', '1001', 'A'),
(2020, 0011, '010021', '1009', 'B-'),
(2021, 0011, '010020', '1029', 'B+'),
(2021, 0012, '010001', '1011', 'B'),
(2022, 0011, '020012', '2011', 'C+'),
(2022, 0012, '010021', '1009', 'A-'),
(2022, 0012, '010020', '1029', 'A'),
(2023, 0011, '020014', '2021', 'A+'),
(2024, 0012, '020012', '2011', ''),
(2024, 0012, '020013', '2001', ''),
(2024, 0012, '020014', '2021', ''),
(2024, 0012, '020015', '2023', ''),
(2024, 0013, '010011', '1009', ''),
(2024, 0013, '010020', '1029', '');


with year_rules as (
    select Student_ID,
           max(case when class = 1009 then term_year end) as latest_enrollment_in_1009,
           max(case when class = 1029 then term_year end) as latest_enrollment_in_1029,
           max(case when class = 1009 
                     and class_mark not in ('','D','F') -- whatever passing is
                then term_year 
               end) as latest_pass_in_1009,
           max(case when class = 1029 
                     and class_mark not in ('','D','F') -- whatever passing is
                then term_year 
               end) as latest_pass_in_1029,
           max(case when class = 2011 then term_year end) as latest_enrollment_in_2011,
           max(case when class = 2021 then term_year end) as latest_enrollment_in_2021,
           max(case when class = 2011 
                     and class_mark not in ('','D','F') -- whatever passing is
                then term_year 
               end) as latest_pass_in_2011,
           max(case when class = 2021
                     and class_mark not in ('','D','F') -- whatever passing is
                then term_year 
               end) as latest_pass_in_2021
      from ClassMarks
     group
        by Student_ID)
select distinct
       s.term,
       s.id,
       s.last_name,
       s.first_name,
       s.degree,
       s.credit,
       case when latest_enrollment_in_1009 = s.term
                    and latest_enrollment_in_1029 = s.term then 1
                   when latest_enrollment_in_2011 = s.term 
                    and latest_enrollment_in_2021 = s.term 
                    and greatest(latest_pass_in_1009, latest_pass_in_1029) < s.term
                        then 2
                   when greatest(latest_pass_in_1009, 
                                 latest_pass_in_1029,
                                 latest_pass_in_2011,
                                 latest_pass_in_2021) < s.term then 3
               end as derived_year
  from Students s
 inner
  join year_rules yr
    on s.id = yr.student_id;