Kentucky Council on Postsecondary Education

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:

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.

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

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

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

Operationalization:

Performance Funding Metric: No

Last Updated: 7/1/2025