CPE Data Guidelines - Enrollment Metric Validation
Total Enrollment:
Definition: An unduplicated count of enrolled students, categorized by student level—Undergraduate (UG) or Graduate (G)—based on student classification codes.
Student Level Classification:
- Undergraduate (UG): Student classification code is one of the following:
'01', '02', '03', '04', '05', '13', '14', '19', '20', '30' - Graduate (G): All other classification codes.
SQL Logic to Determine Student Level: UnderGrad_Grad = CASE WHEN a.classification IN ('01','02','03','04','05','13','14','19','20','30') THEN 'UG' ELSE 'G' END
Metric SQL:
-- Drop temporary tables if they already exist
IF OBJECT_ID('tempdb..#Enr_4A') IS NOT NULL DROP TABLE #Enr_4A;
IF OBJECT_ID('tempdb..#Final_4A') IS NOT NULL DROP TABLE #Final_4A;
-- Step 1: Identify undergraduate enrollment based on classification
SELECT DISTINCT
a.Sem,
a.fice,
a.inst_id,
b.sector_code,
b.inst_description
INTO #Enr_4A
FROM edits.type1_student AS a
INNER JOIN X.lkup_institution AS b
ON a.fice = b.inst_code
WHERE a.submission_id = @submission_id
AND a.classification IN ('01','02','03','04','05','13','14','19','20','30');
-- Step 2: Aggregate and format for metric output
SELECT
B.metric_type_code,
B.metric_line_code,
Submission_id = @submission_id,
A.fice,
A.inst_description,
Metric_number = RIGHT(B.metric_line_code, 2),
B.metric_line_label,
A.Metric_Value,
NULL AS numerator_value,
NULL AS denominator_value,
GETDATE() AS Created_Date
INTO #Final_4A
FROM lkup_metric_lineitem B
INNER JOIN (
SELECT
metric_line_code = 'UNDERGRADUATE_ENROLLMENT_4A',
fice,
inst_description,
Metric_Value = COUNT(*)
FROM #Enr_4A
GROUP BY fice, inst_description
) A
ON B.metric_line_code = A.metric_line_code
ORDER BY inst_description, fice;
First to Second Year Retention
Definition: The percentage of first-time, degree- or credential-seeking students who return to the same institution the following fall to continue their studies. This is calculated for all students and disaggregated by low-income and URM (Underrepresented Minority) status.
Operationalization: This metric measures student persistence by identifying students who re-enroll in the fall term following their first fall or summer enrollment.
- For four-year institutions, the cohort includes first-time, full-time, bachelor's (or equivalent) degree-seeking undergraduates from the previous summer/fall term who are enrolled again in the current fall.
- For KCTCS institutions, the cohort includes first-time, degree- or credential-seeking students from the
previous summer/fall term who either:
- Re-enroll in the current fall term, or
- Successfully complete a credential during the academic year.
Federal exclusions are applied at the end of the period based on consultation with institutional staff.
Performance Funding Metric: No
Cohort Logic: First-year cohort is defined as: GRS IN ('Y', 'P')
Metric SQL Logic
General Retention Check
If a student appears in the current Type 1 Enrollment submission, they are marked as retained:
Logic:
SET C.Retained = 'Y'
FROM #Cohort_Retain C
INNER JOIN Edits.type1_Student D
ON C.institutional_id = D.inst_id AND C.fice = D.fice
WHERE D.submission_id = @submission_id
KCTCS-Specific Logic
- Degree Completion During the Academic Year:
Logic:
SET C.Retained = 'Y'
FROM #Cohort_Retain C
INNER JOIN dbo.Degree D
ON C.institutional_id = D.inst_id AND C.fice = D.fice
WHERE @inst = 'KCTCS'
AND D.Academic_Year = CAST(SUBSTRING(@submission_year, 3, 2) - 1 AS VARCHAR(2))
+ CAST(SUBSTRING(@submission_year, 3, 2) AS VARCHAR(2))
- Degree Completion During the Current Semester:
Logic:
SET C.Retained = 'Y'
FROM #Cohort_Retain C
INNER JOIN Edits.type5_degrees D
ON C.institutional_id = D.inst_id AND C.fice = D.fice
WHERE @inst = 'KCTCS'
AND D.sem = CAST(RTRIM(@submission_year) AS VARCHAR)
+ CAST(SUBSTRING(@submission_year, 3, 2) + 1 AS VARCHAR)
First-to-Second Year Retention – Low-Income Students
Definition: This metric represents the percentage of first-time, degree- or credential-seeking students who return to the institution in the following fall term and are identified as low-income. It is a disaggregated subset of the total first-to-second year retention metric.
Low-Income Definition: Students are classified as low-income if they received a Pell Grant in the Type A financial aid submission.
- Logic: After a student is flagged as retained, then
WHEN TypeA.pell_grants > 0 THEN low_income = 'Y'
Operationalization:
- The cohort is derived from the total first-year cohort (GRS IN ('Y','P')).
- Retention is determined by fall re-enrollment or degree/credential completion (as defined in the general retention metric).
- Only students flagged as low-income are included in this calculation.
Performance Funding Metric: No
Last Updated: 7/1/2025