-- Ingressantes por transferência interna
select pf.NM_PROGR_FORM as Curso, v.NU_ANO_ADMIS as Ano_Ingresso, ti.DS_TP_INGRS as Tipo_Ingresso, count(8) as Qtde_Alunos
from siga_vinculo v, siga_programa_formacao pf, siga_tipo_ingresso ti
where v.CD_PROGR_FORM=pf.CD_PROGR_FORM
  and v.CD_TP_INGRS=ti.CD_TP_INGRS
  and pf.CD_NIVEL=1
  and v.cd_tp_ingrs=6
  and v.NU_ANO_ADMIS>=2010
group by pf.NM_PROGR_FORM, v.NU_ANO_ADMIS, ti.DS_TP_INGRS  
order by pf.NM_PROGR_FORM, v.NU_ANO_ADMIS  

-- Desvinculados por transferência interna
select pf.NM_PROGR_FORM as Curso, substr(sa.cd_perd_letv,1,4) as Ano_Transferencia,tsa.ds_tp_sit_acad as Tipo_Situacao_Academica, count(8) as Qtde_Alunos
from siga_vinculo v, siga_programa_formacao pf, siga_situacao_academica sa, siga_tipo_situacao_academica tsa
where v.CD_PROGR_FORM=pf.CD_PROGR_FORM
  and v.nu_matr_curso=sa.nu_matr_curso
  and sa.cd_tp_sit_acad=tsa.cd_tp_sit_acad
  and pf.CD_NIVEL=1
  and sa.cd_tp_sit_acad=9
  and sa.dt_def_sit=(select max(sa1.dt_def_sit)
                       from siga_situacao_academica sa1
                      where sa1.nu_matr_curso=sa.nu_matr_curso
                        and sa1.cd_perd_letv=sa.cd_perd_letv
                     )
  and sa.cd_perd_letv>='2010.1'                     
group by pf.NM_PROGR_FORM, substr(sa.cd_perd_letv,1,4),tsa.ds_tp_sit_acad
order by pf.NM_PROGR_FORM, substr(sa.cd_perd_letv,1,4)