The SQL syntax to create the institutional, cumulative, relative-scale graduation measures in the PTC is below (this version is for fall entrants, there is an analogous version for spring entrants that is not shown for space purposes).
The following variables are used in the syntax
- CDyy_DEGREE_EARNED_LEVEL_CODE - A variable that indicates in a given year since entry what degree a student earned (if any)
- FA01_DEGREE_PURSUED_LEVEL_CODE - A variable that indicates the degree a student was pursuing at entry
- CDyy_COLLEGE_ID - A variable that indicates the college at which a student earned a degree in a given year
- FA01_COLLEGE_ID - A variable that indicates the college at which a student entered CUNY
- FA01_HEADCOUNT - A convenience variable that allows us to code the variable as 0/1 based on their first year enrollment
There are two things to note about the calculations in the syntax:
- The comparison of CDyy_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE, means that a student is coded as 1 if they received the degree they were initially pursuing or higher.
- The comparison on college_id, (CDyy_COLLEGE_ID = FA01_COLLEGE_ID OR CDyy_COLLEGE_ID = '21'), is what makes this an institutional graduation measure (as opposed to a system measure). However, for the purposes of this variable, earning a degree from the Graduate Center (where our online baccalaureate program is housed) is counted within the institutional graduation rate. This is to be in keeping with how we report institutional graduation to IPEDS.
(CASE WHEN
(CD01_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD01_COLLEGE_ID = FA01_COLLEGE_ID OR CD01_COLLEGE_ID = '21'))
THEN FA01_HEADCOUNT ELSE 0 END) AS GRAD_INST_YR01,
(CASE WHEN
(CD02_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD02_COLLEGE_ID = FA01_COLLEGE_ID OR CD02_COLLEGE_ID = '21'))
OR (CD01_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD01_COLLEGE_ID = FA01_COLLEGE_ID OR CD01_COLLEGE_ID = '21'))
THEN FA01_HEADCOUNT ELSE 0 END) AS GRAD_INST_YR02,
(CASE WHEN
(CD03_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD03_COLLEGE_ID = FA01_COLLEGE_ID OR CD03_COLLEGE_ID = '21'))
OR (CD02_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD02_COLLEGE_ID = FA01_COLLEGE_ID OR CD02_COLLEGE_ID = '21'))
OR (CD01_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD01_COLLEGE_ID = FA01_COLLEGE_ID OR CD01_COLLEGE_ID = '21'))
THEN FA01_HEADCOUNT ELSE 0 END) AS GRAD_INST_YR03,
(CASE WHEN
(CD04_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD04_COLLEGE_ID = FA01_COLLEGE_ID OR CD04_COLLEGE_ID = '21'))
OR (CD03_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD03_COLLEGE_ID = FA01_COLLEGE_ID OR CD03_COLLEGE_ID = '21'))
OR (CD02_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD02_COLLEGE_ID = FA01_COLLEGE_ID OR CD02_COLLEGE_ID = '21'))
OR (CD01_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD01_COLLEGE_ID = FA01_COLLEGE_ID OR CD01_COLLEGE_ID = '21'))
THEN FA01_HEADCOUNT ELSE 0 END) AS GRAD_INST_YR04,
(CASE WHEN
(CD05_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD05_COLLEGE_ID = FA01_COLLEGE_ID OR CD05_COLLEGE_ID = '21'))
OR (CD04_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD04_COLLEGE_ID = FA01_COLLEGE_ID OR CD04_COLLEGE_ID = '21'))
OR (CD03_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD03_COLLEGE_ID = FA01_COLLEGE_ID OR CD03_COLLEGE_ID = '21'))
OR (CD02_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD02_COLLEGE_ID = FA01_COLLEGE_ID OR CD02_COLLEGE_ID = '21'))
OR (CD01_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD01_COLLEGE_ID = FA01_COLLEGE_ID OR CD01_COLLEGE_ID = '21'))
THEN FA01_HEADCOUNT ELSE 0 END) AS GRAD_INST_YR05,
(CASE WHEN
(CD06_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD06_COLLEGE_ID = FA01_COLLEGE_ID OR CD06_COLLEGE_ID = '21'))
OR (CD05_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD05_COLLEGE_ID = FA01_COLLEGE_ID OR CD05_COLLEGE_ID = '21'))
OR (CD04_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD04_COLLEGE_ID = FA01_COLLEGE_ID OR CD04_COLLEGE_ID = '21'))
OR (CD03_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD03_COLLEGE_ID = FA01_COLLEGE_ID OR CD03_COLLEGE_ID = '21'))
OR (CD02_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD02_COLLEGE_ID = FA01_COLLEGE_ID OR CD02_COLLEGE_ID = '21'))
OR (CD01_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD01_COLLEGE_ID = FA01_COLLEGE_ID OR CD01_COLLEGE_ID = '21'))
THEN FA01_HEADCOUNT ELSE 0 END) AS GRAD_INST_YR06,
(CASE WHEN
(CD07_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD07_COLLEGE_ID = FA01_COLLEGE_ID OR CD07_COLLEGE_ID = '21'))
OR (CD06_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD06_COLLEGE_ID = FA01_COLLEGE_ID OR CD06_COLLEGE_ID = '21'))
OR (CD05_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD05_COLLEGE_ID = FA01_COLLEGE_ID OR CD05_COLLEGE_ID = '21'))
OR (CD04_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD04_COLLEGE_ID = FA01_COLLEGE_ID OR CD04_COLLEGE_ID = '21'))
OR (CD03_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD03_COLLEGE_ID = FA01_COLLEGE_ID OR CD03_COLLEGE_ID = '21'))
OR (CD02_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD02_COLLEGE_ID = FA01_COLLEGE_ID OR CD02_COLLEGE_ID = '21'))
OR (CD01_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD01_COLLEGE_ID = FA01_COLLEGE_ID OR CD01_COLLEGE_ID = '21'))
THEN FA01_HEADCOUNT ELSE 0 END) AS GRAD_INST_YR07,
(CASE WHEN
(CD08_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD08_COLLEGE_ID = FA01_COLLEGE_ID OR CD08_COLLEGE_ID = '21')
OR (CD07_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD07_COLLEGE_ID = FA01_COLLEGE_ID OR CD07_COLLEGE_ID = '21'))
OR (CD06_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD06_COLLEGE_ID = FA01_COLLEGE_ID OR CD06_COLLEGE_ID = '21'))
OR (CD05_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD05_COLLEGE_ID = FA01_COLLEGE_ID OR CD05_COLLEGE_ID = '21'))
OR (CD04_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD04_COLLEGE_ID = FA01_COLLEGE_ID OR CD04_COLLEGE_ID = '21'))
OR (CD03_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD03_COLLEGE_ID = FA01_COLLEGE_ID OR CD03_COLLEGE_ID = '21'))
OR (CD02_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD02_COLLEGE_ID = FA01_COLLEGE_ID OR CD02_COLLEGE_ID = '21'))
OR (CD01_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD01_COLLEGE_ID = FA01_COLLEGE_ID OR CD01_COLLEGE_ID = '21'))
THEN FA01_HEADCOUNT ELSE 0 END) AS GRAD_INST_YR08,
(CASE WHEN
(CD09_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD09_COLLEGE_ID = FA01_COLLEGE_ID OR CD09_COLLEGE_ID = '21'))
OR (CD08_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD08_COLLEGE_ID = FA01_COLLEGE_ID OR CD08_COLLEGE_ID = '21'))
OR (CD07_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD07_COLLEGE_ID = FA01_COLLEGE_ID OR CD07_COLLEGE_ID = '21'))
OR (CD06_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD06_COLLEGE_ID = FA01_COLLEGE_ID OR CD06_COLLEGE_ID = '21'))
OR (CD05_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD05_COLLEGE_ID = FA01_COLLEGE_ID OR CD05_COLLEGE_ID = '21'))
OR (CD04_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD04_COLLEGE_ID = FA01_COLLEGE_ID OR CD04_COLLEGE_ID = '21'))
OR (CD03_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD03_COLLEGE_ID = FA01_COLLEGE_ID OR CD03_COLLEGE_ID = '21'))
OR (CD02_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD02_COLLEGE_ID = FA01_COLLEGE_ID OR CD02_COLLEGE_ID = '21'))
OR (CD01_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD01_COLLEGE_ID = FA01_COLLEGE_ID OR CD01_COLLEGE_ID = '21'))
THEN FA01_HEADCOUNT ELSE 0 END) AS GRAD_INST_YR09,
(CASE WHEN
(CD10_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD10_COLLEGE_ID = FA01_COLLEGE_ID OR CD10_COLLEGE_ID = '21'))
OR (CD09_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD09_COLLEGE_ID = FA01_COLLEGE_ID OR CD09_COLLEGE_ID = '21'))
OR (CD08_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD08_COLLEGE_ID = FA01_COLLEGE_ID OR CD08_COLLEGE_ID = '21'))
OR (CD07_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD07_COLLEGE_ID = FA01_COLLEGE_ID OR CD07_COLLEGE_ID = '21'))
OR (CD06_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD06_COLLEGE_ID = FA01_COLLEGE_ID OR CD06_COLLEGE_ID = '21'))
OR (CD05_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD05_COLLEGE_ID = FA01_COLLEGE_ID OR CD05_COLLEGE_ID = '21'))
OR (CD04_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD04_COLLEGE_ID = FA01_COLLEGE_ID OR CD04_COLLEGE_ID = '21'))
OR (CD03_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD03_COLLEGE_ID = FA01_COLLEGE_ID OR CD03_COLLEGE_ID = '21'))
OR (CD02_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD02_COLLEGE_ID = FA01_COLLEGE_ID OR CD02_COLLEGE_ID = '21'))
OR (CD01_DEGREE_EARNED_LEVEL_CODE >= FA01_DEGREE_PURSUED_LEVEL_CODE
AND (CD01_COLLEGE_ID = FA01_COLLEGE_ID OR CD01_COLLEGE_ID = '21'))
THEN FA01_HEADCOUNT ELSE 0 END) AS GRAD_INST_YR10,