create temp table tmp_st_studierendenstammdaten(
	st_studstamm_nr INTEGER  not null, 
matrikelnummer INTEGER , 
vorname VARCHAR(255) , 
familienname VARCHAR(255) , 
geburtsdatum timestamptz , 
st_land1_nr INTEGER , 
st_land2_nr INTEGER , 
geburt_st_land_nr INTEGER , 
personentyp_name VARCHAR(100) , 
personentyp_kb VARCHAR(10) , 
aufnahmedatum timestamptz , 
abmeldungsdatum timestamptz , 
geschlecht INTEGER , 
d_geburt DATE , 
ca12_staat INTEGER , 
second_nationality INTEGER , 
d_exmatr DATE , 
abm_art_p char(10),
semester_max INTEGER 
) 
;

--zuerst Ermittlung max. Semester
select S.st_studstamm_nr,max(SM.semester_tid)::integer as semester_max
into temp tmp_max
from costage_st_studierendenstammdaten_neu S,
costage_st_studien ST,
 costage_st_semester SM,
 costage_st_studienstatus SA
where S.st_studstamm_nr = ST.st_studstamm_nr
and SA.st_studium_nr=ST.st_studium_nr
and SA.st_sem_nr=SM.st_sem_nr
and SA.studienstatustyp not in ('a','o') 
and S.matrikelnummer is not null
group by 1
;




insert into tmp_st_studierendenstammdaten(
 st_studstamm_nr,
       matrikelnummer,
       vorname,
       familienname,
       geburtsdatum,
       st_land1_nr,
       st_land2_nr,
       geburt_st_land_nr,
       personentyp_name,
       personentyp_kb,
       aufnahmedatum,
       abmeldungsdatum,
       geschlecht,
 --      ca12_staat,
 --      second_nationality,
       d_geburt,
--       d_exmatr,
       semester_max)
select 
S.st_studstamm_nr,
       val(S.matrikelnummer),
       vorname,
       familienname,
       geburtsdatum,
       st_land1_nr,
       st_land2_nr,
       geburt_st_land_nr,
       personentyp_name,
       personentyp_kb,
       date_val(aufnahmedatum),
       abmeldungsdatum,
       (case when S.geschlecht='M' then 1
when S.geschlecht='W' then 2
when S.geschlecht='X' then 3
else 4 end)::smallint as geschlecht,
date(S.geburtsdatum) as d_geburt,
M.semester_max
FROM costage_st_laender L, costage_st_studierendenstammdaten_neu S left outer join tmp_max M
on (M.st_studstamm_nr=S.st_studstamm_nr)
where L.st_land_nr=S.st_land1_nr
and S.matrikelnummer is not null
 ;

 --TODO: statt semesterende das tatsächliche Abmeldungsdatum
update tmp_st_studierendenstammdaten set d_exmatr=date(S.semester_ende),
abm_art_p=A.st_abm_art_kurzbezeichnung
from costage_st_personen_abmeldungen A,costage_st_semester S
where A.st_sem_nr=S.st_sem_nr
and A.st_studstamm_nr=tmp_st_studierendenstammdaten.st_studstamm_nr
and S.semester_tid=tmp_st_studierendenstammdaten.semester_max
and A.person_flag='J';

 
update tmp_st_studierendenstammdaten set ca12_staat=val(L.statistik_code) 
from costage_st_laender L
where L.st_land_nr=tmp_st_studierendenstammdaten.st_land1_nr;

update tmp_st_studierendenstammdaten set second_nationality=val(L.statistik_code) 
from costage_st_laender L
where L.st_land_nr=tmp_st_studierendenstammdaten.st_land2_nr;

delete from costage_st_studierendenstammdaten
where 0 < (select count(*) from tmp_st_studierendenstammdaten) --bei CSV Upload kann die Tab. leer sein, daher abfangen
;

insert into costage_st_studierendenstammdaten(
 st_studstamm_nr,
       matrikelnummer,
       vorname,
       familienname,
       geburtsdatum,
       st_land1_nr,
       st_land2_nr,
       geburt_st_land_nr,
       personentyp_name,
       personentyp_kb,
       aufnahmedatum,
       abmeldungsdatum,
       geschlecht,
       ca12_staat,
       second_nationality,
       d_geburt,
       d_exmatr,
       abm_art_p,
       semester_max)
select 
st_studstamm_nr,
       matrikelnummer,
       vorname,
       familienname,
       geburtsdatum,
       st_land1_nr,
       st_land2_nr,
       geburt_st_land_nr,
       personentyp_name,
       personentyp_kb,
       aufnahmedatum,
       abmeldungsdatum,
       geschlecht,
       ca12_staat,
       second_nationality,
       d_geburt,
       d_exmatr,
       abm_art_p,
       semester_max
FROM tmp_st_studierendenstammdaten
 ;

drop table tmp_max;
drop table tmp_st_studierendenstammdaten;