Code for Measure C03


Progression
This is a measuring using a cohort of degree/certificate–seeking students enrolling in 6 or more hours during the fall or spring 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.

WITH cte AS
(
SELECT dbo.student_table.fice_code
,academic_year
,ssn_id
,ROW_NUMBER() OVER ( PARTITION BY dbo.student_table.fice_code, academic_year, ssn_id ORDER BY term ) AS rownumber
FROM dbo.student_table
INNER JOIN dbo.fice_table
ON dbo.student_table.fice_code = dbo.fice_table.fice_code
WHERE academic_year = @year
AND inst_type = '2'
AND term IN ( '1', '2' )
AND degree_intent IN ( '2', '4', '6', '7', '8' )
)
INSERT [Performance].[2YrProgressionFallSpring]
(
[fice_code]
,[academic_year]
,[ssn_id]
,[initial_cr_hrs]
,[cr_hrs_in_2_years]
,[award_in_2_years]
)
SELECT cte.fice_code
,cte.academic_year
,cte.ssn_id
,SUM(credit_hours)
,[Performance].[fn2YrProgressionCreditHoursFallSpring](cte.fice_code, cte.academic_year
,cte.ssn_id)
,[Performance].[fnHighestAwardIn2Yrs](cte.fice_code, cte.academic_year, cte.ssn_id)
--,[Performance].[fnHighestAwardIn3Yrs](cte.fice_code, cte.academic_year, cte.ssn_id)
FROM cte
INNER JOIN dbo.registration_table
ON cte.fice_code = dbo.registration_table.fice_code
AND cte.academic_year = dbo.registration_table.academic_year
AND cte.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
WHERE ( tech_inst_funding NOT IN ( 'O', 'C', 'M', 'T' ) OR tech_inst_funding IS NULL )
AND cte.rownumber = 1
GROUP BY cte.fice_code, cte.academic_year, cte.ssn_id
HAVING SUM(credit_hours) >= 6;


To identify the cohort students .

SELECT p.academic_year,
f.inst_type,
f.school_abbr,
COUNT(DISTINCT(p.ssn_id)) AS cohort
FROM [sisdb].[Performance].[2YrProgressionFallSpring] p
JOIN fice_table f
ON p.fice_code = f.fice_code
WHERE f.inst_type = '2'
AND p.academic_year BETWEEN '2006' AND '2010'
GROUP BY p.academic_year, f.inst_type, f.school_abbr
ORDER BY p.academic_year, f.inst_type, f.school_abbr;


To determine the number of students that progressed.

SELECT p.academic_year,
f.inst_type,
f.school_abbr,
COUNT(DISTINCT(p.ssn_id)) AS cohort
FROM [sisdb].[Performance].[2YrProgressionFallSpring] p
JOIN fice_table f
ON p.fice_code = f.fice_code
WHERE f.inst_type = '2'
AND p.academic_year BETWEEN '2006' AND '2010'
AND (p.cr_hrs_in_2_years >= '18'
OR p.award_in_2_years IS NOT NULL)
GROUP BY p.academic_year,
f.inst_type,
f.school_abbr
ORDER BY p.academic_year,
f.inst_type,
f.school_abbr;