"Tiedonkeruu koskee kaikkia yliopistoissa vuonna [vuosi] suoritettuja tutkintoja."

Tietojen toimitus

CSC toimittaa vuosittain yliopistojen tutkintotiedonkeruun tiedot Tilastokeskukselle sovitun aikataulun mukaisesti.
Tiedot toimitetaan siirtotiedostona: Tietuekuvaus.

Poiminta

Alustus

Dokumentaation tueksi on tuotu SQL-kielisiä kyselyitä, joissa taustalla on temp-tietokantataulu #tktutk johon tiedot kerätään, joka vastaa tietuekuvaukseltaan hyvin lähelle sitä mikä Tilastokeskuksen tiedonkeruissa halutaan, sekä yksirivinen temp-tietokantataulu #param, jossa on tieto nyt käsittelyssä olevasta korkeakoulusta sekä tiedonkeruun kannalta oleellisia muita tietoja kuten tilastointivuosi (VUOSI).

SQL
-- Temp-taulu tietojen valintaa varten
-- NB! param-taulussa saa olla vain yksi rivi!
create table #param (
	vuosi varchar(4),
	org varchar(30),--KK-lyhenne (DB-nimi)
	tunn varchar(5),--Oppilaitostunnus
	tilpvm date,--Tilastointipäivä
)
;
insert into #param (vuosi,org,kk,tilpvm)
	select
	@vuosi as vuosi
	,UPPER(DB_NAME()) as org
	,(case
	  when UPPER(DB_NAME()) = 'AA' then '03'
	  when UPPER(DB_NAME()) = 'AYO' then '25'
	  when UPPER(DB_NAME()) = 'HY' then '01'
	  when UPPER(DB_NAME()) = 'ISY' then '26'
	  when UPPER(DB_NAME()) = 'JY' then '06'
	  when UPPER(DB_NAME()) = 'LAY' then '21'
	  when UPPER(DB_NAME()) = 'LTY' then '14'
	  when UPPER(DB_NAME()) = 'OY' then '04'
	  when UPPER(DB_NAME()) = 'SHH' then '10'
	  when UPPER(DB_NAME()) = 'TAIY' then '28'
	  when UPPER(DB_NAME()) = 'TaY' then '05'
	  when UPPER(DB_NAME()) = 'TTY' then '15'
	  when UPPER(DB_NAME()) = 'TY' then '27'
	  when UPPER(DB_NAME()) = 'VY' then '13'
	  else 'NA'
	  end
	) as kk
	,@vuosi+'-09-20' as tilpvm
;
--select * from #param
-- Temp-taulu tiedonkeruun tietoja varten
create table #tktutk (
	kk varchar(2),--Yliopisto
	ht varchar(11),--Henkilötunnus
	kirtu varchar(5),--Kirjoihintulo yliopistoon
	opoik varchar(5),--Opiskeluoikeuden alkaminen nykyisessä tutkinnossa
	sv varchar(3),--Aineenopettajan pedagogiset opinnot
	om varchar(1),--Opiskelumuoto
	suorpvm varchar(8),--Tutkinnon suorituspäivämäärä
	kkun varchar(3),--Koulutusyksikön sijaintikunta
	sp varchar(1),--Sukupuoli
	aikieliy varchar(1),--Äidinkieli
	kansaly varchar(3),--Kansalaisuus
	snimi nvarchar(255),--Sukunimi
	enimi nvarchar(255),--Etunimet
--NB! pituus
	opoylio varchar(4),--Omassa yliopistossa suoritetut opintopisteet (pl. avoin yliopisto-opetus)
	opaylio varchar(3),--Avoimessa yliopisto-opetuksessa suoritetut opintopisteet (sekä omat että muu yliopisto)
	opmylio varchar(3),--Muissa yliopistoissa suoritetut opintopisteet (pl. avoin yliopisto)
	opamk varchar(3),--Ammattikorkeakouluissa suoritetut opintopisteet
	opulk varchar(3),--Ulkomailla suoritetut opintopisteet
	opmuu varchar(3),--Muualla suoritetut opintopisteet
	opyht varchar(4),--Opintopisteet yhteensä
	lkmmt varchar(1),--Lukuvuosimaksu ja maksullinen tilauskoulutus
	koulky varchar(6),--Koulutuskoodi
--
	optutk varchar(4),--Tutkinto-opintosuorituksen opintopisteet
--
    ohjauksenAla      varchar(3), -- OKM Ohjauksen ala
    ekkoodi         varchar(3), -- Erikoistumiskoulutuksen koodi  
    kvtutk          varchar(2), -- Opintosuorituksen luokittelun 9 (Suoritus on kansainvälinen yhteistutkinto) ja 10 (Suoritus on kansainvälinen kaksoistutkinto) tieto
	oppijanumero varchar(200), --kansallinenoppija_id
  --
	opiskelijaavain varchar(100),
	opintosuoritusavain varchar(100),
	opiskeluoikeusavain varchar(100),
	suorituspaivamaara date,
	opiskelija_id int,
	opintosuoritus_id int,
	opiskeluoikeus_id int
)
;


Perusjoukon valinta

Haetaan tietovarannosta kaikki tutkinto-lajiset opintosuoritukset, jotka on suoritettu tilastointivuoden aikana.

Liitetään suoritukseen opiskelija (henkilö), josta saadaan perustietoja:

  • Korkeakoulu (kk)
  • Henkilötunnus (ht)
  • Sukupuoli (sp)
  • Sukunimi (snimi)
  • Etunimet (enimi)
  • Kansalaisuus (kansaly) :: nb! vain suomalaiset
  • Äidinkieli (aikieliy)
  • Kirjoilletulo (kirtu) :: tässä päätellään lukukausi kuukauden perusteella: suurempi tai yhtäsuuri kuin elokuu => syyslukukausi, muuten kevätlukukausi
