IPEDS 12-month Enrollment Report

A couple of tables and views have been created for this report in SQL Server. 

1. CS_Multi_Term_Tbl : This is the base table and has a list of courses students enrolled along with students' demographic data.  IPEDS_YEAR was added based on course start date from July 1 through June 30 of the year. For example, IPEDS_YEAR of "2021" indicates class start date is between 7/1/2020 and 6/30/2021. New IPEDS year will be loaded after the summer census. PeopleSoft table used are:
   • UM_STUD_CENS_VW - Student Reporting Extract - getting student's demographic data
   • UM_STD_ENR_C_VW - Student Enrollment Table - getting student's course enrollment data
   • CLASS_TBL - Class Table - getting a course start date
   • UM_STDNT_COHORT - Student Cohort - getting cohort type (first-time/ transfer)
   The SQL code for the CS_Multi_Term_Tbl (IPEDS Year 2021 Sample) is below.

   SELECT DISTINCT
     A.STRM,
     '2021' IPEDS_YEAR,
     A.INSTITUTION,
     A.UM_INST_HOST,
     A.EMPLID,
     A.ACAD_PROG_PRIMARY,
     A.UM_DEGREE_NONDEG,
     A.UM_ETHNIC_GRP_DESC,
     A.Acad_Level_Bot,
     A.Um_Unt_Tak_Prg_Rc,
     A.SEX,
     A.ACAD_CAREER,
     B.STDNT_ENRL_STATUS,
     B.CRSE_CAREER,
     A.ADMIT_TERM,
     A.ADMIT_TYPE,
     A.RESIDENCY,
     A.ACAD_PROG,
     A.ACAD_PLAN,
     A.UM_ACAD_PLAN_DESCR,
     A.CITY,
     A.STATE,
     A.STATE_DESCR1,
     A.POSTAL,
     A.COUNTRY,
     A.COUNTRY_DESCR1,
     B.SUBJECT,
     B.CATALOG_NBR,
     B.CLASS_SECTION,
     B.CLASS_NBR,
     B.DESCR,
     B.ACAD_ORG,
     B.UM_ACAD_ORG_DESCR,
     B.UNT_PRGRSS,
     B.UNT_PRGRSS_FA,
     B.CAMPUS,
     B.LOCATION,
     B.DESCR_LOCATION,
     B.um_class_dlv_mode,
     B.um_dist_class,
     B.UM_DIST_ATTR_FLAG,
     B.INSTRUCTION_MODE,
     E.START_DT,
     F.Um_Cohort_Type,
     A.Degree,
     CASE 
       WHEN F.Um_Cohort_Type in ('FYR', 'EFY') then 'First-time'
       WHEN F.Um_Cohort_Type = 'TRF' then 'Transfer-in'
       else 'Continuing'
     end as cohort_category,
     'CS_IPEDS_MT' DI_Job_Pid,
     sysdate DI_Create_Date,
     sysdate DI_Modified_Date
     FROM SYSADM.PS_UM_STUD_CENS_VW A
     LEFT OUTER JOIN SYSADM.PS_UM_STD_ENR_C_VW B
       ON A.EMPLID = B.EMPLID
      AND A.INSTITUTION = B.INSTITUTION
      AND A.STRM = B.STRM
     LEFT OUTER JOIN SYSADM.PS_CLASS_TBL E
       ON B.INSTITUTION = E.INSTITUTION
      AND B.STRM = E.STRM
      AND B.CLASS_NBR = E.CLASS_NBR
     LEFT OUTER JOIN SYSADM.PS_um_stdnt_cohort F
       on A.INSTITUTION = F.INSTITUTION
      and A.ACAD_CAREER = F.ACAD_CAREER
      and A.emplid = F.EMPLID
      and A.strm = F.Um_Cohort_Strm
      and F.um_cohort_code='RETENTION'
      and ((F.action_dt = (SELECT MAX(F_ED.action_dt) 
                             FROM sysadm.ps_um_stdnt_cohort F_ED
                            WHERE F.INSTITUTION = F_ED.INSTITUTION
                              and F.ACAD_CAREER = F_ED.ACAD_CAREER
                              and F.EMPLID = F_ED.EMPLID
                              and F.UM_COHORT_STRM = F_ED.UM_COHORT_STRM
                              and F.um_cohort_code = F_ED.um_cohort_code
                              and F_ED.action_dt <= SYSDATE)) or F.action_dt is null)
    WHERE A.STRM BETWEEN '2030' AND '2130'
      AND E.START_DT BETWEEN TO_DATE('20' || substr('2030', 1, 2) || '-07-01','YYYY-MM-DD') AND TO_DATE('20' || substr('2130', 1, 2) || '-06-30','YYYY-MM-DD')

2. CS_MTT_Emplid_Listing_VW : Getting a list of EMPLID with student's first term based on the hierarchy for each IPEDS year. The hierarchy used is fall > spring > summer of the first year > summer of the second year. UM and UMM data have been combined. The SQL code is below.

   select distinct
     d.emplid,
     d.ipeds_year,
     d.INST_COMBINED,
     substring(d.min_strm, 2, 4) First_Full_STRM
     from (SELECT
             a.EMPLID,
             a.IPEDS_YEAR,
    CASE WHEN a.INSTITUTION = 'UMS04' THEN 'UMS05' ELSE a.INSTITUTION END as INST_COMBINED,
             min(c.strm_order) min_strm
             FROM [UMS_CS_DM_DEV].[dbo].[CS_Multi_Term_Tbl] a
             left outer join (select
                                b.emplid,
           b.ipeds_year,
           b.INSTITUTION,
           substring(b.STRM, 3, 1) + b.STRM strm_order
                                from [UMS_CS_DM_DEV].[dbo].[CS_Multi_Term_Tbl] b) c
               on a.EMPLID = c.Emplid
              and a.ipeds_year = c.ipeds_year
              and a.INSTITUTION = c.INSTITUTION
           group by a.EMPLID, a.IPEDS_YEAR, CASE WHEN a.INSTITUTION = 'UMS04' THEN 'UMS05' ELSE a.INSTITUTION END) d

