Add graduation.sql to utDataStoR

[andreabringhurst]

Need to add a standard graduation.sql to utDataStoR. Here is a start of one. This comes from the campus narrative, but it needs to be reviewed and determined what things should and should not be included in the standard graduation sql.

SELECT a.student_id, a.sis_system_id, b.is_athlete, b.is_veteran, a.cohort_start_term_id, a.cohort_code, a.cohort_code_desc, CASE WHEN b.is_athlete THEN 'BA' -- if athlete, change degree level to BA ELSE a.cohort_degree_level_code END AS cohort_degree_level_code, CASE WHEN b.is_athlete THEN 'Bachelor' -- if athlete, change degree level to Bachelor ELSE a.cohort_degree_level_desc END AS cohort_degree_level_desc, a.cohort_desc, a.full_time_part_time_code, c.is_graduated, a.is_exclusion, b.gender_code, b.death_date, b.ipeds_race_ethnicity, d.ipeds_award_level_code, c.graduated_term_id, c.degree_id, c.graduation_date, c.primary_program_id AS grad_program_id, d.college_desc AS grad_college_desc, d.college_abbrv AS grad_college_abbrv, e.term_start_date, CASE WHEN c.graduation_date - e.term_start_date < 0 THEN NULL ELSE c.graduation_date - e.term_start_date END AS days_to_graduate, f.primary_program_id AS entering_program_id, g.college_desc AS entering_college_desc, g.college_abbrv AS entering_college_abbrv

        FROM export.student_term_cohort a
   LEFT JOIN export.student b ON b.student_id = a.student_id
   LEFT JOIN export.degrees_awarded c
          ON c.student_id = a.student_id
         AND c.is_highest_undergraduate_degree_awarded
         AND c.degree_status_code = 'AW'
   LEFT JOIN export.academic_programs d
          ON d.program_id = c.primary_program_id
   LEFT JOIN export.term e
          ON e.term_id = a.cohort_start_term_id
   LEFT JOIN export.student_term_level_version f
          ON f.student_id = a.student_id AND f.term_id = a.cohort_start_term_id
         AND version_desc = 'Current' -- there seems to have missing data in version
   LEFT JOIN export.academic_programs g
          ON g.program_id = f.primary_program_id

[andreabringhurst]

This issue is waiting for the next enhancements to utDataStoR. The sql needs to be reviewed. The goal is to create a standard sql regarding students who graduate from Utah Tech. We may want to look into two different sql's. One that is credential driven, and one that is student driven. @JoyBaule should be the lead on this, she knows the data best. This will require the addition of a graduation vignette and a make_graduation_sql function.

[JoyBaule]

@andreabringhurst I am summarizing the conversation we had today regarding future enhancements for utDataStoR with an emphasis on the need for the addition of SQL for graduation/credentials received. At present we could easily provide up the three individual SQL queries.

1. USHE Graduation - In 2024 we added two new version tables to Edify

• export.student_degree_program_application_version

• export.student_term_level_graduate_version

The query built using these two tables is a snapshot of graduation data going back five years to Spring 2019. Because USHE Graduation is based on graduation dates (example: 06-30-2023 to 07-01-2024), these version tables were used to build the graduation submission file for 2024-25 compliance reporting to USHE.

3. A modified version of the SQL query built using the two new version tables referenced above was used for the 2024-25 IPEDS Completion (graduation) file using the same graduation date range. USHE and IPEDS graduation/completion files must match exactly in total count of credentials awarded and the number of students awarded.

4. A credentials awarded Tableau dashboard has been active since Spring 2020 with a history that currently goes back to academic year 2012-13. The query used to run the dashboard is based on data that is current and not in version tables. The total number of credentials awarded on the dashboard will fluctuate slightly from what has been officially reported to USHE and IPEDS.

All three of these queries should be considered when creating graduation SQL to the next round of enhancements for utDataStoR and documented in a graduation vignette to denote the differences between each query and the purpose they serve.