/************************************************************* Canvas & Course Enrollment Information ***********************/ /* Calculates estimated score based on assignment submissions */ SELECT course_section_dim.course_id AS course_id, pseudonym_dim.sis_user_id AS sis_user_id, pseudonym_dim.user_id AS user_id, (round((sum(submission_fact.published_score)/sum(assignment_dim.points_possible)), 2)*100) AS est_assn_scr FROM (((canvas_warehouse_sandbox.course_section_dim AS course_section_dim INNER JOIN canvas_warehouse_sandbox.assignment_dim AS assignment_dim ON course_section_dim.course_id = assignment_dim.course_id ) INNER JOIN canvas_warehouse_sandbox.submission_dim AS submission_dim ON assignment_dim.id = submission_dim.assignment_id ) INNER JOIN canvas_warehouse_sandbox.submission_fact AS submission_fact ON submission_dim.id = submission_fact.submission_id ) INNER JOIN canvas_warehouse_sandbox.pseudonym_dim AS pseudonym_dim ON submission_dim.user_id = pseudonym_dim.user_id WHERE (assignment_dim.due_at is not null AND assignment_dim.workflow_state = 'published' AND assignment_dim.points_possible > 0 AND submission_dim.workflow_state = 'graded' AND pseudonym_dim.sis_user_id not like '8000%' AND pseudonym_dim.sis_user_id is not null AND course_section_dim.sis_source_id is not null ) GROUP BY course_section_dim.course_id, pseudonym_dim.user_id, pseudonym_dim.sis_user_id; /* stu_canvas_score_temp-creates preliminary view of Canvas score; creates array of SIS id*/ SELECT pseudonym_dim.sis_user_id AS prsn_univ_id, enrollment_term_dim.sis_source_id AS acad_term_cd, course_score_fact.current_score AS current_score, course_score_fact.final_score AS final_score, est_assn_scr_set.est_assn_scr AS est_assn_scr, split('-', course_section_dim.sis_source_id) AS sis_source_array, course_section_dim.id AS id, course_section_dim.canvas_id AS canvas_id, course_section_dim.name AS name, course_section_dim.course_id AS course_id, course_section_dim.enrollment_term_id AS enrollment_term_id, course_section_dim.default_section AS default_section, course_section_dim.accepting_enrollments AS accepting_enrollments, course_section_dim.can_manually_enroll AS can_manually_enroll, course_section_dim.start_at AS start_at, course_section_dim.end_at AS end_at, course_section_dim.created_at AS created_at, course_section_dim.updated_at AS updated_at, course_section_dim.workflow_state AS workflow_state, course_section_dim.restrict_enrollments_to_section_dates AS restrict_enrollments_to_section_dates, course_section_dim.nonxlist_course_id AS nonxlist_course_id, course_section_dim.sis_source_id AS sis_source_id FROM ((canvas_warehouse_sandbox.course_section_dim AS course_section_dim INNER JOIN (((canvas_warehouse_sandbox.enrollment_dim AS enrollment_dim INNER JOIN canvas_warehouse_sandbox.course_score_dim AS course_score_dim ON enrollment_dim.id = course_score_dim.enrollment_id ) INNER JOIN canvas_warehouse_sandbox.course_score_fact AS course_score_fact ON course_score_dim.id = course_score_fact.score_id ) INNER JOIN canvas_warehouse_sandbox.pseudonym_dim AS pseudonym_dim ON enrollment_dim.user_id = pseudonym_dim.user_id ) ON course_section_dim.id = enrollment_dim.course_section_id ) INNER JOIN canvas_warehouse_sandbox.enrollment_term_dim AS enrollment_term_dim ON course_section_dim.enrollment_term_id = enrollment_term_dim.id ) LEFT OUTER JOIN est_assn_scr_set AS est_assn_scr_set ON (course_section_dim.course_id = est_assn_scr_set.course_id AND enrollment_dim.user_id = est_assn_scr_set.user_id) WHERE (course_section_dim.sis_source_id is not null AND enrollment_term_dim.sis_source_id in ('4148', '4152', '4155', '4158', '4162', '4165', '4168', '4172', '4175', '4178', '4182', '4185', '4188', '4192', '4195', '4198', '4205', '4208', '4212', '4215', '4218', '4202') AND pseudonym_dim.sis_user_id not like '8000%' AND enrollment_dim.type = 'StudentEnrollment'); /*stu_canvas_score; Gets class number of array from preliminary view*/ SELECT stu_canvas_score_temp.prsn_univ_id AS prsn_univ_id, stu_canvas_score_temp.acad_term_cd AS acad_term_cd, (stu_canvas_score_temp.sis_source_array)[4].string AS cls_nbr, stu_canvas_score_temp.est_assn_scr AS est_assn_scr, stu_canvas_score_temp.current_score AS current_score, stu_canvas_score_temp.final_score AS final_score, stu_canvas_score_temp.sis_source_id AS sis_source_id, stu_canvas_score_temp.course_id AS course_id FROM stu_canvas_score_temp; /* Gets SIS info for course */ SELECT concat(trim(crs_subj_cd), '-', trim(crs_catlg_nbr)) AS course, sr_enrl_st.prsn_univ_id AS prsn_univ_id, sr_enrl_st.acad_career_shrt_desc AS acad_career_shrt_desc, sr_enrl_st.cls_nbr AS cls_nbr, sr_enrl_st.acad_term_cd AS acad_term_cd, sr_enrl_st.stu_grd_basis_shrt_desc AS stu_grd_basis_shrt_desc, sr_enrl_st.crs_ofcl_grd_cd AS crs_ofcl_grd_cd, sr_enrl_st.stu_enrl_stat_shrt_desc AS stu_enrl_stat_shrt_desc, sr_enrl_st.stu_enrl_stat_reas_shrt_desc AS stu_enrl_stat_reas_shrt_desc, sr_enrl_st.stu_lst_enrl_reas_shrt_desc AS stu_lst_enrl_reas_shrt_desc, sr_enrl_st.pplsft_acad_org_lvl_1_cd AS pplsft_acad_org_lvl_1_cd, sr_enrl_st.pplsft_acad_org_lvl_2_cd AS pplsft_acad_org_lvl_2_cd, sr_enrl_st.pplsft_acad_org_lvl_3_cd AS pplsft_acad_org_lvl_3_cd, sr_enrl_st.pplsft_acad_org_lvl_4_cd AS pplsft_acad_org_lvl_4_cd, sr_enrl_st.pplsft_acad_org_lvl_5_cd AS pplsft_acad_org_lvl_5_cd, sr_enrl_st.crs_catlg_nbr AS crs_catlg_nbr, sr_enrl_st.stu_pos_nbr AS stu_pos_nbr, sr_enrl_st.cls_drp_if_enrl_nbr AS cls_drp_if_enrl_nbr, sr_enrl_st.acad_grp_cd AS acad_grp_cd, sr_enrl_st.acad_grp_desc AS acad_grp_desc, sr_enrl_st.crs_subj_dept_cd AS crs_subj_dept_cd, sr_enrl_st.cls_drp_crs_subj_cd AS cls_drp_crs_subj_cd, sr_enrl_st.cls_drp_crs_catlg_nbr AS cls_drp_crs_catlg_nbr, sr_enrl_st.enrl_rqst_src_lst_updt_cd AS enrl_rqst_src_lst_updt_cd, sr_enrl_st.enrl_rqst_src_lst_updt_desc AS enrl_rqst_src_lst_updt_desc, sr_enrl_st.crs_grd_inpt_cd AS crs_grd_inpt_cd, sr_enrl_st.cls_lnk_ovrd_ind AS cls_lnk_ovrd_ind, sr_enrl_st.crs_lapse_to_grd_cd AS crs_lapse_to_grd_cd, sr_enrl_st.crs_lapse_grd_stat_cd AS crs_lapse_grd_stat_cd, sr_enrl_st.crs_lapse_grd_stat_desc AS crs_lapse_grd_stat_desc, sr_enrl_st.crs_lapse_ddln_dt AS crs_lapse_ddln_dt, sr_enrl_st.cls_instr_gds_cmp_email_addr AS cls_instr_gds_cmp_email_addr, sr_enrl_st.crs_cnt AS crs_cnt, sr_enrl_st.cls_rqmt_dsgt_cd AS cls_rqmt_dsgt_cd, sr_enrl_st.cls_rqmt_dsgt_optn_ind AS cls_rqmt_dsgt_optn_ind, sr_enrl_st.cls_rqmt_dsgt_grd_cd AS cls_rqmt_dsgt_grd_cd, sr_enrl_st.tscrpt_nte_id AS tscrpt_nte_id, sr_enrl_st.cls_instr_ntwrk_id AS cls_instr_ntwrk_id, sr_enrl_st.crs_eqvl_id AS crs_eqvl_id, sr_enrl_st.cls_strt_dt AS cls_strt_dt, sr_enrl_st.cls_end_dt AS cls_end_dt, sr_enrl_st.iu_wait_type AS iu_wait_type, sr_enrl_st.cmp_loc_desc AS cmp_loc_desc, sr_enrl_st.crs_subj_iu_fin_coa_cd AS crs_subj_iu_fin_coa_cd, sr_enrl_st.crs_subj_iu_org_cd AS crs_subj_iu_org_cd, sr_enrl_st.crs_subj_iu_org_nm AS crs_subj_iu_org_nm, sr_enrl_st.crs_subj_iu_rc_cd AS crs_subj_iu_rc_cd, sr_enrl_st.crs_subj_iu_rc_nm AS crs_subj_iu_rc_nm, sr_enrl_st.stu_drvd_cls_enrl_stat_desc AS stu_drvd_cls_enrl_stat_desc, sr_enrl_st.stu_drvd_cls_enrl_stat_rptdesc AS stu_drvd_cls_enrl_stat_rptdesc, sr_enrl_st.cls_ftw_crn_nbr AS cls_ftw_crn_nbr, sr_enrl_st.cls_instr_univ_id AS cls_instr_univ_id, sr_enrl_st.stu_enrl_stat_cd AS stu_enrl_stat_cd FROM sr_enrl_st WHERE (acad_term_cd in (4148, 4152, 4155, 4158, 4162, 4165, 4168, 4172, 4175, 4178, 4182, 4185, 4188, 4192, 4195, 4198, 4205, 4208, 4212, 4215, 4202) AND inst_cd = 'IUBLA' AND acad_career_cd = 'UGRD'); /*stu_course_data*/ /* Combines SIS and Canvas enrollment details */ SELECT stu_crs_info.acad_grp_cd AS crs_acad_grp_cd, stu_crs_info.pplsft_acad_org_lvl_1_cd AS crs_pplsft_acad_org_lvl_1_cd, stu_crs_info.pplsft_acad_org_lvl_2_cd AS crs_pplsft_acad_org_lvl_2_cd, stu_crs_info.pplsft_acad_org_lvl_3_cd AS crs_pplsft_acad_org_lvl_3_cd, stu_crs_info.pplsft_acad_org_lvl_4_cd AS crs_pplsft_acad_org_lvl_4_cd, stu_crs_info.pplsft_acad_org_lvl_5_cd AS crs_pplsft_acad_org_lvl_5_cd, stu_crs_info.acad_grp_desc AS crs_acad_grp_desc, trim(stu_crs_info.crs_catlg_nbr) AS crs_catlg_nbr, stu_crs_info.acad_term_cd AS acad_term_cd, stu_crs_info.crs_subj_dept_cd AS crs_subj_dept_cd, stu_crs_info.course AS course, stu_crs_info.cls_nbr AS cls_nbr, stu_crs_info.crs_ofcl_grd_cd AS crs_ofcl_grd_cd, stu_canvas_score.est_assn_scr AS est_assn_scr, stu_canvas_score.prsn_univ_id AS prsn_univ_id, stu_canvas_score.current_score AS current_score, stu_canvas_score.sis_source_id AS sis_source_id, hash(concat(stu_crs_info.cls_instr_univ_id, stu_crs_info.acad_term_cd, stu_crs_info.course)) AS instructor_term_course_id, stu_crs_info.stu_enrl_stat_cd AS stu_enrl_stat_cd FROM stu_canvas_score AS stu_canvas_score LEFT OUTER JOIN stu_crs_info AS stu_crs_info ON (trim(stu_canvas_score.prsn_univ_id) = trim(stu_crs_info.prsn_univ_id) AND trim(stu_canvas_score.acad_term_cd) = trim(stu_crs_info.acad_term_cd) AND trim(stu_canvas_score.cls_nbr) = trim(stu_crs_info.cls_nbr)) WHERE (stu_crs_info.crs_ofcl_grd_cd not in ('NC', 'NY', 'R', 'ZZ') AND stu_crs_info.crs_ofcl_grd_cd is not null ); /***************************************** Demographic, Program, & Degree Information *****************************/ /* Create view of application information */ SELECT DISTINCT ir_adm_appl_st.prsn_univ_id AS prsn_univ_id, ir_adm_appl_st.appl_acad_pgm_cd AS appl_acad_pgm_cd, ir_adm_appl_st.appl_acad_pgm_desc AS appl_acad_pgm_desc, ir_adm_appl_st.appl_acad_grp_cd AS appl_acad_grp_cd, ir_adm_appl_st.appl_acad_grp_desc AS appl_acad_grp_desc, ir_adm_appl_st.appl_plan_1_cd AS appl_plan_1_cd, ir_adm_appl_st.appl_plan_1_desc AS appl_plan_1_desc, ir_adm_appl_st.appl_plan_2_cd AS appl_plan_2_cd, ir_adm_appl_st.appl_plan_2_desc AS appl_plan_2_desc, ir_adm_appl_st.appl_plan_3_cd AS appl_plan_3_cd, ir_adm_appl_st.appl_plan_3_desc AS appl_plan_3_desc, ir_adm_appl_st.appl_acad_career_cd AS appl_acad_career_cd, ir_adm_appl_st.appl_nbr AS appl_nbr, ir_adm_appl_st.appl_pgm_nbr AS appl_pgm_nbr, ir_adm_appl_st.appl_admt_term_cd AS appl_admt_term_cd, ir_adm_appl_st.proc_run_dt AS proc_run_dt, ir_adm_appl_st.appl_drvd_admt_term_cd AS appl_drvd_admt_term_cd, ir_adm_appl_st.proc_wk_of_clndr_yr_nbr AS proc_wk_of_clndr_yr_nbr, ir_adm_appl_st.weeks_from_census AS weeks_from_census, ir_adm_appl_st.days_from_census AS days_from_census, ir_adm_appl_st.acad_term_prd_desc AS acad_term_prd_desc, ir_adm_appl_st.row_src_desc AS row_src_desc, ir_adm_appl_st.appl_admt_typ_cd AS appl_admt_typ_cd, ir_adm_appl_st.appl_admt_typ_desc AS appl_admt_typ_desc, ir_adm_appl_st.appl_inst_cd AS appl_inst_cd, ir_adm_appl_st.ir_rpt_inst_cd AS ir_rpt_inst_cd, ir_adm_appl_st.ir_drvd_acad_career_cd AS ir_drvd_acad_career_cd, ir_adm_appl_st.ir_drvd_acad_career_desc AS ir_drvd_acad_career_desc, ir_adm_appl_st.ir_drvd_admt_term_cd AS ir_drvd_admt_term_cd, ir_adm_appl_st.ir_drvd_admt_typ_cd AS ir_drvd_admt_typ_cd, ir_adm_appl_st.admt_trm_appl_dt AS admt_trm_appl_dt, ir_adm_appl_st.admt_trm_admt_dt AS admt_trm_admt_dt, ir_adm_appl_st.admt_trm_dpst_dt AS admt_trm_dpst_dt, ir_adm_appl_st.admt_trm_enrl_dt AS admt_trm_enrl_dt, ir_adm_appl_st.admt_trm_enrl_degr_skng_dt AS admt_trm_enrl_degr_skng_dt, ir_adm_appl_st.stu_drv_enrl_stat_ind AS stu_drv_enrl_stat_ind, ir_adm_appl_st.ir_appl_dt AS ir_appl_dt, ir_adm_appl_st.ir_admt_dt AS ir_admt_dt, ir_adm_appl_st.ir_dpst_dt AS ir_dpst_dt, ir_adm_appl_st.ir_enrl_dt AS ir_enrl_dt, ir_adm_appl_st.ir_enrl_degr_skng_dt AS ir_enrl_degr_skng_dt, ir_adm_appl_st.ir_drvd_admt_trm_enrl_stat_ind AS ir_drvd_admt_trm_enrl_stat_ind, ir_adm_appl_st.ir_drvd_admt_trm_prm_pgm_cd AS ir_drvd_admt_trm_prm_pgm_cd, ir_adm_appl_st.ir_drvd_admt_trm_plan_1_cd AS ir_drvd_admt_trm_plan_1_cd, ir_adm_appl_st.ir_drvd_admt_trm_census_dt AS ir_drvd_admt_trm_census_dt, ir_adm_appl_st.ir_drvd_admt_trm_cen_enrl_ind AS ir_drvd_admt_trm_cen_enrl_ind, ir_adm_appl_st.acad_drvd_cohort_cd AS acad_drvd_cohort_cd, ir_adm_appl_st.acad_drvd_cohort_inst_cd AS acad_drvd_cohort_inst_cd, ir_adm_appl_st.prsn_ferpa_cmplt_rstrct_ind AS prsn_ferpa_cmplt_rstrct_ind, ir_adm_appl_st.row_use_for_headcount_ind AS row_use_for_headcount_ind, ir_adm_appl_st.row_use_hdct_undupl_ind AS row_use_hdct_undupl_ind, ir_adm_appl_st.row_use_chrt_hdct_undupl_ind AS row_use_chrt_hdct_undupl_ind, ir_adm_appl_st.ir_rpt_incl_excl_reas_desc AS ir_rpt_incl_excl_reas_desc, ir_adm_appl_st.row_use_hdct_cur_appl_ind AS row_use_hdct_cur_appl_ind, ir_adm_appl_st.cur_appl_eff_dt AS cur_appl_eff_dt, ir_adm_appl_st.cur_appl_eff_seq_nbr AS cur_appl_eff_seq_nbr, ir_adm_appl_st.appl_pgm_stat_cd AS appl_pgm_stat_cd, ir_adm_appl_st.appl_pgm_stat_desc AS appl_pgm_stat_desc, ir_adm_appl_st.appl_pgm_actn_cd AS appl_pgm_actn_cd, ir_adm_appl_st.appl_pgm_actn_desc AS appl_pgm_actn_desc, ir_adm_appl_st.appl_pgm_reas_cd AS appl_pgm_reas_cd, ir_adm_appl_st.appl_pgm_reas_desc AS appl_pgm_reas_desc, ir_adm_appl_st.appl_action_dt AS appl_action_dt, ir_adm_appl_st.appl_admt_lvl_cd AS appl_admt_lvl_cd, ir_adm_appl_st.appl_admt_lvl_desc AS appl_admt_lvl_desc, ir_adm_appl_st.appl_cond_admt_ind AS appl_cond_admt_ind, ir_adm_appl_st.appl_acad_pgm_dual_cd AS appl_acad_pgm_dual_cd, ir_adm_appl_st.appl_joint_pgm_aprv_ind AS appl_joint_pgm_aprv_ind, ir_adm_appl_st.dir_admt_ind AS dir_admt_ind, ir_adm_appl_st.appl_acad_grp_intnd_cd AS appl_acad_grp_intnd_cd, ir_adm_appl_st.appl_acad_grp_intnd_desc AS appl_acad_grp_intnd_desc, ir_adm_appl_st.appl_acad_lvl_cd AS appl_acad_lvl_cd, ir_adm_appl_st.appl_plan_1_typ_desc AS appl_plan_1_typ_desc, ir_adm_appl_st.appl_plan_1_sub_plan_1_cd AS appl_plan_1_sub_plan_1_cd, ir_adm_appl_st.appl_plan_1_sub_plan_2_cd AS appl_plan_1_sub_plan_2_cd, ir_adm_appl_st.appl_plan_1_ownr_1_org_cd AS appl_plan_1_ownr_1_org_cd, ir_adm_appl_st.appl_plan_1_ownr_1_pct AS appl_plan_1_ownr_1_pct, ir_adm_appl_st.appl_plan_1_ownr_2_org_cd AS appl_plan_1_ownr_2_org_cd, ir_adm_appl_st.appl_plan_1_ownr_2_pct AS appl_plan_1_ownr_2_pct, ir_adm_appl_st.appl_plan_1_onl_ind AS appl_plan_1_onl_ind, ir_adm_appl_st.appl_plan_2_typ_desc AS appl_plan_2_typ_desc, ir_adm_appl_st.appl_plan_2_sub_plan_1_cd AS appl_plan_2_sub_plan_1_cd, ir_adm_appl_st.appl_plan_2_sub_plan_2_cd AS appl_plan_2_sub_plan_2_cd, ir_adm_appl_st.appl_plan_2_ownr_1_org_cd AS appl_plan_2_ownr_1_org_cd, ir_adm_appl_st.appl_plan_2_ownr_1_pct AS appl_plan_2_ownr_1_pct, ir_adm_appl_st.appl_plan_2_ownr_2_org_cd AS appl_plan_2_ownr_2_org_cd, ir_adm_appl_st.appl_plan_2_ownr_2_pct AS appl_plan_2_ownr_2_pct, ir_adm_appl_st.appl_plan_2_onl_ind AS appl_plan_2_onl_ind, ir_adm_appl_st.appl_plan_3_typ_desc AS appl_plan_3_typ_desc, ir_adm_appl_st.appl_plan_3_sub_plan_1_cd AS appl_plan_3_sub_plan_1_cd, ir_adm_appl_st.appl_plan_3_sub_plan_2_cd AS appl_plan_3_sub_plan_2_cd, ir_adm_appl_st.appl_plan_3_ownr_1_org_cd AS appl_plan_3_ownr_1_org_cd, ir_adm_appl_st.appl_plan_3_ownr_1_pct AS appl_plan_3_ownr_1_pct, ir_adm_appl_st.appl_plan_3_ownr_2_org_cd AS appl_plan_3_ownr_2_org_cd, ir_adm_appl_st.appl_plan_3_ownr_2_pct AS appl_plan_3_ownr_2_pct, ir_adm_appl_st.appl_plan_3_onl_ind AS appl_plan_3_onl_ind, ir_adm_appl_st.appl_cntr_cd AS appl_cntr_cd, ir_adm_appl_st.appl_cntr_desc AS appl_cntr_desc, ir_adm_appl_st.appl_fee_stat_cd AS appl_fee_stat_cd, ir_adm_appl_st.appl_fee_stat_desc AS appl_fee_stat_desc, ir_adm_appl_st.appl_fee_pd_amt AS appl_fee_pd_amt, ir_adm_appl_st.ext_adm_appl_nbr AS ext_adm_appl_nbr, ir_adm_appl_st.appl_fee_calc_stat_cd AS appl_fee_calc_stat_cd, ir_adm_appl_st.appl_fee_calc_stat_desc AS appl_fee_calc_stat_desc, ir_adm_appl_st.appl_mthd_cd AS appl_mthd_cd, ir_adm_appl_st.appl_mthd_desc AS appl_mthd_desc, ir_adm_appl_st.appl_cmplt_ind AS appl_cmplt_ind, ir_adm_appl_st.appl_cmpltn_dt AS appl_cmpltn_dt, ir_adm_appl_st.appl_web_sbmt_dt AS appl_web_sbmt_dt, ir_adm_appl_st.appl_drvd_crr_lvl_cmb_sort_nbr AS appl_drvd_crr_lvl_cmb_sort_nbr, ir_adm_appl_st.appl_drvd_expnd_lvl_nm AS appl_drvd_expnd_lvl_nm, ir_adm_appl_st.appl_drvd_ipeds_rpt_lvl_nm AS appl_drvd_ipeds_rpt_lvl_nm, ir_adm_appl_st.appl_drvd_degr_seek_ind AS appl_drvd_degr_seek_ind, ir_adm_appl_st.ir_tcnt_hist_ptcpn_ind AS ir_tcnt_hist_ptcpn_ind, ir_adm_appl_st.ir_tcnt_awd_rcpt_ind AS ir_tcnt_awd_rcpt_ind, ir_adm_appl_st.ir_hs_dplm_typ_desc AS ir_hs_dplm_typ_desc, ir_adm_appl_st.ir_hm_schl_ind AS ir_hm_schl_ind, ir_adm_appl_st.ir_frst_gen_ind AS ir_frst_gen_ind, ir_adm_appl_st.prsn_pref_full_nm AS prsn_pref_full_nm, ir_adm_appl_st.prsn_pref_email_addr AS prsn_pref_email_addr, ir_adm_appl_st.prsn_pref_phn_nbr AS prsn_pref_phn_nbr, ir_adm_appl_st.prsn_gndr_cd AS prsn_gndr_cd, ir_adm_appl_st.prsn_birth_dt AS prsn_birth_dt, ir_adm_appl_st.prsn_age_nbr AS prsn_age_nbr, ir_adm_appl_st.prsn_death_dt AS prsn_death_dt, ir_adm_appl_st.prsn_vet_ind AS prsn_vet_ind, ir_adm_appl_st.prsn_vet_fmly_ind AS prsn_vet_fmly_ind, ir_adm_appl_st.prsn_ethnic_dtl_wht_ind AS prsn_ethnic_dtl_wht_ind, ir_adm_appl_st.prsn_ethnic_dtl_blk_ind AS prsn_ethnic_dtl_blk_ind, ir_adm_appl_st.prsn_ethnic_dtl_hisp_ind AS prsn_ethnic_dtl_hisp_ind, ir_adm_appl_st.prsn_ethnic_dtl_asn_ind AS prsn_ethnic_dtl_asn_ind, ir_adm_appl_st.prsn_ethnic_dtl_amin_ind AS prsn_ethnic_dtl_amin_ind, ir_adm_appl_st.prsn_ethnic_dtl_pcfc_isldr_ind AS prsn_ethnic_dtl_pcfc_isldr_ind, ir_adm_appl_st.prsn_ethnic_vldtd_ind AS prsn_ethnic_vldtd_ind, ir_adm_appl_st.prsn_ethnic_hisp_ind AS prsn_ethnic_hisp_ind, ir_adm_appl_st.prsn_drvd_adm_ethnic_cd AS prsn_drvd_adm_ethnic_cd, ir_adm_appl_st.prsn_drvd_adm_ethnic_desc AS prsn_drvd_adm_ethnic_desc, ir_adm_appl_st.prsn_vprmt_typ_cd AS prsn_vprmt_typ_cd, ir_adm_appl_st.prsn_2nd_ctzn_cntry_cd AS prsn_2nd_ctzn_cntry_cd, ir_adm_appl_st.prsn_2nd_ctzn_cntry_nm AS prsn_2nd_ctzn_cntry_nm, ir_adm_appl_st.stu_cnty_srvc_rgn_ind AS stu_cnty_srvc_rgn_ind, ir_adm_appl_st.stu_hs_cnty_srvc_rgn_ind AS stu_hs_cnty_srvc_rgn_ind, ir_adm_appl_st.stu_msep_elig_ind AS stu_msep_elig_ind, ir_adm_appl_st.stu_msep_pmt_ind AS stu_msep_pmt_ind, ir_adm_appl_st.stu_msea_elig_ind AS stu_msea_elig_ind, ir_adm_appl_st.stu_msea_pmt_ind AS stu_msea_pmt_ind, ir_adm_appl_st.stu_ksea_elig_ind AS stu_ksea_elig_ind, ir_adm_appl_st.stu_ksea_pmt_ind AS stu_ksea_pmt_ind, ir_adm_appl_st.stu_isea_elig_ind AS stu_isea_elig_ind, ir_adm_appl_st.stu_isea_pmt_ind AS stu_isea_pmt_ind, ir_adm_appl_st.fed_trio_sss_stu_grp_stat_cd AS fed_trio_sss_stu_grp_stat_cd, ir_adm_appl_st.fed_trio_sss_stu_grp_stat_desc AS fed_trio_sss_stu_grp_stat_desc, ir_adm_appl_st.stu_gen_ed_core_src_cd AS stu_gen_ed_core_src_cd, ir_adm_appl_st.stu_gen_ed_core_src_desc AS stu_gen_ed_core_src_desc, ir_adm_appl_st.stu_gen_ed_core_inst_nm AS stu_gen_ed_core_inst_nm, ir_adm_appl_st.ir_drvd_intl_ind AS ir_drvd_intl_ind, ir_adm_appl_st.ir_drvd_adm_intl_ind AS ir_drvd_adm_intl_ind, ir_adm_appl_st.appl_ofcl_res_cd AS appl_ofcl_res_cd, ir_adm_appl_st.appl_ofcl_tuit_res_cd AS appl_ofcl_tuit_res_cd, ir_adm_appl_st.appl_ofcl_tuit_res_excpn_cd AS appl_ofcl_tuit_res_excpn_cd, ir_adm_appl_st.appl_assmd_res_desc AS appl_assmd_res_desc, ir_adm_appl_st.ir_res_cd AS ir_res_cd, ir_adm_appl_st.ir_res_desc AS ir_res_desc, ir_adm_appl_st.ir_assmd_res_desc AS ir_assmd_res_desc, ir_adm_appl_st.placement_test_ind AS placement_test_ind, ir_adm_appl_st.ap_test_credit_ind AS ap_test_credit_ind, ir_adm_appl_st.ap_test_credit_hrs AS ap_test_credit_hrs, ir_adm_appl_st.other_test_credit_ind AS other_test_credit_ind, ir_adm_appl_st.other_test_credit_hrs AS other_test_credit_hrs, ir_adm_appl_st.dc_credit_ind AS dc_credit_ind, ir_adm_appl_st.dc_credit_hrs AS dc_credit_hrs, ir_adm_appl_st.xfr_credit_ind AS xfr_credit_ind, ir_adm_appl_st.xfr_credit_hrs AS xfr_credit_hrs, ir_adm_appl_st.appl_test_optn_rcrt_ctgry_val AS appl_test_optn_rcrt_ctgry_val, ir_adm_appl_st.stu_drvd_sat_composite_nbr AS stu_drvd_sat_composite_nbr, ir_adm_appl_st.stu_drvd_sat_or_act_cnv_ind AS stu_drvd_sat_or_act_cnv_ind, ir_adm_appl_st.stu_sat_calc_composite_nbr AS stu_sat_calc_composite_nbr, ir_adm_appl_st.stu_act_to_sat_max_nbr AS stu_act_to_sat_max_nbr, ir_adm_appl_st.stu_agncy_drvd_sat_cmpst_nbr AS stu_agncy_drvd_sat_cmpst_nbr, ir_adm_appl_st.stu_agncy_drvd_sat_act_cnv_ind AS stu_agncy_drvd_sat_act_cnv_ind, ir_adm_appl_st.stu_agncy_sat_calc_cmpst_nbr AS stu_agncy_sat_calc_cmpst_nbr, ir_adm_appl_st.stu_agncy_act_to_sat_max_nbr AS stu_agncy_act_to_sat_max_nbr, ir_adm_appl_st.stu_2019_drvd_sat_cmpst_nbr AS stu_2019_drvd_sat_cmpst_nbr, ir_adm_appl_st.stu_2019_drvd_src_cnv_ind AS stu_2019_drvd_src_cnv_ind, ir_adm_appl_st.stu_2019_sat_calc_nbr AS stu_2019_sat_calc_nbr, ir_adm_appl_st.stu_2019_act2sat_max_nbr AS stu_2019_act2sat_max_nbr, ir_adm_appl_st.stu_agncy2019_drv_satcmpst_nbr AS stu_agncy2019_drv_satcmpst_nbr, ir_adm_appl_st.stu_agncy2019_drvd_src_cnv_ind AS stu_agncy2019_drvd_src_cnv_ind, ir_adm_appl_st.stu_agncy2019_sat_calc_nbr AS stu_agncy2019_sat_calc_nbr, ir_adm_appl_st.stu_agncy2019_act2sat_max_nbr AS stu_agncy2019_act2sat_max_nbr, ir_adm_appl_st.stu_agslsc_2019_satco_nbr AS stu_agslsc_2019_satco_nbr, ir_adm_appl_st.stu_agslsc_2019_src_cnv_ind AS stu_agslsc_2019_src_cnv_ind, ir_adm_appl_st.stu_agslsc_2019_sat_calc_nbr AS stu_agslsc_2019_sat_calc_nbr, ir_adm_appl_st.stu_agslsc_2019_act2sat_maxnbr AS stu_agslsc_2019_act2sat_maxnbr, ir_adm_appl_st.stu_agsl_2019_satco_nbr AS stu_agsl_2019_satco_nbr, ir_adm_appl_st.stu_agsl_2019_drvd_src_cnv_ind AS stu_agsl_2019_drvd_src_cnv_ind, ir_adm_appl_st.stu_agsl_2019_sat_calc_nbr AS stu_agsl_2019_sat_calc_nbr, ir_adm_appl_st.stu_agsl_2019_act2sat_max_nbr AS stu_agsl_2019_act2sat_max_nbr, ir_adm_appl_st.stu_sat_v_scr_nbr AS stu_sat_v_scr_nbr, ir_adm_appl_st.stu_sat_m_scr_nbr AS stu_sat_m_scr_nbr, ir_adm_appl_st.stu_sat_w_scr_nbr AS stu_sat_w_scr_nbr, ir_adm_appl_st.stu_act_e_scr_nbr AS stu_act_e_scr_nbr, ir_adm_appl_st.stu_act_m_scr_nbr AS stu_act_m_scr_nbr, ir_adm_appl_st.stu_act_c_scr_nbr AS stu_act_c_scr_nbr, ir_adm_appl_st.stu_act_r_scr_nbr AS stu_act_r_scr_nbr, ir_adm_appl_st.stu_act_s_scr_nbr AS stu_act_s_scr_nbr, ir_adm_appl_st.stu_act_w_scr_nbr AS stu_act_w_scr_nbr, ir_adm_appl_st.stu_act_ew_scr_nbr AS stu_act_ew_scr_nbr, ir_adm_appl_st.stu_act_ela_scr_nbr AS stu_act_ela_scr_nbr, ir_adm_appl_st.stu_act_stem_scr_nbr AS stu_act_stem_scr_nbr, ir_adm_appl_st.stu_act_ws_scr_nbr AS stu_act_ws_scr_nbr, ir_adm_appl_st.stu_agncy_sat_v_scr_nbr AS stu_agncy_sat_v_scr_nbr, ir_adm_appl_st.stu_agncy_sat_m_scr_nbr AS stu_agncy_sat_m_scr_nbr, ir_adm_appl_st.stu_agncy_sat_w_scr_nbr AS stu_agncy_sat_w_scr_nbr, ir_adm_appl_st.stu_agncy_act_e_scr_nbr AS stu_agncy_act_e_scr_nbr, ir_adm_appl_st.stu_agncy_act_m_scr_nbr AS stu_agncy_act_m_scr_nbr, ir_adm_appl_st.stu_agncy_act_c_scr_nbr AS stu_agncy_act_c_scr_nbr, ir_adm_appl_st.stu_agncy_act_r_scr_nbr AS stu_agncy_act_r_scr_nbr, ir_adm_appl_st.stu_agncy_act_s_scr_nbr AS stu_agncy_act_s_scr_nbr, ir_adm_appl_st.stu_agncy_act_w_scr_nbr AS stu_agncy_act_w_scr_nbr, ir_adm_appl_st.stu_agncy_act_ew_scr_nbr AS stu_agncy_act_ew_scr_nbr, ir_adm_appl_st.stu_agncy_act_ela_scr_nbr AS stu_agncy_act_ela_scr_nbr, ir_adm_appl_st.stu_agncy_act_stem_scr_nbr AS stu_agncy_act_stem_scr_nbr, ir_adm_appl_st.stu_agncy_act_ws_scr_nbr AS stu_agncy_act_ws_scr_nbr, ir_adm_appl_st.stu_sat_erws_scr_nbr AS stu_sat_erws_scr_nbr, ir_adm_appl_st.stu_sat_mss_scr_nbr AS stu_sat_mss_scr_nbr, ir_adm_appl_st.stu_drvd_sat_erws_scr_nbr AS stu_drvd_sat_erws_scr_nbr, ir_adm_appl_st.stu_drvd_sat_erws_source AS stu_drvd_sat_erws_source, ir_adm_appl_st.stu_drvd_sat_mss_scr_nbr AS stu_drvd_sat_mss_scr_nbr, ir_adm_appl_st.stu_drvd_sat_mss_source AS stu_drvd_sat_mss_source, ir_adm_appl_st.stu_tst_scr_gmat_to_nbr AS stu_tst_scr_gmat_to_nbr, ir_adm_appl_st.stu_tst_scr_gmat_qu_nbr AS stu_tst_scr_gmat_qu_nbr, ir_adm_appl_st.stu_tst_scr_gmat_ve_nbr AS stu_tst_scr_gmat_ve_nbr, ir_adm_appl_st.stu_tst_scr_gre_w_nbr AS stu_tst_scr_gre_w_nbr, ir_adm_appl_st.stu_tst_scr_gre_qu_nbr AS stu_tst_scr_gre_qu_nbr, ir_adm_appl_st.stu_tst_scr_gre_ve_nbr AS stu_tst_scr_gre_ve_nbr, ir_adm_appl_st.stu_tst_scr_gre_venw_vrbl_nbr AS stu_tst_scr_gre_venw_vrbl_nbr, ir_adm_appl_st.stu_tst_scr_gre_qunw_nbr AS stu_tst_scr_gre_qunw_nbr, ir_adm_appl_st.stu_drvd_tst_gre_venw_vrbl_nbr AS stu_drvd_tst_gre_venw_vrbl_nbr, ir_adm_appl_st.stu_drvd_tst_gre_qunw_nbr AS stu_drvd_tst_gre_qunw_nbr, ir_adm_appl_st.stu_tst_scr_gre_wr_nbr AS stu_tst_scr_gre_wr_nbr, ir_adm_appl_st.stu_tst_scr_lsat_to_nbr AS stu_tst_scr_lsat_to_nbr, ir_adm_appl_st.stu_hs_org_id AS stu_hs_org_id, ir_adm_appl_st.stu_hs_nm AS stu_hs_nm, ir_adm_appl_st.stu_hs_grad_dt AS stu_hs_grad_dt, ir_adm_appl_st.stu_hs_cty_nm AS stu_hs_cty_nm, ir_adm_appl_st.stu_hs_cnty_nm AS stu_hs_cnty_nm, ir_adm_appl_st.stu_hs_st_cd AS stu_hs_st_cd, ir_adm_appl_st.stu_hs_zip_cd AS stu_hs_zip_cd, ir_adm_appl_st.stu_hs_cntry_cd AS stu_hs_cntry_cd, ir_adm_appl_st.stu_hs_cls_rnk_nbr AS stu_hs_cls_rnk_nbr, ir_adm_appl_st.stu_hs_cls_sz_nbr AS stu_hs_cls_sz_nbr, ir_adm_appl_st.stu_hs_pctl_nbr AS stu_hs_pctl_nbr, ir_adm_appl_st.stu_hs_sum_cnvrt_gpa_nbr AS stu_hs_sum_cnvrt_gpa_nbr, ir_adm_appl_st.stu_hs_unofcl_cnvrt_gpa_nbr AS stu_hs_unofcl_cnvrt_gpa_nbr, ir_adm_appl_st.ir_stu_hs_ext_org_ctgry_cd AS ir_stu_hs_ext_org_ctgry_cd, ir_adm_appl_st.stu_hs_dta_src_desc AS stu_hs_dta_src_desc, ir_adm_appl_st.stu_hs_iu_gpa_flag AS stu_hs_iu_gpa_flag, ir_adm_appl_st.stu_hs_iu_campus_gpa_nbr AS stu_hs_iu_campus_gpa_nbr, ir_adm_appl_st.stu_trnfr_col_org_id AS stu_trnfr_col_org_id, ir_adm_appl_st.stu_trnfr_col_nm AS stu_trnfr_col_nm, ir_adm_appl_st.stu_trnfr_col_schl_typ_cd AS stu_trnfr_col_schl_typ_cd, ir_adm_appl_st.stu_trnfr_col_atnd_to_dt AS stu_trnfr_col_atnd_to_dt, ir_adm_appl_st.stu_trnfr_col_cty_nm AS stu_trnfr_col_cty_nm, ir_adm_appl_st.stu_trnfr_col_cnty_nm AS stu_trnfr_col_cnty_nm, ir_adm_appl_st.stu_trnfr_col_st_cd AS stu_trnfr_col_st_cd, ir_adm_appl_st.stu_trnfr_col_st_nm AS stu_trnfr_col_st_nm, ir_adm_appl_st.stu_trnfr_col_zip_cd AS stu_trnfr_col_zip_cd, ir_adm_appl_st.stu_trnfr_col_cntry_cd AS stu_trnfr_col_cntry_cd, ir_adm_appl_st.stu_trnfr_col_sum_gpa_typ_cd AS stu_trnfr_col_sum_gpa_typ_cd, ir_adm_appl_st.stu_trnfr_col_sum_gpa_nbr AS stu_trnfr_col_sum_gpa_nbr, ir_adm_appl_st.stu_trnfr_col_sum_cnv_gpa_nbr AS stu_trnfr_col_sum_cnv_gpa_nbr, ir_adm_appl_st.ext_degr_org_descr AS ext_degr_org_descr, ir_adm_appl_st.ext_degr_descr AS ext_degr_descr, ir_adm_appl_st.ext_degr_education_lvl_desc AS ext_degr_education_lvl_desc, ir_adm_appl_st.max_trnsc_cmpl_dt AS max_trnsc_cmpl_dt, ir_adm_appl_st.max_fnl_trnsc_cmpl_dt AS max_fnl_trnsc_cmpl_dt, ir_adm_appl_st.iu_degr AS iu_degr, ir_adm_appl_st.prsn_appl_dt_hm_ln1_addr AS prsn_appl_dt_hm_ln1_addr, ir_adm_appl_st.prsn_appl_dt_hm_ln2_addr AS prsn_appl_dt_hm_ln2_addr, ir_adm_appl_st.prsn_appl_dt_hm_cnty_cd AS prsn_appl_dt_hm_cnty_cd, ir_adm_appl_st.prsn_appl_dt_hm_cnty_nm AS prsn_appl_dt_hm_cnty_nm, ir_adm_appl_st.prsn_appl_dt_hm_city_nm AS prsn_appl_dt_hm_city_nm, ir_adm_appl_st.prsn_appl_dt_hm_st_cd AS prsn_appl_dt_hm_st_cd, ir_adm_appl_st.prsn_appl_dt_hm_st_nm AS prsn_appl_dt_hm_st_nm, ir_adm_appl_st.prsn_appl_dt_hm_zip_cd AS prsn_appl_dt_hm_zip_cd, ir_adm_appl_st.prsn_appl_dt_hm_cntry_cd AS prsn_appl_dt_hm_cntry_cd, ir_adm_appl_st.prsn_appl_dt_hm_cntry_nm AS prsn_appl_dt_hm_cntry_nm, ir_adm_appl_st.prsn_appl_dt_hm_to_inst_mi AS prsn_appl_dt_hm_to_inst_mi, ir_adm_appl_st.prsn_cohort_ln1_addr AS prsn_cohort_ln1_addr, ir_adm_appl_st.prsn_cohort_ln2_addr AS prsn_cohort_ln2_addr, ir_adm_appl_st.prsn_cohort_cnty_cd AS prsn_cohort_cnty_cd, ir_adm_appl_st.prsn_cohort_cnty_nm AS prsn_cohort_cnty_nm, ir_adm_appl_st.prsn_cohort_city_nm AS prsn_cohort_city_nm, ir_adm_appl_st.prsn_cohort_st_cd AS prsn_cohort_st_cd, ir_adm_appl_st.prsn_cohort_st_nm AS prsn_cohort_st_nm, ir_adm_appl_st.prsn_cohort_zip_cd AS prsn_cohort_zip_cd, ir_adm_appl_st.prsn_cohort_cntry_cd AS prsn_cohort_cntry_cd, ir_adm_appl_st.prsn_cohort_cntry_nm AS prsn_cohort_cntry_nm, ir_adm_appl_st.max_appl_stat_bl AS max_appl_stat_bl, ir_adm_appl_st.max_appl_stat_co AS max_appl_stat_co, ir_adm_appl_st.max_appl_stat_ea AS max_appl_stat_ea, ir_adm_appl_st.max_appl_stat_fw AS max_appl_stat_fw, ir_adm_appl_st.max_appl_stat_in AS max_appl_stat_in, ir_adm_appl_st.max_appl_stat_ko AS max_appl_stat_ko, ir_adm_appl_st.max_appl_stat_nw AS max_appl_stat_nw, ir_adm_appl_st.max_appl_stat_sb AS max_appl_stat_sb, ir_adm_appl_st.max_appl_stat_se AS max_appl_stat_se, ir_adm_appl_st.multi_app_cnt AS multi_app_cnt, ir_adm_appl_st.appl_rgn_lvl4_cd AS appl_rgn_lvl4_cd, ir_adm_appl_st.appl_rgn_cd AS appl_rgn_cd, ir_adm_appl_st.adm_rcrt_cat_athl_desc AS adm_rcrt_cat_athl_desc, ir_adm_appl_st.adm_rcrt_cat_dvrst_desc AS adm_rcrt_cat_dvrst_desc, ir_adm_appl_st.adm_rcrt_cat_lgcy_fmly_desc AS adm_rcrt_cat_lgcy_fmly_desc, ir_adm_appl_st.adm_rcrt_cat_vet_mil_desc AS adm_rcrt_cat_vet_mil_desc, ir_adm_appl_st.adm_rcrt_cat_bhvr_desc AS adm_rcrt_cat_bhvr_desc, ir_adm_appl_st.adm_rcrt_cat_trnscrpt_desc AS adm_rcrt_cat_trnscrpt_desc, ir_adm_appl_st.adm_chkl_cnslr_rcmnd_ltr_dt AS adm_chkl_cnslr_rcmnd_ltr_dt, ir_adm_appl_st.adm_chkl_7th_sem_grd_dt AS adm_chkl_7th_sem_grd_dt, ir_adm_appl_st.adm_chkl_hs_trnsc_dt AS adm_chkl_hs_trnsc_dt, ir_adm_appl_st.adm_chkl_coll_trnsc_dt AS adm_chkl_coll_trnsc_dt, ir_adm_appl_st.adm_chkl_fee_wvr_dt AS adm_chkl_fee_wvr_dt, ir_adm_appl_st.adm_chkl_sat_act_dt AS adm_chkl_sat_act_dt, ir_adm_appl_st.adm_chkl_new_tst_scores_dt AS adm_chkl_new_tst_scores_dt, ir_adm_appl_st.sf_id AS sf_id, ir_adm_appl_st.intent AS intent, ir_adm_appl_st.last_upd_dttm AS last_upd_dttm FROM ir_adm_appl_st WHERE (appl_acad_career_cd = 'UGRD' AND appl_admt_lvl_cd in (5) AND appl_plan_1_desc not in ('Post Baccalaureate Courses', 'Nondegree Undergraduate') AND appl_acad_pgm_cd not in ('NDEG0', 'NDEG1', 'NDEG2') AND appl_inst_cd = 'IUBLA' AND appl_admt_typ_cd not in ('FYD', 'FYL', 'FYM', 'HSS', 'ICG', 'RTG', 'TRG', 'VSG', 'VSU') AND appl_admt_typ_cd is not null ); /* stu_degr_stat gets degree information for students if degree has been awareded-excluding BAs*/ SELECT sr_stu_degr_gt.prsn_univ_id AS prsn_univ_id, sr_stu_degr_gt.stu_degr_ckot_stat_cd AS stu_degr_ckot_stat_cd, sr_stu_degr_gt.acad_degr_cd AS acad_degr_cd, sr_stu_degr_gt.acad_degr_desc AS acad_degr_desc, sr_stu_degr_gt.acad_plan_mjr1_degr_cd AS degr_acad_plan_mjr1_degr_cd, sr_stu_degr_gt.acad_plan_mjr1_pgm_cd AS degr_acad_plan_mjr1_pgm_cd, sr_stu_degr_gt.acad_plan_mjr1_pgm_desc AS degr_acad_plan_mjr1_pgm_desc, sr_stu_degr_gt.acad_plan_mjr1_grp_cd AS degr_acad_plan_mjr1_grp_cd, sr_stu_degr_gt.acad_plan_mjr1_grp_desc AS degr_acad_plan_mjr1_grp_desc, sr_stu_degr_gt.acad_plan_mjr2_cd AS degr_acad_plan_mjr2_cd, sr_stu_degr_gt.acad_plan_mjr2_desc AS degr_acad_plan_mjr2_desc, sr_stu_degr_gt.acad_plan_mjr2_degr_cd AS degr_acad_plan_mjr2_degr_cd, sr_stu_degr_gt.acad_plan_mjr2_degr_ed_lvl_cd AS degr_acad_plan_mjr2_degr_ed_lvl_cd, sr_stu_degr_gt.acad_plan_mjr2_pgm_cd AS degr_acad_plan_mjr2_pgm_cd, sr_stu_degr_gt.acad_plan_mjr2_pgm_desc AS degr_acad_plan_mjr2_pgm_desc, sr_stu_degr_gt.acad_plan_mjr2_grp_cd AS degr_acad_plan_mjr2_grp_cd, sr_stu_degr_gt.acad_plan_mjr2_grp_desc AS degr_acad_plan_mjr2_grp_desc, sr_stu_degr_gt.acad_plan_mjr3_cd AS degr_acad_plan_mjr3_cd, sr_stu_degr_gt.acad_plan_mjr3_desc AS degr_acad_plan_mjr3_desc, sr_stu_degr_gt.acad_plan_mjr3_degr_cd AS degr_acad_plan_mjr3_degr_cd, sr_stu_degr_gt.acad_plan_mjr3_degr_ed_lvl_cd AS degr_acad_plan_mjr3_degr_ed_lvl_cd, sr_stu_degr_gt.acad_plan_mjr3_pgm_cd AS degr_acad_plan_mjr3_pgm_cd, sr_stu_degr_gt.acad_plan_mjr3_pgm_desc AS degr_acad_plan_mjr3_pgm_desc, sr_stu_degr_gt.acad_plan_mjr3_grp_cd AS degr_acad_plan_mjr3_grp_cd, sr_stu_degr_gt.acad_plan_mjr3_grp_desc AS degr_acad_plan_mjr3_grp_desc, sr_stu_degr_gt.stu_expct_grad_term_desc AS degr_stu_expct_grad_term_desc, sr_stu_degr_gt.stu_expct_grad_term_cd AS degr_stu_expct_grad_term_cd, sr_stu_degr_gt.stu_degr_cmpltn_term_cd AS stu_degr_cmpltn_term_cd, sr_stu_degr_gt.acad_plan_mjr1_cd AS acad_plan_mjr1_cd, sr_stu_degr_gt.acad_plan_mjr1_desc AS acad_plan_mjr1_desc, sr_stu_degr_gt.stu_degr_cmpltn_term_desc AS stu_degr_cmpltn_term_desc FROM sr_stu_degr_gt WHERE (stu_degr_cmpltn_term_cd in (4148, 4152, 4155, 4158, 4162, 4165, 4168, 4172, 4175, 4178, 4182, 4185, 4188, 4192, 4195, 4198, 4205, 4208, 4212, 4215) AND acad_career_cd = 'UGRD' AND acad_degr_ed_lvl_cd in (2, 4) AND inst_cd = 'IUBLA'); /*stu_degr_ba_dtls-creates view of BA degrees awarded */ SELECT ir_cen_trm_snpsht_gt.prsn_univ_id AS prsn_univ_id, ir_cen_trm_snpsht_gt.acad_prm_plan_1_cd AS acad_prm_plan_1_cd, ir_cen_trm_snpsht_gt.acad_prm_plan_1_desc AS acad_prm_plan_1_desc, ir_cen_trm_snpsht_gt.acad_prm_plan_1_org_cd AS acad_prm_plan_1_org_cd, ir_cen_trm_snpsht_gt.acad_prm_plan_1_org_desc AS acad_prm_plan_1_org_desc, ir_cen_trm_snpsht_gt.acad_prm_plan_2_cd AS acad_prm_plan_2_cd, ir_cen_trm_snpsht_gt.acad_prm_plan_2_desc AS acad_prm_plan_2_desc, ir_cen_trm_snpsht_gt.acad_prm_plan_2_org_cd AS acad_prm_plan_2_org_cd, ir_cen_trm_snpsht_gt.acad_prm_plan_2_org_desc AS acad_prm_plan_2_org_desc, ir_cen_trm_snpsht_gt.acad_prm_plan_3_cd AS acad_prm_plan_3_cd, ir_cen_trm_snpsht_gt.acad_prm_plan_3_desc AS acad_prm_plan_3_desc, ir_cen_trm_snpsht_gt.acad_prm_plan_3_org_cd AS acad_prm_plan_3_org_cd, ir_cen_trm_snpsht_gt.acad_prm_plan_3_org_desc AS acad_prm_plan_3_org_desc, sr_stu_degr_gt.acad_plan_mjr1_degr_cd AS acad_plan_mjr1_degr_cd, sr_stu_degr_gt.acad_plan_mjr1_degr_ed_lvl_cd AS acad_plan_mjr1_degr_ed_lvl_cd, sr_stu_degr_gt.acad_plan_mjr1_pgm_cd AS acad_plan_mjr1_pgm_cd, sr_stu_degr_gt.acad_plan_mjr1_pgm_desc AS acad_plan_mjr1_pgm_desc, sr_stu_degr_gt.acad_plan_mjr1_grp_cd AS acad_plan_mjr1_grp_cd, sr_stu_degr_gt.acad_plan_mjr1_grp_desc AS acad_plan_mjr1_grp_desc, sr_stu_degr_gt.acad_sub_plan_mjr1_cd AS acad_sub_plan_mjr1_cd, sr_stu_degr_gt.acad_sub_plan_mjr1_desc AS acad_sub_plan_mjr1_desc, sr_stu_degr_gt.acad_subpln_mjr1_hons_pfx_cd AS acad_subpln_mjr1_hons_pfx_cd, sr_stu_degr_gt.acad_subpln_mjr1_hons_pfx_desc AS acad_subpln_mjr1_hons_pfx_desc, sr_stu_degr_gt.acad_subpln_mjr1_hons_sfx_cd AS acad_subpln_mjr1_hons_sfx_cd, sr_stu_degr_gt.acad_subpln_mjr1_hons_sfx_desc AS acad_subpln_mjr1_hons_sfx_desc, sr_stu_degr_gt.acad_plan_mjr2_cd AS acad_plan_mjr2_cd, sr_stu_degr_gt.acad_plan_mjr2_desc AS acad_plan_mjr2_desc, sr_stu_degr_gt.acad_plan_mjr2_seq_nbr AS acad_plan_mjr2_seq_nbr, sr_stu_degr_gt.acad_plan_mjr2_dplm_desc AS acad_plan_mjr2_dplm_desc, sr_stu_degr_gt.acad_plan_mjr2_hons_pfx_cd AS acad_plan_mjr2_hons_pfx_cd, sr_stu_degr_gt.acad_plan_mjr2_hons_pfx_desc AS acad_plan_mjr2_hons_pfx_desc, sr_stu_degr_gt.acad_plan_mjr2_hons_sfx_cd AS acad_plan_mjr2_hons_sfx_cd, sr_stu_degr_gt.acad_plan_mjr2_hons_sfx_desc AS acad_plan_mjr2_hons_sfx_desc, sr_stu_degr_gt.acad_plan_mjr2_degr_cd AS acad_plan_mjr2_degr_cd, sr_stu_degr_gt.acad_plan_mjr2_degr_ed_lvl_cd AS acad_plan_mjr2_degr_ed_lvl_cd, sr_stu_degr_gt.acad_plan_mjr2_pgm_cd AS acad_plan_mjr2_pgm_cd, sr_stu_degr_gt.acad_plan_mjr2_pgm_desc AS acad_plan_mjr2_pgm_desc, sr_stu_degr_gt.acad_plan_mjr2_grp_cd AS acad_plan_mjr2_grp_cd, sr_stu_degr_gt.acad_plan_mjr2_grp_desc AS acad_plan_mjr2_grp_desc, sr_stu_degr_gt.acad_sub_plan_mjr2_cd AS acad_sub_plan_mjr2_cd, sr_stu_degr_gt.acad_sub_plan_mjr2_desc AS acad_sub_plan_mjr2_desc, sr_stu_degr_gt.acad_subpln_mjr2_hons_pfx_cd AS acad_subpln_mjr2_hons_pfx_cd, sr_stu_degr_gt.acad_subpln_mjr2_hons_pfx_desc AS acad_subpln_mjr2_hons_pfx_desc, sr_stu_degr_gt.acad_subpln_mjr2_hons_sfx_cd AS acad_subpln_mjr2_hons_sfx_cd, sr_stu_degr_gt.acad_subpln_mjr2_hons_sfx_desc AS acad_subpln_mjr2_hons_sfx_desc, sr_stu_degr_gt.acad_plan_mjr3_cd AS acad_plan_mjr3_cd, sr_stu_degr_gt.acad_plan_mjr3_desc AS acad_plan_mjr3_desc, sr_stu_degr_gt.acad_plan_mjr3_seq_nbr AS acad_plan_mjr3_seq_nbr, sr_stu_degr_gt.acad_plan_mjr3_dplm_desc AS acad_plan_mjr3_dplm_desc, sr_stu_degr_gt.acad_plan_mjr3_hons_pfx_cd AS acad_plan_mjr3_hons_pfx_cd, sr_stu_degr_gt.acad_plan_mjr3_hons_pfx_desc AS acad_plan_mjr3_hons_pfx_desc, sr_stu_degr_gt.acad_plan_mjr3_hons_sfx_cd AS acad_plan_mjr3_hons_sfx_cd, sr_stu_degr_gt.acad_sub_plan_mjr3_cd AS acad_sub_plan_mjr3_cd, sr_stu_degr_gt.acad_plan_mjr3_hons_sfx_desc AS acad_plan_mjr3_hons_sfx_desc, sr_stu_degr_gt.acad_sub_plan_mjr3_desc AS acad_sub_plan_mjr3_desc, sr_stu_degr_gt.acad_subpln_mjr3_hons_pfx_cd AS acad_subpln_mjr3_hons_pfx_cd, sr_stu_degr_gt.acad_subpln_mjr3_hons_pfx_desc AS acad_subpln_mjr3_hons_pfx_desc, sr_stu_degr_gt.acad_subpln_mjr3_hons_sfx_cd AS acad_subpln_mjr3_hons_sfx_cd, sr_stu_degr_gt.acad_subpln_mjr3_hons_sfx_desc AS acad_subpln_mjr3_hons_sfx_desc, sr_stu_degr_gt.acad_plan_mjr3_degr_cd AS acad_plan_mjr3_degr_cd, sr_stu_degr_gt.acad_plan_mjr3_degr_ed_lvl_cd AS acad_plan_mjr3_degr_ed_lvl_cd, sr_stu_degr_gt.acad_plan_mjr3_pgm_cd AS acad_plan_mjr3_pgm_cd, sr_stu_degr_gt.acad_plan_mjr3_pgm_desc AS acad_plan_mjr3_pgm_desc, sr_stu_degr_gt.acad_plan_mjr3_grp_cd AS acad_plan_mjr3_grp_cd, sr_stu_degr_gt.acad_plan_mjr3_grp_desc AS acad_plan_mjr3_grp_desc FROM ir_cen_trm_snpsht_gt AS ir_cen_trm_snpsht_gt INNER JOIN sr_stu_degr_gt AS sr_stu_degr_gt ON (ir_cen_trm_snpsht_gt.prsn_univ_id = sr_stu_degr_gt.prsn_univ_id AND ir_cen_trm_snpsht_gt.inst_cd = sr_stu_degr_gt.inst_cd AND ir_cen_trm_snpsht_gt.acad_term_cd = sr_stu_degr_gt.stu_degr_cmpltn_term_cd) WHERE (ir_cen_trm_snpsht_gt.acad_career_cd = 'UGRD' AND sr_stu_degr_gt.acad_career_cd = 'UGRD' AND ir_cen_trm_snpsht_gt.inst_cd = 'IUBLA' AND ir_cen_trm_snpsht_gt.acad_prm_plan_1_desc not in ('Non-Degree Undergraduate', 'Non-Degree Baccalaureate') AND ir_cen_trm_snpsht_gt.acad_term_prd_desc like 'Offic%' AND ir_cen_trm_snpsht_gt.acad_term_cd in (4148, 4152, 4155, 4158, 4162, 4165, 4168, 4172, 4175, 4178, 4182, 4185, 4188, 4192, 4195, 4198, 4205, 4208, 4212, 4215) AND sr_stu_degr_gt.inst_cd = 'IUBLA' AND sr_stu_degr_gt.acad_degr_desc in ('Bachelor of Arts', 'Bachelor of Fine Arts', 'Bachelor of Liberal Studies') AND sr_stu_degr_gt.stu_degr_cmpltn_term_cd in (4148, 4152, 4155, 4158, 4162, 4165, 4168, 4172, 4175, 4178, 4182, 4185, 4188, 4192, 4195, 4198, 4205, 4208) AND sr_stu_degr_gt.stu_degr_ckot_stat_cd = 'AW'); /* sacks_stu_demo_data Combines Demo, Degree, and Application data */ SELECT DISTINCT ir_cen_trm_snpsht_gt.prsn_univ_id AS prsn_univ_id, ir_cen_trm_snpsht_gt.acad_term_cd AS acad_term_cd, ir_cen_trm_snpsht_gt.acad_term_desc AS acad_term_desc, case WHEN (ir_cen_trm_snpsht_gt.prsn_gndr_cd is null ) THEN -1 WHEN (ir_cen_trm_snpsht_gt.prsn_gndr_cd = 'F') THEN 1 ELSE 0 END AS female_gndr_ind, ir_cen_trm_snpsht_gt.prsn_drvd_rpt_ethnic_desc AS ipeds_ethnic_desc, case WHEN (ir_cen_trm_snpsht_gt.stu_pell_acpt_awd_trm_ind is null ) THEN -1 WHEN (ir_cen_trm_snpsht_gt.stu_pell_acpt_awd_trm_ind = 'Y') THEN 1 ELSE 0 END AS pell_eligibility, case WHEN (ir_cen_trm_snpsht_gt.stu_drvd_sat_composite_nbr is null ) THEN -1 ELSE ir_cen_trm_snpsht_gt.stu_drvd_sat_composite_nbr END AS stu_drvd_sat_composite_nbr, ir_cen_trm_snpsht_gt.acad_grp_desc AS trm_acad_grp_desc, ir_cen_trm_snpsht_gt.acad_prm_plan_1_cd AS trm_acad_prm_plan_1_cd, ir_cen_trm_snpsht_gt.acad_prm_plan_1_desc AS trm_acad_prm_plan_1_desc, ir_cen_trm_snpsht_gt.acad_prm_plan_2_cd AS trm_acad_prm_plan_2_cd, ir_cen_trm_snpsht_gt.acad_prm_plan_2_desc AS trm_acad_prm_plan_2_desc, ir_cen_trm_snpsht_gt.acad_prm_plan_3_cd AS trm_acad_prm_plan_3_cd, ir_cen_trm_snpsht_gt.acad_prm_plan_3_desc AS trm_acad_prm_plan_3_desc, stu_degr_stat.stu_degr_cmpltn_term_desc AS stu_degr_cmpltn_term_desc, case WHEN (ir_cen_trm_snpsht_gt.stu_hs_sum_cnvrt_gpa_nbr is null ) THEN -1 ELSE ir_cen_trm_snpsht_gt.stu_hs_sum_cnvrt_gpa_nbr END AS stu_hs_sum_cnvrt_gpa_nbr, stu_appl_dtls.appl_acad_pgm_cd AS appl_acad_pgm_cd, stu_appl_dtls.appl_acad_pgm_desc AS appl_acad_pgm_desc, stu_appl_dtls.appl_acad_grp_cd AS appl_acad_grp_cd, stu_appl_dtls.appl_acad_grp_desc AS appl_acad_grp_desc, stu_appl_dtls.appl_plan_1_cd AS appl_plan_1_cd, stu_appl_dtls.appl_plan_1_desc AS appl_plan_1_desc, stu_appl_dtls.appl_plan_2_cd AS appl_plan_2_cd, stu_appl_dtls.appl_plan_2_desc AS appl_plan_2_desc, stu_appl_dtls.appl_plan_3_cd AS appl_plan_3_cd, stu_appl_dtls.appl_plan_3_desc AS appl_plan_3_desc, case WHEN (stu_appl_dtls.appl_acad_pgm_cd is null ) THEN -1 WHEN ((stu_appl_dtls.appl_plan_1_cd = ir_cen_trm_snpsht_gt.acad_prm_plan_1_cd OR stu_appl_dtls.appl_plan_1_cd = ir_cen_trm_snpsht_gt.acad_prm_plan_2_cd OR stu_appl_dtls.appl_plan_1_cd = ir_cen_trm_snpsht_gt.acad_prm_plan_3_cd)) THEN 1 ELSE 0 END AS appl_pln_mtch_trm_plan_ind, stu_degr_stat.acad_degr_desc AS acad_degr_desc, stu_degr_stat.acad_degr_cd AS acad_degr_cd, stu_degr_stat.degr_acad_plan_mjr1_degr_cd AS degr_acad_plan_mjr1_degr_cd, stu_degr_stat.degr_acad_plan_mjr1_pgm_cd AS degr_acad_plan_mjr1_pgm_cd, stu_degr_stat.degr_acad_plan_mjr1_pgm_desc AS degr_acad_plan_mjr1_pgm_desc, stu_degr_stat.degr_acad_plan_mjr1_grp_cd AS degr_acad_plan_mjr1_grp_cd, stu_degr_stat.degr_acad_plan_mjr1_grp_desc AS degr_acad_plan_mjr1_grp_desc, stu_degr_stat.degr_acad_plan_mjr2_cd AS degr_acad_plan_mjr2_cd, stu_degr_stat.degr_acad_plan_mjr2_desc AS degr_acad_plan_mjr2_desc, stu_degr_stat.degr_acad_plan_mjr2_degr_ed_lvl_cd AS degr_acad_plan_mjr2_degr_ed_lvl_cd, stu_degr_stat.degr_acad_plan_mjr2_degr_cd AS degr_acad_plan_mjr2_degr_cd, stu_degr_stat.degr_acad_plan_mjr2_pgm_cd AS degr_acad_plan_mjr2_pgm_cd, stu_degr_stat.degr_acad_plan_mjr2_pgm_desc AS degr_acad_plan_mjr2_pgm_desc, stu_degr_stat.degr_acad_plan_mjr2_grp_cd AS degr_acad_plan_mjr2_grp_cd, stu_degr_stat.degr_acad_plan_mjr2_grp_desc AS degr_acad_plan_mjr2_grp_desc, stu_degr_stat.degr_acad_plan_mjr3_cd AS degr_acad_plan_mjr3_cd, stu_degr_stat.degr_acad_plan_mjr3_desc AS degr_acad_plan_mjr3_desc, stu_degr_stat.degr_acad_plan_mjr3_degr_cd AS degr_acad_plan_mjr3_degr_cd, stu_degr_stat.degr_acad_plan_mjr3_degr_ed_lvl_cd AS degr_acad_plan_mjr3_degr_ed_lvl_cd, stu_degr_stat.degr_acad_plan_mjr3_pgm_cd AS degr_acad_plan_mjr3_pgm_cd, stu_degr_stat.degr_acad_plan_mjr3_pgm_desc AS degr_acad_plan_mjr3_pgm_desc, stu_degr_stat.degr_acad_plan_mjr3_grp_cd AS degr_acad_plan_mjr3_grp_cd, stu_degr_stat.degr_acad_plan_mjr3_grp_desc AS degr_acad_plan_mjr3_grp_desc, stu_degr_stat.stu_degr_cmpltn_term_cd AS stu_degr_cmpltn_term_cd, stu_degr_stat.stu_degr_ckot_stat_cd AS stu_degr_ckot_stat_cd, stu_degr_ba_dtls.acad_prm_plan_1_cd AS acad_prm_plan_1_cd, stu_degr_ba_dtls.acad_prm_plan_1_desc AS acad_prm_plan_1_desc, stu_degr_ba_dtls.acad_prm_plan_1_org_cd AS acad_prm_plan_1_org_cd, stu_degr_ba_dtls.acad_prm_plan_1_org_desc AS acad_prm_plan_1_org_desc, stu_degr_ba_dtls.acad_prm_plan_2_cd AS acad_prm_plan_2_cd, stu_degr_ba_dtls.acad_prm_plan_2_desc AS acad_prm_plan_2_desc, stu_degr_ba_dtls.acad_prm_plan_2_org_cd AS acad_prm_plan_2_org_cd, stu_degr_ba_dtls.acad_prm_plan_2_org_desc AS acad_prm_plan_2_org_desc, stu_degr_ba_dtls.acad_prm_plan_3_cd AS acad_prm_plan_3_cd, stu_degr_ba_dtls.acad_prm_plan_3_desc AS acad_prm_plan_3_desc, stu_degr_ba_dtls.acad_prm_plan_3_org_cd AS acad_prm_plan_3_org_cd, stu_degr_ba_dtls.acad_prm_plan_3_org_desc AS acad_prm_plan_3_org_desc, stu_degr_ba_dtls.acad_plan_mjr1_degr_cd AS acad_plan_mjr1_degr_cd, stu_degr_ba_dtls.acad_plan_mjr1_degr_ed_lvl_cd AS acad_plan_mjr1_degr_ed_lvl_cd, stu_degr_ba_dtls.acad_plan_mjr1_pgm_cd AS acad_plan_mjr1_pgm_cd, stu_degr_ba_dtls.acad_plan_mjr1_pgm_desc AS acad_plan_mjr1_pgm_desc, stu_degr_ba_dtls.acad_plan_mjr1_grp_cd AS acad_plan_mjr1_grp_cd, stu_degr_ba_dtls.acad_plan_mjr1_grp_desc AS acad_plan_mjr1_grp_desc, stu_degr_ba_dtls.acad_sub_plan_mjr1_cd AS acad_sub_plan_mjr1_cd, stu_degr_ba_dtls.acad_sub_plan_mjr1_desc AS acad_sub_plan_mjr1_desc, stu_degr_ba_dtls.acad_subpln_mjr1_hons_pfx_cd AS acad_subpln_mjr1_hons_pfx_cd, stu_degr_ba_dtls.acad_subpln_mjr1_hons_pfx_desc AS acad_subpln_mjr1_hons_pfx_desc, stu_degr_ba_dtls.acad_subpln_mjr1_hons_sfx_cd AS acad_subpln_mjr1_hons_sfx_cd, stu_degr_ba_dtls.acad_subpln_mjr1_hons_sfx_desc AS acad_subpln_mjr1_hons_sfx_desc, stu_degr_ba_dtls.acad_plan_mjr2_cd AS acad_plan_mjr2_cd, stu_degr_ba_dtls.acad_plan_mjr2_desc AS acad_plan_mjr2_desc, stu_degr_ba_dtls.acad_plan_mjr2_seq_nbr AS acad_plan_mjr2_seq_nbr, stu_degr_ba_dtls.acad_plan_mjr2_dplm_desc AS acad_plan_mjr2_dplm_desc, stu_degr_ba_dtls.acad_plan_mjr2_hons_pfx_cd AS acad_plan_mjr2_hons_pfx_cd, stu_degr_ba_dtls.acad_plan_mjr2_hons_pfx_desc AS acad_plan_mjr2_hons_pfx_desc, stu_degr_ba_dtls.acad_plan_mjr2_hons_sfx_cd AS acad_plan_mjr2_hons_sfx_cd, stu_degr_ba_dtls.acad_plan_mjr2_hons_sfx_desc AS acad_plan_mjr2_hons_sfx_desc, stu_degr_ba_dtls.acad_plan_mjr2_degr_cd AS acad_plan_mjr2_degr_cd, stu_degr_ba_dtls.acad_plan_mjr2_degr_ed_lvl_cd AS acad_plan_mjr2_degr_ed_lvl_cd, stu_degr_ba_dtls.acad_plan_mjr2_pgm_cd AS acad_plan_mjr2_pgm_cd, stu_degr_ba_dtls.acad_plan_mjr2_pgm_desc AS acad_plan_mjr2_pgm_desc, stu_degr_ba_dtls.acad_plan_mjr2_grp_cd AS acad_plan_mjr2_grp_cd, stu_degr_ba_dtls.acad_plan_mjr2_grp_desc AS acad_plan_mjr2_grp_desc, stu_degr_ba_dtls.acad_sub_plan_mjr2_cd AS acad_sub_plan_mjr2_cd, stu_degr_ba_dtls.acad_sub_plan_mjr2_desc AS acad_sub_plan_mjr2_desc, stu_degr_ba_dtls.acad_subpln_mjr2_hons_pfx_cd AS acad_subpln_mjr2_hons_pfx_cd, stu_degr_ba_dtls.acad_subpln_mjr2_hons_pfx_desc AS acad_subpln_mjr2_hons_pfx_desc, stu_degr_ba_dtls.acad_subpln_mjr2_hons_sfx_cd AS acad_subpln_mjr2_hons_sfx_cd, stu_degr_ba_dtls.acad_subpln_mjr2_hons_sfx_desc AS acad_subpln_mjr2_hons_sfx_desc, stu_degr_ba_dtls.acad_plan_mjr3_cd AS acad_plan_mjr3_cd, stu_degr_ba_dtls.acad_plan_mjr3_desc AS acad_plan_mjr3_desc, stu_degr_ba_dtls.acad_plan_mjr3_seq_nbr AS acad_plan_mjr3_seq_nbr, stu_degr_ba_dtls.acad_plan_mjr3_dplm_desc AS acad_plan_mjr3_dplm_desc, stu_degr_ba_dtls.acad_plan_mjr3_hons_pfx_cd AS acad_plan_mjr3_hons_pfx_cd, stu_degr_ba_dtls.acad_plan_mjr3_hons_pfx_desc AS acad_plan_mjr3_hons_pfx_desc, stu_degr_ba_dtls.acad_plan_mjr3_hons_sfx_cd AS acad_plan_mjr3_hons_sfx_cd, stu_degr_ba_dtls.acad_sub_plan_mjr3_cd AS acad_sub_plan_mjr3_cd, stu_degr_ba_dtls.acad_plan_mjr3_hons_sfx_desc AS acad_plan_mjr3_hons_sfx_desc, stu_degr_ba_dtls.acad_sub_plan_mjr3_desc AS acad_sub_plan_mjr3_desc, stu_degr_ba_dtls.acad_subpln_mjr3_hons_pfx_cd AS acad_subpln_mjr3_hons_pfx_cd, stu_degr_ba_dtls.acad_subpln_mjr3_hons_pfx_desc AS acad_subpln_mjr3_hons_pfx_desc, stu_degr_ba_dtls.acad_subpln_mjr3_hons_sfx_cd AS acad_subpln_mjr3_hons_sfx_cd, stu_degr_ba_dtls.acad_subpln_mjr3_hons_sfx_desc AS acad_subpln_mjr3_hons_sfx_desc, stu_degr_ba_dtls.acad_plan_mjr3_degr_cd AS acad_plan_mjr3_degr_cd, stu_degr_ba_dtls.acad_plan_mjr3_degr_ed_lvl_cd AS acad_plan_mjr3_degr_ed_lvl_cd, stu_degr_ba_dtls.acad_plan_mjr3_pgm_cd AS acad_plan_mjr3_pgm_cd, stu_degr_ba_dtls.acad_plan_mjr3_pgm_desc AS acad_plan_mjr3_pgm_desc, stu_degr_ba_dtls.acad_plan_mjr3_grp_cd AS acad_plan_mjr3_grp_cd, stu_degr_ba_dtls.acad_plan_mjr3_grp_desc AS acad_plan_mjr3_grp_desc, ir_cen_trm_snpsht_gt.acadlvl_beg_term_desc AS acadlvl_beg_term_desc FROM ((ir_cen_trm_snpsht_gt AS ir_cen_trm_snpsht_gt LEFT OUTER JOIN stu_degr_stat AS stu_degr_stat ON ir_cen_trm_snpsht_gt.prsn_univ_id = stu_degr_stat.prsn_univ_id ) LEFT OUTER JOIN stu_appl_dtls AS stu_appl_dtls ON ir_cen_trm_snpsht_gt.prsn_univ_id = stu_appl_dtls.prsn_univ_id ) LEFT OUTER JOIN stu_degr_ba_dtls AS stu_degr_ba_dtls ON ir_cen_trm_snpsht_gt.prsn_univ_id = stu_degr_ba_dtls.prsn_univ_id WHERE (ir_cen_trm_snpsht_gt.acad_career_cd = 'UGRD' AND ir_cen_trm_snpsht_gt.acad_term_cd in (4148, 4152, 4155, 4158, 4162, 4165, 4168, 4172, 4175, 4178, 4182, 4185, 4188, 4192, 4195, 4198, 4205, 4208, 4212, 4215, 4202) AND ir_cen_trm_snpsht_gt.inst_cd = 'IUBLA' AND ir_cen_trm_snpsht_gt.acad_term_prd_desc = 'Corrected Grades - GRD1 Excl'); /******************************************************* Combines Data ******************************/ /*sacks21_data adds outcome & retention fields for introductory courses and hashes identifiers; there are some redundancies as I was working on multiple paths to the same field measure */ SELECT hash(sacks_stu_demo_data.prsn_univ_id) AS student_id, sacks_stu_demo_data.acad_term_cd AS acad_term_cd, sacks_stu_demo_data.acad_term_desc AS acad_term_desc, sacks_stu_demo_data.female_gndr_ind AS female_gndr_ind, sacks_stu_demo_data.ipeds_ethnic_desc AS ipeds_ethnic_desc, sacks_stu_demo_data.stu_hs_sum_cnvrt_gpa_nbr AS stu_hs_sum_cnvrt_gpa_nbr, sacks_stu_demo_data.pell_eligibility AS pell_eligibility, sacks_stu_demo_data.stu_drvd_sat_composite_nbr AS stu_drvd_sat_composite_nbr, sacks_stu_demo_data.trm_acad_grp_desc AS trm_acad_grp_desc, sacks_stu_demo_data.trm_acad_prm_plan_1_desc AS trm_acad_prm_plan_1_desc, sacks_stu_demo_data.trm_acad_prm_plan_2_desc AS trm_acad_prm_plan_2_desc, sacks_stu_demo_data.trm_acad_prm_plan_3_desc AS trm_acad_prm_plan_3_desc, sacks_stu_demo_data.stu_degr_cmpltn_term_desc AS stu_degr_cmpltn_term_desc, sacks_stu_demo_data.appl_pln_mtch_trm_plan_ind AS appl_pln_mtch_trm_plan_ind, stu_course_data.crs_acad_grp_desc AS crs_acad_grp_desc, stu_course_data.crs_acad_grp_cd AS crs_acad_grp_cd, stu_course_data.course AS course, hash((sacks_stu_demo_data.acad_term_cd||stu_course_data.cls_nbr)) AS section_id, stu_course_data.crs_ofcl_grd_cd AS crs_ofcl_grd_cd, stu_course_data.current_score AS canvas__score, sacks_stu_demo_data.trm_acad_prm_plan_1_cd AS trm_acad_prm_plan_1_cd, sacks_stu_demo_data.trm_acad_prm_plan_2_cd AS trm_acad_prm_plan_2_cd, sacks_stu_demo_data.trm_acad_prm_plan_3_cd AS trm_acad_prm_plan_3_cd, sacks_stu_demo_data.appl_acad_pgm_cd AS appl_acad_pgm_cd, sacks_stu_demo_data.appl_acad_pgm_desc AS appl_acad_pgm_desc, sacks_stu_demo_data.appl_acad_grp_cd AS appl_acad_grp_cd, sacks_stu_demo_data.appl_acad_grp_desc AS appl_acad_grp_desc, sacks_stu_demo_data.appl_plan_1_cd AS appl_plan_1_cd, sacks_stu_demo_data.appl_plan_1_desc AS appl_plan_1_desc, sacks_stu_demo_data.appl_plan_2_cd AS appl_plan_2_cd, sacks_stu_demo_data.appl_plan_2_desc AS appl_plan_2_desc, sacks_stu_demo_data.appl_plan_3_cd AS appl_plan_3_cd, sacks_stu_demo_data.appl_plan_3_desc AS appl_plan_3_desc, sacks_stu_demo_data.acad_degr_desc AS acad_degr_desc, sacks_stu_demo_data.acad_degr_cd AS acad_degr_cd, sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd AS degr_acad_plan_mjr1_degr_cd, sacks_stu_demo_data.degr_acad_plan_mjr1_pgm_cd AS degr_acad_plan_mjr1_pgm_cd, sacks_stu_demo_data.degr_acad_plan_mjr1_pgm_desc AS degr_acad_plan_mjr1_pgm_desc, sacks_stu_demo_data.degr_acad_plan_mjr1_grp_cd AS degr_acad_plan_mjr1_grp_cd, sacks_stu_demo_data.degr_acad_plan_mjr1_grp_desc AS degr_acad_plan_mjr1_grp_desc, sacks_stu_demo_data.degr_acad_plan_mjr2_cd AS degr_acad_plan_mjr2_cd, sacks_stu_demo_data.degr_acad_plan_mjr2_desc AS degr_acad_plan_mjr2_desc, sacks_stu_demo_data.degr_acad_plan_mjr2_degr_ed_lvl_cd AS degr_acad_plan_mjr2_degr_ed_lvl_cd, sacks_stu_demo_data.degr_acad_plan_mjr2_pgm_cd AS degr_acad_plan_mjr2_pgm_cd, sacks_stu_demo_data.degr_acad_plan_mjr2_degr_cd AS degr_acad_plan_mjr2_degr_cd, sacks_stu_demo_data.degr_acad_plan_mjr2_pgm_desc AS degr_acad_plan_mjr2_pgm_desc, sacks_stu_demo_data.degr_acad_plan_mjr2_grp_cd AS degr_acad_plan_mjr2_grp_cd, sacks_stu_demo_data.degr_acad_plan_mjr2_grp_desc AS degr_acad_plan_mjr2_grp_desc, sacks_stu_demo_data.degr_acad_plan_mjr3_desc AS degr_acad_plan_mjr3_desc, sacks_stu_demo_data.degr_acad_plan_mjr3_cd AS degr_acad_plan_mjr3_cd, sacks_stu_demo_data.degr_acad_plan_mjr3_degr_cd AS degr_acad_plan_mjr3_degr_cd, sacks_stu_demo_data.degr_acad_plan_mjr3_degr_ed_lvl_cd AS degr_acad_plan_mjr3_degr_ed_lvl_cd, sacks_stu_demo_data.degr_acad_plan_mjr3_pgm_cd AS degr_acad_plan_mjr3_pgm_cd, sacks_stu_demo_data.degr_acad_plan_mjr3_pgm_desc AS degr_acad_plan_mjr3_pgm_desc, sacks_stu_demo_data.degr_acad_plan_mjr3_grp_cd AS degr_acad_plan_mjr3_grp_cd, sacks_stu_demo_data.degr_acad_plan_mjr3_grp_desc AS degr_acad_plan_mjr3_grp_desc, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Astronomy & Astrophys' OR (sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr1_pgm_cd = 'ASTPHYSBA') OR (sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.trm_acad_prm_plan_1_cd in ('ASTPHYSBA', 'ASTPHYSBS') AND stu_course_data.course in ('AST-A-221', 'AST-A-222')))) THEN 1 WHEN (((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Biology', 'B. S. in Human Biology') OR (sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'BIOLBA') OR (sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.trm_acad_prm_plan_1_cd = 'BIOLBA')) AND stu_course_data.course in ('BIOL-H-111', 'BIOL-H-112', 'BIOL-L-111', 'BIOL-L-112'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Business' AND stu_course_data.course in ('BUS-A-100', 'BUS-K-201'))) THEN 1 WHEN (((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Biochemistry', 'B. S. in Chemistry') OR (sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'CHEMBA')) AND stu_course_data.course in ('CHEM-C-103', 'CHEM-C-117', 'CHEM-H-117', 'CHEM-J-117', 'CHEM-S-117'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Computer Science') OR (sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'CSCIBA' AND stu_course_data.course in ('CSCI-C-200', 'CSCI-C-211', 'CSCI-H-212', 'CSCI-C-212', 'CSCI-H-200', 'CSCI-H-211')))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Earth Science') AND stu_course_data.course in ('EAS-E-103', 'EAS-E-104', 'EAS-E-105', 'EAS-E-122', 'EAS-E-144'))) THEN 1 WHEN (((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND degr_acad_plan_mjr2_cd = 'ECONBA') OR (sacks_stu_demo_data.acad_degr_desc = 'B.S. Economics' AND stu_course_data.course in ('ECON-B-251', 'ECON-E-201', 'ECON-E-202', 'ECON-E-251', 'ECON-S-201', 'ECON-S-202')))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Informatics' AND stu_course_data.course in ('INFO-H-101', 'INFO-I-101', 'INFO-Y-101'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B.S. Pblc Hlth Community Hlth') AND stu_course_data.course in ('SPH-H-263', 'SPH-K-200', 'SPH-V-205'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'CJUSBA' AND stu_course_data.course in ('CJUS-P-100', 'CJUS-P-200'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Education' AND stu_course_data.course in ('EDUC-G-203', 'EDUC-W-200'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'ENGBA' AND stu_course_data.course in ('ENG-L-203', 'ENG-L-204', 'ENG-L-205'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'HISTBA' AND stu_course_data.course = 'HIST-H-270')) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'COGI' AND stu_course_data.course in ('INTL-I-202', 'INTL-I-203', 'INTL-I-204', 'INTL-I-205'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_pgm_cd = 'MSCH1' AND stu_course_data.course in ('MSCH-C-101', 'MSCH-C-207', 'MSCH-C-208', 'MSCH-C-210', 'MSCH-C-223', 'MSCH-C-229'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'NURS1' AND stu_course_data.course in ('NURS-B-234', 'NURS-B-260'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_pgm_cd = 'SPEA1' AND stu_course_data.course in ('SPEA-A-163', 'SPEA-E-183', 'SPEA-H-124', 'SPEA-V-181', 'SPEA-V-182', 'SPEA-V-184', 'SPEA-V-185', 'SPEA-V-186'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'POLSBA' AND stu_course_data.course in ('POLS-Y-100', 'POLS-Y-103', 'POLS-Y-105', 'POLS-Y-107'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Neuroscience', 'B. S. in Psychology') OR (sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'PSYBA' AND stu_course_data.course in ('PSY-P-155', 'PSY-P-211')))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Social Work' AND stu_course_data.course in ('SWK-S-102', 'SWK-S-141'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_pgm_cd = 'SPH1' AND stu_course_data.course in ('SPH-K-150', 'SPH-M-211'))) THEN 1 ELSE 0 END AS intr_crs_req_degr, sacks_stu_demo_data.stu_degr_cmpltn_term_cd AS stu_degr_cmpltn_term_cd, sacks_stu_demo_data.stu_degr_ckot_stat_cd AS stu_degr_ckot_stat_cd, sacks_stu_demo_data.acad_prm_plan_1_cd AS acad_prm_plan_1_cd, sacks_stu_demo_data.acad_prm_plan_1_desc AS acad_prm_plan_1_desc, sacks_stu_demo_data.acad_prm_plan_1_org_cd AS acad_prm_plan_1_org_cd, sacks_stu_demo_data.acad_prm_plan_1_org_desc AS acad_prm_plan_1_org_desc, sacks_stu_demo_data.acad_prm_plan_2_cd AS acad_prm_plan_2_cd, sacks_stu_demo_data.acad_prm_plan_2_desc AS acad_prm_plan_2_desc, sacks_stu_demo_data.acad_prm_plan_2_org_cd AS acad_prm_plan_2_org_cd, sacks_stu_demo_data.acad_prm_plan_2_org_desc AS acad_prm_plan_2_org_desc, sacks_stu_demo_data.acad_prm_plan_3_cd AS acad_prm_plan_3_cd, sacks_stu_demo_data.acad_prm_plan_3_desc AS acad_prm_plan_3_desc, sacks_stu_demo_data.acad_prm_plan_3_org_cd AS acad_prm_plan_3_org_cd, sacks_stu_demo_data.acad_prm_plan_3_org_desc AS acad_prm_plan_3_org_desc, sacks_stu_demo_data.acad_plan_mjr1_degr_cd AS acad_plan_mjr1_degr_cd, sacks_stu_demo_data.acad_plan_mjr1_degr_ed_lvl_cd AS acad_plan_mjr1_degr_ed_lvl_cd, sacks_stu_demo_data.acad_plan_mjr1_pgm_cd AS acad_plan_mjr1_pgm_cd, sacks_stu_demo_data.acad_plan_mjr1_pgm_desc AS acad_plan_mjr1_pgm_desc, sacks_stu_demo_data.acad_plan_mjr1_grp_cd AS acad_plan_mjr1_grp_cd, sacks_stu_demo_data.acad_plan_mjr1_grp_desc AS acad_plan_mjr1_grp_desc, sacks_stu_demo_data.acad_sub_plan_mjr1_cd AS acad_sub_plan_mjr1_cd, sacks_stu_demo_data.acad_sub_plan_mjr1_desc AS acad_sub_plan_mjr1_desc, sacks_stu_demo_data.acad_subpln_mjr1_hons_pfx_cd AS acad_subpln_mjr1_hons_pfx_cd, sacks_stu_demo_data.acad_subpln_mjr1_hons_pfx_desc AS acad_subpln_mjr1_hons_pfx_desc, sacks_stu_demo_data.acad_subpln_mjr1_hons_sfx_cd AS acad_subpln_mjr1_hons_sfx_cd, sacks_stu_demo_data.acad_subpln_mjr1_hons_sfx_desc AS acad_subpln_mjr1_hons_sfx_desc, sacks_stu_demo_data.acad_plan_mjr2_cd AS acad_plan_mjr2_cd, sacks_stu_demo_data.acad_plan_mjr2_desc AS acad_plan_mjr2_desc, sacks_stu_demo_data.acad_plan_mjr2_seq_nbr AS acad_plan_mjr2_seq_nbr, sacks_stu_demo_data.acad_plan_mjr2_dplm_desc AS acad_plan_mjr2_dplm_desc, sacks_stu_demo_data.acad_plan_mjr2_hons_pfx_cd AS acad_plan_mjr2_hons_pfx_cd, sacks_stu_demo_data.acad_plan_mjr2_hons_pfx_desc AS acad_plan_mjr2_hons_pfx_desc, sacks_stu_demo_data.acad_plan_mjr2_hons_sfx_cd AS acad_plan_mjr2_hons_sfx_cd, sacks_stu_demo_data.acad_plan_mjr2_hons_sfx_desc AS acad_plan_mjr2_hons_sfx_desc, sacks_stu_demo_data.acad_plan_mjr2_degr_cd AS acad_plan_mjr2_degr_cd, sacks_stu_demo_data.acad_plan_mjr2_degr_ed_lvl_cd AS acad_plan_mjr2_degr_ed_lvl_cd, sacks_stu_demo_data.acad_plan_mjr2_pgm_cd AS acad_plan_mjr2_pgm_cd, sacks_stu_demo_data.acad_plan_mjr2_pgm_desc AS acad_plan_mjr2_pgm_desc, sacks_stu_demo_data.acad_plan_mjr2_grp_cd AS acad_plan_mjr2_grp_cd, sacks_stu_demo_data.acad_plan_mjr2_grp_desc AS acad_plan_mjr2_grp_desc, sacks_stu_demo_data.acad_sub_plan_mjr2_cd AS acad_sub_plan_mjr2_cd, sacks_stu_demo_data.acad_sub_plan_mjr2_desc AS acad_sub_plan_mjr2_desc, sacks_stu_demo_data.acad_subpln_mjr2_hons_pfx_cd AS acad_subpln_mjr2_hons_pfx_cd, sacks_stu_demo_data.acad_subpln_mjr2_hons_pfx_desc AS acad_subpln_mjr2_hons_pfx_desc, sacks_stu_demo_data.acad_subpln_mjr2_hons_sfx_cd AS acad_subpln_mjr2_hons_sfx_cd, sacks_stu_demo_data.acad_subpln_mjr2_hons_sfx_desc AS acad_subpln_mjr2_hons_sfx_desc, sacks_stu_demo_data.acad_plan_mjr3_cd AS acad_plan_mjr3_cd, sacks_stu_demo_data.acad_plan_mjr3_desc AS acad_plan_mjr3_desc, sacks_stu_demo_data.acad_plan_mjr3_seq_nbr AS acad_plan_mjr3_seq_nbr, sacks_stu_demo_data.acad_plan_mjr3_dplm_desc AS acad_plan_mjr3_dplm_desc, sacks_stu_demo_data.acad_plan_mjr3_hons_pfx_cd AS acad_plan_mjr3_hons_pfx_cd, sacks_stu_demo_data.acad_plan_mjr3_hons_pfx_desc AS acad_plan_mjr3_hons_pfx_desc, sacks_stu_demo_data.acad_plan_mjr3_hons_sfx_cd AS acad_plan_mjr3_hons_sfx_cd, sacks_stu_demo_data.acad_sub_plan_mjr3_cd AS acad_sub_plan_mjr3_cd, sacks_stu_demo_data.acad_plan_mjr3_hons_sfx_desc AS acad_plan_mjr3_hons_sfx_desc, sacks_stu_demo_data.acad_sub_plan_mjr3_desc AS acad_sub_plan_mjr3_desc, sacks_stu_demo_data.acad_subpln_mjr3_hons_pfx_cd AS acad_subpln_mjr3_hons_pfx_cd, sacks_stu_demo_data.acad_subpln_mjr3_hons_pfx_desc AS acad_subpln_mjr3_hons_pfx_desc, sacks_stu_demo_data.acad_subpln_mjr3_hons_sfx_cd AS acad_subpln_mjr3_hons_sfx_cd, sacks_stu_demo_data.acad_subpln_mjr3_hons_sfx_desc AS acad_subpln_mjr3_hons_sfx_desc, sacks_stu_demo_data.acad_plan_mjr3_degr_cd AS acad_plan_mjr3_degr_cd, sacks_stu_demo_data.acad_plan_mjr3_degr_ed_lvl_cd AS acad_plan_mjr3_degr_ed_lvl_cd, sacks_stu_demo_data.acad_plan_mjr3_pgm_cd AS acad_plan_mjr3_pgm_cd, sacks_stu_demo_data.acad_plan_mjr3_pgm_desc AS acad_plan_mjr3_pgm_desc, sacks_stu_demo_data.acad_plan_mjr3_grp_cd AS acad_plan_mjr3_grp_cd, sacks_stu_demo_data.acad_plan_mjr3_grp_desc AS acad_plan_mjr3_grp_desc, stu_course_data.current_score AS current_score, stu_course_data.crs_pplsft_acad_org_lvl_1_cd AS crs_pplsft_acad_org_lvl_1_cd, stu_course_data.crs_pplsft_acad_org_lvl_2_cd AS crs_pplsft_acad_org_lvl_2_cd, stu_course_data.crs_pplsft_acad_org_lvl_3_cd AS crs_pplsft_acad_org_lvl_3_cd, stu_course_data.crs_pplsft_acad_org_lvl_4_cd AS crs_pplsft_acad_org_lvl_4_cd, stu_course_data.crs_pplsft_acad_org_lvl_5_cd AS crs_pplsft_acad_org_lvl_5_cd, stu_course_data.crs_subj_dept_cd AS crs_subj_dept_cd, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Astronomy & Astrophys' AND stu_course_data.course in ('AST-A-221', 'AST-A-222'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'ASTPHYSBA' AND stu_course_data.course in ('AST-A-221', 'AST-A-222'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'ASTPHYSBA' AND stu_course_data.course in ('AST-A-221', 'AST-A-222'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'ASTPHYSBA' AND stu_course_data.course in ('AST-A-221', 'AST-A-222'))) THEN 1 ELSE 0 END AS ast_intr_degr_mtch, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Biology', 'B. S. in Human Biology') AND stu_course_data.course in ('BIOL-H-111', 'BIOL-H-112', 'BIOL-L-111', 'BIOL-L-112'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'BIOLBA' AND stu_course_data.course in ('BIOL-H-111', 'BIOL-H-112', 'BIOL-L-111', 'BIOL-L-112'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'BIOLBA' AND stu_course_data.course in ('BIOL-H-111', 'BIOL-H-112', 'BIOL-L-111', 'BIOL-L-112'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'BIOLBA' AND stu_course_data.course in ('BIOL-H-111', 'BIOL-H-112', 'BIOL-L-111', 'BIOL-L-112'))) THEN 1 ELSE 0 END AS biol_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Business' AND stu_course_data.course in ('BUS-A-100', 'BUS-K-201'))) THEN 1 ELSE 0 END AS bus_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Biochemistry', 'B.S in Molecular Life Sciences', 'B. S. in Chemistry') AND stu_course_data.course in ('CHEM-C-103', 'CHEM-C-117', 'CHEM-H-117', 'CHEM-J-117', 'CHEM-S-117'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'CHEMBA' AND stu_course_data.course in ('CHEM-C-103', 'CHEM-C-117', 'CHEM-H-117', 'CHEM-J-117', 'CHEM-S-117'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'CHEMBA' AND stu_course_data.course in ('CHEM-C-103', 'CHEM-C-117', 'CHEM-H-117', 'CHEM-J-117', 'CHEM-S-117'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'CHEMBA' AND stu_course_data.course in ('CHEM-C-103', 'CHEM-C-117', 'CHEM-H-117', 'CHEM-J-117', 'CHEM-S-117'))) THEN 1 ELSE 0 END AS chem_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Computer Science') AND stu_course_data.course in ('CSCI-C-200', 'CSCI-C-211', 'CSCI-H-212', 'CSCI-C-212', 'CSCI-H-200', 'CSCI-H-211'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'CSCIBA' AND stu_course_data.course in ('CSCI-C-200', 'CSCI-C-211', 'CSCI-H-212', 'CSCI-C-212', 'CSCI-H-200', 'CSCI-H-211'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'CSCIBA' AND stu_course_data.course in ('CSCI-C-200', 'CSCI-C-211', 'CSCI-H-212', 'CSCI-C-212', 'CSCI-H-200', 'CSCI-H-211'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'CSCIBA' AND stu_course_data.course in ('CSCI-C-200', 'CSCI-C-211', 'CSCI-H-212', 'CSCI-C-212', 'CSCI-H-200', 'CSCI-H-211'))) THEN 1 ELSE 0 END AS csci_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Earth Science') AND stu_course_data.course in ('EAS-E-103', 'EAS-E-104', 'EAS-E-105', 'EAS-E-122', 'EAS-E-144'))) THEN 1 ELSE 0 END AS ear_sci_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Economics' AND stu_course_data.course in ('ECON-B-251', 'ECON-E-201', 'ECON-E-202', 'ECON-E-251', 'ECON-S-201', 'ECON-S-202'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'ECONBA' AND stu_course_data.course in ('ECON-B-251', 'ECON-E-201', 'ECON-E-202', 'ECON-E-251', 'ECON-S-201', 'ECON-S-202'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'ECONBA' AND stu_course_data.course in ('ECON-B-251', 'ECON-E-201', 'ECON-E-202', 'ECON-E-251', 'ECON-S-201', 'ECON-S-202'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'ECONBA' AND stu_course_data.course in ('ECON-B-251', 'ECON-E-201', 'ECON-E-202', 'ECON-E-251', 'ECON-S-201', 'ECON-S-202'))) THEN 1 ELSE 0 END AS econ_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Informatics' AND stu_course_data.course in ('INFO-H-101', 'INFO-I-101', 'INFO-Y-101'))) THEN 1 ELSE 0 END AS info_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Int Sys Engineering' AND stu_course_data.course in ('ENGR-E-111', 'ENGR-E-250'))) THEN 1 ELSE 0 END AS intsys_engr_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Physics' AND stu_course_data.course in ('PHYS-H-221', 'PHYS-P-221'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'PHYSBA' AND stu_course_data.course in ('PHYS-H-221', 'PHYS-P-221'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'PHYSBA' AND stu_course_data.course in ('PHYS-H-221', 'PHYS-P-221'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'PHYSBA' AND stu_course_data.course in ('PHYS-H-221', 'PHYS-P-221'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'PHYSBA' AND stu_course_data.course in ('PHYS-H-221', 'PHYS-P-221'))) THEN 1 ELSE 0 END AS phys_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Astronomy & Astrophys' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'ASTPHYSBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'ASTPHYSBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'ASTPHYSBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Biology', 'B. S. in Human Biology') AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'BIOLBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'BIOLBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'BIOLBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Business' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Biochemistry', 'B.S in Molecular Life Sciences', 'B. S. in Chemistry') AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'CHEMBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'CHEMBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'CHEMBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Computer Science') AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'CSCIBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'CSCIBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'CSCIBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Earth Science') AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Economics' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'ECONBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'ECONBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'ECONBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Informatics' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Int Sys Engineering' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Physics' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'PHYSBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'PHYSBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'PHYSBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'PHYSBA' AND stu_course_data.course in ('MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118'))) THEN 1 ELSE 0 END AS math_crs_req_stembe_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_prm_plan_1_cd = 'FSDSBA1' AND stu_course_data.course in ('SOAD-A-100', 'SOAD-A-109'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_prm_plan_2_cd = 'FSDSBA1' AND stu_course_data.course in ('SOAD-A-100', 'SOAD-A-109'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_prm_plan_3_cd = 'FSDSBA1' AND stu_course_data.course in ('SOAD-A-100', 'SOAD-A-109'))) THEN 1 ELSE 0 END AS app_mchr_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B.S. Pblc Hlth Community Hlth') AND stu_course_data.course in ('SPH-H-263', 'SPH-K-200', 'SPH-V-205'))) THEN 1 ELSE 0 END AS comm_hlt_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'CJUSBA' AND stu_course_data.course in ('CJUS-P-100', 'CJUS-P-200'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'CJUSBA' AND stu_course_data.course in ('CJUS-P-100', 'CJUS-P-200'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'CJUSBA' AND stu_course_data.course in ('CJUS-P-100', 'CJUS-P-200'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'CJUSBA' AND stu_course_data.course in ('CJUS-P-100', 'CJUS-P-200'))) THEN 1 ELSE 0 END AS cjus_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'B. S. in Education' AND stu_course_data.course in ('EDUC-G-203', 'EDUC-W-200'))) THEN 1 ELSE 0 END AS educ_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'ENGBA' AND stu_course_data.course in ('ENG-L-203', 'ENG-L-204', 'ENG-L-205'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'ENGBA' AND stu_course_data.course in ('ENG-L-203', 'ENG-L-204', 'ENG-L-205'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'ENGBA' AND stu_course_data.course in ('ENG-L-203', 'ENG-L-204', 'ENG-L-205'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'ENGBA' AND stu_course_data.course in ('ENG-L-203', 'ENG-L-204', 'ENG-L-205'))) THEN 1 ELSE 0 END AS eng_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'HISTBA' AND stu_course_data.course = 'HIST-H-270')) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'HISTBA' AND stu_course_data.course = 'HIST-H-270')) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'HISTBA' AND stu_course_data.course = 'HIST-H-270')) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'HISTBA' AND stu_course_data.course = 'HIST-H-270')) THEN 1 ELSE 0 END AS hist_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'INTLBA1' AND stu_course_data.course in ('INTL-I-202', 'INTL-I-203', 'INTL-I-204', 'INTL-I-205'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bacehlor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'INTLBA1' AND stu_course_data.course in ('INTL-I-202', 'INTL-I-203', 'INTL-I-204', 'INTL-I-205'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bacehlor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'INTLBA1' AND stu_course_data.course in ('INTL-I-202', 'INTL-I-203', 'INTL-I-204', 'INTL-I-205'))) THEN 1 ELSE 0 END AS intl_std_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_pgm_cd = 'MSCH1' AND stu_course_data.course in ('MSCH-C-101', 'MSCH-C-207', 'MSCH-C-208', 'MSCH-C-210', 'MSCH-C-223', 'MSCH-C-229'))) THEN 1 ELSE 0 END AS media_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBSN001' AND stu_course_data.course in ('NURS-B-234', 'NURS-B-260'))) THEN 1 ELSE 0 END AS nurs_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_pgm_cd = 'SPEA1' AND stu_course_data.course in ('SPEA-A-163', 'SPEA-E-183', 'SPEA-H-124', 'SPEA-V-181', 'SPEA-V-182', 'SPEA-V-184', 'SPEA-V-185', 'SPEA-V-186'))) THEN 1 ELSE 0 END AS policy_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'POLSBA' AND stu_course_data.course in ('POLS-Y-100', 'POLS-Y-103', 'POLS-Y-105', 'POLS-Y-107'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'POLSBA' AND stu_course_data.course in ('POLS-Y-100', 'POLS-Y-103', 'POLS-Y-105', 'POLS-Y-107'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'POLSBA' AND stu_course_data.course in ('POLS-Y-100', 'POLS-Y-103', 'POLS-Y-105', 'POLS-Y-107'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'POLSBA' AND stu_course_data.course in ('POLS-Y-100', 'POLS-Y-103', 'POLS-Y-105', 'POLS-Y-107'))) THEN 1 ELSE 0 END AS polisci_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc in ('B. S. in Neuroscience', 'B. S. in Psychology') AND stu_course_data.course in ('PSY-P-155', 'PSY-P-211'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'PSYBA' AND stu_course_data.course in ('PSY-P-155', 'PSY-P-211'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'PSYBA' AND stu_course_data.course in ('PSY-P-155', 'PSY-P-211'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'PSYBA' AND stu_course_data.course in ('PSY-P-155', 'PSY-P-211'))) THEN 1 ELSE 0 END AS psych_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Social Work' AND stu_course_data.course in ('SWK-S-102', 'SWK-S-141'))) THEN 1 ELSE 0 END AS swk_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_pgm_cd = 'SPH1' AND stu_course_data.course in ('SPH-K-150', 'SPH-M-211'))) THEN 1 ELSE 0 END AS ph_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.degr_acad_plan_mjr2_cd = 'THTRBA' AND stu_course_data.course in ('THTR-T-100', 'THTR-T-121', 'THTR-T-125'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_1_cd = 'THTRBA' AND stu_course_data.course in ('THTR-T-100', 'THTR-T-121', 'THTR-T-125'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_2_cd = 'THTRBA' AND stu_course_data.course in ('THTR-T-100', 'THTR-T-121', 'THTR-T-125'))) THEN 1 WHEN ((sacks_stu_demo_data.degr_acad_plan_mjr1_degr_cd = 'IBA001' AND sacks_stu_demo_data.acad_prm_plan_3_cd = 'THTRBA' AND stu_course_data.course in ('THTR-T-100', 'THTR-T-121', 'THTR-T-125'))) THEN 1 ELSE 0 END AS thtr_intr_crs_mtch_degr, case WHEN (sacks_stu_demo_data.stu_degr_ckot_stat_cd is null ) THEN -1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_1_cd in ('FINASBA1', 'FINASBFA1') AND course in ('SOAD-A-101', 'SOAD-A-102', 'SOAD-A-103'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_2_cd in ('FINASBA1', 'FINASBFA1') AND course in ('SOAD-A-101', 'SOAD-A-102', 'SOAD-A-103'))) THEN 1 WHEN ((sacks_stu_demo_data.acad_degr_desc = 'Bachelor of Arts' AND sacks_stu_demo_data.acad_prm_plan_3_cd in ('FINASBA1', 'FINASBFA1') AND course in ('SOAD-A-101', 'SOAD-A-102', 'SOAD-A-103'))) THEN 1 ELSE 0 END AS stdart_intr_crs_mtch_degr, sacks_stu_demo_data.acadlvl_beg_term_desc AS acadlvl_beg_term_desc, case WHEN ((sacks_stu_demo_data.acadlvl_beg_term_desc = 'Senior' AND stu_course_data.course in ('AST-A-221', 'AST-A-222', 'BIOL-H-111', 'BIOL-H-112', 'BIOL-L-111', 'BIOL-L-112', 'BUS-A-100', 'BUS-K-201', 'CHEM-C-103', 'CHEM-C-117', 'CHEM-H-117', 'CHEM-J-117', 'CHEM-S-117', 'CSCI-C-200', 'CSCI-C-211', 'CSCI-H-212', 'CSCI-C-212', 'CSCI-H-200', 'CSCI-H-211', 'EAS-E-103', 'EAS-E-104', 'EAS-E-105', 'EAS-E-122', 'EAS-E-144', 'ECON-B-251', 'ECON-E-201', 'ECON-E-202', 'ECON-E-251', 'ECON-S-201', 'ECON-S-202', 'INFO-H-101', 'INFO-I-101', 'INFO-Y-101', 'ENGR-E-111', 'ENGR-E-250', 'PHYS-H-221', 'PHYS-P-221', 'MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118', 'SOAD-A-100', 'SOAD-A-109', 'SPH-H-263', 'SPH-K-200', 'SPH-V-205', 'CJUS-P-100', 'CJUS-P-200', 'EDUC-G-203', 'EDUC-W-200', 'ENG-L-203', 'ENG-L-204', 'ENG-L-205', 'HIST-H-270', 'INTL-I-202', 'INTL-I-203', 'INTL-I-204', 'INTL-I-205', 'MSCH-C-101', 'MSCH-C-207', 'MSCH-C-208', 'MSCH-C-210', 'MSCH-C-223', 'MSCH-C-229', 'NURS-B-234', 'NURS-B-260', 'SPEA-A-163', 'SPEA-E-183', 'SPEA-H-124', 'SPEA-V-181', 'SPEA-V-182', 'SPEA-V-184', 'SPEA-V-185', 'SPEA-V-186', 'POLS-Y-100', 'POLS-Y-103', 'POLS-Y-105', 'POLS-Y-107', 'PSY-P-155', 'PSY-P-211', 'SWK-S-102', 'SWK-S-141', 'SPH-K-150', 'SPH-M-211', 'THTR-T-100', 'THTR-T-121', 'THTR-T-125', 'SOAD-A-101', 'SOAD-A-102', 'SOAD-A-103'))) THEN 1 ELSE 0 END AS senior_enrl_intr_crs, stu_course_data.crs_catlg_nbr AS crs_catlg_nbr, case WHEN (stu_course_data.instructor_term_course_id is null ) THEN -1 ELSE stu_course_data.instructor_term_course_id END AS instructor_term_course_id, stu_course_data.est_assn_scr AS est_assn_scr, stu_course_data.stu_enrl_stat_cd AS stu_enrl_stat_cd FROM sacks_stu_demo_data AS sacks_stu_demo_data INNER JOIN stu_course_data AS stu_course_data ON (sacks_stu_demo_data.prsn_univ_id = stu_course_data.prsn_univ_id AND sacks_stu_demo_data.acad_term_cd = stu_course_data.acad_term_cd) ; /* sacks21_dataset- Creates view of combined dataset */ SELECT DISTINCT sacks21_data.student_id AS student_id, sacks21_data.female_gndr_ind AS female_gndr_ind, case WHEN (sacks21_data.ipeds_ethnic_desc is null ) THEN -1 ELSE sacks21_data.ipeds_ethnic_desc END AS ipeds_ethnic_desc, sacks21_data.stu_hs_sum_cnvrt_gpa_nbr AS stu_hs_sum_cnvrt_gpa_nbr, sacks21_data.stu_drvd_sat_composite_nbr AS stu_drvd_sat_composite_nbr, sacks21_data.acad_term_desc AS acad_term_desc, sacks21_data.pell_eligibility AS pell_eligibility, sacks21_data.course AS course, sacks21_data.section_id AS section_id, sacks21_data.crs_ofcl_grd_cd AS crs_ofcl_grd_cd, case WHEN (sacks21_data.canvas__score is null ) THEN -999 ELSE sacks21_data.canvas__score END AS canvas_est_score, case WHEN (sacks21_data.est_assn_scr is null ) THEN -999 ELSE sacks21_data.est_assn_scr END AS canvas_est_assn_scr, case WHEN (sacks21_data.stu_degr_ckot_stat_cd is null ) THEN -1 ELSE sum(ast_intr_degr_mtch, biol_intr_crs_mtch_degr, bus_intr_crs_mtch_degr, chem_intr_crs_mtch_degr, csci_intr_crs_mtch_degr, econ_intr_crs_mtch_degr, info_intr_crs_mtch_degr, intsys_engr_intr_crs_mtch_degr, phys_intr_crs_mtch_degr, math_crs_req_stembe_degr, app_mchr_intr_crs_mtch_degr, comm_hlt_intr_crs_mtch_degr, cjus_intr_crs_mtch_degr, educ_intr_crs_mtch_degr, eng_intr_crs_mtch_degr, hist_intr_crs_mtch_degr, intl_std_intr_crs_mtch_degr, media_intr_crs_mtch_degr, nurs_intr_crs_mtch_degr, policy_intr_crs_mtch_degr, polisci_intr_crs_mtch_degr, psych_intr_crs_mtch_degr, swk_intr_crs_mtch_degr, ph_intr_crs_mtch_degr, thtr_intr_crs_mtch_degr, stdart_intr_crs_mtch_degr) END AS intr_crs_req_for_degr, case WHEN (appl_plan_1_cd is null ) THEN -1 WHEN ((appl_plan_1_cd = 'AAADBAPR' AND crs_subj_dept_cd = 'AAAD' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('ABEHBS', 'ABEHBSPR') AND crs_subj_dept_cd = 'ABEH' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('ACTGBSB', 'ACTGBSBPR') AND crs_subj_dept_cd = 'BUS' AND crs_catlg_nbr in ('100', '201'))) THEN 1 WHEN ((appl_plan_1_cd in ('AHCHBSPR', 'AHDTBS', 'AHHDBS', 'AHHDBSPR', 'AHHEBSPR', 'AHNSBS', 'AHNSBSPR', 'AHSAFBS', 'AHSAFBSPR', 'AHYDBS', 'AHYDBSPR') AND crs_subj_dept_cd in ('HPER', 'SPH') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('AMSTBA', 'AMSTBAPR') AND crs_subj_dept_cd = 'AMST' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('ANTHBA', 'ANTHBAPR') AND crs_subj_dept_cd = 'ANTH' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd = 'ANTHLNGBAP' AND crs_subj_dept_cd in ('ANTH', 'LING') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('APMDBS1') AND crs_subj_dept_cd = 'SOAD' AND crs_catlg_nbr in ('100', '109'))) THEN 1 WHEN ((appl_plan_1_cd in ('ARTHBA', 'ARTHBAPR') AND crs_subj_dept_cd = 'ARTH' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('ARTSMGTBS', 'ARTSMGTBSP') AND crs_subj_dept_cd = 'SPEA' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('ASTPHYBSPR', 'ASTPHYSBA', 'ASTPHYSBAP', 'ASTPHYSBS') AND crs_subj_dept_cd in ('AST', 'PHYS') AND crs_catlg_nbr in ('221', '222'))) THEN 1 WHEN ((appl_plan_1_cd in ('ATATBSPR') AND crs_subj_dept_cd in ('HPER', 'SPH') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('ATSCIBS', 'ATSCIBS') AND crs_subj_dept_cd in ('EAS') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('AUENGSPASP', 'AUENGSPBS', 'AUENGSPBSP') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('BALBS') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('BEPPEBSB', 'BEPPEBSBP', 'BEPPPBSB', 'BEPPPBSBP') AND crs_subj_dept_cd = 'BUS' AND crs_catlg_nbr in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('BIOCBA', 'BIOCBAPR', 'BIOCBS', 'BIOCBSPR') AND crs_subj_dept_cd in ('CHEM', 'BIOL') AND substr(crs_catlg_nbr, 1, 1) in ('111', '112', '103', '117'))) THEN 1 WHEN ((appl_plan_1_cd in ('BIOEDBSEDP') AND crs_subj_dept_cd in ('BIOL', 'EDUC') AND substr(crs_catlg_nbr, 1, 1) in ('203', '200', '111', '112'))) THEN 1 WHEN ((appl_plan_1_cd in ('BIOLBA', 'BIOLBAPR', 'BIOLBS', 'BIOLBSPR', 'BIOLTSBS') AND crs_subj_dept_cd = 'BIOL' AND crs_catlg_nbr in ('111', '112'))) THEN 1 WHEN ((appl_plan_1_cd in ('BIOTECBA', 'BIOTECBAPR', 'BIOTECBS', 'BIOTECBSPR') AND crs_subj_dept_cd in ('BIOL', 'BIOT') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd = 'BSNBM' AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('BUSBSB', 'BUSBSBPR') AND crs_subj_dept_cd = 'BUS' AND substr(crs_catlg_nbr, 1, 1) in ('100', '201'))) THEN 1 WHEN ((appl_plan_1_cd in ('CEUSBA1', 'CEUSBAPR1') AND crs_subj_dept_cd = 'CEUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('CHBSPH', 'CHBSPHPR') AND crs_subj_dept_cd in ('SPH', 'HPER') AND substr(crs_catlg_nbr, 1, 1) in ('263', '200', '235', '241'))) THEN 1 WHEN ((appl_plan_1_cd in ('CHEMBA', 'CHEMBAPR', 'CHEMBS', 'CHEMBSPR') AND crs_subj_dept_cd = 'CHEM' AND substr(crs_catlg_nbr, 1, 1) in ('103', '117'))) THEN 1 WHEN ((appl_plan_1_cd in ('CHMEDBSEDP') AND crs_subj_dept_cd in ('CHEM', 'EDUC') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('CJUSBA', 'CJUSBAPR', 'CJUSTSBA') AND crs_subj_dept_cd = 'CJUS' AND substr(crs_catlg_nbr, 1, 1) in ('100', '200'))) THEN 1 WHEN ((appl_plan_1_cd in ('CLARBM', 'CLARUGPD') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('CLCVBA', 'CLCVBAPR', 'CLSTBA', 'CLSTBAPR') AND crs_subj_dept_cd = 'CLAS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('CMCLBAPR') AND crs_subj_dept_cd = 'MSCH' AND substr(crs_catlg_nbr, 1, 1) in ('101', '207', '208', '210', '223', '229'))) THEN 1 WHEN ((appl_plan_1_cd in ('CMLTBA', 'CMLTBAPR') AND crs_subj_dept_cd = 'CMLT' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('CMPDSGNBS', 'CMPDSNBSPR') AND crs_subj_dept_cd = 'SOAD' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('CMPLNGBSPR', 'COMPLINGBS') AND crs_subj_dept_cd = 'LING' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('COGSBA', 'COGSBAPR', 'COGSBS', 'COGSBSPR') AND crs_subj_dept_cd = 'COGS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('CSCIBA', 'CSCIBAPR', 'CSCIBS1', 'CSCIBSPR1', 'CSCITSBS', 'CSCITSBSPR') AND crs_subj_dept_cd = 'CSCI' AND crs_catlg_nbr in ('200', '211', '212'))) THEN 1 WHEN ((appl_plan_1_cd = 'CSSERBSEDP' AND crs_subj_dept_cd = 'EDUC' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('CYGLPOLBS', 'CYGLPOLBSP') AND crs_subj_dept_cd = 'SGIS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('DANCEBFA', 'DANCEBFAPR') AND crs_subj_dept_cd = 'THTR' AND substr(crs_catlg_nbr, 1, 1) in ('100', '121', '125'))) THEN 1 WHEN ((appl_plan_1_cd in ('DATASCBSPR', 'DATASCIBS') AND crs_subj_dept_cd = 'INFO' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('DBLBM') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('EALCBA1', 'EALCBAPR1') AND crs_subj_dept_cd = 'EALC' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('EASBA', 'EASBAPR', 'EASCIBS', 'EASCIBSPR') AND crs_subj_dept_cd = 'EAS' AND crs_catlg_nbr in ('103', '104', '105', '122', '144'))) THEN 1 WHEN ((appl_plan_1_cd in ('EASTBA1', 'EASTBAPR1') AND crs_subj_dept_cd = 'EALC' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('ECHDTSBSED', 'ECHLDBSED', 'ECHLDBSEDP') AND crs_subj_dept_cd = 'EDUC' AND crs_catlg_nbr in ('203', '200'))) THEN 1 WHEN ((appl_plan_1_cd in ('ECONBA', 'ECONBAPR') AND crs_subj_dept_cd = 'ECON' AND crs_catlg_nbr in ('251', '201', '202'))) THEN 1 WHEN ((appl_plan_1_cd = 'ECONMABAPR' AND crs_subj_dept_cd in ('ECON', 'MATH') AND substr(crs_catlg_nbr, 1, 1) in ('251', '202', '118', '119', '210', '211', '212'))) THEN 1 WHEN ((appl_plan_1_cd = 'ECONPOBAPR' AND crs_subj_dept_cd in ('ECON', 'POLS') AND crs_catlg_nbr in ('251', '201', '202', '100', '103', '105', '107'))) THEN 1 WHEN ((appl_plan_1_cd in ('EHBSPHPR', 'EHEHBSPR') AND crs_subj_dept_cd in ('SPH', 'HPER') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('ELEDRBSED', 'ELEDRBSEDP', 'ELEDTBSEDP', 'ELERTSBSED') AND crs_subj_dept_cd = 'EDUC' AND crs_catlg_nbr in ('203', '200'))) THEN 1 WHEN ((appl_plan_1_cd = 'EMIBM' AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd = 'ENGAAADBA' AND crs_subj_dept_cd in ('ENG', 'AAAD') AND crs_catlg_nbr in ('203', '204', '205'))) THEN 1 WHEN ((appl_plan_1_cd in ('ENGBA', 'ENGBAPR') AND crs_subj_dept_cd = 'ENG' AND crs_catlg_nbr in ('203', '204', '205'))) THEN 1 WHEN ((appl_plan_1_cd in ('ENGEDBSEDP') AND crs_subj_dept_cd in ('ENG', 'EDUC') AND crs_catlg_nbr in ('203', '204', '205', '200'))) THEN 1 WHEN ((appl_plan_1_cd = 'ENSTBA' AND crs_subj_dept_cd = 'SPEA' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('ENTRCIBSB', 'ENTRCIBSBP') AND crs_subj_dept_cd = 'BUS' AND substr(crs_catlg_nbr, 1, 1) in ('100', '201'))) THEN 1 WHEN ((appl_plan_1_cd in ('ENVBSES', 'ENVBSES2', 'ENVBSESPR', 'ENVBSESPR2', 'ENVMGBSPA', 'ENVMGBSPAP', 'ENVSCBSES', 'ENVSUSTBA', 'ENVSUSTBAP') AND crs_subj_dept_cd = 'SPEA' AND crs_catlg_nbr in ('163', '183', '124', '184', '182', '185', '186'))) THEN 1 WHEN ((appl_plan_1_cd in ('EPIBSPH', 'EPIBSPHPR') AND crs_subj_dept_cd = 'SPH' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('ERTSPBSEDP') AND crs_subj_dept_cd in ('AST', 'EDUC', 'EAS', 'GEOL') AND crs_catlg_nbr in ('203', '200'))) THEN 1 WHEN ((appl_plan_1_cd = 'EUPHBM' AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('FINAHBA') AND crs_subj_dept_cd in ('FINH', 'FINS', 'SOAD') AND crs_catlg_nbr in ('101', '102', '103'))) THEN 1 WHEN ((appl_plan_1_cd in ('FINASBA1', 'FINASBAPR1') AND crs_subj_dept_cd in ('FINH', 'FINS', 'SOAD') AND crs_catlg_nbr in ('101', '102', '103'))) THEN 1 WHEN ((appl_plan_1_cd in ('FINBSB', 'FINBSBPR') AND crs_subj_dept_cd = 'BUS' AND crs_catlg_nbr in ('101', '102', '103'))) THEN 1 WHEN ((appl_plan_1_cd in ('FLTEBM', 'FLTEUGPD') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('FOLKBA', 'FOLKBAPR') AND crs_subj_dept_cd in ('FOLK', 'MUS') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('FRENBA', 'FRENBAPR') AND crs_subj_dept_cd = 'FRIT' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('FRNEDBSEDP') AND crs_subj_dept_cd in ('EDUC', 'FRIT') AND crs_catlg_nbr in ('200', '203'))) THEN 1 WHEN ((appl_plan_1_cd in ('FSDSBA1', 'FSDSBAPR1') AND crs_subj_dept_cd in ('SOAD') AND crs_catlg_nbr in ('101', '102', '103'))) THEN 1 WHEN ((appl_plan_1_cd in ('FWBSPH', 'FWBSPHPR') AND crs_subj_dept_cd = 'SPH' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('GAMDSGBSPR', 'GAMEDSGNBS') AND crs_subj_dept_cd in ('MSCH', 'INFO') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('GEOGBAPR', 'GEOGBS', 'GEOGBSPR') AND crs_subj_dept_cd = 'GEOG' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('GEOLBAPR', 'GEOLBS', 'GEOLBSPR') AND crs_subj_dept_cd = 'GEOL' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('GERBA', 'GERBAPR') AND crs_subj_dept_cd = 'GER' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('GNDRBA', 'GNDRBAPR') AND crs_subj_dept_cd = 'GNDR' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('GRMEDBSEDP') AND crs_subj_dept_cd in ('EDUC', 'GER') AND crs_catlg_nbr in ('200', '203'))) THEN 1 WHEN ((appl_plan_1_cd in ('GTRCBM', 'HARPUGPD') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('HISTBA', 'HISTBAPR') AND crs_subj_dept_cd = 'HIST' AND crs_catlg_nbr in ('270'))) THEN 1 WHEN ((appl_plan_1_cd in ('HLTCMPBS', 'HLTCMPBSPR') AND crs_subj_dept_cd = 'SPEA' AND crs_catlg_nbr in ('163', '183', '124', '184', '182', '185', '186'))) THEN 1 WHEN ((appl_plan_1_cd in ('HORNBM', 'HORNBSOF') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('HRMBSPA', 'HRMBSPAPR') AND crs_subj_dept_cd = 'SPEA' AND crs_catlg_nbr in ('163', '183', '124', '184', '182', '185', '186'))) THEN 1 WHEN ((appl_plan_1_cd in ('HSTAAADBAP') AND crs_subj_dept_cd in ('HIST', 'AAAD') AND crs_catlg_nbr in ('270'))) THEN 1 WHEN ((appl_plan_1_cd in ('HUBIBA', 'HUBIBAPR', 'HUBIBS', 'HUBIBSPR') AND crs_subj_dept_cd = 'BIOL' AND crs_catlg_nbr in ('111', '112'))) THEN 1 WHEN ((appl_plan_1_cd in ('INDSBS1', 'INDSBSPR1') AND crs_subj_dept_cd = 'SOAD' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('INFOBS', 'INFOBSPR', 'INFOTSBS', 'INFOTSBSPR') AND crs_subj_dept_cd = 'INFO' AND crs_catlg_nbr in ('101'))) THEN 1 WHEN ((appl_plan_1_cd in ('INFOSYBSBP', 'INFOSYSBSB') AND crs_subj_dept_cd = 'BUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('INTLBA1', 'INTLBAPR1', 'INTLBS1', 'INTLBSPR1', 'INTLWINBA', 'INTLWINBAP') AND crs_subj_dept_cd = 'INTL' AND crs_catlg_nbr in ('202', '205', '203', '204'))) THEN 1 WHEN ((appl_plan_1_cd in ('ISENGRBS', 'ISENGRBSPR') AND crs_subj_dept_cd in ('INFO', 'ENGR') AND crs_catlg_nbr in ('111', '250'))) THEN 1 WHEN ((appl_plan_1_cd in ('ITALBA', 'ITALBAPR') AND crs_subj_dept_cd = 'FRIT' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('JOURBAJ', 'JOURBAJPR', 'JRNLMBAJP1') AND crs_subj_dept_cd = 'MSCH' AND crs_catlg_nbr in ('101', '207', '208', '210', '223', '229'))) THEN 1 WHEN ((appl_plan_1_cd in ('JRNEDBSEDP') AND crs_subj_dept_cd in ('EDUC', 'MSCH') AND crs_catlg_nbr in ('101', '207', '208', '210', '223', '229', '203', '200'))) THEN 1 WHEN ((appl_plan_1_cd in ('JSTUBA', 'JSTUBAPR') AND crs_subj_dept_cd = 'JSTU' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('KYDNCBSKPR') AND crs_subj_dept_cd in ('THTR') AND crs_catlg_nbr in ('100', '121', '125'))) THEN 1 WHEN ((appl_plan_1_cd in ('KYEXSBS', 'KYEXSBSPR', 'KYHFSBSKPR') AND crs_subj_dept_cd in ('KINE', 'SPH') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('KYSMMBS', 'KYSMMBSPR') AND crs_subj_dept_cd in ('KINE', 'BUS', 'SPH') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('LGLSTBSBP') AND crs_subj_dept_cd = 'BUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('LINGBA', 'LINGBAPR') AND crs_subj_dept_cd = 'LING' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('LPPBSPA', 'LPPBSPAPR') AND crs_subj_dept_cd = 'SPEA' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('LSTUAASPR', 'LSTUBBS', 'LSTUBBSPR') AND crs_subj_dept_cd = 'LBST' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('MARKBSB', 'MARKBSBPR') AND crs_subj_dept_cd = 'BUS' AND crs_catlg_nbr in ('100', '201'))) THEN 1 WHEN ((appl_plan_1_cd in ('MATHBA', 'MATHBAPR', 'MATHBS', 'MATHBS1', 'MATHBS1PR') AND crs_subj_dept_cd = 'MATH' AND crs_catlg_nbr in ('118', '119', '120', '211', '212'))) THEN 1 WHEN ((appl_plan_1_cd in ('MATHECBAPR') AND crs_subj_dept_cd in ('ECON', 'MATH') AND crs_catlg_nbr in ('118', '119', '120', '211', '212', '251', '201', '202'))) THEN 1 WHEN ((appl_plan_1_cd in ('MDAC02PR', 'MDAC03', 'MDAC03PR', 'MDAC07PR', 'MEDIABA', 'MEDIABAPR') AND crs_subj_dept_cd = 'MSCH' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('MGMTBSB', 'MGMTBSBP') AND crs_subj_dept_cd = 'BUS' AND crs_catlg_nbr in ('100', '201'))) THEN 1 WHEN ((appl_plan_1_cd in ('MGMTSBSPA', 'MGMTSBSPAP') AND crs_subj_dept_cd = 'SPEA' AND crs_catlg_nbr in ('163', '183', '124', '184', '182', '185', '186'))) THEN 1 WHEN ((appl_plan_1_cd in ('MGTHORGBA', 'MGTHORGBAP') AND crs_subj_dept_cd = 'LAMP' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('MICRBA', 'MICRBAPR', 'MICRBS', 'MICRBSPR', 'MLSBS', 'MLSBSPR') AND crs_subj_dept_cd = 'BIOL' AND crs_catlg_nbr in ('111', '112'))) THEN 1 WHEN ((appl_plan_1_cd in ('MSTHTRBFAP', 'MUSTHTRBFA') AND crs_subj_dept_cd in ('MUS', 'THTR') AND crs_catlg_nbr in ('100', '121', '125'))) THEN 1 WHEN ((appl_plan_1_cd in ('MTHEDBSEDP') AND crs_subj_dept_cd in ('EDUC', 'MATH') AND crs_catlg_nbr in ('118', '119', '120', '211', '212', '200', '203'))) THEN 1 WHEN ((appl_plan_1_cd in ('MUSBMPR', 'MUSCONDMIN', 'MUSEDPR') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd = 'NBSPRPR' AND crs_subj_dept_cd = 'NURS' AND crs_catlg_nbr in ('234', '260'))) THEN 1 WHEN ((appl_plan_1_cd in ('NELC', 'NELCBAPR1') AND crs_subj_dept_cd = 'NELC' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('NEUSBS', 'NEUSBSPR') AND crs_subj_dept_cd in ('BIOL', 'CHEM', 'PSY') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('NPMLDBSPAP', 'NPMLDBSPAP') AND crs_subj_dept_cd = 'SPEA' AND crs_catlg_nbr in ('163', '183', '124', '184', '182', '185', '186'))) THEN 1 WHEN ((appl_plan_1_cd in ('NRBSPBSNP', 'NRBSPBSNP2') AND crs_subj_dept_cd = 'NURS' AND crs_catlg_nbr in ('234', '260'))) THEN 1 WHEN ((appl_plan_1_cd in ('OPRBSB', 'OPRBSBPR', 'OPRTSBSBPR') AND crs_subj_dept_cd = 'BUS' AND crs_catlg_nbr in ('100', '201'))) THEN 1 WHEN ((appl_plan_1_cd in ('OTPREPROF') AND crs_subj_dept_cd = 'SPH' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('P&NPMBSPAP', 'PBMLDBSPAP', 'PBMLDRBSPA') AND crs_subj_dept_cd = 'SPEA' AND crs_catlg_nbr in ('163', '183', '124', '184', '182', '185', '186'))) THEN 1 WHEN ((appl_plan_1_cd in ('PERCBM', 'PERCBSOF', 'PERCJZBM') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('PFMGTBSPA') AND crs_subj_dept_cd = 'SPEA' AND crs_catlg_nbr in ('163', '183', '124', '184', '182', '185', '186'))) THEN 1 WHEN ((appl_plan_1_cd in ('PHILBA', 'PHILBAPR') AND crs_subj_dept_cd = 'PHIL' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('PHILPOBAPR') AND crs_subj_dept_cd in ('PHIL', 'POLS') AND crs_catlg_nbr in ('100', '103', '105', '107'))) THEN 1 WHEN ((appl_plan_1_cd in ('PHYEDBSEDP') AND crs_subj_dept_cd in ('EDUC', 'PHYS') AND crs_catlg_nbr in ('200', '203'))) THEN 1 WHEN ((appl_plan_1_cd in ('PHYSBA', 'PHYSBAPR', 'PHYSBS', 'PHYSBSPR') AND crs_subj_dept_cd = 'PHYS' AND crs_catlg_nbr in ('221'))) THEN 1 WHEN ((appl_plan_1_cd in ('PNOBM', 'PNOBSOF', 'PNOUGPD') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('POLANBSPA', 'POLANBSPAP') AND crs_subj_dept_cd = 'SPEA' AND crs_catlg_nbr in ('163', '183', '124', '184', '182', '185', '186'))) THEN 1 WHEN ((appl_plan_1_cd in ('POLSBA', 'POLSBAPR') AND crs_subj_dept_cd = 'POLS' AND crs_catlg_nbr in ('100', '103', '105', '100', '107'))) THEN 1 WHEN ((appl_plan_1_cd in ('POLSECBAPR') AND crs_subj_dept_cd in ('ECON', 'POLS') AND crs_catlg_nbr in ('103', '100', '105', '107'))) THEN 1 WHEN ((appl_plan_1_cd in ('POLSPHBAPR') AND crs_subj_dept_cd in ('PHIL', 'POLS') AND crs_catlg_nbr in ('100', '103', '105', '107'))) THEN 1 WHEN ((appl_plan_1_cd in ('PHYEDBSEDP') AND crs_subj_dept_cd in ('EDUC', 'PHYS') AND crs_catlg_nbr in ('200', '203'))) THEN 1 WHEN ((appl_plan_1_cd in ('PHYSBA', 'PHYSBAPR', 'PHYSBS', 'PHYSBSPR') AND crs_subj_dept_cd = 'PHYS' AND crs_catlg_nbr in ('221'))) THEN 1 WHEN ((appl_plan_1_cd in ('PNOBM', 'PNOBSOF', 'PNOUGPD') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('POLANBSPA', 'POLANBSPAP') AND crs_subj_dept_cd = 'SPEA' AND crs_catlg_nbr in ('163', '183', '124', '184', '182', '185', '186'))) THEN 1 WHEN ((appl_plan_1_cd in ('POLSBA', 'POLSBAPR') AND crs_subj_dept_cd = 'POLS' AND crs_catlg_nbr in ('100', '103', '105', '100', '107'))) THEN 1 WHEN ((appl_plan_1_cd in ('POLSECBAPR') AND crs_subj_dept_cd in ('ECON', 'POLS') AND crs_catlg_nbr in ('103', '100', '105', '107'))) THEN 1 WHEN ((appl_plan_1_cd in ('POLSPHBAPR') AND crs_subj_dept_cd in ('PHIL', 'POLS') AND crs_catlg_nbr in ('100', '103', '105', '107'))) THEN 1 WHEN ((appl_plan_1_cd in ('PORTBAPR') AND crs_subj_dept_cd = 'LATS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('PROSALEBSB', 'PROSLEBSBP') AND crs_subj_dept_cd = 'BUS' AND crs_catlg_nbr in ('100', '201'))) THEN 1 WHEN ((appl_plan_1_cd in ('PSYBA', 'PSYBAPR', 'PSYBS', 'PSYBSPR', 'PSYTSBA') AND crs_subj_dept_cd = 'PSY' AND crs_catlg_nbr in ('155', '211'))) THEN 1 WHEN ((appl_plan_1_cd in ('RCARTSASPR', 'RCARTSBSPR') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('RELBA', 'RELBAPR') AND crs_subj_dept_cd = 'REL' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('RELPHBAPR') AND crs_subj_dept_cd in ('REL', 'PHIL') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('RLESTBSB', 'RLESTBSBPR') AND crs_subj_dept_cd = 'BUS' AND crs_catlg_nbr in ('100', '201'))) THEN 1 WHEN ((appl_plan_1_cd in ('ROPHEBSRPR', 'RPNCBSR', 'RPNCBSRPR', 'RSPMBSRPR', 'RTHEBSR', 'RTHEBSRPR', 'RTHPYBSR', 'RTHPYBSRPR') AND crs_subj_dept_cd = 'SPH' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('SAXJZBM') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('SECEDBSED', 'SECEDBSEDP') AND crs_subj_dept_cd = 'EDUC' AND crs_catlg_nbr in ('200', '203'))) THEN 1 WHEN ((appl_plan_1_cd in ('SLAVBAPR') AND crs_subj_dept_cd = 'SLAV' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('SOCBA', 'SOCBAPR') AND crs_subj_dept_cd = 'SOC' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('SOCSTBSEDP', 'SPCEDBSEDP', 'SPEDEBSED', 'SPEDEBSEDP', 'SPEDSBSED', 'SPEDSBSEDP') AND crs_subj_dept_cd = 'EDUC' AND crs_catlg_nbr in ('200', '203'))) THEN 1 WHEN ((appl_plan_1_cd in ('SPANBA', 'SPANBAPR') AND crs_subj_dept_cd = 'SPAN' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('SPCMBSB', 'SPCMBSBPR') AND crs_subj_dept_cd = 'BUS' AND crs_catlg_nbr in ('100', '201'))) THEN 1 WHEN ((appl_plan_1_cd in ('SPHSAUBAPR', 'SPHSBA', 'SPHSBAPR', 'SPHSBS', 'SPHSBSPR') AND crs_subj_dept_cd = 'SPHS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('SPHSLIBAPR') AND crs_subj_dept_cd in ('LING', 'SPHS') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('SPHSPSBAPR') AND crs_subj_dept_cd in ('PSY', 'SPHS') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('SPNEDBSEDP') AND crs_subj_dept_cd in ('EDUC', 'SPAN') AND crs_catlg_nbr in ('200', '203'))) THEN 1 WHEN ((appl_plan_1_cd in ('STATBS', 'STATBSPR') AND crs_subj_dept_cd = 'STAT' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('STRTCHASPR') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('SWKBSW', 'SWKBSWPR', 'SWKTSBSW') AND crs_subj_dept_cd = 'SOWC' AND crs_catlg_nbr in ('102', '141'))) THEN 1 WHEN ((appl_plan_1_cd in ('TBNBM', 'TPTBM', 'TUBAUGPD', 'VCLBM', 'VLABM', 'VOCEBM', 'VOCEGMBME', 'VOCEJZBM', 'VOCEUGPD') AND crs_subj_dept_cd = 'MUS' AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 WHEN ((appl_plan_1_cd in ('THTRBA', 'THTRBAPR') AND crs_subj_dept_cd = 'THTR' AND crs_catlg_nbr in ('100', '121', '125'))) THEN 1 WHEN ((appl_plan_1_cd in ('VISARBSED') AND crs_subj_dept_cd in ('EDUC', 'FINS', 'SOAD') AND crs_catlg_nbr in ('200', '203'))) THEN 1 WHEN ((appl_plan_1_cd in ('WLDLNGBSED', 'WLLNGBSEDP') AND crs_subj_dept_cd in ('EDUC', 'SPAN', 'GER', 'LATS', 'FRIT', 'SLAV', 'EALC', 'JSTU', 'NELC') AND substr(crs_catlg_nbr, 1, 1) in ('1', '2'))) THEN 1 ELSE 0 END AS appl_plan_mtch_intr_crs, sacks21_data.instructor_term_course_id AS instructor_term_course_id FROM sacks21_data WHERE stu_enrl_stat_cd = 'E'; /**************************************************************** Delivered Views **************************************/ /* sacks21_intr_crs_data-creates a view that selects introductory courses of interest only; excludes Spring 20 */ SELECT sacks21_dataset.student_id AS student_id, sacks21_dataset.female_gndr_ind AS female_gndr_ind, sacks21_dataset.ipeds_ethnic_desc AS ipeds_ethnic_desc, sacks21_dataset.stu_hs_sum_cnvrt_gpa_nbr AS stu_hs_sum_cnvrt_gpa_nbr, sacks21_dataset.stu_drvd_sat_composite_nbr AS stu_drvd_sat_composite_nbr, sacks21_dataset.acad_term_desc AS acad_term_desc, sacks21_dataset.pell_eligibility AS pell_eligibility, sacks21_dataset.course AS course, sacks21_dataset.section_id AS section_id, sacks21_dataset.crs_ofcl_grd_cd AS crs_ofcl_grd_cd, sacks21_dataset.canvas_est_score AS canvas_est_score, sacks21_dataset.canvas_est_assn_scr AS canvas_est_assn_scr, sacks21_dataset.appl_plan_mtch_intr_crs AS appl_plan_mtch_intr_crs, sacks21_dataset.intr_crs_req_for_degr AS intr_crs_req_for_degr, sacks21_dataset.instructor_term_course_id AS instructor_term_course_id FROM sacks21_dataset WHERE (crs_ofcl_grd_cd not in (' ', 'I', 'W', 'S', 'P') AND course in ('AST-A-221', 'AST-A-222', 'BIOL-H-111', 'BIOL-H-112', 'BIOL-L-111', 'BIOL-L-112', 'BUS-A-100', 'BUS-K-201', 'CHEM-C-103', 'CHEM-C-117', 'CHEM-H-117', 'CHEM-J-117', 'CHEM-S-117', 'CSCI-C-200', 'CSCI-C-211', 'CSCI-C-212', 'CSCI-H-200', 'CSCI-H-211', 'CSCI-H-212', 'EAS-E-103', 'EAS-E-104', 'EAS-E-105', 'EAS-E-122', 'EAS-E-144', 'ECON-B-251', 'ECON-E-201', 'ECON-E-202', 'ECON-E-251', 'ECON-S-201', 'ECON-S-202', 'INFO-H-101', 'INFO-I-101', 'INFO-Y-101', 'ENGR-E-111', 'ENGR-E-250', 'MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118', 'SOAD-A-100', 'SOAD-M-109', 'SPH-H-263', 'SPH-K-200', 'SPH-V-235', 'SPH-V-241', 'CJUS-P-100', 'CJUS-P-200', 'SOAD-A-101', 'SOAD-A-102', 'SOAD-A-103', 'HIST-H-270', 'EDUC-G-203', 'EDUC-W-200', 'ENG-L-203', 'ENG-L-204', 'ENG-L-205', 'INTL-I-202', 'INTL-I-203', 'INTL-I-204', 'INTL-I-205', 'MSCH-C-101', 'MSCH-C-207', 'MSCH-C-208', 'MSCH-C-210', 'MSCH-C-223', 'MSCH-C-229', 'INTL-I-202', 'INTL-I-203', 'INTL-I-204', 'INTL-I-205', 'NURS-B-234', 'NURS-B-260', 'SPEA-A-163', 'SPEA-E-183', 'SPEA-H-124', 'SPEA-V-181', 'SPEA-V-182', 'SPEA-V-184', 'SPEA-V-185', 'SPEA-V-186', 'POLS-Y-100', 'POLS-Y-103', 'POLS-Y-105', 'POLS-Y-107', 'PSY-P-155', 'PSY-P211', 'SWK-S-102', 'SWK-S-141', 'SPH-K-150', 'SPH-M-211', 'THTR-T-100', 'THTR-T-121', 'THTR-T-125') AND acad_term_desc <> 'Spring 2020'); /* sacks21_non_intr_crs_data-creates view of non-introductory courses, excludes Spring 20 */ SELECT sacks21_dataset.student_id AS student_id, sacks21_dataset.female_gndr_ind AS female_gndr_ind, sacks21_dataset.ipeds_ethnic_desc AS ipeds_ethnic_desc, sacks21_dataset.stu_hs_sum_cnvrt_gpa_nbr AS stu_hs_sum_cnvrt_gpa_nbr, sacks21_dataset.stu_drvd_sat_composite_nbr AS stu_drvd_sat_composite_nbr, sacks21_dataset.acad_term_desc AS acad_term_desc, sacks21_dataset.pell_eligibility AS pell_eligibility, sacks21_dataset.course AS course, sacks21_dataset.section_id AS section_id, sacks21_dataset.crs_ofcl_grd_cd AS crs_ofcl_grd_cd, case WHEN (sacks21_dataset.canvas_est_score is null ) THEN -999 ELSE sacks21_dataset.canvas_est_score END AS canvas_est_score, case WHEN (sacks21_dataset.canvas_est_assn_scr is null ) THEN -999 ELSE sacks21_dataset.canvas_est_assn_scr END AS canvas_est_assn_scr, sacks21_dataset.intr_crs_req_for_degr AS intr_crs_req_for_degr, sacks21_dataset.appl_plan_mtch_intr_crs AS appl_plan_mtch_intr_crs, sacks21_dataset.instructor_term_course_id AS instructor_term_course_id FROM sacks21_dataset WHERE (crs_ofcl_grd_cd not in (' ', 'I', 'W', 'S', 'P') AND course not in ('AST-A-221', 'AST-A-222', 'BIOL-H-111', 'BIOL-H-112', 'BIOL-L-111', 'BIOL-L-112', 'BUS-A-100', 'BUS-K-201', 'CHEM-C-103', 'CHEM-C-117', 'CHEM-H-117', 'CHEM-J-117', 'CHEM-S-117', 'CSCI-C-200', 'CSCI-C-211', 'CSCI-C-212', 'CSCI-H-200', 'CSCI-H-211', 'CSCI-H-212', 'EAS-E-103', 'EAS-E-104', 'EAS-E-105', 'EAS-E-122', 'EAS-E-144', 'ECON-B-251', 'ECON-E-201', 'ECON-E-202', 'ECON-E-251', 'ECON-S-201', 'ECON-S-202', 'INFO-H-101', 'INFO-I-101', 'INFO-Y-101', 'ENGR-E-111', 'ENGR-E-250', 'MATH-M-118', 'MATH-M-119', 'MATH-M-120', 'MATH-M-211', 'MATH-M-212', 'MATH-S-118', 'MATH-S-211', 'MATH-S-212', 'MATH-V-118', 'SOAD-A-100', 'SOAD-M-109', 'SPH-H-263', 'SPH-K-200', 'SPH-V-235', 'SPH-V-241', 'CJUS-P-100', 'CJUS-P-200', 'SOAD-A-101', 'SOAD-A-102', 'SOAD-A-103', 'HIST-H-270', 'EDUC-G-203', 'EDUC-W-200', 'ENG-L-203', 'ENG-L-204', 'ENG-L-205', 'INTL-I-202', 'INTL-I-203', 'INTL-I-204', 'INTL-I-205', 'MSCH-C-101', 'MSCH-C-207', 'MSCH-C-208', 'MSCH-C-210', 'MSCH-C-223', 'MSCH-C-229', 'INTL-I-202', 'INTL-I-203', 'INTL-I-204', 'INTL-I-205', 'NURS-B-234', 'NURS-B-260', 'SPEA-A-163', 'SPEA-E-183', 'SPEA-H-124', 'SPEA-V-181', 'SPEA-V-182', 'SPEA-V-184', 'SPEA-V-185', 'SPEA-V-186', 'POLS-Y-100', 'POLS-Y-103', 'POLS-Y-105', 'POLS-Y-107', 'PSY-P-155', 'PSY-P211', 'SWK-S-102', 'SWK-S-141', 'SPH-K-150', 'SPH-M-211', 'THTR-T-100', 'THTR-T-121', 'THTR-T-125') AND acad_term_desc <> 'Spring 2020'); /* sacks21_sp20_data - gets Spring enrollments from datasets for retention */ SELECT sacks21_dataset.student_id AS student_id, sacks21_dataset.acad_term_desc AS acad_term_desc, sacks21_dataset.course AS course, sacks21_dataset.section_id AS section_id, sacks21_dataset.instructor_term_course_id AS instructor_term_course_id, sacks21_dataset.crs_ofcl_grd_cd AS crs_ofcl_grd_cd FROM sacks21_dataset WHERE (acad_term_desc = 'Spring 2020' AND crs_ofcl_grd_cd not in (' ', 'I', 'P', 'W'));