Skip to contents

Headcount

Source: vignettes/headcount.Rmd
headcount.Rmd

Headcount is the unique count of students enrolled at Utah Tech University for a specific period of time and is essential for both reporting and inquiry.

Point In Time Headcount

The Point In Time Headcount offers a daily glimpse into enrollment, detailing the term, date, number of days until term start, and the day’s total enrollment headcount.

/*
daily point in time headcount
Approved on 20250305
This query pulls the daily headcount (enrolled students) for the current term
(see where is_current_term IS TRUE)
The query also provides the date, the week
(calculates the interval between each date and the earliest registration start date, converts this interval to seconds, converts the difference from seconds to week),
and the days to the start of class
(zero is the first day of class, negative days are before class starts, positive counts are after class starts)
*/

SELECT a.term_id,
       date,
       CEIL(EXTRACT(EPOCH FROM AGE(date, MIN(b.registration_start_date) OVER ())) / (7 * 24 * 60 * 60)) AS week_number,
       a.days_to_class_start,
       COUNT(DISTINCT a.student_id) AS headcount
FROM export.daily_enrollment a
         LEFT JOIN export.term b
                   ON a.term_id = b.term_id
WHERE a.is_current_term IS TRUE -- Only pulling current term
  AND a.is_enrolled IS TRUE
GROUP BY a.term_id, date, a.days_to_class_start, b.registration_start_date
ORDER BY date DESC;

Current Headcount

The Current Headcount reflects the live count of enrolled students for a term, which can fluctuate daily and may be adjusted post-term for changes like withdrawals.

/*
Current headcount
Approved on 20241204
provides term and count of students for the most recent year plus 5 years of data (see where add DATE_PART)
using the current version table (see where a.version_desc = 'Current')
*/

SELECT a.term_desc,
       COUNT(a.student_id) AS current_headcount
FROM export.student_term_level_version a
         LEFT JOIN export.term b
                   ON a.term_id = b.term_id
WHERE a.is_enrolled IS TRUE
  AND a.is_primary_level IS TRUE
  AND a.version_desc = 'Current'
  AND DATE_PART('year', NOW()) - b.academic_year_code :: INT <= 5 -- Current year plus last 5 years
GROUP BY a.term_desc
ORDER BY a.term_desc;

Census Headcount

The Census Headcount is a fixed count of students enrolled by the 15th day of instruction, used for static reporting.

The 15th day of instruction aligns with the last day of the term to drop without receiving a “W” grade and is the last day to receive a full refund.

/*
Census/3rd Week headcount
Approved on 20241204
provides term and count of students for the most recent year plus 5 years of data (see where add DATE_Part)
using the census version table (see where a.version_desc = 'Census')
*/

SELECT a.term_desc,
       COUNT(a.student_id) AS census_headcount
FROM export.student_term_level_version a
         LEFT JOIN export.term b
                   ON a.term_id = b.term_id
WHERE a.is_enrolled IS TRUE
  AND a.is_primary_level IS TRUE
  AND a.version_desc = 'Census'
  AND DATE_PART('year', NOW()) - b.academic_year_code :: INT <= 5 -- Current year plus last 5 years
GROUP BY a.term_desc
ORDER BY a.term_desc;

End of Term Headcount

The End of Term Headcount tallies students enrolled at semesters end, including high school concurrent enrollments, providing another static figure for reporting.

/*
End of Term headcount
Approved on 20241204
provides term and count of students for the most recent year plus 5 years of data (see where add DATE_PART)
using the end of term version table (see where a.version_desc = 'End of Term')
*/

SELECT a.term_desc,
       COUNT(a.student_id) AS eot_headcount
FROM export.student_term_level_version a
         LEFT JOIN export.term b
                   ON a.term_id = b.term_id
WHERE a.is_enrolled IS TRUE
  AND a.is_primary_level IS TRUE
  AND a.version_desc = 'End of Term'
  AND DATE_PART('year', NOW()) - b.academic_year_code :: INT <= 5 -- Current year plus last 5 years
GROUP BY a.term_desc
ORDER BY a.term_desc;

IPEDS Fall Enrollment Headcount

For annual reporting, the IPEDS Fall Enrollment Headcount uses the Fall census data, while the IPEDS 12 Month Enrollment Headcount gathers year-round credit enrollment figures.

For the sql, see Census Headcount above

IPEDS 12 Month Enrollment Headcount

The IPEDS 12 Month Enrollment Headcount aims to collect comprehensive enrollment data over a 12-month period. This includes unduplicated census counts of all students enrolled for credit, capturing the full scope of instructional activity throughout the year.

/*
IPEDS 12 Month headcount
Approved on 20250305
This query pulls an unduplicated count of enrolled students for the last academic year
(this query is typically run in the fall for the previous academic year)
using the census version table (see where a.version_desc = 'Census')
 */

SELECT b.academic_year_code,
       COUNT(DISTINCT a.student_id) --unduplicated students over the course of an academic year