SQL
---
-- Lisätään perustiedot (henkilö, opiskelija)
-- * täytyy olla tutkinto
---
insert into #tktutk (
	kk,ht,sp,snimi,enimi,kansaly,aikieliy,kirtu
	,suorpvm
	,suorituspaivamaara
	,opiskelijaavain,opiskelija_id
	,opintosuoritusavain,opintosuoritus_id
	,opiskeluoikeusavain,opiskeluoikeus_id
)
select
	 kk = (select kk from #param)
	,ht = h.Henkilotunnus
	,sp = (select top 1 k.Koodi from Sukupuoli k where k.ID=h.SukupuoliID)
	,snimi = h.Sukunimi
	,enimi = h.Etunimet
	,kansaly =
	--nb! vain suomalaiset tässä (jää null!)
	 (select top 1 m.Koodi
	  from Henkilon_maa hm
	  inner join Maa m on m.ID=hm.MaaID
	  inner join Maan_rooli mr on mr.ID=hm.Maan_rooliID
	  where hm.HenkiloID=h.ID --sido
	  and mr.Koodi='1' --Kansalaisuus
	  and m.Koodi='246' --Suomi
	 )
	,aikieliy = (select top 1
	  case k.Koodi when 'fi' then '1' when 'sv' then '2' else '3' end
	  from Kieli k where k.ID=h.KieliID) ,kirtu = (cast(year(p.Kirjoihintulopaivamaara) as varchar)
  +(case
    -- 8-12 (elo-joulu): syksy
    when month(p.Kirjoihintulopaivamaara)>=8 then '2'
    -- 1-7 (tammi-heinä): kevät
    else '1'
    end)
 )
,oppijanumero = p.Kansallinen_op_id
,opiskelijaavain = p.Avain
,opiskelija_id = p.ID
,DB=h.DB from Henkilo h
inner join Opiskelija p on p.HenkiloID=h.ID
inner join Opintosuoritus s on s.OpiskelijaID=p.ID
inner join Opintosuorituksen_laji ol on ol.ID=s.Opintosuorituksen_lajiID
left join Opiskeluoikeus o on o.ID=s.OpiskeluoikeusID
where 1=1
-- tutkinto-lajinen
and ol.Koodi=1
-- suoritettu tilvuonna
and year(s.Suorituspaivamaara)=(select vuosi from #param)
;

Heti perään lisätään perusjoukkoon erikoislääkärit ja erikoishammaslääkärit siten, että katsotaan muu opintosuoritus -lajisista suorituksista sellaiset joihin liittyy varta vasten oleva pätevyys-koodiston arvo 'ela' tai 'eha', ja suorituspäivä on tilastointivuoden aikana. Yllä mainitun tietojoukon lisäksi päätellään pätevyyskoodin perusteella koulutuskoodi siten, että pätevyys 'ela' => '775101' ja 'eha' => '775201'.

SQL
-- lisätään erikoislääkärit ja -hammaslääkärit
-- nb! lisätään myös koulutuskoodi, joka päätellään pätevyyden kautta!
insert into #tktutk (
	kk,ht,sp,snimi,enimi,kansaly,aikieliy,kirtu
	,suorpvm
	,suorituspaivamaara
	,koulky
	,opiskelijaavain,opiskelija_id
	,opintosuoritusavain,opintosuoritus_id
	,opiskeluoikeusavain,opiskeluoikeus_id
)
select
	 kk = (select kk from #param)
	,ht = h.Henkilotunnus
	,sp = (select top 1 k.Koodi from Sukupuoli k where k.ID=h.SukupuoliID)
	,snimi = h.Sukunimi
	,enimi = h.Etunimet
	,kansaly =
	--nb! vain suomalaiset tässä (jää null!)
	 (select top 1 m.Koodi
	  from Henkilon_maa hm
	  inner join Maa m on m.ID=hm.MaaID
	  inner join Maan_rooli mr on mr.ID=hm.Maan_rooliID
	  where hm.HenkiloID=h.ID --sido
	  and mr.Koodi='1' --Kansalaisuus
	  and m.Koodi='246' --Suomi
	 )
	,aikieliy = (select top 1
	  case k.Koodi when 'fi' then '1' when 'sv' then '2' else '3' end
	  from Kieli k where k.ID=h.KieliID)
	,kirtu = (cast(year(p.Kirjoihintulopaivamaara) as varchar)
	  +(case
		-- 8-12 (elo-joulu): syksy
		when month(p.Kirjoihintulopaivamaara)>=8 then '2'
		-- 1-7 (tammi-heinä): kevät
		else '1'
		end)
	 )
	,suorpvm = convert(varchar,s.Suorituspaivamaara,112) --112=yyyymmdd
	--
	,s.Suorituspaivamaara
	--
	,koulky = (
	  case kp.Koodi
	  when 'ela' then '775101'
	  when 'eha' then '775201'
	  end)
	--
	,p.Avain opiskelijaavain
	,p.ID opiskelija_id
	,s.Avain opintosuoritusavain
	,s.ID opintosuoritus_id
	,o.Avain opiskeluoikeusavain
	,o.ID opiskeluoikeus_id
from Henkilo h
inner join Opiskelija p on p.HenkiloID=h.ID
inner join Opintosuoritus s on s.OpiskelijaID=p.ID
inner join Opintosuorituksen_laji ol on ol.ID=s.Opintosuorituksen_lajiID
--
inner join Henkilon_patevyys hp on hp.HenkiloID=h.id and hp.OpintosuoritusID=s.ID
inner join Patevyys kp on kp.ID=hp.PatevyysID
left join Opiskeluoikeus o on o.ID=s.OpiskeluoikeusID
where 1=1
-- tutkinto- TAI muu-lajinen
and ol.Koodi in ('1','2')
-- suoritettu tilvuonna
and year(s.Suorituspaivamaara)=(select vuosi from #param)
-- täytyy olla pätevyys
and kp.Koodi in ('ela','eha')
-- ei saa olla jo
and s.ID not in (select opintosuoritus_id from #tktutk)
;

Lisätään joukkoon rivit niistä tapauksista, joissa

  • erikoistumiskoulutus (19) -tyyppinen opiskeluoikeus on 
  • päättynyt tilastointivuoden aikana (opiskeluoikeuden tila päättynyt (3) on alkanut tilastovuonna TAI 1.1. seuraavana vuonna ja opiskeluoikeuden päättymispäivä on tilastointivuonna).
SQL
-- erikoistumiskoulutus
insert into #tktutk (
	kk,ht,sp,snimi,enimi,kansaly,aikieliy,kirtu
	,suorpvm
	,suorituspaivamaara
	,koulky
	,opiskelijaavain,opiskelija_id
	,opintosuoritusavain,opintosuoritus_id
	,opiskeluoikeusavain,opiskeluoikeus_id
)
select
	 kk = (select kk from #param)
	,ht = h.Henkilotunnus
	,sp = (select top 1 k.Koodi from Sukupuoli k where k.ID=h.SukupuoliID)
	,snimi = h.Sukunimi
	,enimi = h.Etunimet
	,kansaly =
	--nb! vain suomalaiset tässä (jää null!)
	 (select top 1 m.Koodi
	  from Henkilon_maa hm
	  inner join Maa m on m.ID=hm.MaaID
	  inner join Maan_rooli mr on mr.ID=hm.Maan_rooliID
	  where hm.HenkiloID=h.ID --sido
	  and mr.Koodi=''1'' --Kansalaisuus
	  and m.Koodi=''246'' --Suomi
	 )
	,aikieliy = (select top 1
	  case k.Koodi when ''fi'' then ''1'' when ''sv'' then ''2'' else ''3'' end
	  from Kieli k where k.ID=h.KieliID)
	,kirtu = (cast(year(p.Kirjoihintulopaivamaara) as varchar)
	  +(case
		-- 8-12 (elo-joulu): syksy
		when month(p.Kirjoihintulopaivamaara)>=8 then ''2''
		-- 1-7 (tammi-heinä): kevät
		else ''1''
		end)
	 )
	,suorpvm = CONVERT(varchar,o.Paattymispaivamaara,112)--112=yyyymmdd
	--
	,suorituspaivamaara = o.Paattymispaivamaara
	--
	,koulky = ''999999''
	--
	,p.Avain opiskelijaavain
	,p.ID opiskelija_id
	,null --TODO? s.Avain opintosuoritusavain
	,null --TODO? s.ID opintosuoritus_id
	,o.Avain opiskeluoikeusavain
	,o.ID opiskeluoikeus_id
from Henkilo h
inner join Opiskelija p on p.HenkiloID=h.ID
inner join Opiskeluoikeus o on o.OpiskelijaID=p.ID
inner join Op_oikeuteen_liittyva_tila ot on ot.OpiskeluoikeusID=o.ID
inner join Opiskeluoikeuden_tila ti on ti.ID=ot.Op_oikeuden_tilaID
inner join Opiskeluoikeuden_tyyppi ty on ty.ID=o.Op_oikeuden_tyyppiID
where 1=1
-- tila
-- päättynyt
and ti.Koodi=''3''
-- päättynyt tilvuonna
and(year(ot.Alkamispaivamaara)=(select vuosi from #param)
-- tai päivä sen jälkeen, eli 1.1.vuosi+1
 or year(dateadd(day,-1,ot.Alkamispaivamaara))=(select vuosi from #param)
)
and year(o.Paattymispaivamaara)=(select vuosi from #param)
-- erikoistumiskoulutus
and ty.Koodi in (''19'')
-- virta-kanta: vain yliopistot
and p.DB in (
    select i.DB from Yhteiset.Asetukset.Instanssit i
    inner join Yhteiset.Asetukset.Ohjaus_Parametrit p on p.Asetus=i.OrganisaatioKoodi
    where p.Toiminto='Oppilaitostyyppi' and p.Arvo='42'--Yliopistot
    and i.Lataustuotanto='1'
)
;


Yritetään hakea opiskeluoikeus-tieto vielä erikseen, jos puuttuu:

SQL
---
-- Opiskeluoikeustiedot
---
/*
om varchar(1),--Opiskelumuoto
kkun varchar(3),--Opiskelukunta
lkmmt varchar(1),--Lukukausimaksukokeilu ja maksullinen tilauskoulutus
opoik varchar(5),--Opiskeluoikeuden alkaminen nykyisessä tutkinnossa
*/
-- valitaan yksi per tutkinto, jos puuttuu
-- nb! luultavasti tarpeeton kysely
update T
set opiskeluoikeus_id=o.ID, opiskeluoikeusavain=o.avain
from #tktutk T
inner join Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
inner join Opiskeluoikeuden_tyyppi ot on ot.ID=o.Op_oikeuden_tyyppiID
inner join Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=o.ID
inner join Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
where 1=1
-- päivitetään vain jos puuttuu (toivottavasti ei puutu!)
and T.opiskeluoikeus_id is null
-- tutkinto-tyyppinen
and ot.Koodi in ('1','2','3','4','5','6','7')
-- voimassa SUORpvm
and T.suorituspaivamaara between o.Alkamispaivamaara and coalesce(o.Paattymispaivamaara,'9999-01-01')
-- tila
-- TO-DONE-ish: TULEE OLLA PÄÄTTYNYT
--              mutta ei välttämättä vielä suorituspäivänä (+1 pv pitäisi jo olla, mutta ei katsota)
and kt.Koodi in ('3') --päättynyt
--and T.suorituspaivamaara between lt.Alkamispaivamaara and coalesce(lt.Paattymispaivamaara,'9999-01-01')
;

Tietueet

Oppilaitostunnus (tunn)

Oppilaitostunnus on tietovarannon perusasetuksissa, josta tieto on poimittu parametri-aputauluun heti aluksi. Parametri-taulusta tieto saatiin kullekin riville jo ensimmäisissä poiminnoissa.

Henkilötunnus (ht)

Poimitaan suoraan tietovarantoon tuotu opiskelijan henkilötunnus-tieto.

Kirjoihintulo yliopistoon (kirtu)

Kirtu (kirjoille tulo): Tarkoittaa kirjoille tuloa (opiskelupaikan vastaanottamista ja lukuvuosi-ilmoittautumista) ensimmäisen kerran kyseenomaiseen yliopistoon.

Poimitaan suoraan tietovarantoon tuotu opiskelijan kirjoilletulopäivämäärä-tieto muodossa VVVVL, jossa VVVV on vuosi ja L on lukukausi (2=syksy jos kuukausi on 8-12, 1=kevät muutoin).

Opiskeluoikeuden alkaminen nykyisessä tutkinnossa (opoik)

Opoik (opiskeluoikeuden alkaminen): Tarkoittaa kyseenomaisen opiskeluoikeuden alkamista kyseenomaisessa yliopistossa.

Suoritettuun tutkintoon liitetyn opiskeluoikeuden alkamispäivämäärästä muodostettu tieto VVVVL, jossa VVVV on vuosi ja L on lukukausi (2=syksy jos kuukausi on 8-12, 1=kevät muutoin).

SQL
-- opoik
update T
set opoik=(cast(year(o.Alkamispaivamaara) as varchar)
         +(case
		   -- 8-12 (elo-joulu): syksy
		   when month(o.Alkamispaivamaara)>=8 then '2'
		   -- 1-7 (tammi-heinä): kevät
		   else '1'
           end)
    )
from #tktutk T
inner join Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
;

-- 2017-07-01 Jos Kirtu tyhjä Erikoislääkäri, opiskeluoikeuden alkamispaivamaara
-- kirtu
UPDATE T
SET kirtu=(cast(year(o.Alkamispaivamaara) as varchar)
         +(case
           -- 8-12 (elo-joulu): syksy
           when month(o.Alkamispaivamaara)>=8 then '2'
           -- 1-7 (tammi-heinä): kevät
           else '1'
           end)
    )
FROM #tktutk T
inner join virta.dbo.Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
WHERE kirtu IS NULL
AND T.koulky IN ('775101','775201','775999')
;     




Aineenopettajan pedagogiset opinnot (sv)

Päättely aineenopettajan pedagogisista opinnoista tehdään sen perusteella liittyykö valittuun opintosuoritukseen henkilön pätevyys -tieto, joka on joku seuraavista:

  • "il" ja "ik" = Opettaja
  • "im" = Erilliset opettajan pedagogiset opinnot
  • "oa" = Opettajan pedagogiset opinnot osana tutkintoa

Jos liittyy asetetaan '001', muuten tyhjä.

Opettaja pätevyyskoodit


NB! 2015-01-15: Pätevyydet pitää katsoa koko hierarkiasta!

SQL
-- sv
update T
set sv='001'
from #tktutk T
inner join Henkilon_patevyys hp on hp.OpintosuoritusID=T.opintosuoritus_id
inner join Patevyys p on p.ID=hp.PatevyysID
where 1=1
and p.Koodi in ('ik','il','im') --opettaja, erill opet pedag opinn
and sv is null
;
 
-- 2017-06-30
-- SV-muuttuja vielä kerran kun opintosuoritusten hierarkia on käytettävissä    
UPDATE T
SET T.sv='001'
FROM #tktutk T
inner join #suoritushierarkia s on s.opiskelija_id=T.opiskelija_id --and s.vanhempi_id=T.opintosuoritus_id
inner join virta.dbo.Henkilon_patevyys hp on hp.OpintosuoritusID=s.vanhempi_id
inner join virta.dbo.Patevyys p on p.ID=hp.PatevyysID
WHERE T.sv is null
AND p.Koodi in ('ik','il','im') --opettaja, erill opet pedag opinn
;
 
Opiskelumuoto (om)

Asetuksen mukainen maisteriohjelma -tieto valitaan suoraan opiskeluoikeuden tiedoista. NB! tieto on poistumassa

Opiskelumuodon muun maisteriohjelma -arvon päättely on per opiskeluoikeus:

  • on ylempään korkeakoulututkintoon johtava (ei asetuksen mukainen)
  • ei ole lääketieteen tai hammaslääketieteen lisensiaatti tai ylempään kk-tutkintoon johtava lääketieteen alan erikoistuminen
  • ei liity alempaan korkeakoulututkintoon johtavaa opiskeluoikeutta
Erikoistumiskoulutus, ei-tutkintokoulutus 999999

Erikoistumiskoulutuksen koulutuskoodi jos on. linkkirikki


SQL
-- om :: Opiskelumuoto (nb! koulk jälkeen!)
-- 1=Asetuksen mukainen maisteriohjelma
-- nb! poistumassa
update T
set om=(case when o.Maisteriohjelma='1' then '1' end) -- asetuksen mukainen maisteriohjelma, muutoin erillinen päättely
from #tktutk T
inner join Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
where 1=1
;
-- om
-- 2=Muu maisteriohjelma
-- päätellään opiskeluoikeuksien liittyvyyksistä:
-- * ylempi, ei asetuksen mukainen, ei liittyvää alempaa
-- => maisteriohjelma (ei asetuksen mukainen)
update T
set om='2'
from #tktutk T
inner join Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
inner join Opiskeluoikeuden_tyyppi ot on ot.ID=O.Op_oikeuden_tyyppiID and ot.Koodi='4' --ylempi kk
where 1=1
and T.om is null
and o.ID not in (
	select ol.OpiskeluoikeusID2
	from Opiskeluoikeuden_liittyvyys ol
	inner join Opiskeluoikeus ao on ao.OpiskelijaID=T.opiskelija_id and ao.ID=ol.OpiskeluoikeusID
	inner join Opiskeluoikeuden_tyyppi aot on aot.ID=ao.Op_oikeuden_tyyppiID and aot.Koodi='2' --alempi kk
)
-- Karsitaan lääketieteen lisensiaatit pois:
and T.koulky not like '7721%'
--..ja hammaslääketieteen
and T.koulky not like '7722%'
--..ja varmuuden vuoksi erikoistumiset (ylempi kk-tutkinto)
and T.koulky not like '7751%' and T.koulky not like '7752%'
-- alemman kk-tutkinnon jo suorittaneet (tutkinto liitetty tähän opiskeluoikeuteen!)
and o.ID not in (
	select s.OpiskeluoikeusID
	from Opintosuoritus s
	inner join Opintosuorituksen_laji kl on kl.ID=s.Opintosuorituksen_lajiID
	inner join Tutkintonimike kt on kt.ID=s.TutkintonimikeID
	where s.OpiskelijaID=T.opiskelija_id
	and s.OpiskeluoikeusID is not null --null-arvojen palauttaminen rikkoo ilmeisesti sisäkyselyn käyttötarkoituksen? ts. null==null?!?
	and kl.Koodi='1'--tutkinto
	-- alempi kk-tutkinto; katsotaan koulutuskoodin ekasta numerosta!
	and kt.Koodi like '6%'
)
;


-- Erikoistumiskoulutus,  ei-tutkintokoulutus 999999
UPDATE T
SET T.ohjauksenAla = K.koodi,
    T.koulky = '999999',
    T.opiskeluoikeusavain = o.avain,
    T.opiskeluoikeus_id = o.id

FROM #tktutk T
    INNER JOIN  virta.dbo.Opiskeluoikeus o ON o.id = T.opiskeluoikeus_id
    INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi ot ON ot.ID = o.Op_oikeuden_tyyppiID
    INNER JOIN virta.dbo.koulutusala K ON K.id = O.koulutusalaid
WHERE     1=1
--  erikoistumiskoulutus (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('19')
AND K.versio = 'ohjausala'


-- Erikoistumiskoulutus, 999999 eikä ole tuotu ohjauksen alaa?
UPDATE T
SET  T.koulky = '999999',
    T.opiskeluoikeusavain = o.avain,
    T.opiskeluoikeus_id = o.id
FROM #tktutk T
    INNER JOIN  virta.dbo.Opiskeluoikeus o ON o.id = T.opiskeluoikeus_id
    INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi ot ON ot.ID = o.Op_oikeuden_tyyppiID
    INNER JOIN virta.dbo.koulutusala K ON K.id = O.koulutusalaid
WHERE     1=1
--  erikoistumiskoulutus (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('19')
AND K.versio <> 'ohjausala'

 
-- Erikoistumiskoulutuksen koulutuskoodi jos on
UPDATE T
SET T.ekkoodi = E.koodi 
FROM #tktutk T
    INNER JOIN  virta.dbo.Opiskeluoikeus o ON o.id = T.opiskeluoikeus_id
    INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi ot ON ot.ID = o.Op_oikeuden_tyyppiID
    INNER JOIN virta.dbo.koulutusala K ON K.id = O.koulutusalaid
    INNER JOIN virta.dbo.op_oik_liittyva_erikoistumiskoulutus OE ON OE.opiskeluoikeusid = o.id
    INNER JOIN virta.dbo.Erikoistumiskoulutus E ON E.id = OE.erikoistumiskoulutusid
WHERE     1=1
-- erikoistumisopinnot  (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('19')
AND K.versio = 'ohjausala'

    



Tutkinnon suorituspäivämäärä (suorpvm)

Suoritetun tutkinnon suorituspäivämäärä muodossa VVVVKKPP.

Koulutusyksikön sijaintikunta (kkun)

Tieto sellaisenaan tietovarannosta valituksi tulleesta opiskeluoikeudesta tilastointipäivänä voimassaolleesta opiskeluoikeusjaksosta.

SQL
-- kkun
update T
set kkun=k.Koodi
from #tktutk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Kunta k on k.ID=oj.KuntaID
where T.suorituspaivamaara between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
;
Sukupuoli (sp)

Poimitaan suoraan tietovarantoon tuotu opiskelijan sukupuoli-tieto.

Äidinkieli (aikieliy)

Poimitaan suoraan tietovarantoon tuotu opiskelijan äidinkieli-tieto ja päätellään arvoksi 1=suomi, 2=ruotsi ja 3=muu.

Korjataan puuttuvat arvot kategoriaan 3=muu.

SQL
-- : aikieliy
-- nb! arvot 1,2,3, jossa 3=muu (tuntemattomat tähän / 25.9.2014 AC-kokous "TK-OKM-Virta")
update T
set aikieliy='3'
from #tktutk T
where aikieliy is null
;

 

Kansalaisuus (kansaly)

Poimitaan suoraan tietovarantoon tuotu opiskelijan kansalaisuus-tieto. Perusjoukon keräämisen yhteydessä lisättiin suomalaiset. Päivitetään erikseen kansalaisuus vielä niille, joilta kansalaisuus puuttuu, mutta on tietovarannossa jokin muu kuin Suomi. Jos monta kansalaisuutta, valikoituu kansalaisuus "satunnaisesti".

Lopuksi korjataan vielä puuttuvat tuntemattomiksi.

SQL
-- kansaly
-- : muut kuin suomi
update T
set kansaly=(
  select top 1 m.Koodi
  from Henkilon_maa hm
  inner join Maa m on m.ID=hm.MaaID
  inner join Maan_rooli mr on mr.ID=hm.Maan_rooliID
  inner join Opiskelija p on p.HenkiloID=hm.HenkiloID
  where p.ID=T.opiskelija_id --sido
  and mr.Koodi='1' --Kansalaisuus
)
from #tktutk T
where kansaly is null
;
-- tuntemattomia
-- : kansaly
update T
set kansaly='999'
from #tktutk T
where kansaly is null
;

 

Sukunimi (snimi)

Poimitaan suoraan tietovarantoon tuotu opiskelijan sukunimi-tieto.

Etunimet (enimi)

Poimitaan suoraan tietovarantoon tuotu opiskelijan etunimet-tieto.

Lukuvuosiimaksu ja maksullinen tilauskoulutus (lkmmt)

Tieto sellaisenaan tietovarannosta tutkinnon suorituspäivänä voimassaolleesta opiskeluoikeusjaksosta rahoituslähde-tiedosta.

SQL
-- lkmmt
update T
set lkmmt=
 (case (select k.Koodi from Rahoituslahde k where k.ID=oj.RahoituslahdeID)
  when '4' then '2' -- 4=Maksullinen tilauskoulutus
  when '5' then '1' -- 5=Lukuvuosimaksu, entinen lukukausimaksukokeilu
  else ''  end)
from #tktutk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
where T.suorituspaivamaara between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
;
Koulutuskoodi (koulky)

Haetaan tieto suoraan kullakin rivillä tunnetulle opintosuoritukselle (tutkinnolle).

SQL
-- koulky
update T
set koulky=k.Koodi --!
from #tktutk T
inner join Opintosuoritus s on s.ID=T.opintosuoritus_id
inner join Tutkintonimike k on k.ID=s.TutkintonimikeID
where koulky is null -- ei ole asetettu vielä
;


Opintopisteet

TODO: Tässä kohdassa olevat päättelyt kannattaa katsoa kriittisesti läpi!

TODO: Opintosuoritusten järjestävä-roolin OSUUS-tietoja ei ole vielä tarkasteltu!

Oma temp-taulu, joka nopeuttaa oleellisesti kyselyitä. Viedään temp-tauluun kaikki lehtitason 'muu opintosuoritus' -lajiset opintosuoritukset, jotka liittyvät opiskelijoihin jotka poimittiin perusjoukkoon. Lehtitason opintosuoritus on suoritus johon ei sisälly muita opintosuorituksia, pl. opintosuorituksen lajien 3 ja 4 suoritukset. Kunkin opintosuorituksen luokitteluista ja järjestävä- sekä lähde-roolisista organisaatioista päätellään opintosuorituksen kategoria tiedonkeruuseen liittyen.

NB! Kaikki opintopisteet lasketaan kokonaislukuna. Tämä siis siten, että opintopisteet lasketaan yhteen, jonka jälkeen saatu summa pyöristetään kokonaisluvuksi.

NB! YO-sektorilla VALINTA: Mikäli yliopistolla on sisältyvyydet käytössä, jokaisen suorituksen (id) yhteydessä etsitään opintosuoritusten sisältyvyyksien kautta ko. opintosuorituksen vanhempana olevia opintosuorituksia. Tätä "vanhempien ja isovanhempien ja isoiso... (jne) listaa" käytetään hyväksi siinä kuuluuko suoritus perusjoukkoon valittuun tutkinto-opintosuoritukseen. Mikäli yliopistolla EI OLE sisältyvyydet käytössä (taulu Op_suor_sisaltyvyys on tyhjä), katsotaan että kullakin opintosuorituksella on tieto samasta opiskeluoikeudesta kuin tutkinto-opintosuorituksella (=sama malli kuin on AMK-sektorilla käytössä).

NB! YO-sektorilla: Jatkotutkinnoille ei lasketa ja merkitä opintopisteitä. Tämä tarkistetaan siten, että mikäli suoritetun tutkinnon koulutuskoodi alkaa merkillä "8" tai merkeillä "775", tulkitaan tutkinto jatkotutkinnoksi, ja opintopisteitä ei merkitä.

 

Seuraavat opintopistetiedot täytetään tässä "yhdessä nipussa":

Omassa yliopistossa suoritetut opintopisteet (pl. avoin yliopisto-opetus) (opoylio)
  • Mikäli suoritusta ei luokitella muihin kategorioihin, tulee suorituksen opintopisteet tähän kategoriaan. Eli tämä on ns. oletuskategoria.
Avoimessa yliopisto-opetuksessa suoritetut opintopisteet (sekä omat että muu yliopisto) (opaylio)
  • Mikäli opintosuorituksen luokittelu sisältää arvon 1="avoimessa", lasketaan opintopisteet tähän kategoriaan. Riippumatta organisaatio-tiedoista!
Muissa yliopistoissa suoritetut opintopisteet (pl. avoin yliopisto) (opmylio)
  • Mikäli suorituksen järjestävä-roolisista (tai hyväksiluetun opintosuorituksen tapauksessa lähde-roolin) organisaatioista löytyy oppilaitostyypiltään 42='Yliopistot' tai 43='Sotilaskorkeakoulut' oleva organisaatio, joka ei ole sama kuin kenen tietoja nyt käsitellään, lasketaan opintopisteet tähän kategoriaan.
  • Kategoriaan "avoimessa suoritettu" ei saa olla asetettu, eli opintosuorituksella ei saa olla opintosuorituksen luokittelua 1="avoimessa".
Ammattikorkeakouluissa suoritetut opintopisteet (opamk)
  • Mikäli suorituksen järjestävä-roolisista (tai hyväksiluetun opintosuorituksen tapauksessa lähde-roolin) organisaatioista löytyy oppilaitostyypiltään 41='Ammattikorkeakoulut' oleva organisaatio, joka ei ole sama kuin kenen tietoja nyt käsitellään, lasketaan opintopisteet tähän kategoriaan.
  • Kategoriaan "avoimessa suoritettu" ei saa olla asetettu, eli opintosuorituksella ei saa olla opintosuorituksen luokittelua 1="avoimessa".
Ulkomailla suoritetut opintopisteet (opulk)
  • Mikäli suorituksen järjestävä-roolisista (tai hyväksiluetun opintosuorituksen tapauksessa lähde-roolin) organisaatioista löytyy organisaatio UK='Ulkomainen korkeakoulu' tai UM='Ulkomainen muu oppilaitos', joka ei ole sama kuin kenen tietoja nyt käsitellään, lasketaan opintopisteet tähän kategoriaan.
  • Kategoriaan "avoimessa suoritettu" ei saa olla asetettu, eli opintosuorituksella ei saa olla opintosuorituksen luokittelua 1="avoimessa".
Muualla suoritetut opintopisteet (opmuu)
  • Mikäli suorituksen järjestävä-roolisista (tai hyväksiluetun opintosuorituksen tapauksessa lähde-roolin) organisaatioista löytyy
    • organisaatio joka ei ole tyypiltään 41='Ammattikorkeakoulu', 42='Yliopisto' tai 43='Sotilaskorkeakoulut', tai
    • organisaatio itsessään on 63='Kansanopistot', 64='Kansalaisopistot', 66='Kesäyliopistot', 99='Muut oppilaitokset', Muu='Pienet oppilaitokset ja koulutuksenjärjestäjät (alle 5 opiskelijaa)' tai XX='Muodollisen koulutuksen ulkopuolella',
      ja
    • organisaatio ei ole sama kuin minkä tahansa korkeakoulun tiedot tietovarannossa (varmuuden vuoksi tarkistus)
      ja 
    • organisaatio itsessään ei ole 41='Ammattikorkeakoulut', 42='Yliopistot', 43='Sotilaskorkeakoulut', UK='Ulkomainen korkeakoulu' tai UM='Ulkomainen muu oppilaitos',
      ja
    • organisaatio ei ole sama kuin kenen tietoja nyt käsitellään
    lasketaan opintopisteet tähän kategoriaan.
  • Kategoriaan "avoimessa suoritettu" ei saa olla asetettu, eli opintosuorituksella ei saa olla opintosuorituksen luokittelua 1="avoimessa".
Opintopisteet yhteensä (opyht)
  • Opintopisteet yhteensä on kaikkien tutkinto-opintosuoritukseen sisältyvien (aputaulussa olevien; lehtitaso) opintosuoritusten laajuuksien summa.
Tutkinnon opintopisteet ()
  • Tämä tieto on uusi (12.1.2015) ja tullut mukaan korvaamaan "opintopisteet yhteensä" -tietoa.
  • Tieto on suoraan tutkinto-opintosuoritukselle (laji=1; eli tiedonkeruun perusjoukon rivin tieto) tallennettu laajuus-tieto.
  • Tästä tiedosta lasketaan "omassa yliopistossa suoritetut opintopisteet".


SQL
---
-- Opinnot, suoritukset, opintopisteet
---
--
-- "opintopistekysymykset eivät koske jatkotutkintoja opiskelevia, joille opintopistetietoja ei merkitä"
-- Opintopisteitä varten tehdä oma erillinen aputaulu
-- Temp-taulu tietojen valintaa varten
-- NB! param-taulussa saa olla vain yksi rivi!
IF OBJECT_ID('tempdb..#pisteet') IS NOT NULL
BEGIN
	truncate table #pisteet
	drop table #pisteet
	if @debug<>0 print convert(varchar,getdate(),120)+' DROP temp-taulu pisteet'
END
;
create table #pisteet (
	opiskelija_id int not null,
	opiskeluoikeus_id int,
	opintosuoritus_id int not null,
	laajuus decimal(18,6) not null,
	suoritus_pvm date not null,
	hyvaksiluettu_pvm date,
	oylio bit,
	aylio bit,
	mylio bit,
	amk bit,
	ulk bit,
	muu bit
)
if @debug<>0 print convert(varchar,getdate(),120)+' CREATE temp-taulu pisteet'
;
-- TODO: OSUUDET
insert into #pisteet (opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm,hyvaksiluettu_pvm,oylio,aylio,mylio,amk,ulk,muu)
	select s.OpiskelijaID as opiskelija_id
	, s.OpiskeluoikeusID as opiskeluoikeus_id
	, s.ID as opintosuoritus_id
	, s.Laajuus as laajuus
	, s.Suorituspaivamaara as suoritus_pvm
	, shl.Hyvaksilukupaivamaara as hyvaksiluettu_pvm
	-- opintopisteiden jaottelu
	-- oma ylio: käytetään halpaa logiikkaa, eli yhteensä miinus muut. tässä null kunnes..!
	, null as oylio
	--avoin ylio
	,coalesce((select distinct 1
	  from Op_suor_liittyva_luokittelu ll
	  inner join Op_suor_luokittelu k on k.ID=ll.Opintosuorituksen_luokitteluID
	  where ll.OpintosuoritusID=s.ID
	  and k.Koodi='1' -- avoimessa
	 ),0) as aylio
	--muu ylio
	,coalesce((select distinct 1
	  from Op_suorituksen_organisaatio sg
	  inner join Organisaatio g on g.ID=sg.OrganisaatioID
	  inner join Organisaation_rooli gr on gr.ID=sg.Organisaation_rooliID
	  where sg.OpintosuoritusID=s.ID
	  and(gr.Koodi='2' -- järjestävä
	   or(gr.Koodi='3' and shl.Hyvaksilukupaivamaara is not null) --hyväksiluettu
	  )
	  and(g.Koodi in (
			SELECT Organisaatio_koodi FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi
			WHERE Oppilaitostyyppi_koodi='42' or Oppilaitostyyppi_koodi='43'
		)
	   or g.Koodi='42' or g.Koodi='43'
	  )
	  -- ei oma yliopisto
	  and g.Koodi <> (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=(select org from #param))
	  -- varmuuden vuoksi/yhteentoimivuus
	  and g.Koodi <> (select kk from #param)
	 ),0) as mylio
	--amk
	,coalesce((select distinct 1
	  from Op_suorituksen_organisaatio sg
	  inner join Organisaatio g on g.ID=sg.OrganisaatioID
	  inner join Organisaation_rooli gr on gr.ID=sg.Organisaation_rooliID
	  where sg.OpintosuoritusID=s.ID
	  and(gr.Koodi='2' -- järjestävä
	   or(gr.Koodi='3' and shl.Hyvaksilukupaivamaara is not null) --hyväksiluettu
	  )
	  and(g.Koodi in (SELECT Organisaatio_koodi FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi WHERE Oppilaitostyyppi_koodi='41')
	   or g.Koodi='41'
	  )
	  -- ei oma yliopisto
	  and g.Koodi <> (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=(select org from #param))
	  -- varmuuden vuoksi/yhteentoimivuus
	  and g.Koodi <> (select kk from #param)
	 ),0) as amk
	--ulk
	,coalesce((select distinct 1
	  from Op_suorituksen_organisaatio sg
	  inner join Organisaatio g on g.ID=sg.OrganisaatioID
	  inner join Organisaation_rooli gr on gr.ID=sg.Organisaation_rooliID
	  where sg.OpintosuoritusID=s.ID
	  and(gr.Koodi='2' -- järjestävä
	   or(gr.Koodi='3' and shl.Hyvaksilukupaivamaara is not null) --hyväksiluettu
	  )
	  --ei tarvita: and g.Koodi not in (SELECT Organisaatio_koodi FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi)
	  and g.Koodi in ('UK','UM')
	  -- ei oma yliopisto
	  and g.Koodi <> (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=(select org from #param))
	  -- varmuuden vuoksi/yhteentoimivuus
	  and g.Koodi <> (select kk from #param)
	 ),0) as ulk
	--muu
	,coalesce((select distinct 1
	  from Op_suorituksen_organisaatio sg
	  inner join Organisaatio g on g.ID=sg.OrganisaatioID
	  inner join Organisaation_rooli gr on gr.ID=sg.Organisaation_rooliID
	  where sg.OpintosuoritusID=s.ID
	  and(gr.Koodi='2' -- järjestävä
	   or(gr.Koodi='3' and shl.Hyvaksilukupaivamaara is not null) --hyväksiluettu
	  )
	  -- XX yksin ei riitä
	  --and g.Koodi in ('XX')
	  --DONE-ish? opplaittyyp tarkistus!
	  and(g.Koodi in (
			SELECT Organisaatio_koodi FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi
			WHERE Oppilaitostyyppi_koodi is not null and Oppilaitostyyppi_koodi not in ('41','42','43')
		)
	   or g.Koodi in ('63','64','66','99','Muu','XX')
	  )
	  -- leikataan nämä pois
	  and g.Koodi not in (select Asetus from Yhteiset.Asetukset.Ohjaus_Parametrit p where p.Toiminto='Oppilaitostyyppi')
	  and g.Koodi not in ('41','42','43','UK','UM')
	  -- ei oma yliopisto
	  and g.Koodi <> (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=(select org from #param))
	  -- varmuuden vuoksi/yhteentoimivuus
	  and g.Koodi <> (select kk from #param)
	 ),0) as muu
	from Opintosuoritus s
	inner join Opintosuorituksen_laji sl on sl.ID=s.Opintosuorituksen_lajiID
	-- HYVÄKSILUETTU
	left join Opsuorituksen_hyvaksiluku shl on shl.OpintosuoritusID=s.ID
	cross join #param -- saa olla vain yksi rivi!
	where 1=1
	--muu opintosuoritus
	and sl.Koodi='2'
	-- lehtitaso, tai ei ainakaan vanhempi (lajeja 3 ja 4 ei huomioida)
	and s.ID not in (
		select ss.OpintosuoritusID from Op_suor_sisaltyvyys ss
		-- 2015-01-19: lajeja 3 ja 4 ei huomioida
		where ss.OpintosuoritusID not in (
			select sss.ID
			from Opintosuoritus sss
			inner join Opintosuorituksen_laji sssl on sssl.ID=sss.Opintosuorituksen_lajiID
			where sssl.Koodi in ('3','4')
		)
	)
	-- mukaan vain tutkinnon suorittaneiden pisteet!
	and s.OpiskelijaID in (select T.opiskelija_id from #tktutk T)
	
;
CREATE NONCLUSTERED INDEX IX_pisteet_opiskelija_id ON #pisteet (opiskelija_id)
INCLUDE (opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm)
;
CREATE NONCLUSTERED INDEX IX_pisteet_suoritus_pvm ON #pisteet (suoritus_pvm)
INCLUDE (opiskelija_id,opiskeluoikeus_id,laajuus)
;
CREATE NONCLUSTERED INDEX IX_pisteet_hyvaksiluettu_pvm ON #pisteet (hyvaksiluettu_pvm)
INCLUDE (opiskelija_id,opiskeluoikeus_id,laajuus,suoritus_pvm)
;
--select * from #pisteet
-- suoritusten hierarkia-aputaulu
-- tehdään hierarkiasta kaksitasoinen
-- sisältyvyys-hierarkia:
--  TUTK
--    KOK
--      LEHTI
-- => tauluksi jossa lehtitason suoritus linkitetään kaikkiin sen vanhempiin:
--  LEHTI KOK
--  LEHTI TUTK
--/*
IF OBJECT_ID('tempdb..#suoritushierarkia') IS NOT NULL
BEGIN
	truncate table #suoritushierarkia
	drop table #suoritushierarkia
--	if @debug<>0 print convert(varchar,getdate(),120)+' DROP temp-taulu pisteet'
END
;
create table #suoritushierarkia (
	opiskelija_id int not null,
	suoritus_id int not null,
	vanhempi_id int not null
)
;
-- rekursiivisella CTE (common table expression) kyselyllä
-- * vahdittava että toimii sillä on virheherkkä!
--   löytää kyllä silmukoita (jättää ulos tuloksesta), mutta esimerkiksi
--   linkki itseensä joissakin tilanteissa on tälle kyselylle liikaa.
-- => linkit itseensä on erikseen katsottu kyselyssä pois!
;WITH  dcba --lehdestä alkaen!
AS (
	-- anchor
	SELECT  OpintosuoritusID2 as id, OpintosuoritusID2 as lapsi, OpintosuoritusID2 as lehti
	FROM    Op_suor_sisaltyvyys
	WHERE   OpintosuoritusID2 NOT in (
		-- lehtitaso, tai ei ainakaan vanhempi (lajeja 3 ja 4 ei huomioida)
		select ss.OpintosuoritusID from Op_suor_sisaltyvyys ss
		-- 2015-01-19: lajeja 3 ja 4 ei huomioida
		where ss.OpintosuoritusID not in (
			select sss.ID
			from Opintosuoritus sss
			inner join Opintosuorituksen_laji sssl on sssl.ID=sss.Opintosuorituksen_lajiID
			where sssl.Koodi in ('3','4')
		)
	)
	-- voi olla monta kertaa, joten:
	group by OpintosuoritusID2
	UNION ALL
	-- recursive member
	SELECT     r.OpintosuoritusID as id, r.OpintosuoritusID2 as lapsi, a.lehti
	FROM       Op_suor_sisaltyvyys AS r
	INNER JOIN dcba AS a ON r.OpintosuoritusID2 = a.id
	WHERE r.OpintosuoritusID<>r.OpintosuoritusID2 -- estetään linkit itseensä!
)
INSERT INTO #suoritushierarkia (opiskelija_id,suoritus_id,vanhempi_id)
SELECT DISTINCT s.opiskelijaID,dcba.lehti,s.ID
FROM Opintosuoritus s
left join dcba on dcba.id=s.ID
WHERE 1=1
and dcba.lehti is not null
-- mukaan vain tutkinnon suorittaneiden pisteet!
and s.OpiskelijaID in (select T.opiskelija_id from #tktutk T)
-- rekursion määrä (0 sallisi kaiken), eli hierarkian maksimisyvyys
option (maxrecursion 20)
;
CREATE NONCLUSTERED INDEX IX_suoritushierarkia_opiskelija_id ON #suoritushierarkia (opiskelija_id)
INCLUDE (suoritus_id,vanhempi_id)
;
CREATE NONCLUSTERED INDEX IX_suoritushierarkia_suoritus_id ON #suoritushierarkia (suoritus_id)
INCLUDE (opiskelija_id,vanhempi_id)
;
CREATE NONCLUSTERED INDEX IX_suoritushierarkia_vanhempi_id ON #suoritushierarkia (vanhempi_id)
INCLUDE (opiskelija_id,suoritus_id)
;

---
-- Opinnot, suoritukset, opintopisteet
---
-- EI KOSKE JATKOTUTKINTOJA (koulk: 8%,775%)

--
-- VIRTA-625: Lisätään tutkinto-opintosuorituksen opintopisteet mukaan tiedonkeruuseen.
-- NB! "omassa yliopistossa suoritetut opintopisteet" on nyt tutkinto-opintosuorituksen
--  opintopisteet miinus muiden kategorioiden mukaisesti suoritetut opintopisteet
update T
set optutk = cast(cast(round(s.Laajuus,0) as int) as varchar)
from #tktutk T
inner join Opintosuoritus s on s.ID=T.opintosuoritus_id
where T.optutk is null
-- ei jatkotutkintoja:
AND T.koulky NOT LIKE '8%' --lisensiaatit, tohtorit
--AND T.koulky NOT LIKE '775%' --erikois[*]lääkärit
AND T.koulky NOT IN ('775101','775201','775999')

;
-- Minkä perusteella katsotaan kuuluuko suoritus mukaan laskettaviin?
-- - sisältyvyydet (hierarkia)
-- VAI
-- - opiskeluoikeus-kytkös
-- SISÄLTYVYYS (HIERARKIA)
IF (0 < (SELECT TOP 1 COUNT(*) FROM Op_suor_sisaltyvyys)) BEGIN
	-- kategoriat ja yhteensä (summa lehtitasolta)
	update T
	set
	 --opoylio :: oma yliopisto erikseen. ks. alla!
	 opaylio=(
	  select cast(cast(round(sum(
		-- sekä oman yo:n että muut
		case when s.aylio = 1 then s.Laajuus else 0 end),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and  s.opintosuoritus_id in
		(select suoritus_id from #suoritushierarkia where opiskelija_id=T.opiskelija_id and vanhempi_id=T.opintosuoritus_id)
	  group by s.opiskelija_id
	)
	, opmylio=(
	  select cast(cast(round(sum(
		-- muu yo (nb! pl. avoin)
		case when s.mylio = 1 and s.aylio=0 then s.Laajuus else 0 end),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and  s.opintosuoritus_id in
		(select suoritus_id from #suoritushierarkia where opiskelija_id=T.opiskelija_id and vanhempi_id=T.opintosuoritus_id)
	  group by s.opiskelija_id
	)
	, opamk=(
	  select cast(cast(round(sum(
		-- nb! pl. avoin; oma lisäys!
		case when s.amk = 1 and s.aylio=0 then s.Laajuus else 0 end),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and  s.opintosuoritus_id in
		(select suoritus_id from #suoritushierarkia where opiskelija_id=T.opiskelija_id and vanhempi_id=T.opintosuoritus_id)
	  group by s.opiskelija_id
	)
	, opulk=(
	  select cast(cast(round(sum(
		-- nb! pl. avoin; oma lisäys!
		case when s.ulk = 1 and s.aylio=0 then s.Laajuus else 0 end),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and  s.opintosuoritus_id in
		(select suoritus_id from #suoritushierarkia where opiskelija_id=T.opiskelija_id and vanhempi_id=T.opintosuoritus_id)
	  group by s.opiskelija_id
	)
	, opmuu=(
	  select cast(cast(round(sum(
		-- nb! pl. avoin; oma lisäys!
		case when s.muu = 1 and s.aylio=0 then s.Laajuus else 0 end),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and  s.opintosuoritus_id in
		(select suoritus_id from #suoritushierarkia where opiskelija_id=T.opiskelija_id and vanhempi_id=T.opintosuoritus_id)
	  group by s.opiskelija_id
	)
	, opyht=(
	  select cast(cast(round(sum(
		s.Laajuus),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and  s.opintosuoritus_id in
		(select suoritus_id from #suoritushierarkia where opiskelija_id=T.opiskelija_id and vanhempi_id=T.opintosuoritus_id)
	  group by s.opiskelija_id
	)
	from #tktutk T
	where T.opyht is null
	-- ei jatkotutkintoja:
	AND T.koulky NOT LIKE '8%' --lisensiaatit, tohtorit
    --AND T.koulky NOT LIKE '775%' --erikois[*]lääkärit
    AND T.koulky NOT IN ('775101','775201','775999')
	;
-- OPISKELUOIKEUS-KYTKÖS
END ELSE BEGIN
	-- kategoriat ja yhteensä (summa lehtitasolta)
	update T
	set
	 --opoylio :: oma yliopisto erikseen. ks. alla!
	 opaylio=(
	  select cast(cast(round(sum(
		-- sekä oman yo:n että muut
		case when s.aylio = 1 then s.Laajuus else 0 end),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and s.opiskeluoikeus_id=T.opiskeluoikeus_id
	  group by s.opiskelija_id
	)
	, opmylio=(
	  select cast(cast(round(sum(
		-- muu yo (nb! pl. avoin)
		case when s.mylio = 1 and s.aylio=0 then s.Laajuus else 0 end),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and s.opiskeluoikeus_id=T.opiskeluoikeus_id
	  group by s.opiskelija_id
	)
	, opamk=(
	  select cast(cast(round(sum(
		-- nb! pl. avoin; oma lisäys!
		case when s.amk = 1 and s.aylio=0 then s.Laajuus else 0 end),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and s.opiskeluoikeus_id=T.opiskeluoikeus_id
	  group by s.opiskelija_id
	)
	, opulk=(
	  select cast(cast(round(sum(
		-- nb! pl. avoin; oma lisäys!
		case when s.ulk = 1 and s.aylio=0 then s.Laajuus else 0 end),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and s.opiskeluoikeus_id=T.opiskeluoikeus_id
	  group by s.opiskelija_id
	)
	, opmuu=(
	  select cast(cast(round(sum(
		-- nb! pl. avoin; oma lisäys!
		case when s.muu = 1 and s.aylio=0 then s.Laajuus else 0 end),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and s.opiskeluoikeus_id=T.opiskeluoikeus_id
	  group by s.opiskelija_id
	)
	, opyht=(
	  select cast(cast(round(sum(
		s.Laajuus),0) as int) as varchar)
	  from #pisteet s
	  where s.opiskelija_id=T.opiskelija_id
	  and s.opiskeluoikeus_id=T.opiskeluoikeus_id
	  group by s.opiskelija_id
	)
	from #tktutk T
	where T.opyht is null
	-- ei jatkotutkintoja:
	AND T.koulky NOT LIKE '8%' --lisensiaatit, tohtorit
	AND T.koulky NOT LIKE '775%' --erikois[*]lääkärit
	;
END -- hierarkia vs opiskeluoikeus-kytkös

-- oma yliopisto: = "yhteensä - muut kategoriat"
-- VIRTA-625: oma yliopisto = tutkinnon opintopisteet - muut kategoriat
update T
set opoylio = cast(
	--cast(coalesce(opyht,0) as int)
	cast(coalesce(optutk,0) as int)
	- (
	   cast(coalesce(opaylio,0) as int)
	 + cast(coalesce(opmylio,0) as int)
	 + cast(coalesce(opamk,0) as int)
	 + cast(coalesce(opulk,0) as int)
	 + cast(coalesce(opmuu,0) as int)
	)
as varchar)
from #tktutk T
where 1=1
-- ei jatkotutkintoja:
AND T.koulky NOT LIKE '8%' --lisensiaatit, tohtorit
--AND T.koulky NOT LIKE '775%' --erikois[*]lääkärit
AND T.koulky NOT IN ('775101','775201','775999')
;

-- 2018-01-14 
-- 2018-01-04
-- 2017-12-18
-- 2017-06-30
-- SV-muuttuja vielä kerran kun opintosuoritusten hierarkia on käytettävissä    
UPDATE T
SET T.sv='001'
FROM #tktutk T
inner join #suoritushierarkia s on s.opiskelija_id=T.opiskelija_id and s.vanhempi_id=T.opintosuoritus_id
inner join virta.dbo.Henkilon_patevyys hp on hp.OpintosuoritusID=s.vanhempi_id
inner join virta.dbo.Patevyys p on p.ID=hp.PatevyysID
WHERE T.sv is null
AND p.Koodi in ('ik','il','im') --opettaja, erill opet pedag opinn
AND T.koulky NOT LIKE '6%'
AND T.koulky NOT LIKE '8%'
AND T.suorituspaivamaara >= hp.Alkamispaivamaara
;

-- Vain JY hierarkian vanhempi suoritus
UPDATE T
SET T.sv='001'
FROM #tktutk T
inner join #suoritushierarkia s on s.opiskelija_id=T.opiskelija_id --and s.vanhempi_id=T.opintosuoritus_id
inner join virta.dbo.Henkilon_patevyys hp on hp.OpintosuoritusID=s.vanhempi_id
inner join virta.dbo.Patevyys p on p.ID=hp.PatevyysID
WHERE T.sv is null
AND    HP.db = 'JY'
AND p.Koodi in ('ik','il','im') --opettaja, erill opet pedag opinn

AND T.koulky NOT LIKE '6%'
AND T.koulky NOT LIKE '8%'
AND T.suorituspaivamaara >= hp.Alkamispaivamaara
;



-- 2017-07-01 Jos Kirtu tyhjä Erikoislääkäri, opiskeluoikeuden alkamispaivamaara
-- kirtu
UPDATE T
SET kirtu=(cast(year(o.Alkamispaivamaara) as varchar)
         +(case
           -- 8-12 (elo-joulu): syksy
           when month(o.Alkamispaivamaara)>=8 then '2'
           -- 1-7 (tammi-heinä): kevät
           else '1'
           end)
    )
FROM #tktutk T
inner join virta.dbo.Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
WHERE kirtu IS NULL
AND T.koulky IN ('775101','775201','775999')
;     

Kansainvälinen tutkinto (kvtutk)

Poimitaan opintosuorituksen luokittelu 9 tai 10 jos löytyy.


Kansallinen oppijanumero (oppijanumero)

Poimitaan mukaan kansallinenopid jos löytyy.

SQL
-- kvtutk tiedon päivitys
UPDATE T
SET  T.kvtutk = OSLL.koodi
FROM #tktutk T
    INNER JOIN virta.dbo.Op_suor_liittyva_luokittelu OSL ON OSL.OpintosuoritusID = T.opintosuoritus_id
    INNER JOIN virta.dbo.Op_suor_luokittelu OSLL ON OSLL.id = OSL.Opintosuorituksen_luokitteluID
WHERE OSLL.Koodi IN ('9','10')


-- päivitetään kvtutk tyhjä tiedoksi jos se on NULL
UPDATE #tktutk set kvtutk = ''  where kvtutk IS NULL

Ensimmäinen kirjoihintuloajankohta ko. tutkintoon (kirtu1pv)

Haetaan ensimmäinen kirjoihintuloajankohta ko. tutkintoon siten, että otetaan siirtopäivämäärä, jos on, muutoin opiskeluoikeuden alkamispäivä ja muunnetaan muotoon VVVVKKPP.

-- kirtu1pv :: Ensimmäinen kirjoihintuloajankohta ko. tutkintoon
-- NB siirto-opiskelijat
update T
set kirtu1pv=CONVERT(varchar,coalesce(T.oikeus_siirtopaivamaara,T.oikeus_alkamispaivamaara),112)--112=yyyymmdd
from #tktutk T
; 
Ko. tutkintoon kirjoihintulovuosi ko. yliopistossa(kirtupv)

Haetaan opiskeluoikeuden alkamispäivä ja muunnetaan muotoon VVVVKKPP.

kirtupv
-- kirtupv :: Ko. tutkintoon kirjoihintuloajankohta ko. yliopistossa
-- NB siirto-opiskelijat
update T
set kirtupv=CONVERT(varchar,T.oikeus_alkamispaivamaara,112)--112=yyyymmdd
from #tktutk T
;


Tulos

SQL
---
-- TULOS
---
-- CSV-sarakkeet
select
 ISNULL(kk,'')     as "Yliopisto" --(kk)
,ISNULL(ht,'')        "Henkilötunnus" --(ht)
,ISNULL(kirtu,'')     "Kirjoihintulo yliopistoon" --(kirtu)
,ISNULL(opoik,'')     "Opiskeluoikeuden alkaminen nykyisessä tutkinnossa" --(opoik)
,ISNULL(sv,'')        "Aineenopettajan pedagogiset opinnot" --(sv)
,ISNULL(om,'')        "Opiskelumuoto" --(om)
,ISNULL(suorpvm,'')   "Tutkinnon suorituspäivämäärä" --(suorpvm)
,ISNULL(kkun,'')      "Koulutusyksikön sijaintikunta" --(kkun)
,ISNULL(sp,'')        "Sukupuoli" --(sp)
,ISNULL(aikieliy,'')  "Äidinkieli" --(aikieliy)
,ISNULL(kansaly,'')   "Kansalaisuus" --(kansaly)
,ISNULL(snimi,'')     "Sukunimi" --(snimi)
,ISNULL(enimi,'')     "Etunimet" --(enimi)
,ISNULL(opoylio,'')   "Omassa yliopistossa suoritetut opintopisteet (pl. avoin yliopisto-opetus)" --(opoylio)
,ISNULL(opaylio,'')   "Avoimessa yliopisto-opetuksessa suoritetut opintopisteet (sekä omat että muu yliopisto)" --(opaylio)
,ISNULL(opmylio,'')   "Muissa yliopistoissa suoritetut opintopisteet (pl. avoin yliopisto)" --(opmylio)
,ISNULL(opamk,'')     "Ammattikorkeakouluissa suoritetut opintopisteet" --(opamk)
,ISNULL(opulk,'')     "Ulkomailla suoritetut opintopisteet" --(opulk)
,ISNULL(opmuu,'')     "Muualla suoritetut opintopisteet" --(opmuu)
,ISNULL(opyht,'')     "Opintopisteet yhteensä" --(opyht)
,ISNULL(lkmmt,'')     "Lukukausimaksukokeilu ja maksullinen tilauskoulutus" --(lkmmt)
,ISNULL(koulky,'')    "Koulutuskoodi" --(koulky)
-- VIRTA-625: tutkinto-opintosuorituksen opintopisteet
,ISNULL(optutk,'')     "Tutkinnon opintopisteet" --()
, ohjauksenAla			"OKM Ohjauksen ala"
, ekkoodi				"Erikoistumiskoulutuksen koodi"
 -- korkeakoulujen pyynnöstä, lisäkentät
,T.opiskelijaavain      "Opiskelija-avain"
,T.opiskeluoikeusavain  "Opiskeluoikeusavain"
,T.opintosuoritusavain  "Opintosuoritusavain"
from #tktutk T
where T.opintosuoritus_id is not null
order by ht,suorpvm,koulky

Tarkistus

Suoritetaan tarkistuksia poimittuihin tietoihin liittyen. Pohjaksi on otettu Tilastokeskuksen aiemmin tekemän tarkistusohjelman kuvaus.

NB! Tarkistuksia tulee jalostaa vielä. Nyt on kuitenkin jotakin pohjaa. Lisäksi tarkistuksia haluttaneen lisää. Dokumentointia ei vielä ole yritettykään tehdä kattavaksi, sillä niin paljon tarkennusta vaativia kohtia on nyt.

SQL
---
-- TARKISTUS
---
--!!!!!!!! KOPIOITU AMK-SEKTORILTA !!!!!!!--
/*
Tilastokeskuksen tarkistusohjelman suorittamat tarkistukset
Kun amk-tutkintotiedonkeruun aineisto on toimitettu TK:n palvelimelle, virheentarkistusohjelma tarkistaa seuraavat asiat. Kunkin virhetyypin yhteydessä on mainittu sen nimike palautetulosteessa.


TUNN = tunn (oppilaitostunnus) on virheellinen

onko oppilaitostunnus jokin voimassaolevista 5-numeroisista tunnuksista

AVAIN = oltunn - tutk - ko - sv (tutkintokoodi - koulutusohjelma - suuntautumisvaihtoehto) -koodikombinaatio on virheellinen

avaimen (oppilaitos-tutkinto-ko-sv) perusteella verrataan opetushallinnon koulutusohjelmapäätöksiin

AVAIN2 = oltunn - tutk - ko - sv - koulk (tutkintokoodi - koulutusohjelma - suuntautumisvaihtoehto - koulutuskoodi) -koodikombinaatio on virheellinen

Jos koulutuskoodi on merkitty, niin avaimen (oppilaitos-tutkinto-ko-sv-koulutuskoodi) perusteella verrataan opetushallinnon koulutusohjelmapäätöksiin, jotka on kooditettu Tilastokeskuksen koulutuskoodilla

TUTK = tutkinto virheellinen

verrataan tutkintokoodia koulutusohjelmapäätöksissä oleviin mahdollisiin tutkintokoodeihin

KO = koulutusohjelma virheellinen

verrataan ko-koodia koulutusohjelmapäätöksissä oleviin mahdollisiin ko-koodeihin

SV = suuntautumisvaihtoehto virheellinen

verrataan sv-koodia koulutusohjelmapäätöksissä oleviin mahdollisiin sv-koodeihin

KOULTYP = koulutustyyppi on virheellinen

KOULTYP1 = muuttujan arvo ei ole 1, 2, 3, 5 eikä 6
KOULTYP2 = erikoistumisopinnoissa ja opettajankoulutuksessa koodi on joko 3 tai 5, muuten 1, 2 tai 6
KOULTYP3= koulutusohjelma ei sallittu erikoistumisopinnoissa tai opettajankoulutuksessa

SRHTUP =

ilmoitus sairaanhoitajista, joilla myös kätilön, ensihoitajan tai terveydenhoitajan tutkinto (tuplasairaanhoitajat)

KKIELI = kkieli (koulutuksen opetuskieli) -koodiarvo on virheellinen

koulutusohjelman opetuskieli löytyy opm:n päätöksistä
(jos koulutusohjelmaa ei löydy, virhettä ei enää erikseen listata tässä kohdassa.)

KKUN = kkun (koulutuksen sijaintikunta) -koodiarvo on virheellinen

Verrataan toimilupapäätöksiin, onko amk:lla lupa järjestää koulutusta kyseisessä kunnassa.Ammattikorkeakoulun koulutustehtävässä määrättävä sijaintipaikka koskee ammattikorkeakoulututkintoon johtavaa nuorten koulutuksena järjestettävää koulutusta ja ylempään ammattikorkeakoulututkintoon johtavaa koulutusta.

KKUN2 = kkun2 (kunta) -koodiarvo on virheellinen

Verrataan voimassa olevaan kuntaluokitukseen ( koulutustyyppi 2, 3 ja 5 ).

HT = henkilötunnus (tai sukupuoli) on virheellinen tai puutteellinen

HT1 = Puutteelliset henkilötunnukset - loppuosa puuttuu
HT2 = henkilötunnukset, joiden jakojäännös ei täsmää - loppuosa virheellinen
HT3 = sukupuolitieto virheellinen

HT4 = Hetu-välimerkki jos 2000-luvulla syntynyt ei '-' vaan 'A'

AIKIELI = äidinkielitieto virheellinen

verrataan äidinkieltä sallittujen kielten listaan

ASKUN = asuinkuntatieto virheellinen

verrataan kuntatietoa sallittujen kuntien listaan

KANSAL = kansalaisuustieto virheellinen

verrataan kansallisuustietoa sallittujen kansallisuuksien listaan

KIRTUPV = kirjoihintuloajankohta on virheellinen

KIRTUPV1 = tarkistetaan, onko koulutusohjelmapäätös ollut voimassa silloin kun opiskelija on aloittanut opintonsa kyseisessä tutkinnossa missä tahansa amk:ssa.
(jos koulutusohjelmaa ei löydy, virhettä ei enää erikseen listata tässä kohdassa.)
KIRTUPV2 = kirjoihintulovuosi ei saa olla suurempi kuin tilastovuosi, kirjoihintulokuukausi saa arvon 1-12, päivä arvon 1-31

SUORAIK = tutkinnon suoritusajankohta on virheellinen

tutkinnon suoritusvuoden täytyy olla sama kuin tilastovuosi, suorituskuukausi saa arvon 1-12, päivä arvon 1-31

TU = sama tutkinto on aineistossa kahteen kertaan

TU1 = tarkistetaan onko joillakin havainnoilla samat arvot joka muuttujalla - täydellinen tuplatietue
TU2 = tarkistetaan onko joillakin havainnoilla samat oppilaitostunnus-tutkinto-ko-sv -kombinaatiot eli henkilöllä täsmälleen sama tutkinto aineistossa kahteen kertaan

OP = suoritetuissa opintopisteissä tarkistettavaa (voi olla 0 - 300)

OP1 = omassa ammattikorkeakoulussa suoritetut opintopisteet
OP2 = muissa ammattikorkeakouluissa suoritetut opintopisteet
OP3 = yliopistoissa suoritetut opintopisteet
OP4 = yleissivistävissä ja ammatillisissa oppilaitoksissa suoritetut opintopisteet
OP5 = ulkomailla suoritetut opintopisteet
OP6 = muualla suoritetut opintopisteet
OP7 = opintopisteet yhteensä

LASNALK = läsnä- ja poissaololukukausissa virhe

läsnä- ja poissaololukukausien yhteenlaskettua määrää verrataan ensimmäisen kirjoihintulon ja tutkinnonsuoritusajankohdan perusteella laskettuihin käytettyihin kausiin.

 OPLAAJ = opintojen laajuus virheellinen

OPLAAJ1=muodollinen virhe (voi olla 1 - 300)
OPLAAJ2 = ei voi olla enemmän kuin opm:n päätöksessä määrätty tutkinnon pituus.

KIRTU1PV = Ensimmäinen kirjoihintuloajankohta ko. tutkintoon virheellinen

KIRTU1PV = kirjoihintulokuukausi voi saada arvon 1-12, päivä arvon 1-31
KIRTU1P2 = ei voi olla suurempi kuin kirjoihintulo tähän tutkintoon tässä amk:ssa eikä suurempi kuin tilastovuosi

OPETTAJA = Lastentarhanpettajan koulutus muodoltaan virheellinen

OPETT1 = voi olla 1, 2, tai tyhjä.
OPETT2 = opettajankoulutus ei ole sosionomi-tutkinnolla

RAHLAHDE = Rahoituslähde muodoltaan virheellinen

RAHLAHDE=voi olla joko 1, 2, 3, 4 tai 5
RAHLAHD4= rahoituslähde on 4 ja maksullisen tilauskoulutuksen sijaintimaatieto puuttuu

TILMAA = Maksullisen tilauskoulutuksen sijaintimaatieto virheellinen

Verrataan sijaintimaatietoa sallittujen maiden listaan
*/
--/*
IF OBJECT_ID('tempdb..#tarkistus') IS NOT NULL
BEGIN
  truncate table #tarkistus
  drop table #tarkistus
  print convert(varchar,getdate(),120)+' DROP temp-taulu'
END
;
create table #tarkistus (
  tunn varchar(5),  
  --koultyp1 varchar(1),
  --koultyp2 varchar(8),
  --koultyp3 varchar(8),
  koulky_avain varchar(20),
  kkieli varchar(2),
  kkun2 varchar(3),
  ht1 varchar(11),
  ht2 varchar(11),
  ht3 varchar(11),
  ht4 varchar(11),
  aikieli varchar(2),
  askun varchar(3),
  kansal varchar(3),
  kirtupv2 varchar(8),
  suoraik varchar(8),
  tu1 varchar(1),
  tu2 varchar(1),
  op1 varchar(4),
  op2 varchar(4),
  op3 varchar(4),
  op4 varchar(4),
  op5 varchar(4),
  op6 varchar(4),
  op7 varchar(4),
  --lasnalk varchar(10),
  --oplaaj1 varchar(4),
  --kirtu1pv varchar(8),
  --kirtu1p2 varchar(8),
  --opett1 varchar(2),
  --opett2 varchar(8),
  --rahlahde varchar(1),
  --koulutuskoodi varchar(15), 
  --
  opiskelijaavain varchar(100),
  opintosuoritusavain varchar(100),
  opiskeluoikeusavain varchar(100),
  opiskelija_id int,
  opintosuoritus_id int,
  opiskeluoikeus_id int,
  db varchar(8) 
)
print convert(varchar,getdate(),120)+' CREATE temp-taulu #tarkistus'
;

insert into #tarkistus
select
 tunn = ''--(case when kk not in (select koodi from virta..Organisaatio) then kk else '' end)
 --,koulky_avain = (case 
  --        when koulky not in (select koodi from virta..Tutkintonimike) then koulky 
  --        when koulky IS NULL then 'puuttuu' 
  --      else '' end)

,koulky_avain = (case         
        when koulky IS NULL AND opiskeluoikeusavain IS NOT NULL then 'puuttuu'
        when koulky not in (select koodi from virta.dbo.Tutkintonimike) then koulky 
        when cast(substring(koulky,1,1) as int) not between 6 and 9 then koulky 
        when koulky LIKE '%00' then koulky  + '_00 loppuinen' 
        else '' 
        end)


--,koultyp1 = (case when koultyp not in ('1','2','3','5','6') then koultyp else '' end)
--,koultyp2 = (case
--  when koultyp in ('3','5') and len(koulk)=6 and koulk<>'999999' then koultyp+' '+koulk
--  when koultyp in ('1','2','6') and koulk='999999' then koultyp+' '+koulk
--  else '' end)
--,koultyp3 = (case
--  when koultyp in ('1','2','6') and koulk='999999' then koultyp+' '+koulk
--  else '' end)

,kkieli = ''--(case when kiel not in ('fi','sv','en','de') then kiel else '' end)
--,kkun = --toimilupapäätökset?
,kkun2 = (case
  --when koultyp in ('2','3','5') --miksi?
  when 1=1
  and kkun not in (select koodi from virta..Kunta cross join #param where #param.tilpvm between coalesce(Alkamispaivamaara,'1900-1-1') and coalesce(Paattymispaivamaara,'9999-1-1'))
  then kkun else '' end)
,ht1 = (case when len(ht)<11 then ht else '' end)
,ht2 = (case
  when len(ht)=11
  and substring(ht,11,1) <>
    case when cast(SUBSTRING(ht,1,6) + SUBSTRING(ht,8,3) as int) % 31 = 0 then '0'
    else SUBSTRING('123456789ABCDEFHJKLMNPRSTUVWXY',cast(SUBSTRING(ht,1,6) + SUBSTRING(ht,8,3) as int) % 31,1) end
  then ht
  else '' end)
,ht3 = (case
  when len(ht)=11 and sp='1' and cast(SUBSTRING(ht,10,1) as int) % 2 = 0 then ht
  when len(ht)=11 and sp='2' and cast(SUBSTRING(ht,10,1) as int) % 2 = 1 then ht
  else '' end)
-- 2000 luvulla syntyneet välimerkki, tulisi olla A
,ht4 = (CASE 
    WHEN LEN(ht)<11  AND SUBSTRING(ht,7,1) = '-' AND (CAST(SUBSTRING(ht,5,2) AS int) < 10) THEN ht 
    ELSE '' 
    END)    
,aikieli = (case
  --when aikieliy not in (select koodi from virta..Kieli cross join #param where #param.tilpvm between coalesce(Alkamispaivamaara,'1900-1-1') and coalesce(Paattymispaivamaara,'9999-1-1'))
  when aikieliy not in ('1','2','3')
  then aikieliy
  else '' end)
,askun = ''--(case
  --when askunta not in (select koodi from virta..Kunta cross join #param where #param.tilpvm between coalesce(Alkamispaivamaara,'1900-1-1') and coalesce(Paattymispaivamaara,'9999-1-1'))
  --then askunta
  --else '' end)
,kansal = ''--(case
  --when kansal not in (select koodi from virta..Maa cross join #param where #param.tilpvm between coalesce(Alkamispaivamaara,'1900-1-1') and coalesce(Paattymispaivamaara,'9999-1-1'))
  --then kansal
  --else '' end)
,kirtupv2 = (case
  when cast(substring(kirtu,1,4) as int) > (select year(tilpvm) from #param) then kirtu
  --when cast(substring(kirtu,5,2) as int) not between 1 and 12 then kirtu
  --when cast(substring(kirtu,7,2) as int) not between 1 and 31 then kirtu
  else '' end)
,suoraik = (case
  when cast(substring(suorpvm,1,4) as int) <> (select year(tilpvm) from #param) then suorpvm
  when cast(substring(suorpvm,5,2) as int) not between 1 and 12 then suorpvm
  when cast(substring(suorpvm,7,2) as int) not between 1 and 31 then suorpvm
  else '' end)
,tu1 = (case
  when exists (
    select 1 from #tktutk t2 where t2.opiskelijaavain=#tktutk.opiskelijaavain and t2.kk = #tktutk.kk
    group by kk,kkun,ht,snimi,enimi,sp,aikieliy,--askunta,
    kansaly,kirtu,
    suorpvm,
    opoylio,opmylio,opamk,opaylio,opulk,opmuu,opyht,--lasnalk,poissalk,laajuus,kirtu1pv,lastu,
    sv,om,
    --rahlahde,
    koulky,optutk
    having count(*)>1
  ) then '1'
  else '' end)
,tu2 = (case
  when exists (
    select 1 from #tktutk t2 where t2.opiskelijaavain=#tktutk.opiskelijaavain and t2.kk = #tktutk.kk
    group by kk, koulky, suorpvm, ht, snimi, enimi, sp
    having count(*)>1
  ) then '1'
  else '' end)
,op1 = (case when cast(opoylio as int)>500 then opoylio else '' end)
,op2 = (case when cast(opmylio as int)>300 then opmylio else '' end)
,op3 = (case when cast(opamk as int)>300 then opamk else '' end)
,op4 = (case when cast(opaylio as int)>300 then opaylio else '' end)
,op5 = (case when cast(opulk as int)>300 then opulk else '' end)
,op6 = (case when cast(opmuu as int)>300 then opmuu else '' end)
,op7 = (case when cast(opyht as int)>500 then opyht else '' end)
--,lasnalk = (case
--  when cast(lasnalk as int)+cast(poissalk as int) >
--    ((cast(substring(suorpvm,1,4) as int) - cast(substring(kirtu1pv,1,4) as int)) * 2)
--    +1+((case when cast(substring(suorpvm,5,2) as int)<8 then 1 else 2 end
--      -case when cast(substring(kirtu1pv,5,2) as int)<8 then 1 else 2 end))
--  then lasnalk+'+'+poissalk+ '<'+ cast(
--    ((cast(substring(suorpvm,1,4) as int) - cast(substring(kirtu1pv,1,4) as int)) * 2)
--     +1+((case when cast(substring(suorpvm,5,2) as int)<8 then 1 else 2 end
--       -case when cast(substring(kirtu1pv,5,2) as int)<8 then 1 else 2 end))
--    as varchar)
--  else '' end)
--,oplaaj1 = (case when cast(laajuus as int) > 300 then laajuus else '' end)
--,kirtu1pv = (case
--  when cast(substring(kirtu1pv,5,2) as int) not between 1 and 12 then kirtu1pv
--  when cast(substring(kirtu1pv,7,2) as int) not between 1 and 31 then kirtu1pv
--  else '' end)
--,kirtu1p2 = (case
--  when cast(kirtu1pv as int) > cast(kirtupv as int) then kirtu1pv
--  when cast(substring(kirtu1pv,1,4) as int) > (select year(tilpvm) from #param) then kirtu1pv
--  else '' end)
--,opett1 = (case when coalesce(lastu,'') not in ('','1','2' ,'3','4','5') then lastu else '' end)
--,opett2 = (case
--  when lastu in ('1' ,'3','4','5') and koulk in ('671201','771301') --sosionomi (amk), sosiaaliala; sosionomi (ylempi amk), sosiaaliala
--  then koulk+'+'+lastu
--  else '' end)
--,rahlahde = (case when rahlahde not in ('1','2','3','4','5') then rahlahde else '' end)
--,koulutuskoodi = (case when koulky not in (select koodi from virta..Tutkintonimike) then koulky+'1' else '' end)
--
,opiskelijaavain
,opintosuoritusavain
,opiskeluoikeusavain
,opiskelija_id
,opintosuoritus_id
,opiskeluoikeus_id
,db = (select Asetus from Yhteiset.Asetukset.Ohjaus_Parametrit where Toiminto='YO_tunnus' and Arvo=kk)
from #tktutk
;

/*
select * from #tarkistus
where ''<>
tunn+--koultyp1+koultyp2+koultyp3+
srhtup+
kkieli+kkun2+ht1+ht2+ht3+aikieli+askun+kansal+
kirtupv2+suoraik+tu1+tu2+
op1+op2+op3+op4+op5+op6+op7--+
--lasnalk+oplaaj1+kirtu1pv+kirtu1p2+
--opett1+opett2+rahlahde
;
--*/

-- transponoi tulokseksi
/*
create table TK_tarkistus (
  virhekoodi varchar(20),
  virhe nvarchar(200),
  arvo varchar(50),
  opiskelijaavain varchar(100),
  opintosuoritusavain varchar(100),
  opiskeluoikeusavain varchar(100),
  opiskelija_id int,
  opintosuoritus_id int,
  opiskeluoikeus_id int,
  db varchar(8),
  luoja nvarchar(128) not null default suser_name(),
  luontipaivamaara datetime not null default getdate(),
  tiedonkeruu varchar(20),
)
--*/
delete from TK_tarkistus where tiedonkeruu='tk_tutk_yo';
insert into TK_tarkistus (virhekoodi,virhe,arvo,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,tiedonkeruu)
select 'tunn' as VIRHEKOODI, 'oppilaitostunnus on virheellinen' as VIRHE,tunn AS ARVO,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>tunn
---union select 'koultyp1', 'koulutustyyppi on virheellinen; muuttujan arvo ei ole 1, 2, 3, 5 eikä 6', koultyp1,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>koultyp1
---union select 'koultyp2', 'erikoistumisopinnoissa ja opettajankoulutuksessa koodi on joko 3 tai 5, muuten 1, 2 tai 6', koultyp2,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>koultyp2
---union select 'koultyp3', 'koulutusohjelma ei sallittu erikoistumisopinnoissa tai opettajankoulutuksessa',koultyp3,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>koultyp3

union select 'koulky_avain', 'tutkintokoodi on virheellinen tai puuttuu',koulky_avain,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>koulky_avain

union select 'kkieli', 'koulutuksen opetuskieli -koodiarvo on virheellinen', kkieli,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>kkieli
union select 'kkun2', 'kunta-koodiarvo on virheellinen',kkun2,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>kkun2
union select 'ht1', 'Puutteellinen henkilötunnus - loppuosa puuttuu', ht1,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>ht1
union select 'ht2', 'henkilötunnus, jonka jakojäännös ei täsmää - loppuosa virheellinen', ht2,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>ht2
union select 'ht3', 'sukupuolitieto virheellinen', ht3,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>ht3
union select 'ht4', 'Väärä välimerkki lopussa. Jos 2000 luvulla syntynyt tulee olla A ei -',ht4,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>ht4
union select 'aikieli', 'äidinkielitieto virheellinen', aikieli,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>aikieli
union select 'askun', 'asuinkuntatieto virheellinen', askun,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>askun
union select 'kansal', 'kansalaisuustieto virheellinen', kansal,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>kansal
union select 'kirtupv2', 'kirjoihintulovuosi ei saa olla suurempi kuin tilastovuosi, kirjoihintulokuukausi saa arvon 1-12, päivä arvon 1-31', kirtupv2,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>kirtupv2
union select 'suoraik', 'tutkinnon suoritusajankohta on virheellinen',suoraik,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>suoraik
union select 'tu1', 'joillakin havainnoilla samat arvot joka muuttujalla - täydellinen tuplatietue', tu1,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>tu1
union select 'tu2', 'sama oppilaitostunnus-koulutustyyppi-koulutuskoodi -kombinaatio useita kertoja', tu2,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>tu2
union select 'op1', 'omassa ammattikorkeakoulussa suoritetuissa opintopisteissä tarkistettavaa (voi olla 0 - 300)',op1,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>op1
union select 'op2', 'muissa ammattikorkeakouluissa suoritetuissa opintopisteissä tarkistettavaa (voi olla 0 - 300)',op2,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>op2
union select 'op3', 'yliopistoissa suoritetuissa opintopisteissä tarkistettavaa (voi olla 0 - 300)',op3,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>op3
union select 'op4', 'yleissivistävissä ja ammatillisissa oppilaitoksissa suoritetuissa opintopisteissä tarkistettavaa (voi olla 0 - 300)',op4,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>op4
union select 'op5', 'ulkomailla suoritetuissa opintopisteissä tarkistettavaa (voi olla 0 - 300)',op5,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>op5
union select 'op6', 'muualla suoritetuissa opintopisteissä tarkistettavaa (voi olla 0 - 300)',op6,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>op6
union select 'op7', 'opintopisteet yhteensä tarkistettavaa (voi olla 0 - 300)',op7,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>op7
--union select 'koulutuskoodi', 'koulutuskoodi on virheellinen',koulutuskoodi,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_tutk_yo' from #tarkistus where ''<>koulutuskoodi
---union select 'lasnalk', 'läsnä- ja poissaololukukausien yhteenlaskettua määrää verrataan ensimmäisen kirjoihintulon ja tutkinnonsuoritusajankohdan perusteella laskettuihin käytettyihin kausiin',lasnalk,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>lasnalk
---union select 'oplaaj1', 'Opintojen laajuus virheellinen; muodollinen virhe (voi olla 1 - 300)', oplaaj1,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>oplaaj1
---union select 'kirtu1pv', 'Ensimmäinen kirjoihintuloajankohta ko. tutkintoon virheellinen; kirjoihintulokuukausi voi saada arvon 1-12, päivä arvon 1-31', kirtu1pv,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>kirtu1pv
---union select 'kirtu1p2', 'Ensimmäinen kirjoihintuloajankohta ko. tutkintoon virheellinen; ei voi olla suurempi kuin kirjoihintulo tähän tutkintoon tässä amk:ssa eikä suurempi kuin tilastovuosi', kirtu1p2,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>kirtu1p2
---union select 'opett1', 'Lastentarhanpettajan koulutus muodoltaan virheellinen; voi olla 1, 2, 3, 4, 5 tai tyhjä.', opett1,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>opett1
---union select 'opett2', 'opettajankoulutus ei ole sosionomi-tutkinnolla', opett2,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>opett2
---union select 'rahlahde', 'Rahoituslähde muodoltaan virheellinen. Voi olla 1, 2, 3, 4 tai 5.', rahlahde,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_yo' from #tarkistus where ''<>rahlahde
;
print convert(varchar,getdate(),120)+' tarkistustiedot viety tauluun TK_tarkistus'
;


Muutoshistoria

27.5.2019 :: HT4 tarkistuksen lisäys :: wiki-versio 37

  • Lisätty tarkistus hetutomille, syntymäajan välimerkki, muodossa ppkkvv-. Hetu-välimerkki jos henkilö 2000-luvulla syntynyt ei saa olla '-' vaan 'A', syntymäaika tulee olla muodossa ppkkvvA

10.1.2019 ::Kvtutk sarakkeen (Kansainvälinen tutkinto) lisäys :: wiki-versio 36

  • Lisätty kvtutk (Kansainvälinen tutkinto) sarake, johon  katsotaan onko opintosuorituksen luokittelua 9 tai 10 jos on kyseinen koodi laitetaan sarakkeelle, muuten tyhjää.

5.10.2017 ::Tu1 ja tu2 käsittelyyn muutos tarkistuksessa :: wiki-versio 35

  • tu1 ja tu2 käsittelyyn lisätty korkeakoulu päättely, koska samalla opiskelija-avaimella voi olla useita tuloksia eri korkeakouluista.

2.7.2017 :: Erikoislääkäri kirjoilletulo päivämäärä (kirtu-muuttuja). Aineopettaja pätevyys hierarkiasta (SV-muuttuja)  :: wiki-versio 34
  • Erikoislääkärit ('775101','775201','775999') kirjoilletulopvm opiskeluoikeuden alkamispvm jos tyhjä, kirtu-muuttuja.
    Aineopettaja pätevyys suoritushierarkiasta (SV-muuttuja 001), pätevyydet ik, il, im# (opettaja, erill opet pedag opinn).

28.5.2017 :: Erikoiseläinlääkäri opintopisteet :: wiki-versio 33
  • Erikoiseläinlääkäri (775301) opintopisteet koska on tutkintoon johtava koulutus

20.1.2017 :: Ohjausala ja erikoistumikoulutus koodi :: wiki-versio 32
  • Ohjausala sarake (1-2), 999999 koodeille (Erikoistumiskoulutus). Erikoistumiskoulutuksen koulutuskoodi jos on. linkkirikki
22.1.2016 :: Erikoistumikoulutus :: wiki-versio 31
  • Poimitaan koulutuskoodilla 999999 mukaan myös erikoistumiskoulutukset
12.1.2016 :: Erikois[hammas]lääkärit :: wiki-versio 29
  • Poimitaan ei-tutkinto-opintoina olevat erikoislääkärin ja erikoishammaslääkärin suoritukset mukaan perusjoukkoon pätevyystiedon perusteella.
  • Korjattu lisäksi pätevyyskoodeja liittyen SV-muuttujaan (nk. aineope).
  • Lisätty "varmuuden vuoksi" ehtoja pariin kohtaan (mm. koulky-muuttuja), ettei jyrätä yli jo pääteltyä tietoa-
12.10.2015 :: Päivitetty "Aineenopettajan pedagogiset opinnot (sv)" poimintaa :: wiki-versio 22
  • Uudet opettajan pätevyyskoodit "ik","il","im" (entiset "1" ja "904")
28.1.2015 :: Opintopisteitä ei merkitä jatkotutkinnoille :: wiki-versio 21
  • Laitettu opintopisteiden laskentaan tarkistus jatkotutkinnoista. Jos jatkotutkinto, ei lasketa ja merkitä opintopisteitä
27.1.2015 :: Opintosuoritusten valinta opintopistelaskentaan: hierarkia vs. opiskeluoikeus-kytkös :: wiki-versio 20
  • Poistettu TAI-ehto opintosuoritusten valitsemisesta opintopistekertymiä laskettaessa
  • Tehty VALINTA: Mikäli yliopistolla on sisältyvyydet käytössä, tehdään päättely hierarkian kautta, muutoin opiskeluoikeus-kytköksen kautta.
26.1.2015 :: Opintosuoritusten lehtitason määrityksen korjaus :: wiki-versio 19
  • Muutettu vain opintosuoritusten opintopistelaskennassa nk. lehtitaso-tarkastelu olemaan ottamatta huomioon opintosuorituksen lajien 3 ja 4 opintosuorituksia.
15.1.2015 :: Pätevyydet katsotaan koko opintosuoritusten hierarkiasta
12.1.2015 :: Tutkinto-opintosuorituksen opintopisteet :: wiki-versio 18
  • Lisätty uusi sarake "Tutkinnon opintopisteet", jonka sisällöksi tuodaan juuri tutkinto-opintoon (laji=1) tallennettu laajuustieto.
  • Tutkinnon laajuus -tiedosta päätellään sitten "omassa yliopistossa suoritetut opintopisteet" (tutkinnon opintopisteet miinus kategorisoidut opintopisteet.
18-19.12.2014 :: Opintopistekertymiä kehitetty :: wiki-versio 17
  • Opintosuoritusten hierarkia -kyselyä kehitetty. Nyt toteutuksessa ei enää ole kuin periaatteellinen syvyys-/tasorajoitin (20-tasoinen hierarkia sallitaan).
  • Jaottelua kehitetty seuraavasti:
    • Lisätty yliopistokategoriaan myös oppilaitostyyppi 43='Sotilaskorkeakoulut'.
    • Avoimessa korkeakoulussa suoritetut opintopisteet rankataan nyt kaikista muista luokista pois.
    • Korjattu opintopistekertymien jaottelu "muiden yliopistojen" osalta. Poiminnassa ei aiemmin pärjätty sen tiedon kanssa, että yliopisto itse oli järjestävä-roolisena organisaationa.
  • Lisäksi juuri opintosuoritusten käsittelyä on nopeutettu löytämällä tietomassaan sopivia rajoittimia (ei käsitellä kaikkia koko korkeakoulun opintosuorituksia vaan vain niitä jotka liittyvät perusjoukossa poimittuihin).
  • Opiskelumuoto-muuttujan (OM) arvon 2 päättely kopioitu opiskelijatiedonkeruusta.
15.12.2014 :: Parannettu opintopisteiden jakautumisen poimintaa :: wiki-versio 16
  • Tuotu kattavampi aputaulu eri organisaatioiden (oppilaitosten) oppilaitostyypeistä mukaan
  • Tarkasteltu opintopisteiden jakautumisten kategoriointia paremmaksi
  • Huomioitu syvempi hierarkia opintosuorituksilla
8-9.12.2014 :: Korjauksia ja CSV-muoto :: wiki-versio 13
  • Korjattu
    • Opiskeluoikeuden päättely
    • Opintosuoritusten kertymä tutkinto-opinnon hierarkiasta
  • Tulos CSV-muotoiseksi
17.11.2014 :: Automaatin aika :: wiki-versio 7


1 Comment

  1. Lauri Jokipii

    Tiedonkeruun katselmointi 2.12.2014:

    Yleisesti

    • dokumentaatio paremmaksi
    • "positio"-tieto muuttunut
      • NB! positiot poistuu kokonaan CSV-tiedostomuodon myötä

    Perusjoukon valinta

    • kirtu-tiedossa haasteita

    Opiskelumuoto, muu kuin asetuksen mukainen maisteriohjelma

    • Tietoa ei ole tietovarannossa

    Opintopisteiden jaottelu ja päättely vielä kesken, tarkentuu.