Definition
The item "
IR First STRM as Degree" in the folder "CBIL History Facts" is the earliest fall or spring term in which a student was enrolled for courses in a degree-seeking program within an academic career in a particular institution.
Also see
Degree Status and
New Student
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_FTDS purge;
create table IR.CBIL_CF_FTDS
tablespace CBIL_DATA
as select
distinct SUBQ.EMPLID,
SUBQ.ACAD_CAREER,
SUBQ.INSTITUTION,
SUBQ.IR_FIRST_STRM_AS_DEGREE
from (select CART.EMPLID, CART.ACAD_CAREER, CART.INSTITUTION,
CART.STRM as IR_FIRST_STRM_AS_DEGREE,
rank() over (partition by CART.EMPLID, CART.ACAD_CAREER, CART.INSTITUTION
order by CART.STRM asc) as MAX_RANK
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) SUBQ
where MAX_RANK = 1;