Code for Measure U14


Course Completion
This is a Successful Course Completion Rate calculation. It compares the number of all SSCH hours earned to all SSCH attempted in non-remedial courses.  The Successful Course Completion Rate is expressed as a percentage and changes over time are expressed as a difference in percentage points. ( The successful grades include A, B, C, D, S, K [CR], and R.)

SQL statements are in RED.


SQL Code for all SSCH.

SELECT c.academic_year,
f.inst_type,
f.school_abbr,
SUM(c.credit_hours * c.enrollment) AS ssch
FROM course_table c
JOIN fice_table f
ON c.fice_code = f.fice_code
WHERE f.inst_type IN ('1','2')
AND c.academic_year IN ('2006', '2007', '2008','2009', '2010')
AND c.course_level IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', 'A')
AND UPPER(c.tech_inst_funding) NOT IN ('O', 'C', 'M', 'T')
GROUP BY c.academic_year, f.inst_type, f.school_abbr
ORDER BY c.academic_year, f.inst_type, f.school_abbr;


SQL Code for all completed SSCH.

SELECT c.academic_year,
f.inst_type,
f.school_abbr,
(c.credit_hours * COUNT(r.ssn_id)) AS ssch
FROM course_table c
JOIN fice_table f
ON c.fice_code = f.fice_code
JOIN registration_table r
ON c.fice_code = r.fice_code
AND c.academic_year = r.academic_year
AND c.term = r.term
AND c.sequence = r.sequence
AND c.level_sequence = r.level_sequence
WHERE f.inst_type IN ('1','2')
AND c.academic_year IN ('2006', '2007', '2008','2009', '2010')
AND c.course_level IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', 'A')
AND UPPER(c.tech_inst_funding) NOT IN ('O', 'C', 'M', 'T')
AND UPPER(r.end_of_term) IN ('A', 'B', 'C', 'D', 'S', 'K', 'R', '1')
GROUP BY c.academic_year, f.inst_type, f.school_abbr, c.credit_hours
ORDER BY c.academic_year, f.inst_type, f.school_abbr, c.credit_hours;

The results for the completed SSCH will be grouped by credit hours and will need to be summed to produce an overall total of completed SSCH for the institution.