I am trying to recreate the cascading sum logic of our Student Information System (SIS) to build a tracking report that will aggregate all graduation requirements in one place.

Currently courses can have between 1 and 5 subjects associated with them, each subject has a cap or minimum requirement to be met, and if that total is met then credit that would feed into that subject would cascade to subject2, subject 3, etc. Then finally into Electives if all subject “buckets” are full.

Is this possible with SQL, as I would prefer to run this at the SQL level?

I had tried setting up a recursive CTE to calculate this but a solution has eluded me. Any help or suggestions would be very appreciated.

Example Tables:

credit req table

student course history sample

CREATE TABLE StudentCourses (
    stuID INT,
    crsID VARCHAR(10),
    crsTitle VARCHAR(50),
    creditEarned DECIMAL(3,3),
    subject1 VARCHAR(50),
    subject2 VARCHAR(50),
    subject3 VARCHAR(50),
    subject4 VARCHAR(50),
    subject5 VARCHAR(50)
);

INSERT INTO StudentCourses (stuID, crsID, crsTitle, creditEarned, subject1, subject2, subject3, subject4, subject5)
VALUES
(12345, 'TRMSC', 'SPANISH I', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '7732', 'CHEMISTRY', 0.500, 'SCIENCE', NULL, NULL, NULL, NULL),
(12345, '2371', 'AP ENG LIT/COMP', 0.500, 'ENGLISH', NULL, NULL, NULL, NULL),
(12345, '3611', 'PAP INT MTH III', 0.500, 'MATHEMATICS', NULL, NULL, NULL, NULL),
(12345, '8031', 'PAP WA ST HIST', 0.500, 'WASH ST HISTORY', NULL, NULL, NULL, NULL),
(12345, '7772', 'PHYSICS', 0.500, 'SCIENCE', NULL, NULL, NULL, NULL),
(12345, '2242', 'PAP ENGLISH 10', 0.500, 'ENGLISH', NULL, NULL, NULL, NULL),
(12345, '8242', 'AP US HISTORY', 0.500, 'U.S. HISTORY', NULL, NULL, NULL, NULL),
(12345, '0262', 'POTTERY I', 0.500, 'ART', NULL, NULL, NULL, NULL),
(12345, '2372', 'AP ENG LIT/COMP', 0.500, 'ENGLISH', NULL, NULL, NULL, NULL),
(12345, '1521', 'SPANISH 2', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '7641', 'ZOOLOGY', 0.500, 'SCIENCE', NULL, NULL, NULL, NULL),
(12345, '4281', 'BUSINESS LAW', 0.500, 'CAREER & TECH', NULL, NULL, NULL, NULL),
(12345, '1522', 'SPANISH 2', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '4291', 'AD BUSINESS LAW', 0.500, 'CAREER & TECH', 'CAREER & TECH', NULL, NULL, NULL),
(12345, '9872', 'STUD GOVT', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '7642', 'ZOOLOGY', 0.500, 'SCIENCE', NULL, NULL, NULL, NULL),
(12345, '9871', 'STUD GOVT', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, 'TRMTH', 'ALGEBRA', 1.000, 'MATHEMATICS', NULL, NULL, NULL, NULL),
(12345, '2171', 'PAP ENGLISH 9', 0.500, 'ENGLISH', NULL, NULL, NULL, NULL),
(12345, '3821', 'AP CALCULUS', 0.500, 'MATHEMATICS', NULL, NULL, NULL, NULL),
(12345, '4201', 'TECH TOOL FYF', 0.500, 'CAREER & TECH', NULL, NULL, NULL, NULL),
(12345, '0261', 'POTTERY I', 0.500, 'ART', NULL, NULL, NULL, NULL),
(12345, '6252V', 'HEALTH WELLNESS', 0.500, 'HEALTH', NULL, NULL, NULL, NULL),
(12345, '9603H', 'HSPE SCIENCE', 0.250, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '6291', 'PSYCH/HLTH ISS', 0.500, 'CAREER & TECH', NULL, NULL, NULL, NULL),
(12345, '2592', 'SR COMP/CON CUL', 0.500, 'ENGLISH', NULL, NULL, NULL, NULL),
(12345, '7771', 'PHYSICS', 0.500, 'SCIENCE', NULL, NULL, NULL, NULL),
(12345, '2591', 'SR COMP/CON CUL', 0.500, 'ENGLISH', 'CAREER & TECH', NULL, NULL, NULL),
(12345, '9871', 'STUD GOVT', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '3822', 'AP CALCULUS', 0.500, 'MATHEMATICS', NULL, NULL, NULL, NULL),
(12345, '9871', 'STUD GOVT', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '7731', 'CHEMISTRY', 0.500, 'SCIENCE', 'CAREER & TECH', NULL, NULL, NULL),
(12345, '8072', 'PAP WORLD STUDY', 0.500, 'CONT WRLD PROB', NULL, NULL, NULL, NULL),
(12345, '9872', 'STUD GOVT', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '6202', 'AEROBIC FITNESS', 0.500, 'PHYSICAL ED', NULL, NULL, NULL, NULL),
(12345, '9601H', 'HSPE READING', 0.250, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '3602', 'INTEG MATH III', 0.500, 'MATHEMATICS', NULL, NULL, NULL, NULL),
(12345, 'TRMSC', 'SPANISH I', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '3831', 'CALCULUS II', 0.500, 'MATHEMATICS', NULL, NULL, NULL, NULL),
(12345, '3832', 'CALCULUS II', 0.500, 'MATHEMATICS', NULL, NULL, NULL, NULL),
(12345, '8441', 'AP COMP GOV/POL', 0.500, 'CIVICS', NULL, NULL, NULL, NULL),
(12345, '9872', 'STUD GOVT', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '6292', 'PSYCH/HLTH ISS', 0.500, 'CAREER & TECH', NULL, NULL, NULL, NULL),
(12345, '9872', 'STUD GOVT', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '6201', 'AEROBIC FITNESS', 0.500, 'PHYSICAL ED', NULL, NULL, NULL, NULL),
(12345, '2172', 'PAP ENGLISH 9', 0.500, 'ENGLISH', NULL, NULL, NULL, NULL),
(12345, '9604H', 'HSPE WRITING', 0.250, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, 'TRMTH', 'GEOMETRY', 1.000, 'MATHEMATICS', NULL, NULL, NULL, NULL),
(12345, '3701', 'PAP INT MATH IV', 0.500, 'MATHEMATICS', NULL, NULL, NULL, NULL),
(12345, '3702', 'PAP INT MATH IV', 0.500, 'MATHEMATICS', NULL, NULL, NULL, NULL),
(12345, '8241', 'AP US HISTORY', 0.500, 'U.S. HISTORY', NULL, NULL, NULL, NULL),
(12345, '8511', 'CUL PRJ/HS BYND', 0.500, 'SENIOR PROJECT', NULL, NULL, NULL, NULL),
(12345, '9871', 'STUD GOVT', 0.500, 'MISCELLANEOUS', NULL, NULL, NULL, NULL),
(12345, '8442', 'AP COMP GOV/POL', 0.500, 'CONT WRLD PROB', NULL, NULL, NULL, NULL);

Expected Outcome (math is not accounting for cascade)