Created by Miki Yanagi, last modified on Feb 02, 2022
This view was created for calculating retention rates. It is based on UM_STUD_CENS_VW by academic career and institution, and it is tracking enrollment for 20 long terms. It includes all academic career.
| Key | Record.Fieldname | Format | XLAT | Heading Text | Notes |
|---|---|---|---|---|---|
| → | ACAD_CAREER - Academic Career | Char4 | N | Career | |
| → | EMPLID - Empl ID | Char11 | ID | ||
| → | INSTITUTION - Academic Institution | Char5 | Institution | ||
| UM_1ST_DEGR_TERM - First -Degree Term | Char4 | First Degr Term | The first term enrolled as degree-seeking student. If none, '9999'. | ||
| UM_FALL_SPRNG_TRMS - Fall Spring Terms | Char100 | Fall Sprng Trm | The fall and spring terms enrolled | ||
| UM_TERM_LAST - Term Last | Char4 | Term Last | The last term enrolled | ||
| UM_TERM1 - Term1 | Char4 | Term1 | The first long term enrolled (regardless of degree-seeking status) | ||
| UM_TERM10 - Term10 | Char4 | Term10 | The 10th long term enrolled (regardless of degree-seeking status) | ||
| UM_TERM11 - Term11 | Char4 | Term11 | |||
| UM_TERM12 - Term12 | Char4 | Term12 | |||
| UM_TERM13 - Term13 | Char4 | Term13 | |||
| UM_TERM14 - Term14 | Char4 | Term14 | |||
| UM_TERM15 - Term15 | Char4 | Term15 | |||
| UM_TERM16 - Term16 | Char4 | Term16 | |||
| UM_TERM17 - Term17 | Char4 | Term17 | |||
| UM_TERM18 - Term18 | Char4 | Term18 | |||
| UM_TERM19 - Term19 | Char4 | Term19 | |||
| UM_TERM2 - Term2 | Char4 | Term2 | |||
| UM_TERM20 - Term20 | Char4 | Term20 | |||
| UM_TERM3 - Term3 | Char4 | Term3 | |||
| UM_TERM4 - Term4 | Char4 | Term4 | |||
| UM_TERM5 - Term5 | Char4 | Term5 | |||
| UM_TERM6 - Term6 | Char4 | Term6 | |||
| UM_TERM7 - Term7 | Char4 | Term7 | |||
| UM_TERM8 - Term8 | Char4 | Term8 | |||
| UM_TERM9 - Term9 | Char4 | Term9 | |||
| UM_UNITS1 - Units1 | Num6.3 | Units1 | Credit hours (um_unt_tak_prg_rc) for the term 1 | ||
| UM_UNITS10 - Units10 | Num6.3 | Units10 | Credit hours (um_unt_tak_prg_rc) for the term 10 | ||
| UM_UNITS11 - Units11 | Num6.3 | Units11 | |||
| UM_UNITS12 - Units12 | Num6.3 | Units12 | |||
| UM_UNITS13 - Units13 | Num6.3 | Units13 | |||
| UM_UNITS14 - Units14 | Num6.3 | Units14 | |||
| UM_UNITS15 - Units15 | Num6.3 | Units15 | |||
| UM_UNITS16 - Units16 | Num6.3 | Units16 | |||
| UM_UNITS17 - Units17 | Num6.3 | Units17 | |||
| UM_UNITS18 - Units18 | Num6.3 | Units18 | |||
| UM_UNITS19 - Units19 | Num6.3 | Units19 | |||
| UM_UNITS2 - Units2 | Num6.3 | Units2 | |||
| UM_UNITS20 - Units20 | Num6.3 | Units20 | |||
| UM_UNITS3 - Units3 | Num6.3 | Units3 | |||
| UM_UNITS4 - Units4 | Num6.3 | Units4 | |||
| UM_UNITS5 - Units5 | Num6.3 | Units5 | |||
| UM_UNITS6 - Units6 | Num6.3 | Units6 | |||
| UM_UNITS7 - Units7 | Num6.3 | Units7 | |||
| UM_UNITS8 - Units8 | Num6.3 | Units8 | |||
| UM_UNITS9 - Units9 | Num6.3 | Units9 |
Data Dictionary Links
Clicking the linked (blue) fields in any table will take you to the Data Dictionary and provide you with more information about that field as well as field values if they are available.
SQL Code for UM_RETENT_FS_IN Expand source
SELECT a.emplid,
a.institution,
a.acad_career,
CASE
WHEN MAX(a.strm1) > ' ' THEN
MAX(a.strm1) || ','
ELSE
' '
END || CASE
WHEN MAX(a.strm2) > ' ' THEN
MAX(a.strm2) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm3) > ' ' THEN
MAX(a.strm3) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm4) > ' ' THEN
MAX(a.strm4) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm5) > ' ' THEN
MAX(a.strm5) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm6) > ' ' THEN
MAX(a.strm6) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm7) > ' ' THEN
MAX(a.strm7) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm8) > ' ' THEN
MAX(a.strm8) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm9) > ' ' THEN
MAX(a.strm9) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm10) > ' ' THEN
MAX(a.strm10) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm11) > ' ' THEN
MAX(a.strm11) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm12) > ' ' THEN
MAX(a.strm12) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm13) > ' ' THEN
MAX(a.strm13) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm14) > ' ' THEN
MAX(a.strm14) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm15) > ' ' THEN
MAX(a.strm15) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm16) > ' ' THEN
MAX(a.strm16) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm17) > ' ' THEN
MAX(a.strm17) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm18) > ' ' THEN
MAX(a.strm18) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm19) > ' ' THEN
MAX(a.strm19) || ','
ELSE
''
END || CASE
WHEN MAX(a.strm20) > ' ' THEN
MAX(a.strm20) || ','
ELSE
''
END,
MAX(GREATEST(nvl(a.strm1, '0000'),
nvl(a.strm2, '0000'),
nvl(a.strm3, '0000'),
nvl(a.strm4, '0000'),
nvl(a.strm5, '0000'),
nvl(a.strm6, '0000'),
nvl(a.strm7, '0000'),
nvl(a.strm8, '0000'),
nvl(a.strm9, '0000'),
nvl(a.strm10, '0000'),
nvl(a.strm11, '0000'),
nvl(a.strm12, '0000'),
nvl(a.strm13, '0000'),
nvl(a.strm14, '0000'),
nvl(a.strm15, '0000'),
nvl(a.strm16, '0000'),
nvl(a.strm17, '0000'),
nvl(a.strm18, '0000'),
nvl(a.strm19, '0000'),
nvl(a.strm20, '0000'))) GREAT_TERM,
MIN(LEAST(CASE
WHEN a.nd1 = ' ' THEN
nvl(a.strm1, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd2 = ' ' THEN
nvl(a.strm2, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd3 = ' ' THEN
nvl(a.strm3, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd4 = ' ' THEN
nvl(a.strm4, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd5 = ' ' THEN
nvl(a.strm5, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd6 = ' ' THEN
nvl(a.strm6, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd7 = ' ' THEN
nvl(a.strm7, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd8 = ' ' THEN
nvl(a.strm8, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd9 = ' ' THEN
nvl(a.strm9, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd10 = ' ' THEN
nvl(a.strm10, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd11 = ' ' THEN
nvl(a.strm11, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd12 = ' ' THEN
nvl(a.strm12, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd13 = ' ' THEN
nvl(a.strm13, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd14 = ' ' THEN
nvl(a.strm14, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd15 = ' ' THEN
nvl(a.strm15, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd16 = ' ' THEN
nvl(a.strm16, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd17 = ' ' THEN
nvl(a.strm17, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd18 = ' ' THEN
nvl(a.strm18, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd19 = ' ' THEN
nvl(a.strm19, '9999')
ELSE
'9999'
END,
CASE
WHEN a.nd20 = ' ' THEN
nvl(a.strm20, '9999')
ELSE
'9999'
END)) FIRST_DEGREE_TERM,
nvl(MAX(a.strm1), ' ') strm1,
MAX(a.units1) units1,
nvl(MAX(a.strm2), ' ') strm2,
MAX(a.units2) units2,
nvl(MAX(a.strm3), ' ') strm3,
MAX(a.units3) units3,
nvl(MAX(a.strm4), ' ') strm4,
MAX(a.units4) units4,
nvl(MAX(a.strm5), ' ') strm5,
MAX(a.units5) units5,
nvl(MAX(a.strm6), ' ') strm6,
MAX(a.units6) units6,
nvl(MAX(a.strm7), ' ') strm7,
MAX(a.units7) units7,
nvl(MAX(a.strm8), ' ') strm8,
MAX(a.units8) units8,
nvl(MAX(a.strm9), ' ') strm9,
MAX(a.units9) units9,
nvl(MAX(a.strm10), ' ') strm10,
MAX(a.units10) units10,
nvl(MAX(a.strm11), ' ') strm11,
MAX(a.units11) units11,
nvl(MAX(a.strm12), ' ') strm12,
MAX(a.units12) units12,
nvl(MAX(a.strm13), ' ') strm13,
MAX(a.units13) units13,
nvl(MAX(a.strm14), ' ') strm14,
MAX(a.units14) units14,
nvl(MAX(a.strm15), ' ') strm15,
MAX(a.units15) units15,
nvl(MAX(a.strm16), ' ') strm16,
MAX(a.units16) units16,
nvl(MAX(a.strm17), ' ') strm17,
MAX(a.units17) units17,
nvl(MAX(a.strm18), ' ') strm18,
MAX(a.units18) units18,
nvl(MAX(a.strm19), ' ') strm19,
MAX(a.units19) units19,
nvl(MAX(a.strm20), ' ') strm20,
MAX(a.units20) units20
FROM (SELECT x7.institution,
x7.emplid,
x7.acad_career,
CASE
WHEN ranky = 1 THEN
x7.strm
ELSE
''
END STRM1,
CASE
WHEN ranky = 1 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS1,
CASE
WHEN ranky = 1 THEN
x7.nd
ELSE
''
END ND1,
CASE
WHEN ranky = 2 THEN
x7.strm
ELSE
''
END STRM2,
CASE
WHEN ranky = 2 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS2,
CASE
WHEN ranky = 2 THEN
x7.nd
ELSE
''
END ND2,
CASE
WHEN ranky = 3 THEN
x7.strm
ELSE
''
END STRM3,
CASE
WHEN ranky = 3 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS3,
CASE
WHEN ranky = 3 THEN
x7.nd
ELSE
''
END ND3,
CASE
WHEN ranky = 4 THEN
x7.strm
ELSE
''
END STRM4,
CASE
WHEN ranky = 4 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS4,
CASE
WHEN ranky = 4 THEN
x7.nd
ELSE
''
END ND4,
CASE
WHEN ranky = 5 THEN
x7.strm
ELSE
''
END STRM5,
CASE
WHEN ranky = 5 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS5,
CASE
WHEN ranky = 5 THEN
x7.nd
ELSE
''
END ND5,
CASE
WHEN ranky = 6 THEN
x7.strm
ELSE
''
END STRM6,
CASE
WHEN ranky = 6 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS6,
CASE
WHEN ranky = 6 THEN
x7.nd
ELSE
''
END ND6,
CASE
WHEN ranky = 7 THEN
x7.strm
ELSE
''
END STRM7,
CASE
WHEN ranky = 7 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS7,
CASE
WHEN ranky = 7 THEN
x7.nd
ELSE
''
END ND7,
CASE
WHEN ranky = 8 THEN
x7.strm
ELSE
''
END STRM8,
CASE
WHEN ranky = 8 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS8,
CASE
WHEN ranky = 8 THEN
x7.nd
ELSE
''
END ND8,
CASE
WHEN ranky = 9 THEN
x7.strm
ELSE
''
END STRM9,
CASE
WHEN ranky = 9 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS9,
CASE
WHEN ranky = 9 THEN
x7.nd
ELSE
''
END ND9,
CASE
WHEN ranky = 10 THEN
x7.strm
ELSE
''
END STRM10,
CASE
WHEN ranky = 10 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS10,
CASE
WHEN ranky = 10 THEN
x7.nd
ELSE
''
END ND10,
CASE
WHEN ranky = 11 THEN
x7.strm
ELSE
''
END STRM11,
CASE
WHEN ranky = 11 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS11,
CASE
WHEN ranky = 11 THEN
x7.nd
ELSE
''
END ND11,
CASE
WHEN ranky = 12 THEN
x7.strm
ELSE
''
END STRM12,
CASE
WHEN ranky = 12 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS12,
CASE
WHEN ranky = 12 THEN
x7.nd
ELSE
''
END ND12,
CASE
WHEN ranky = 13 THEN
x7.strm
ELSE
''
END STRM13,
CASE
WHEN ranky = 13 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS13,
CASE
WHEN ranky = 13 THEN
x7.nd
ELSE
''
END ND13,
CASE
WHEN ranky = 14 THEN
x7.strm
ELSE
''
END STRM14,
CASE
WHEN ranky = 14 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS14,
CASE
WHEN ranky = 14 THEN
x7.nd
ELSE
''
END ND14,
CASE
WHEN ranky = 15 THEN
x7.strm
ELSE
''
END STRM15,
CASE
WHEN ranky = 15 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS15,
CASE
WHEN ranky = 15 THEN
x7.nd
ELSE
''
END ND15,
CASE
WHEN ranky = 16 THEN
x7.strm
ELSE
''
END STRM16,
CASE
WHEN ranky = 16 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS16,
CASE
WHEN ranky = 16 THEN
x7.nd
ELSE
''
END ND16,
CASE
WHEN ranky = 17 THEN
x7.strm
ELSE
''
END STRM17,
CASE
WHEN ranky = 17 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS17,
CASE
WHEN ranky = 17 THEN
x7.nd
ELSE
''
END ND17,
CASE
WHEN ranky = 18 THEN
x7.strm
ELSE
''
END STRM18,
CASE
WHEN ranky = 18 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS18,
CASE
WHEN ranky = 18 THEN
x7.nd
ELSE
''
END ND18,
CASE
WHEN ranky = 19 THEN
x7.strm
ELSE
''
END STRM19,
CASE
WHEN ranky = 19 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS19,
CASE
WHEN ranky = 19 THEN
x7.nd
ELSE
''
END ND19,
CASE
WHEN ranky = 20 THEN
x7.strm
ELSE
''
END STRM20,
CASE
WHEN ranky = 20 THEN
x7.um_unt_tak_prg_rc
ELSE
0
END UNITS20,
CASE
WHEN ranky = 20 THEN
x7.nd
ELSE
''
END ND20,
x7.ranky
FROM (SELECT x6.emplid,
x6.acad_career,
x6.stdnt_car_nbr,
x6.adm_appl_nbr,
x6.institution,
x6.acad_prog,
x6.acad_plan,
X6.ND,
x6.strm,
x6.um_unt_tak_prg_rc,
x6.term_year,
x6.last_fall,
x6.last_term,
x6.diff,
rank() over(partition BY x6.institution, x6.emplid, x6.acad_career ORDER BY x6.emplid, x6.strm) ranky
FROM (SELECT x5.emplid,
x5.acad_career,
x5.stdnt_car_nbr,
x5.adm_appl_nbr,
x5.institution,
x5.acad_prog,
x5.acad_plan,
CASE
WHEN x5.UM_DEGREE_NONDEG = 'Non-Degree' THEN
'ND'
ELSE
' '
END ND,
x5.strm,
x5.um_unt_tak_prg_rc,
substr(x5.strm, 1, 2) term_year,
lag(x5.strm, 1, 0) over(partition BY x5.institution, x5.emplid, x5.acad_career ORDER BY x5.emplid, x5.strm) last_fall,
to_number(substr(lag(x5.strm, 1, 0)
over(partition BY
x5.institution,
x5.emplid,
x5.acad_career ORDER BY
x5.emplid,
x5.strm),
1,
2)) Last_Term,
to_number(substr(x5.strm, 1, 2)) -
to_number(substr(lag(x5.strm, 1, 0)
over(partition BY
x5.institution,
x5.emplid,
x5.acad_career ORDER BY
x5.emplid,
x5.strm),
1,
2)) Diff
FROM (SELECT institution,
emplid,
acad_career,
stdnt_car_nbr,
adm_appl_nbr,
acad_prog,
acad_plan,
strm,
acad_prog_primary,
um_degree_nondeg,
um_unt_tak_prg_rc
FROM ps_um_stud_cens_vw
WHERE substr(strm, 3, 2) IN ('10', '20')) x5) x6) x7,
ps_um_stud_cens_vw s
WHERE x7.emplid = s.emplid
AND x7.acad_career = s.acad_career
AND x7.stdnt_car_nbr = s.stdnt_car_nbr
AND x7.adm_appl_nbr = s.adm_appl_nbr
AND x7.institution = s.institution
AND x7.acad_prog = s.acad_prog
AND x7.acad_plan = s.acad_plan
AND x7.strm = s.strm) a
GROUP BY a.institution, a.emplid, a.acad_career;
