select spriden_id, spriden_last_name, spriden_first_name, sgbstdn_levl_code, sgbstdn_program_1, sgbstdn_styp_code, (select max(sfrstcr_term_code) from sfrstcr where sgbstdn_pidm = sfrstcr_pidm and sfrstcr_rsts_code in ('RE', 'RW')) max_reg_term, (select max(shrtckn_term_code) from shrtckn where sgbstdn_pidm = shrtckn_pidm) max_hist_term from sgbstdn a join spriden on sgbstdn_pidm = spriden_pidm and spriden_change_ind is null where a.sgbstdn_stst_code = 'AS' and a.sgbstdn_term_code_eff = (select max(b.sgbstdn_term_code_eff) from sgbstdn b where a.sgbstdn_pidm = b.sgbstdn_pidm) and a.sgbstdn_term_code_eff <= '201320' and exists (select 1 from sgbstdn c where a.sgbstdn_pidm = c.sgbstdn_pidm and c.sgbstdn_term_code_eff <= '201220') and not exists (select 1 from shrtckn where a.sgbstdn_pidm = shrtckn_pidm and shrtckn_term_code >= '201220') and not exists (select 1 from sfrstcr where a.sgbstdn_pidm = sfrstcr_pidm and sfrstcr_rsts_code in ('RE', 'RW') and sfrstcr_term_code >= '201220') and not exists (select 1 from shrtram where a.sgbstdn_pidm = shrtram_pidm and shrtram_term_code_entered >= '201220') order by spriden_last_name, spriden_first_name, sgbstdn_program_1, spriden_id