select count(distinct t1.person_uid)					
    , academic_year					
    ,case when t2.program is null then ap.program else t2.program  end as program_new					
    ,case when t2.major_desc is null then ap.major_desc else t2.major_desc  end as major_new					
from					
student_course t1					
left join					
(					
    select distinct person_uid, academic_period, program, major_desc, primary_program_ind					
    from 					
    academic_study ac1					
    where academic_year <='1819'					
        and academic_period=(					
            select max(academic_period) from academic_study ac2					
            where ac2.academic_year <= '1819' and ac2.registered_ind='Y' and ac1.person_uid=ac2.person_uid)					
                and primary_program_ind='Y'					
    ) t2 on t1.person_uid=t2.person_uid					
left join					
(					
    select  distinct person_uid, academic_period, program, major_desc					
    from admissions_application ap1					
    where academic_year='1819' 					
        and academic_period=(select max(academic_period) 					
            from admissions_application ap2					
            where academic_year<='1819' and primary_program_ind='Y' and ap1.person_uid=ap2.person_uid)					
    ) ap on ap.person_uid=t1.person_uid					
where  t1.academic_year in ('1819')   and final_grade is not null and REGISTRATION_STATUS!='WE'					
group by t1.academic_year					
,case when t2.program is null then ap.program else t2.program  end					
,case when t2.major_desc is null then ap.major_desc else t2.major_desc end					
union all					
select count(distinct t1.person_uid)					
    , academic_year					
    ,case when t2.program is null then ap.program else t2.program  end as program_new					
    ,case when t2.major_desc is null then ap.major_desc else t2.major_desc  end as major_new					
from					
student_course t1					
left join					
(					
    select distinct person_uid, academic_period, program, major_desc, primary_program_ind					
    from 					
    academic_study ac1					
    where academic_year <='1617'					
        and academic_period=(					
            select max(academic_period) from academic_study ac2					
            where ac2.academic_year <= '1617' and ac2.registered_ind='Y' and ac1.person_uid=ac2.person_uid)					
                and primary_program_ind='Y'					
    ) t2 on t1.person_uid=t2.person_uid					
left join					
(					
    select  distinct person_uid, academic_period, program, major_desc					
    from admissions_application ap1					
    where academic_year='1617' 					
        and academic_period=(select max(academic_period) 					
            from admissions_application ap2					
            where academic_year<='1617' and primary_program_ind='Y' and ap1.person_uid=ap2.person_uid)					
    ) ap on ap.person_uid=t1.person_uid					
where  t1.academic_year in ('1617')   and final_grade is not null and REGISTRATION_STATUS!='WE'					
group by t1.academic_year					
,case when t2.program is null then ap.program else t2.program  end					
,case when t2.major_desc is null then ap.major_desc else t2.major_desc end					
union all					
select count(distinct t1.person_uid)					
    , academic_year					
    ,case when t2.program is null then ap.program else t2.program  end as program_new					
    ,case when t2.major_desc is null then ap.major_desc else t2.major_desc  end as major_new
  from					
student_course t1					
left join					
(					
    select distinct person_uid, academic_period, program, major_desc, primary_program_ind					
    from 					
    academic_study ac1					
    where academic_year <='1718'					
        and academic_period=(					
            select max(academic_period) from academic_study ac2					
            where ac2.academic_year <= '1718' and ac2.registered_ind='Y' and ac1.person_uid=ac2.person_uid)					
                and primary_program_ind='Y'					
    ) t2 on t1.person_uid=t2.person_uid					
left join					
(					
    select  distinct person_uid, academic_period, program, major_desc					
    from admissions_application ap1					
    where academic_year='1718' 					
        and academic_period=(select max(academic_period) 					
            from admissions_application ap2					
            where academic_year<='1718' and primary_program_ind='Y' and ap1.person_uid=ap2.person_uid)					
    ) ap on ap.person_uid=t1.person_uid					
where  t1.academic_year in ('1718')   and final_grade is not null and REGISTRATION_STATUS!='WE'					
group by t1.academic_year					
,case when t2.program is null then ap.program else t2.program  end					
,case when t2.major_desc is null then ap.major_desc else t2.major_desc end					
union all					
select count(distinct t1.person_uid)					
    , academic_year					
    ,case when t2.program is null then ap.program else t2.program  end as program_new					
    ,case when t2.major_desc is null then ap.major_desc else t2.major_desc  end as major_new  
   from					
student_course t1					
left join					
(					
    select distinct person_uid, academic_period, program, major_desc, primary_program_ind					
    from 					
    academic_study ac1					
    where academic_year <='2021'					
        and academic_period=(					
            select max(academic_period) from academic_study ac2					
            where ac2.academic_year <= '2021' and ac2.registered_ind='Y' and ac1.person_uid=ac2.person_uid)					
                and primary_program_ind='Y'					
    ) t2 on t1.person_uid=t2.person_uid					
left join					
(					
    select  distinct person_uid, academic_period, program, major_desc					
    from admissions_application ap1					
    where academic_year='2021' 					
        and academic_period=(select max(academic_period) 					
            from admissions_application ap2					
            where academic_year<='2021' and primary_program_ind='Y' and ap1.person_uid=ap2.person_uid)					
    ) ap on ap.person_uid=t1.person_uid					
where  t1.academic_year in ('2021')   and final_grade is not null and REGISTRATION_STATUS!='WE'					
group by t1.academic_year					
,case when t2.program is null then ap.program else t2.program  end					
,case when t2.major_desc is null then ap.major_desc else t2.major_desc end					
union all					
select count(distinct t1.person_uid)					
    , academic_year					
    ,case when t2.program is null then ap.program else t2.program  end as program_new					
    ,case when t2.major_desc is null then ap.major_desc else t2.major_desc  end as major_new 
    from					
student_course t1					
left join					
(					
    select distinct person_uid, academic_period, program, major_desc, primary_program_ind					
    from 					
    academic_study ac1					
    where academic_year <='1920'					
        and academic_period=(					
            select max(academic_period) from academic_study ac2					
            where ac2.academic_year <= '1920' and ac2.registered_ind='Y' and ac1.person_uid=ac2.person_uid)					
                and primary_program_ind='Y'					
    ) t2 on t1.person_uid=t2.person_uid					
left join					
(					
    select  distinct person_uid, academic_period, program, major_desc					
    from admissions_application ap1					
    where academic_year='1920' 					
        and academic_period=(select max(academic_period) 					
            from admissions_application ap2					
            where academic_year<='1920' and primary_program_ind='Y' and ap1.person_uid=ap2.person_uid)					
    ) ap on ap.person_uid=t1.person_uid					
where  t1.academic_year in ('1920')   and final_grade is not null and REGISTRATION_STATUS!='WE'					
group by t1.academic_year					
,case when t2.program is null then ap.program else t2.program  end					
,case when t2.major_desc is null then ap.major_desc else t2.major_desc end