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
  • No labels