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
|