Code for Measure U16


Progression
This is a measuring using a cohort of degree/certificate –seeking students enrolling in 6 or more hours during the fall semester. This cohort is then tracked through the next academic year to identify how many students in the cohort earned a total 18 or more credit hours through the two academic years (including remedial/developmental courses). The Progression Rate is expressed as a percentage and changes over time are expressed as a difference in percentage points.

SQL statements are in RED.


To build the progression table.

INSERT [Performance].[4YrProgression]
( [fice_code]
,[academic_year]
,[term]
,[ssn_id]
,[degree_intent]
,[initial_cr_hrs]
,[cr_hrs_in_2_years]
,[award_in_2_years] )
SELECT dbo.student_table.fice_code
,dbo.student_table.academic_year
,dbo.student_table.term
,dbo.student_table.ssn_id
,degree_intent
,SUM(credit_hours)
,[Performance].[fn4YrProgressionCreditHours](dbo.student_table.fice_code
,dbo.student_table.academic_year
,dbo.student_table.term, dbo.student_table.ssn_id)
,[Performance].[fnHighestAwardIn2Yrs](dbo.student_table.fice_code
,dbo.student_table.academic_year, dbo.student_table.ssn_id)
FROM dbo.student_table
INNER JOIN dbo.registration_table
ON dbo.student_table.fice_code = dbo.registration_table.fice_code
AND dbo.student_table.academic_year = dbo.registration_table.academic_year
AND dbo.student_table.term = dbo.registration_table.term
AND dbo.student_table.ssn_id = dbo.registration_table.ssn_id
INNER JOIN dbo.course_table
ON dbo.registration_table.fice_code = dbo.course_table.fice_code
AND dbo.registration_table.academic_year = dbo.course_table.academic_year
AND dbo.registration_table.term = dbo.course_table.term
AND dbo.registration_table.sequence = dbo.course_table.sequence
AND dbo.registration_table.level_sequence = dbo.course_table.level_sequence
INNER JOIN dbo.fice_table
ON dbo.student_table.fice_code = dbo.fice_table.fice_code
WHERE dbo.student_table.academic_year = @year
AND dbo.student_table.term = '1'
AND inst_type IN ( '1', '2' )
AND ( degree_intent <> '3' )
AND student_level IN ( '01', '02', '03', '04' )
AND ( tech_inst_funding NOT IN ( 'O', 'C', 'M', 'T' ) OR tech_inst_funding IS NULL)
GROUP BY dbo.student_table.fice_code
,dbo.student_table.academic_year
,dbo.student_table.term
,dbo.student_table.ssn_id
,degree_intent
HAVING SUM(credit_hours) >= 6;


To identify the cohort students .

SELECT x.academic_year,
f.inst_type,
f.school_abbr,
COUNT(x.ssn_id) AS cohort
FROM sisdb.[performance].[4yrprogression] x
JOIN fice_table f
ON x.fice_code = f.fice_code
WHERE f.inst_type = '1'
AND x.academic_year IN ('2006', '2007', '2008', '2009', '2010')
AND x.degree_intent <> '03'
GROUP BY x.academic_year, f.inst_type, f.school_abbr
ORDER BY x.academic_year, f.inst_type, f.school_abbr;


To determine the number of students that progressed.

SELECT x.academic_year,
f.inst_type,
f.school_abbr,
COUNT(x.ssn_id) AS progressed
FROM sisdb.[performance].[4yrprogression] x
JOIN fice_table f
ON x.fice_code = f.fice_code
WHERE f.inst_type = '1'
AND x.academic_year IN ('2006', '2007', '2008', '2009', '2010')
AND (x.cr_hrs_in_2_years >= '18' OR x.award_in_2_years IS NOT NULL)
GROUP BY x.academic_year, f.inst_type, f.school_abbr
ORDER BY x.academic_year, f.inst_type, f.school_abbr;