Code for Measures C04-C07


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 C04-C07.

SQL statements are in RED.

SQL Code

SELECT g . academic_year ,
f . inst_type ,
f . school_abbr ,
COUNT(g . degree_level) AS total
FROM graduated_student_table g
JOIN fice_table f
ON g . fice_code = f . fice_code
WHERE f . inst_type IN ( '2' )
AND g . academic_year IN ( '2006' , '2007' , '2008' , '2009' , '2010' )
AND g . degree_level IN ( '01', '02', '03', '04' )
GROUP BY g . academic_year , f . inst_type , f . school_abbr
ORDER BY g . academic_year , f . inst_type , f . school_abbr;


Certificates of Proficiency
This is an overall headcount of all Certificates of Proficiency awarded by the institution. This is degree level 01 only.

SQL Code

SELECT g . academic_year ,
f . inst_type ,
f . school_abbr ,
COUNT (CASE WHEN g . degree_level IN ( '01' ) THEN g . degree_level ELSE NULL END ) AS cp
FROM graduated_student_table g
JOIN fice_table f
ON g . fice_code = f . fice_code
WHERE f . inst_type IN ( '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;


Technical Certificates
This is an overall headcount of all Technical Certificates awarded by the institution. This is degree level 02 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 ('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 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 ('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;


Advanced Certificates
This is an overall headcount of all Advanced Certificates awarded by the institution. This is degree levels 04 only.

SQL Code

SELECT g . academic_year ,
f . inst_type ,
f . school_abbr ,
COUNT (CASE WHEN g . degree_level IN ( '04' ) THEN g . degree_level ELSE NULL END ) AS ac
FROM graduated_student_table g
JOIN fice_table f
ON g . fice_code = f . fice_code
WHERE f . inst_type IN ('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.