Add Annualized FTE SQL

[andreabringhurst]

Annualized FTE is a calculation used by both USHE and IPEDS. It is created by summing the credits of a particular level (undergraduate, graduate, professional), divided by the number of credits in a full-time load. This sql should create the Annualized FTE for USHE and IPEDS (one for each if they are different). So that this reporting measure is easy to find.

[andreabringhurst]

@JoyBaule do you have any starting sql that could be added here for future reference?

[JoyBaule]

The 12 month FTE calculations for USHE and IPEDS were reviewed at code review on 11/14/24. @CSPierce01 will review the two SQL queries for editing help to create all the computations within each SQL:

/*
This SQL query calculates annualized FTE for USHE.

USHE uses End of Term data to calculate their annualized FTE.

They add the total FTE by each term (Summer, Fall, Spring) and
divide by 2 to get Annualized FTE.

*/

WITH CTE_graduate_fte AS
         (SELECT a.term_id,
                 ROUND(SUM(a.attempted_credits) / 10, 2) as eot_graduate_fte
          FROM export.student_section_version a
          WHERE a.is_enrolled IS TRUE
            AND a.version_desc = 'End of Term'
            AND a.course_level_id = 'GR'
          GROUP BY a.term_id),

     CTE_undergrad_fte AS
         (SELECT a.term_id,
                 ROUND(SUM(a.attempted_credits) / 15, 2) as eot_undergrad_fte
          FROM export.student_section_version a
          WHERE a.is_enrolled IS TRUE
            AND a.version_desc = 'End of Term'
            AND a.course_level_id = 'UG'
          GROUP BY a.term_id),

   CTE_ushe_academic_year AS
       (SELECT academic_year_desc,
       academic_year_code,
       term_id,
       term_desc,
       season,
       CASE
           WHEN season = 'Summer' THEN to_char(CAST(academic_year_code AS INTEGER) + 1, 'FM9999') --format mask
           ELSE academic_year_code
       END AS ushe_academic_year_code
FROM export.term)

SELECT b.term_desc,
       ushe_academic_year_code,
       COALESCE(d.eot_undergrad_fte, 0) AS eot_undergrad_fte,
       COALESCE(c.eot_graduate_fte, 0) AS eot_graduate_fte,
       ROUND(COALESCE(c.eot_graduate_fte, 0) + COALESCE(d.eot_undergrad_fte, 0), 2) AS eot_total_fte
FROM export.student_section_version a
         LEFT JOIN CTE_ushe_academic_year b
                   ON a.term_id = b.term_id
         LEFT JOIN CTE_graduate_fte c
                   ON a.term_id = c.term_id
         LEFT JOIN CTE_undergrad_fte d
                   ON a.term_id = d.term_id
WHERE a.is_enrolled IS TRUE
  AND a.version_desc = 'End of Term'
  AND DATE_PART('year', NOW()) - b.ushe_academic_year_code :: INT <= 5 -- Current year plus last 5 years
GROUP BY b.term_desc, c.eot_graduate_fte, d.eot_undergrad_fte, ushe_academic_year_code
ORDER BY b.term_desc;

[JoyBaule]

@CSPierce01 Here is the SQL for the IPEDS 12 Month Enrollment FTE:

/*
This SQL query calculates IPEDS 12 Month Enrollment FTE.

-- IPEDS - based on 24 UG and 30 GR And is based on Census

The total attempted credits for UG and GR are summed then divided
by 24 for Undergraduate and 30 for Graduate to create FTE for
each level.

The totals for Undergraduate FTE and Graduate FTE are added together to
get the 12 Month Enrollment FTE by academic year.

*/

WITH CTE_combined_twelve_month_fte AS (
    SELECT
        b.academic_year_code,
        b.academic_year_desc,
        ROUND(SUM(CASE
                       WHEN a.course_level_id = 'GR'
                       THEN a.attempted_credits
                       ELSE 0 END) / 24, 0) AS census_graduate_twelve_month_fte,
        ROUND(SUM(CASE
                       WHEN a.course_level_id = 'UG'
                       THEN a.attempted_credits
                       ELSE 0 END) / 30, 0) AS census_undergrad_twelve_month_fte
    FROM
        export.student_section_version a
    LEFT JOIN
        export.term b
           ON a.term_id = b.term_id
    WHERE a.is_enrolled IS TRUE
      AND a.version_desc = 'Census'
 GROUP BY b.academic_year_desc, b.academic_year_code
)
SELECT a.academic_year_desc,
    a.academic_year_code,
    a.census_graduate_twelve_month_fte,
    a.census_undergrad_twelve_month_fte,
    (a.census_graduate_twelve_month_fte + a.census_undergrad_twelve_month_fte) AS total_census_twelve_month_fte
FROM
    CTE_combined_twelve_month_fte a
WHERE DATE_PART('year', NOW()) - a.academic_year_code :: INT <= 5
GROUP BY a.academic_year_desc, a.academic_year_code, a.census_graduate_twelve_month_fte, a.census_undergrad_twelve_month_fte
ORDER BY a.academic_year_desc , academic_year_code;

[andreabringhurst]

@CSPierce01 when you have updated sql, please post it here so we can add it to the pull request for fte #22 .