Code for Measures U01-U06


Total Credentials
This is an overall headcount of all credentials awarded by the institution except that it excludes Certificates of Proficiency. This is degree levels 02, 03, 04, 05, 06, 07, 08/11/12, 09/17, 10/18, and 19. (01 is Certificates of Proficiency and it is excluded.)

The following SQL code can be used for measures U01 - U06.

SQL statements are in RED.

SQL Code

SELECT g . academic_year ,
f . inst_type ,
f . school_abbr ,
COUNT ( CASE WHEN g . degree_level IN ( '02' ) THEN g . degree_level ELSE NULL END ) AS tc ,
COUNT ( CASE WHEN g . degree_level IN ( '03' ) THEN g . degree_level ELSE NULL END ) AS assoc ,
COUNT ( CASE WHEN g . degree_level IN ( '04' ) THEN g . degree_level ELSE NULL END ) AS ac ,
COUNT ( CASE WHEN g . degree_level IN ( '05' ) THEN g . degree_level ELSE NULL END ) AS bacc ,
COUNT ( CASE WHEN g . degree_level IN ( '06' ) THEN g . degree_level ELSE NULL END ) AS postbacc ,
COUNT ( CASE WHEN g . degree_level IN ( '07' ) THEN g . degree_level ELSE NULL END ) AS mast ,
COUNT ( CASE WHEN g . degree_level IN ( '08' , '11' , '12' ) THEN g . degree_level ELSE NULL END ) AS spec ,
COUNT ( CASE WHEN g . degree_level IN ( '09' , '17' ) THEN g . degree_level ELSE NULL END ) AS doc_rs ,
COUNT ( CASE WHEN g . degree_level IN ( '10' , '18' ) THEN g . degree_level ELSE NULL END ) AS doc_prof
FROM graduated_student_table g
JOIN fice_table f
ON g . fice_code = f . fice_code
WHERE f . inst_type IN ( '1' , '2' )
AND g . academic_year IN ( '2006' , '2007' , '2008' , '2009' , '2010' )
GROUP BY g . academic_year , f . inst_type , f . school_abbr
ORDER BY g . academic_year , f . inst_type , f . school_abbr;


Bachelor Degrees
This is an overall headcount of all bachelor degrees awarded by the institution. This is degree level 05 (Bachelor's degree) only.

SQL Code

SELECT g . academic_year ,
f . inst_type ,
f . school_abbr ,
COUNT ( CASE WHEN g . degree_level IN ( '05' ) THEN g . degree_level ELSE NULL END ) AS bacc
FROM graduated_student_table g
JOIN fice_table f
ON g . fice_code = f . fice_code
WHERE f . inst_type IN ( '1' , '2' )
AND g . academic_year IN ( '2006' , '2007' , '2008' , '2009' , '2010' )
GROUP BY g . academic_year , f . inst_type , f . school_abbr
ORDER BY g . academic_year , f . inst_type , f . school_abbr;


Associate Degrees
This is an overall headcount of all associate degrees awarded by the institution. This is degree level 03 (Associate's degree) only.

SQL Code

SELECT g . academic_year ,
f . inst_type ,
f . school_abbr ,
COUNT ( CASE WHEN g . degree_level IN ( '03' ) THEN g . degree_level ELSE NULL END ) AS assoc
FROM graduated_student_table g
JOIN fice_table f
ON g . fice_code = f . fice_code
WHERE f . inst_type IN ( '1' , '2' )
AND g . academic_year IN ( '2006' , '2007' , '2008' , '2009' , '2010' )
GROUP BY g . academic_year , f . inst_type , f . school_abbr
ORDER BY g . academic_year , f . inst_type , f . school_abbr;


1-Year Technical Certificates
This is an overall headcount of all Technical Certificates awarded by the institution. This is degree level 02 (Technical Certificate) only.

SQL Code

SELECT g . academic_year ,
f . inst_type ,
f . school_abbr ,
COUNT ( CASE WHEN g . degree_level IN ( '02' ) THEN g . degree_level ELSE NULL END ) AS tc
FROM graduated_student_table g
JOIN fice_table f
ON g . fice_code = f . fice_code
WHERE f . inst_type IN ( '1' , '2' )
AND g . academic_year IN ( '2006' , '2007' , '2008' , '2009' , '2010' )
GROUP BY g . academic_year , f . inst_type , f . school_abbr
ORDER BY g . academic_year , f . inst_type , f . school_abbr;


Master and Specialist Degrees
This is an overall headcount of all Master and Specialist degrees awarded by the institution. This is degree levels 07 (Master's degree) and 08 (Post-Masters, Specialist, Post-First Professional Degree/Certificate) only.

SQL Code

SELECT g . academic_year ,
f . inst_type ,
f . school_abbr ,
COUNT ( CASE WHEN g . degree_level IN ( '07' ) THEN g . degree_level ELSE NULL END ) AS mast ,
COUNT ( CASE WHEN g . degree_level IN ( '08' , '11' , '12' ) THEN g . degree_level ELSE NULL END ) AS spec
FROM graduated_student_table g
JOIN fice_table f
ON g . fice_code = f . fice_code
WHERE f . inst_type IN ( '1' , '2' )
AND g . academic_year IN ( '2006' , '2007' , '2008' , '2009' , '2010' )
GROUP BY g . academic_year , f . inst_type , f . school_abbr
ORDER BY g . academic_year , f . inst_type , f . school_abbr;


Doctoral Degrees
This is an overall headcount of all Doctoral degrees awarded by the institution. This is degree levels 09/17 (Doctoral Degree - Research/Scholarship) and 10/18 (Doctoral Degree - Professional Practice).

SQL Code

SELECT g . academic_year ,
f . inst_type ,
f . school_abbr ,
COUNT ( CASE WHEN g . degree_level IN ( '09' , '17' ) THEN g . degree_level ELSE NULL END ) AS doc_rs ,
COUNT ( CASE WHEN g . degree_level IN ( '10' , '18' ) THEN g . degree_level ELSE NULL END ) AS doc_prof
FROM graduated_student_table g
JOIN fice_table f
ON g . fice_code = f . fice_code
WHERE f . inst_type IN ( '1' , '2' )
AND g . academic_year IN ( '2006' , '2007' , '2008' , '2009' , '2010' )
GROUP BY g . academic_year , f . inst_type , f . school_abbr
ORDER BY g . academic_year , f . inst_type , f . school_abbr;


NOTE: The years must be changed for future measures.