Definition

The item "IR Prior STRM as Degree" in the folder "CBIL History Facts" is the most recent fall or spring term prior to the current term in which a student was enrolled for courses in a degree-seeking academic program within an academic career in a particular institution.

Also see Degree Status and Readmit

SQL Code

drop table IR.CBIL_CF_STDNT_CAR_TERM_THIN purge;
create table IR.CBIL_CF_STDNT_CAR_TERM_THIN
tablespace CBIL_DATA
as select EMPLID,
ACAD_CAREER,
INSTITUTION,
STRM,
ACAD_PROG_PRIMARY
from ERP.CF_STDNT_CAR_TERM;

drop table IR.CBIL_CF_STDNT_ENRL_DISTINCT purge;
create table IR.CBIL_CF_STDNT_ENRL_DISTINCT
tablespace CBIL_DATA
as select distinct EMPLID,
ACAD_CAREER,
INSTITUTION,
STRM
from ERP.CF_STDNT_ENRL
where STDNT_ENRL_STATUS = 'E';

drop table IR.CBIL_CF_PTDS_T1 purge;
create table IR.CBIL_CF_PTDS_T1
tablespace CBIL_DATA
as select
distinct CART.EMPLID, CART.ACAD_CAREER, CART.INSTITUTION, CART.STRM
from IR.CBIL_CF_STDNT_CAR_TERM_THIN CART,
IR.CBIL_CF_STDNT_ENRL_DISTINCT ENRL
where substr(CART.ACAD_PROG_PRIMARY,1,4) not in ('NDEG')
and substr(CART.STRM,4,1) <> '6'
and substr(ENRL.STRM,4,1) <> '6'
and ENRL.EMPLID = CART.EMPLID
and ENRL.ACAD_CAREER = CART.ACAD_CAREER
and ENRL.INSTITUTION = CART.INSTITUTION
and ENRL.STRM = CART.STRM;

drop table IR.CBIL_CF_PTDS purge;
create table IR.CBIL_CF_PTDS
tablespace CBIL_DATA
as select
distinct SUBQ.CF_EMPLID,
SUBQ.CF_ACAD_CAREER,
SUBQ.CF_INSTITUTION,
SUBQ.CF_STRM,
SUBQ.IR_TERM_ENROLLED_DATE,
SUBQ.IR_PRIOR_STRM_AS_DEGREE
from (select CART.CF_EMPLID, CART.CF_ACAD_CAREER, CART.CF_INSTITUTION, CART.CF_STRM, CART.IR_TERM_ENROLLED_DATE,
ENRL.STRM as IR_PRIOR_STRM_AS_DEGREE,
rank() over (partition by CART.CF_EMPLID, CART.CF_ACAD_CAREER, CART.CF_INSTITUTION, CART.CF_STRM, CART.IR_TERM_ENROLLED_DATE
order by ENRL.STRM desc) as MAX_RANK
from IR.CBIL_CF_CAR_TERM CART,
IR.CBIL_CF_PTDS_T1 ENRL
where ENRL.EMPLID = CART.CF_EMPLID
and ENRL.ACAD_CAREER = CART.CF_ACAD_CAREER
and ENRL.INSTITUTION = CART.CF_INSTITUTION
and ENRL.STRM < CART.CF_STRM) SUBQ
where MAX_RANK = 1;

.