FROM export.student_term_level_version a
         LEFT JOIN export.term b
                   ON a.term_id = b.term_id
WHERE a.is_enrolled IS TRUE
  AND a.is_primary_level IS TRUE
  AND a.version_desc = 'Census'
  AND DATE_PART('year', NOW()) - b.academic_year_code :: INT = 1 -- Last academic year
GROUP BY academic_year_code;

USHE Headcount

The USHE Headcount provides a comprehensive view of enrollment by reporting the unduplicated net headcount five times each fiscal year:

  • Summer End of Term: Captures enrollment at the conclusion of the summer term.
  • Fall Census: Records enrollment as of the 15th day of instruction for the fall term.
  • Fall End of Term: Tallies enrollment at the end of the fall semester.
  • Spring Census: Documents enrollment on the 15th day of instruction for the spring term.
  • Spring End of Term: Counts enrollment at the close of the spring semester.

Census: USHE Census Headcount is the net unduplicated number of students enrolled as of the 15th day of instruction of an academic term. Census headcount is based on snapshot data, this headcount returns a static headcount and may be modified to meet USHE reporting element criteria.

For the sql, see Census Headcount above

End of Term: USHE End of Term Headcount is the net unduplicated number of students enrolled at the end of the semester and after the high school concurrent enrollment grades are entered. Based on snapshot data, this headcount returns a static headcount and may be modified to meet USHE reporting element criteria.

For the sql, see End of Term Headcount above

The Banner Headcount is calculated using the same logic as Current Headcount, but is based on data from the Banner system. This sql provides a live count of Headcount for a term, which can fluctuate daily and may be adjusted post-term for changes like withdrawals. For this sql to align with the Current Headcount sql, the sql must be run off of the REPT instance of Banner.

/*
Banner Headcount query
Approved on 20230719
Approved on 20241204
This query pulls enrolled students for a specific term (see where a.sfrstcr_term_code =)
and removes any test students enrolled (see the cte query and the where b.twgrrole IS NULL)
note that the a.sfrstcr_camp_code is not XXX or ACE
this query provides an unduplicated count of enrolled students by term
this query is the equivalent of Edify headcount current if ran on REPT and for the same term
 */

WITH basic_headcount AS (SELECT DISTINCT a.sfrstcr_term_code,
                                         a.sfrstcr_pidm
                         FROM saturn.sfrstcr a
                         WHERE a.sfrstcr_rsts_code IN (SELECT a1.stvrsts_code
                                                       FROM saturn.stvrsts a1
                                                       WHERE a1.stvrsts_incl_sect_enrl = 'Y')
                           AND (a.sfrstcr_camp_code NOT IN ('XXX', 'ACE'))),
     cte_test_users AS (SELECT a.twgrrole_pidm,
                               a.twgrrole_role
                        FROM twgrrole a
                        WHERE a.twgrrole_role = 'TESTUSER')
SELECT a.sfrstcr_term_code,
      COUNT(a.sfrstcr_pidm)
FROM basic_headcount a
LEFT JOIN cte_test_users b ON b.twgrrole_pidm = a.sfrstcr_pidm
WHERE a.sfrstcr_term_code = '202440' -- change to desired term
AND b.twgrrole_role IS NULL -- removes test users
GROUP BY a.sfrstcr_term_code;

The Banner Headcount Details uses the same logic as Banner Headcount, but provides unit record data instead of a count from the Banner system.

/*
Banner Headcount Details query
Approved on 20241204
This query pulls enrolled students (pidm) for a specific term (see where a.sfrstcr_term_code =)
and removes any test students enrolled (see the cte query and the where b.twgrrole IS NULL)
note that the a.sfrstcr_camp_code is not XXX or ACE
this query provides a term_code and pidm
this query provided the student id that would equal the count that is in Edify headcount current if ran on REPT and for the same term
 */

WITH basic_headcount AS (SELECT DISTINCT a.sfrstcr_term_code,
                                         a.sfrstcr_pidm
                         FROM saturn.sfrstcr a
                         WHERE a.sfrstcr_rsts_code IN (SELECT a1.stvrsts_code
                                                       FROM saturn.stvrsts a1
                                                       WHERE a1.stvrsts_incl_sect_enrl = 'Y')
                           AND (a.sfrstcr_camp_code NOT IN ('XXX', 'ACE'))),
     cte_test_users AS (SELECT a.twgrrole_pidm,
                               a.twgrrole_role
                        FROM twgrrole a
                        WHERE a.twgrrole_role = 'TESTUSER')
SELECT a.sfrstcr_term_code,
       a.sfrstcr_pidm
FROM basic_headcount a
LEFT JOIN cte_test_users b ON b.twgrrole_pidm = a.sfrstcr_pidm
WHERE a.sfrstcr_term_code = '202440' -- change to desired term
AND b.twgrrole_role IS NULL;  -- removes test users