3. CS_MTT_Distance_Count_VW : It indicates the student's distance education status. In order to get the status, it compared the total course count for distance only with the total course count during the IPEDS year per student by institution. UM and UMM data have been combined. The SQL code is below.

   SELECT
   b.EMPLID,
   b.IPEDS_YEAR,
   b.INST_COMBINED,
   b.DISTANCE_COUNT,
   b.TOTAL_COUNT,
   (CASE WHEN b.DISTANCE_COUNT = b.TOTAL_COUNT THEN 'Distance Only'
         WHEN b.DISTANCE_COUNT = 0 THEN 'In-person Only'
         ELSE 'Both'
          END) as DIST_CATEGORY
   FROM (
         SELECT
         EMPLID,
         IPEDS_YEAR,
         CASE WHEN INSTITUTION= 'UMS04' THEN 'UMS05' ELSE INSTITUTION END as INST_COMBINED,
         SUM(CASE WHEN UM_DIST_CLASS = 'NOT DIST' THEN 0 ELSE 1 END) as DISTANCE_COUNT,
         COUNT(*) as TOTAL_COUNT
         FROM [UMS_CS_DM_DEV].[dbo].[CS_Multi_Term_Tbl]
         GROUP BY
         EMPLID,
         IPEDS_YEAR,
         CASE WHEN INSTITUTION= 'UMS04' THEN 'UMS05' ELSE INSTITUTION END
        ) b

4. CS_MTT_Student_Demographic_VW : Returning student's demographic data (sex, ethnicity, acad_career, um_degree_nondeg) and credit hours for student per term. When a student enrolled in both UM and UMM, the data from student's degree institution per term has been selected. The SQL code is below.

   select
     a.EMPLID,
     a.STRM,
     a.IPEDS_YEAR,
     a.INST_COMBINED,
     a.SEX,
     a.UM_ETHNIC_GRP_DESC,
     a.ACAD_CAREER,
     a.UM_DEGREE_NONDEG,
     a.COHORT_CATEGORY,
     a.UNT_PRGRSS,
     case
       when a.ACAD_PROG_PRIMARY in ('NDEC', 'NECN') then 'Y'
   	else 'N'
     end as Early_Coll
     from (select
             f.EMPLID,
             f.STRM,
             f.IPEDS_YEAR,
             f.institution as INST_COMBINED,
             f.acad_prog_primary,
             MIN(f.SEX) SEX,
             MAX(f.UM_ETHNIC_GRP_DESC) UM_ETHNIC_GRP_DESC,
             MIN(f.ACAD_CAREER) ACAD_CAREER,
             MIN(f.UM_DEGREE_NONDEG) UM_DEGREE_NONDEG,
             MAX(f.COHORT_CATEGORY) COHORT_CATEGORY,
             SUM(f.UNT_PRGRSS) UNT_PRGRSS
            from [UMS_CS_DM_PRD].[dbo].[CS_Multi_Term_Tbl] f
           where f.INSTITUTION not in ('UMS05','UMS04')
          group by f.EMPLID, f.STRM, f.IPEDS_YEAR, f.institution, f.acad_prog_primary
   
   union
   
   select
     c.EMPLID,
     c.STRM,
     c.IPEDS_YEAR,
     c.acad_prog_primary,
     'UMS05' as INST_COMBINED,
     MIN(c.SEX) SEX,
     MAX(c.UM_ETHNIC_GRP_DESC) UM_ETHNIC_GRP_DESC,
     MIN(c.ACAD_CAREER) ACAD_CAREER,
     MIN(c.UM_DEGREE_NONDEG) UM_DEGREE_NONDEG,
     MAX(c.COHORT_CATEGORY) COHORT_CATEGORY,
     e.UNT_PRGRSS  from [UMS_CS_DM_PRD].[dbo].[CS_Multi_Term_Tbl] c
     left outer join (Select
                        d.EMPLID,
                        d.STRM,
                        d.IPEDS_YEAR,
                        SUM(d.UNT_PRGRSS) UNT_PRGRSS
                        from [UMS_CS_DM_PRD].[dbo].[CS_Multi_Term_Tbl] d
                       where d.INSTITUTION in ('UMS05','UMS04')
                      group by EMPLID, STRM, IPEDS_YEAR) e
       on c.emplid       = e.EMPLID
      and c.strm         = e.strm
      and c.IPEDS_YEAR   = e.IPEDS_YEAR
    where c.INSTITUTION in ('UMS05','UMS04')
      and c.UM_DEGREE_NONDEG = (select min(aaa.UM_DEGREE_NONDEG)
                                  from [UMS_CS_DM_PRD].[dbo].[CS_Multi_Term_Tbl] aaa
                                 where c.EMPLID      = aaa.EMPLID
                                   and c.STRM        = aaa.STRM
                                   and aaa.INSTITUTION in ('UMS05','UMS04'))
   group by c.EMPLID, c.STRM, c.IPEDS_YEAR, e.UNT_PRGRSS, c.acad_prog_primary) A

5. Another table, Credit hours based on UM_INST_HOST, was created in Power BI to validate cross-listed course hours. The fields are from CS_Multi_Term_Tbl and UM and UMM were combined.

Data Fields Generated