"Tiedot ammattikorkeakouluissa kalenterivuoden [vuosi] aikana suoritetuista tutkinnoista, erikoistumisopinnoista, erikoistumiskoulutuksista ja ammatillisesta opettajankoulutuksesta."

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)
	kk varchar(5),--Oppilaitos
	tilpvm date,--Tilastointipäivä
)
;
insert into #param (vuosi,org,kk,tilpvm)
	select
	@vuosi as vuosi
	,UPPER(DB_NAME()) as org
	,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=DB_NAME()) as kk
	,@vuosi+'-09-20' as tilpvm
;
--select * from #param

-- Temp-taulu tiedonkeruun tietoja varten
create table #tktutk (
    tunn varchar(5),--Oppilaitostunnus
    koultyp varchar(1),--Koulutustyyppi
    --1=Amk-tutkintoon johtava nuorten koulutus
    --2=Amk-tutkintoon johtava aikuiskoulutus
    --3=Erikoistumisopinnot
    --5=Ammatillinen opettajankoulutus
    --6=Ylempi ammattikorkeakoulututkinto
    --7=Erikoistumiskoulutus
    kiel varchar(2),--Koulutuksen opetuskieli
    kkun varchar(3),--Opiskelukunta
    ht varchar(11),--Henkilötunnus
    snimi varchar(255),--Opiskelijan sukunimi
    enimi varchar(255),--Opiskelijan etunimet
    sp varchar(1),--Opiskelijan sukupuoli
    aikieli varchar(2),--Opiskelijan äidinkieli
    askunta varchar(3),--Opiskelijan vakinainen asuinkunta.
    kansal varchar(3),--Opiskelijan kansalaisuus
    kirtupv varchar(8),--Ko. tutkintoon kirjoihintuloajankohta ko. ammattikorkeakoulussa
    suorpvm varchar(8),--Tutkinnon suorittamisajankohta ko. ammattikorkeakoulussa
    opoamk varchar(3),--Omassa ammattikorkeakoulussa suoritetut opintopisteet (Sisältää työharjoittelun)
    opmamk varchar(3),--Muissa ammattikorkeakouluissa suoritetut opintopisteet (Suomessa)
    opylio varchar(3),--Yliopistoissa suoritetut opintopisteet (Suomessa)
    opylei varchar(3),--Yleissivistävissä ja ammatillisissa oppilaitoksissa suoritetut opintopisteet (Suomessa)
    opulk varchar(3),--Ulkomailla suoritetut opintopisteet
    opmuu varchar(3),--Muualla suoritetut opintopisteet
    opyht varchar(4),--Opintopisteet yhteensä (Tutkintoa varten suoritetut opintopisteet (myös tutkintovaatimusten yli menevät opintopisteet)
    lasnalk varchar(4),--Läsnäololukukausien määrä ko. tutkinnossa yhteensä
    poissalk varchar(4),--Poissaololukukausien määrä ko. tutkinnossa yhteensä
    laajuus varchar(4),--Ammattikorkeakoululain 14 §:ssä säädetään opintojen hyväksilukemisesta. Opintojen laajuudella tarkoitetaan  hyväksilukemisen jälkeen opiskelijan suoritettavaksi jäävää opintojen määrää opintopisteinä.
    kirtu1pv varchar(8),--Ajankohta, jolloin opiskelija on ilmoittautunut ensimmäistä kertaa läsnä- tai poissaolevaksi kyseiseen tutkintoon missä tahansa ammatti-korkeakoulussa. (Siirto-opiskelijoilla eri kuin kirjoihintulo ko.tutkintoon omassa amk:ssa.) Merkitään: vvvvkkpp
    lastu varchar(1),--Sosionomi (amk) -tutkinnon suorittanut, jonka aineyhdistelmään kuuluu 60 opintopisteen laajuiset varhaiskasvatuksen ja sosiaalipedagogiikan opinnot
    rahlahde varchar(1),--Opiskelijan rahoitustausta
    tilmaa varchar(3),--Maksullisen tilauskoulutuksen sijaintimaa
    koulk varchar(6),--Koulutuskoodi
    --
    optutk varchar(4),--Tutkinto-opintosuorituksen opintopisteet
    ohjauksenAla      varchar(3), -- OKM Ohjauksen ala
    ekkoodi         varchar(3), -- Erikoistumiskoulutuksen koodi    
    puuttuulk       varchar(4)  -- Puuttuu tieto 4 lukukaudet
    kvtutk         varchar(2), -- Opintosuorituksen luokittelun 9 (Suoritus on kansainvälinen yhteistutkinto) ja 10 (Suoritus on kansainvälinen kaksoistutkinto) tieto

    oppijanumero varchar(200), --Kansallinenoppijaid
    suorituspaivamaara date,
    --
    opiskelijaavain varchar(100),
    opintosuoritusavain varchar(100),
    opiskeluoikeusavain varchar(100),
    opiskelija_id int,
    opintosuoritus_id int,
    opiskeluoikeus_id int,
    -- avuksi
    oikeus_alkamispaivamaara date,
    oikeus_paattymispaivamaara date,
    oikeus_siirtopaivamaara date
)
;


Perusjoukon valinta

Haetaan tietovarannosta kaikki

  • tutkinto-lajiset opintosuoritukset, jotka on 
  • suoritettu tilastointivuoden aikana.

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

  • erikoistumisopinnot (12), ammatillinen opettajakoulutus (15) tai 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).

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

  • Korkeakoulu (tunn)
  • Henkilötunnus (ht)
  • Sukupuoli (sp)
  • Sukunimi (snimi)
  • Etunimet (enimi)
  • Kansalaisuus (kansal), korjataan puuttuvat tiedot arvoon 999='Tuntematon'
  • Äidinkieli (aikieli), korjataan puuttuvat tiedot arvoon 99='Tuntematon'
  • Asuinkunta (askunta)
  • Suorituspäivämäärä (suorpvm)
  • Kansallinen oppijanumero (oppijanumero)
SQL
---
-- Lisätään perustiedot (henkilö, opiskelija)
-- * täytyy olla tutkinto

---

insert into #tktutk (
    tunn,ht,sp,snimi,enimi,kansal,aikieli,askunta
    ,suorpvm
    ,suorituspaivamaara
    ,oppijanumero
    ,opiskelijaavain,opiskelija_id
    ,opintosuoritusavain,opintosuoritus_id
    ,opiskeluoikeusavain,opiskeluoikeus_id
    ,oikeus_alkamispaivamaara,oikeus_paattymispaivamaara,oikeus_siirtopaivamaara
)
select tunn = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,ht = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,snimi = h.Sukunimi
,enimi = h.Etunimet
,kansal = (select top 1 m.Koodi
  from virta.dbo.Henkilon_maa hm
  inner join virta.dbo.Maa m on m.ID=hm.MaaID
  inner join virta.dbo.Maan_rooli mr on mr.ID=hm.Maan_rooliID
  where hm.HenkiloID=h.ID --sido
  and mr.Koodi='1' --Kansalaisuus
 )
,aikieli = (select top 1 UPPER(k.Koodi) from virta.dbo.Kieli k where k.ID=h.KieliID)
,askunta = (select top 1 k.Koodi from virta.dbo.Kunta k where k.ID=h.KuntaID)
,suorpvm = CAST(YEAR(s.Suorituspaivamaara) as varchar)
           +RIGHT('00'+CAST(MONTH(s.Suorituspaivamaara) as varchar),2)
           +RIGHT('00'+CAST(DAY(s.Suorituspaivamaara) as varchar),2)
--
,suorituspaivamaara = s.Suorituspaivamaara
--
,oppijanumero = p.Kansallinen_op_id
,opiskelijaavain = p.Avain
,opiskelija_id = p.ID
,opintosuoritusavain = s.Avain
,opintosuoritus_id = s.ID
,opiskeluoikeusavain = o.Avain
,opiskeluoikeus_id = o.ID
--
,oikeus_alkamispaivamaara = o.Alkamispaivamaara
,oikeus_paattymispaivamaara = coalesce(o.Paattymispaivamaara,'9999-01-01')
,oikeus_siirtopaivamaara = o.Siirtopaivamaara

from virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Opintosuoritus s on s.OpiskelijaID=p.ID
inner join virta.dbo.Opintosuorituksen_laji ol on ol.ID=s.Opintosuorituksen_lajiID
inner join virta.dbo.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)
-- virta-kanta: vain ammattikorkeakoulut
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='41'--Ammattikorkeakoulut
    and i.Lataustuotanto='1'
)
;


-- + erikoistumisopinnot ja ammatilliset opettajakoulutukset päättyneen opiskeluoikeuden mukaan (tyyppi=12 tai 15, tila=3)
-- + erikoistumiskoulutus (tyyppi=19, tila=3)
insert into #tktutk (
    tunn,ht,sp,snimi,enimi,kansal,aikieli,askunta
    ,suorpvm
    ,koulk
    ,suorituspaivamaara
    ,opiskelijaavain,opiskelija_id
    ,opiskeluoikeusavain,opiskeluoikeus_id
    ,oikeus_alkamispaivamaara,oikeus_paattymispaivamaara,oikeus_siirtopaivamaara
)
select tunn = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,ht = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,snimi = h.Sukunimi
,enimi = h.Etunimet
,kansal = (select top 1 m.Koodi
  from virta.dbo.Henkilon_maa hm
  inner join virta.dbo.Maa m on m.ID=hm.MaaID
  inner join virta.dbo.Maan_rooli mr on mr.ID=hm.Maan_rooliID
  where hm.HenkiloID=h.ID --sido
  and mr.Koodi='1' --Kansalaisuus
 )
,aikieli = (select top 1 UPPER(k.Koodi) from virta.dbo.Kieli k where k.ID=h.KieliID)
,askunta = (select top 1 k.Koodi from virta.dbo.Kunta k where k.ID=h.KuntaID)
,suorpvm = CONVERT(varchar,o.Paattymispaivamaara,112)--112=yyyymmdd
,koulk = '999999' --koulutuskoodi
--
,suorituspaivamaara = o.Paattymispaivamaara
--
,opiskelijaavain = p.Avain
,opiskelija_id = p.ID
,opiskeluoikeusavain = o.Avain
,opiskeluoikeus_id = o.ID
--
,oikeus_alkamispaivamaara = o.Alkamispaivamaara
,oikeus_paattymispaivamaara = coalesce(o.Paattymispaivamaara,'9999-01-01')
,oikeus_siirtopaivamaara = o.Siirtopaivamaara

from virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Opiskeluoikeus o on o.OpiskelijaID=p.ID
inner join virta.dbo.Op_oikeuteen_liittyva_tila ot on ot.OpiskeluoikeusID=o.ID
inner join virta.dbo.Opiskeluoikeuden_tila ti on ti.ID=ot.Op_oikeuden_tilaID
inner join virta.dbo.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)
-- erikoistumisopinnot ja ammatillinen opettajakoulutus, erikoistumiskoulutus
and ty.Koodi in ('12','15','19') 

-- Ei jos opiskeluoikeus_id jo on viety, tutkintosuoritus vaikka ei ole tutkintoon johtava
AND o.id NOT IN (  SELECT T.opiskeluoikeus_id FROM #tktutk T )

-- virta-kanta: vain ammattikorkeakoulut
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='41'--Ammattikorkeakoulut
    and i.Lataustuotanto='1'
)
;



-- tuntemattomia
-- : kansal
update T
set kansal='999'
from #tktutk T
where kansal is null
;
-- : aikieli
update T
set aikieli='99'
from #tktutk T
where aikieli is null
;

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.

Koulutustyyppi (koultyp)

Koulutustyyppi päätellään opiskeluoikeuden tyypin ja opiskeluoikeuden tiedon Aikuiskoulutus perusteella.

Mikäli opiskeluoikeuden tyyppi on:

  • 1=Ammattikorkeakoulututkinto, katsotaan lisäksi Aikuiskoulutus-tieto ja mikäli se on:
    • 1=kyllä, eli on aikuiskoulutusta, koulutustyyppi saa arvon 2=Amk-tutkintoon johtava aikuiskoulutus
    • muuten koulutustyyppi saa arvon 1=Amk-tutkintoon johtava nuorten koulutus
  • 3=Ylempi ammattikorkekoulututkinto, koulutustyyppi saa arvon 6=Ylempi korkeakoulututkinto
  • 12=Erikoistumisopinnot, koulutustyyppi saa arvon 3=Erikoistumisopinnot
  • 15=Ammatillinen opettajankoulutus, koulutustyyppi saa arvon 5=Ammatillinen opettajankoulutus
  • 19=Erikoistumiskoulutus, koulutustyyppi saa arvon 7=Erikoistumiskoulutus

    Ohjauksenala ei-tutkintoon johtaville. Erikoistumiskoulutuksen koulutuskoodi jos sellainen on.


SQL
-- koultyp :: Koulutustyyppi
update T
set koultyp=
(
	case ot.Koodi
	when '1'--	Ammattikorkeakoulututkinto
	 then
		case
		when o.Aikuiskoulutus='1' then '2'--=Amk-tutkintoon johtava aikuiskoulutus
		when o.Aikuiskoulutus='2' then '1'--=Amk-tutkintoon johtava nuorten koulutus
		else '1'
		end
	when '3'  then '6'--=Ylempi ammattikorkeakoulututkinto
	when '12' then '3'--=Erikoistumisopinnot
	when '15' then '5'--=Ammatillinen opettajankoulutus
	when '19' then '7'--=Erikoistumiskoulutus
	end
)
from #tktutk T
-- TO-DO? Vaihda opintosuoritukseen? Koulutuskoodin kautta?
inner join Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
inner join Opiskeluoikeuden_tyyppi ot on ot.ID=o.Op_oikeuden_tyyppiID
;



-- AMK
-- Erikoistumiskoulutus, ja muu ei-tutkintokoulutusta
UPDATE T
SET T.ohjauksenAla = K.koodi,
    t.koulk = '999999'

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
    INNER JOIN koulutusala K ON K.id = O.koulutusalaid
WHERE     1=1
-- erikoistumisopinnot ja ammatillinen opettajakoulutus, erikoistumiskoulutus (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('12','15','19')
AND K.versio = 'ohjausala'


-- Erikoistumiskoulutuksen koulutuskoodi
UPDATE T
SET T.ekkoodi = E.koodi 

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
    INNER JOIN koulutusala K ON K.id = O.koulutusalaid
    INNER JOIN op_oik_liittyva_erikoistumiskoulutus OE ON OE.opiskeluoikeusid = o.id
    INNER JOIN Erikoistumiskoulutus E ON E.id = OE.erikoistumiskoulutusid
WHERE     1=1
-- erikoistumisopinnot ja ammatillinen opettajakoulutus, erikoistumiskoulutus (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('19')
AND K.versio = 'ohjausala'






Koulutuksen opetuskieli (kiel) (kkieli)

Haetaan opetuskieli-tieto tiedetyn opiskeluoikeuden opiskeluoikeusjaksosta.

SQL
--kiel
update T
set kiel=upper(k.Koodi)
from #tktutk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Kieli k on k.ID=oj.KieliID
where T.suorituspaivamaara between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
;

 

Opiskelukunta (kkun)

Haetaan opetuskunta-tieto tiedetyn opiskeluoikeuden 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')
;

 

Henkilötunnus (ht)

Tieto tietovarannossa olevasta opiskelijan (henkilön) tiedosta sellaisenaan.

Sukunimi (snimi)

Tieto tietovarannossa olevasta opiskelijan (henkilön) tiedosta sellaisenaan.

Etunimet (enimi)

Tieto tietovarannossa olevasta opiskelijan (henkilön) tiedosta sellaisenaan.

Sukupuoli (sp)

Tieto tietovarannossa olevasta opiskelijan (henkilön) tiedosta sellaisenaan.

Äidinkieli (aikieli)

Tieto tietovarannossa olevasta opiskelijan (henkilön) tiedosta sellaisenaan. Puuttuvan tiedon tilalle vaihdetaan arvo '99'.

Opiskelijan vakinainen asuinkunta (askun)

Tieto tietovarannossa olevasta opiskelijan (henkilön) tiedosta sellaisenaan.

Opiskelijan kansalaisuus (kansal)

Tieto tietovarannossa olevasta opiskelijan (henkilön) tiedosta sellaisenaan. Puuttuvan tiedon tilalle vaihdetaan arvo '999'.

Ko. tutkintoon kirjoihintulovuosi ko. ammattikorkeakoulussa (kirtupv)

Haetaan opiskeluoikeuden alkamispäivä ja muunnetaan muotoon VVVVKKPP.

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

 

Tutkinnon suorittamisajankohta ko. ammattikorkeakoulussa (suorpvm)

Tutkinnon (tutkinto-opintosuorituksen) suorituspäivämäärä ja muunnetaan muotoon VVVVKKPP.

Lukukausien lukumäärät (lasnalk, poissalk)
Läsnä- ja poissaololukukausien laskentaan aputaulujen luonti

Tehty lukukausien laskentaan kaksi poimintamallia ("ilmomalli"; ks. aputaulujen luonti; sql-osuus alla)

  • Poimintamallit
    • "välitila": KOTA-seminaarissa esitetty ja alla kuvattu. (käytetään LasnaPoissaLukukausiMaara-tietoa ja oletetaan, että vain poissaoloja)
    • "toinenkk": logiikka, jossa korkeakoulu on ilmoittanut kaikki opiskeluoikeuteen liittyvät lukukausi-ilmoittautumiset lukukausi-ilmoittautumisina riippumatta siitä onko tieto oman kk:n tieto vai ei. (ei käytetä LasnaPoissaLukukausiMaara-tietoa ollenkaan)
  • Valinta poimintamalliin kuulumisesta tehdään aluksi yleisellä tasolla per korkeakoulu sen perusteella, onko mihin tahansa siirto-opiskelijan (siirtopäivämäärä!=null) opiskeluoikeuteen liitetty lukukausi-ilmoittautumistietoa, joka lukukausi-ilmoittautuminen on päättynyt jo ennen kuin opiskeluoikeus on alkanut. Jos löytyy, tulkitaan tuo lukukausi-ilmoittautumiseksi joka on tehty siellä lähdekorkeakoulussa ja siis malli on "toinenkk", muutoin "välitila".
SQL
-- VÄLITILARATKAISU
-- Otetaan tietovarantoon tuodut toisessa korkeakoulussa alun perin tehdyt lukukausi-ilmoittautumiset huomioon syksyllä 2015!
IF OBJECT_ID('tempdb..#ilmomalli') IS NOT NULL
BEGIN
	truncate table #ilmomalli
	drop table #ilmomalli
	if @debug<>0 print convert(varchar,getdate(),120)+' DROP temp-taulu ilmomalli'
END
;
create table #ilmomalli (
	toinen_kk bit -- 1 jos tuotu toisen kk:n ilmoja, 0 muutoin (ja mennään "välitilalla")
)
;
insert into #ilmomalli (toinen_kk)
	select case when exists (
		select *
		from Opiskeluoikeus bo
		join Lukukausi_ilmoittautuminen bl on bl.OpiskeluoikeusID=bo.ID
		where bo.Siirtopaivamaara is not null
		and bo.Alkamispaivamaara>bl.Paattymispaivamaara
	) then 1 else 0 end
;
IF OBJECT_ID('tempdb..#poissaolo_maara_ennen') IS NOT NULL
BEGIN
	truncate table #poissaolo_maara_ennen
	drop table #poissaolo_maara_ennen
	if @debug<>0 print convert(varchar,getdate(),120)+' DROP temp-taulu poissaolo_maara_ennen'
END
;
create table #poissaolo_maara_ennen (
	opiskeluoikeusid int,
	lkm int
)
;
insert into #poissaolo_maara_ennen (opiskeluoikeusid,lkm)
	select l.OpiskeluoikeusID, count(*)
	from Lukukausi_ilmoittautuminen l
	inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
	join #tktutk T on T.opiskeluoikeus_id=l.OpiskeluoikeusID
	where lt.Koodi in ('2','3') --poissa, poissa ei kuluta
	-- siirto-opiskelija; KYLLÄ aiemman kk:n ilmoja
	and T.oikeus_siirtopaivamaara is not null and T.oikeus_siirtopaivamaara<T.oikeus_alkamispaivamaara
	and coalesce(l.Paattymispaivamaara,'9999-01-01') < coalesce(T.oikeus_alkamispaivamaara,'1900-01-01')
	-- kk:lla on aiemman kk:n ilmoja (yleisesti, ei vain tämä tapaus)?
	and 1 = (select toinen_kk from #ilmomalli)
	group by l.OpiskeluoikeusID
;
-- ne joilla tiedot on oikein, eli EI OLE aiemman kk:n ilmoja tuotu:
insert into #poissaolo_maara_ennen (opiskeluoikeusid,lkm)
	select T.opiskeluoikeus_id, o.Lasna_poissa_lukukausien_summa
	from #tktutk T
	join Opiskeluoikeus o on o.id=T.opiskeluoikeus_id
	where T.opiskeluoikeus_id NOT IN (select opiskeluoikeusid from #poissaolo_maara_ennen)
	-- siirto-opiskelija; EI aiemman kk:n ilmoja
	and T.oikeus_siirtopaivamaara is not null and T.oikeus_siirtopaivamaara<T.oikeus_alkamispaivamaara
	and o.Lasna_poissa_lukukausien_summa is not null
	-- kk:lla EI OLE aiemman kk:n ilmoja (yleisesti, ei vain tämä tapaus)?
	and 1 <> (select toinen_kk from #ilmomalli)
;

 

Läsnäololukukaudet (lasnalk)

 

Käytettyjen läsnäololukausien määrä tilastointivuoden loppuun, tai tutkinnon suorittamispäivään, mennessä mukaan lukien siirto-opiskelijoiden käyttämät lukukaudet aiemmassa korkeakoulussa.

 

NB! Koska siirto-opiskelijoihin liittyen tietovarannon tiedot sisältävät paikan vain yhdelle lukukausien läsnä- ja poissaolojen lukumäärälle, on tämä yksi paikka otettu vain poissaolojen käyttöön. Tässä kohdassa lasketaan:

 

  • lähettävässä korkeakoulussa käytetyt läsnäololukukaudet,
  • johon lisätään lukukausien määrä aiemmassa korkeakoulussa,
    • lasketaan lukukausien viimeisten päivien lukumäärä aikavälillä opiskeluoikeuden alkamispäivä - siirtopäivä
    • sql-kysely voi olla vähän vaikea tulkita, sillä siinä pelataan vuosien kanssa, mutta yllä mainittu on tarkoitus
  • mikäli kyse on siirto-opiskelijasta, poistetaan lukumäärästä ilmoitettu poissaolojen lukumäärä.

 

NB2! Tehty poimintaan kaksi poimintamallia (ilmomalli; ks. yllä)

SQL
-- lasnalk :: Läsnäololukukaudet
update T
set lasnalk
 =coalesce(
 (select count(*)
 from Lukukausi_ilmoittautuminen l
 inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
 and lt.Koodi in ('1') --läsnä
 where l.OpiskeluoikeusID=T.opiskeluoikeus_id
 and l.Paattymispaivamaara<=(select vuosi from #param)+'-12-31'
 -- ei sallita ennen opiskeluoikeutta olleita (siirto-opiskelija)
 and l.Paattymispaivamaara > T.oikeus_alkamispaivamaara
 -- "varmuuden vuoksi" ilmoittautumiset pois, eli tutkinnon suorittamisen jälkeen alkavat lk:t pois
 and l.Alkamispaivamaara < T.suorituspaivamaara
 )
 ,0)
 + case
 when oikeus_siirtopaivamaara is not null and oikeus_alkamispaivamaara is not null then
 case
 -- Ensin poikkeus: Samana vuonna tapahtunut alkaminen ja siirto
 when DATEPART(year,oikeus_siirtopaivamaara) = DATEPART(year,oikeus_alkamispaivamaara) then
 case
 when DATEPART(month,oikeus_alkamispaivamaara) < 8 then 0 -- jos 31.7. on jo kohdekk:ssa: 0
 when DATEPART(month,oikeus_siirtopaivamaara) >= 8 then 0 -- jos alkanut vasta myöh: 0
 else 1 -- muutoin kevät lk lähdekk:ssa (syksy on kohdekk:ssa!)
 end
 -- "normi", eri vuodet:
 else
 -- lk alku: 1.vuosi: pvm(kk) < 8 => 2 (ekan vuoden molemmat lk:t) muutoin 1
 ( case when DATEPART(month,oikeus_siirtopaivamaara) < 8 then 2
 else 1 -- syys lk joka tapauksessa (muista, eri vuodet)
 end)
 -- lk loppu: 2.vuosi: pvm(kk) >= 8 => 1 muutoin 0
 +(case when DATEPART(month,oikeus_alkamispaivamaara) >= 8 then 1 else 0 end)
 -- vuodet: (vuosien erotus - 1) x 2 (muista, eri vuodet tässä, eli ei tule negatiivista lukua; 0 saa tulla -1:n kanssa)
 +( (DATEPART(year,oikeus_alkamispaivamaara) - DATEPART(year,oikeus_siirtopaivamaara) - 1) * 2 )
 end
 -- miinus ne, jotka on ilmoitettu poissaoloksi
 - coalesce((select lkm from #poissaolo_maara_ennen where opiskeluoikeusid=T.opiskeluoikeus_id),0)
 else 0
 end
from #tktutk T
WHERE T.opiskeluoikeus_id NOT IN (SELECT OPL.OpiskeluoikeusID FROM Op_oik_siir_opisk_liittyva_lukukausi OPL )
-- 2017 muutos FF
;
Poissaololukukaudet (poissalk)

Käytettyjen poissaololukausien määrä tilastointivuoden loppuun, tai tutkinnon suorituspäivään, mennessä mukaan lukien siirto-opiskelijoiden käyttämät lukukaudet aiemmassa korkeakoulussa.

NB!: Koska siirto-opiskelijoihin liittyen tietovarannon tiedot sisältävät paikan vain yhdelle lukukausien läsnä- ja poissaolojen lukumäärälle, on tämä yksi paikka otettu vain poissaolojen käyttöön. Tässä kohdassa tuo tieto lasketaan yhteen tietoja lähettävässä korkeakoulussa käytettyihin poissaolojen lukumäärään.

NB2! Tehty poimintaan kaksi poimintamallia (ilmomalli; ks. yllä) 

SQL
-- poissalk :: Poissaololukukaudet
update T
set poissalk
    =coalesce(
        (select count(*)
         from Lukukausi_ilmoittautuminen l
         inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
         and lt.Koodi in ('2','3') --poissa, poissa ei kuluta
         where l.OpiskeluoikeusID=T.opiskeluoikeus_id
         and l.Paattymispaivamaara<=(select vuosi from #param)+'-12-31'
         -- ei sallita ennen opiskeluoikeutta olleita (siirto-opiskelija)
         and l.Paattymispaivamaara > T.oikeus_alkamispaivamaara
         -- "varmuuden vuoksi" ilmoittautumiset pois, eli tutkinnon suorittamisen jälkeen alkavat lk:t pois
         and l.Alkamispaivamaara < T.suorituspaivamaara
        )
    ,0)
    + coalesce((select lkm from #poissaolo_maara_ennen where opiskeluoikeusid=T.opiskeluoikeus_id),0)
from #tktutk T
WHERE   T.opiskeluoikeus_id NOT IN (SELECT OPL.OpiskeluoikeusID FROM  Op_oik_siir_opisk_liittyva_lukukausi OPL )
-- 2017 muutos FF

;


-- Katsotaan onko Op_oik_siir_opisk_liittyva_lukukausi taulussa poissa ja läsnä-olot siirto-opiskelijalle
-- 2017-01-12
 
UPDATE T  
SET lasnalk

    = COALESCE(
    
        (SELECT count(*)
         FROM Lukukausi_ilmoittautuminen l
             INNER join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
         AND lt.Koodi in ('1') --läsnä
         WHERE l.OpiskeluoikeusID=T.opiskeluoikeus_id
         AND l.Paattymispaivamaara<=(select vuosi from #param)+'-12-31'
         -- ei sallita ennen opiskeluoikeutta olleita (siirto-opiskelija)
         AND l.Paattymispaivamaara > T.oikeus_alkamispaivamaara
         -- "varmuuden vuoksi" ilmoittautumiset pois, eli tutkinnon suorittamisen jälkeen alkavat lk:t pois
         and l.Alkamispaivamaara < T.suorituspaivamaara
        )
    ,0)
    
    + COALESCE(
    
        (        SELECT OL.Maara 
                FROM Op_oik_siir_opisk_liittyva_lukukausi OL 
                    INNER JOIN Lukukausi_ilm_tila LT ON LT.id = OL.Lukukausi_ilm_tilaID 
                WHERE OL.OpiskeluoikeusID = T.opiskeluoikeus_id 
                AND LT.Koodi = '1' 
        )         
    ,0)            
        
FROM #tktutk T    
WHERE   T.opiskeluoikeus_id IN  (SELECT OPL.OpiskeluoikeusID FROM  Op_oik_siir_opisk_liittyva_lukukausi OPL  )


-- 2016 muutos poissaolo siirto-opiskelijat
-- 2017-01-12

UPDATE T
SET poissalk
    
        = COALESCE(
    
        (SELECT count(*)
         FROM Lukukausi_ilmoittautuminen l
             INNER join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
         AND lt.Koodi IN ('2','3') --Poissa
         WHERE l.OpiskeluoikeusID=T.opiskeluoikeus_id
         AND l.Paattymispaivamaara<=(select vuosi from #param)+'-12-31'
         -- ei sallita ennen opiskeluoikeutta olleita (siirto-opiskelija)
         AND l.Paattymispaivamaara > T.oikeus_alkamispaivamaara
          -- "varmuuden vuoksi" ilmoittautumiset pois, eli tutkinnon suorittamisen jälkeen alkavat lk:t pois
         and l.Alkamispaivamaara < T.suorituspaivamaara
         
        )
    ,0)        
    
    + COALESCE(
    
            (    SELECT OL.Maara 
                FROM Op_oik_siir_opisk_liittyva_lukukausi OL 
                    INNER JOIN Lukukausi_ilm_tila LT ON LT.id = OL.Lukukausi_ilm_tilaID 
                WHERE OL.OpiskeluoikeusID = T.opiskeluoikeus_id 
                AND LT.Koodi = '2' 
            ) 
    , 0 )        
FROM #tktutk T
WHERE   T.opiskeluoikeus_id  IN (    SELECT OPL.OpiskeluoikeusID  FROM  Op_oik_siir_opisk_liittyva_lukukausi OPL )


 
Puuttuvat lukukaudet (puuttuulk)

Puuttuvien lukukausien määrä (lukukausi ilmoittautumisen tila 4 lukumäärä, niistä korkeakouluista jotka sen ilmoittavat) tilastointivuoden loppuun mennessä.


update T
set puuttuulk
    =coalesce(
        (select count(*)
         from virta.dbo.Lukukausi_ilmoittautuminen l
         inner join virta.dbo.Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
         and lt.Koodi in ('4') -- puuttuu
         where l.OpiskeluoikeusID=T.opiskeluoikeus_id
         and l.Paattymispaivamaara<=(select vuosi from #param)+'-12-31'
         -- ei sallita ennen opiskeluoikeutta olleita (siirto-opiskelija)
         and l.Paattymispaivamaara > T.oikeus_alkamispaivamaara
        )
    ,0)
    
from #tktutk T


 
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.

SQL
-- 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
; 
Lastentarhanopettajan pätevyyteen tähtäävät opinnot (lastu) (opettaja)

Haetaan henkilön pätevyys -tiedon perusteella, kun se liittyy ko. tutkintoon. Pätevyys-tieto:

  • iq => 1=Opiskelijalle, joka suorittaa sosionomi (amk) -tutkintoa kuuluu aineyhdistelmään opintoja, jotka tähtäävät lastentarhanopettajan pätevyyteen
  • 12 => 2=Sairaanhoitajan pätevyys (tutkintokeruu)
  • ja => 3=Ammatillinen opettajankoulutus, pedagogiset opinnot
  • jc => 4=Ammatillinen opettajankoulutus, opinto-ohjaaja
  • jb => 5=Ammatillinen opettajankoulutus, erityisopettaja
  • vs => 6 = tutkintoon sisältyy varhaiskasvatukseen ja sosiaalipedagogiikkaan suuntautuneet vähintään 60 opintopisteen laajuiset opinnot (amk)
  • vo => 7 = erilliset varhaiskasvatuksen sosionomipätevyyteen tähtäävät opinnot (amk) 60 op

NB! Tyhjät jää (edelleen) tyhjäksi. Erityisesti arvo 2 ei tarkoita siis enää "ei" vaan sairaanhoitajaa.

NB! 2015-01-15: Pätevyydet pitää katsoa koko hierarkiasta! Tämä tehdään katsomalla, että pätevyys löytyy opintosuorituksesta jolla on sama opiskeluoikeus-liittyvyys kuin on tutkinto-opinnolla.

NB! 2016-02-05: Poikkeuspoiminta vuoden 2015 tutkintotiedonkeruussa: Katsotaan ammatillisen opettajankoulutuksen pätevyydet lisäksi opiskeluoikeuksien kautta.

SQL
-- lastu
--Sosionomi (amk) -tutkinnon suorittanut, jonka aineyhdistelmään kuuluu
--60 opintopisteen laajuiset varhaiskasvatuksen ja sosiaalipedagogiikan opinnot
--1=Kyllä, 2=Ei
-- NB! 2015-01-12: Tyhjät jää tyhjäksi tarkoittaen "ei". Arvo 2 saanut uuden merkityksen!
-- NB! 2015-01-15: Pätevyydet pitää katsoa koko hierarkiasta!
-- NB! 2015-09-22: Korjattu vanha lastentarhaopettaja pätevyyskoodi 2=> iq
update T
set lastu='1'
from #tktutk T
inner join Opintosuoritus s on s.OpiskeluoikeusID = T.opiskeluoikeus_id
inner join Henkilon_patevyys hp on hp.OpintosuoritusID=s.ID
inner join Patevyys p on p.ID=hp.PatevyysID
where 1=1
and p.Koodi in ('iq') --lastentarhanopettaja
and lastu is null
;
-- Tuplasairaanhoitajat
-- 2015-01-12: Viranomaistiedonkeruuryhmässä sovittiin, että laitetaan pätevyyskoodi 12 lastu-muuttujaan arvoksi 2
--  Tämä on siis nk. tuplasairaanhoitajien se toinen pätevyystieto!
update T
set lastu='2'
from #tktutk T
inner join Opintosuoritus s on s.OpiskeluoikeusID = T.opiskeluoikeus_id
inner join Henkilon_patevyys hp on hp.OpintosuoritusID=s.ID
inner join Patevyys p on p.ID=hp.PatevyysID
where 1=1
and p.Koodi in ('12') --sairaanhoitaja
-- ei jyrätä (ei pitäisi ollakaan samoissa tutkinnoissa)
and lastu is null
--TIE-90: Sairaanhoitajan tutkintoon ei saa liittyä sairaanhoitajan pätevyyttä
and s.TutkintonimikeID not in (select tu.ID from virta.dbo.Tutkintonimike tu where tu.Koodi='671101')
;
-- lastu :: Ammatillinen opettajankoulutus, pedagogiset opinnot Pätevyyteen tähtäävät opinnot
update T
set lastu='3'
from #tktutk T
inner join Opintosuoritus s on s.OpiskeluoikeusID = T.opiskeluoikeus_id
inner join Henkilon_patevyys hp on hp.OpintosuoritusID=s.ID
inner join Patevyys p on p.ID=hp.PatevyysID
where p.Koodi='ja'
-- ei jyrätä (ei pitäisi ollakaan samoissa tutkinnoissa)
and lastu is null
;
-- lastu :: Ammatillinen opettajankoulutus, opinto-ohjaaja Pätevyyteen tähtäävät opinnot
update T
set lastu='4'
from #tktutk T
inner join Opintosuoritus s on s.OpiskeluoikeusID = T.opiskeluoikeus_id
inner join Henkilon_patevyys hp on hp.OpintosuoritusID=s.ID
inner join Patevyys p on p.ID=hp.PatevyysID
where p.Koodi='jc'
-- ei jyrätä (ei pitäisi ollakaan samoissa tutkinnoissa)
and lastu is null
;
-- lastu :: Ammatillinen opettajankoulutus, erityisopettaja Pätevyyteen tähtäävät opinnot
update T
set lastu='5'
from #tktutk T
inner join Opintosuoritus s on s.OpiskeluoikeusID = T.opiskeluoikeus_id
inner join Henkilon_patevyys hp on hp.OpintosuoritusID=s.ID
inner join Patevyys p on p.ID=hp.PatevyysID
where p.Koodi='jb'
-- ei jyrätä (ei pitäisi ollakaan samoissa tutkinnoissa)
and lastu is null
;


-- NB! AMK-OPE poikkeus vuoden 2015 tutkinnoille.
--     Poimitaan saavutetut pätevyystiedot opiskeluoikeudesta liittyen ammatilliseen opettajankoulutukseen
-- lastu :: Ammatillinen opettajankoulutus, pedagogiset opinnot Pätevyyteen tähtäävät opinnot
update T
set lastu='3'
from #tktutk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID = T.opiskeluoikeus_id
inner join Opiskeluoikeusjakson_patevyys jp on jp.OpiskeluoikeusjaksoID = oj.ID
inner join Patevyys p on p.ID=jp.PatevyysID
where p.Koodi='ja'
-- ei jyrätä (ei pitäisi ollakaan samoissa tutkinnoissa)
and lastu is null
;
-- lastu :: Ammatillinen opettajankoulutus, opinto-ohjaaja Pätevyyteen tähtäävät opinnot
update T
set lastu='4'
from #tktutk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID = T.opiskeluoikeus_id
inner join Opiskeluoikeusjakson_patevyys jp on jp.OpiskeluoikeusjaksoID = oj.ID
inner join Patevyys p on p.ID=jp.PatevyysID
where p.Koodi='jc'
-- ei jyrätä (ei pitäisi ollakaan samoissa tutkinnoissa)
and lastu is null
;
-- lastu :: Ammatillinen opettajankoulutus, erityisopettaja Pätevyyteen tähtäävät opinnot
update T
set lastu='5'
from #tktutk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID = T.opiskeluoikeus_id
inner join Opiskeluoikeusjakson_patevyys jp on jp.OpiskeluoikeusjaksoID = oj.ID
inner join Patevyys p on p.ID=jp.PatevyysID
where p.Koodi='jb'
-- ei jyrätä (ei pitäisi ollakaan samoissa tutkinnoissa)
and lastu is null
;
-- / 2015 poikkeus amk-ope

--vs ja vo lastu lisätty 20.1.2022
update T
set lastu='6'
from #tktutk T
inner join virta.dbo.Opintosuoritus s on s.OpiskeluoikeusID = T.opiskeluoikeus_id
inner join virta.dbo.Henkilon_patevyys hp on hp.OpintosuoritusID=s.ID
inner join virta.dbo.Patevyys p on p.ID=hp.PatevyysID
where p.Koodi='vs'
and lastu is null
;

update T
set lastu='7'
from #tktutk T
inner join virta.dbo.Opintosuoritus s on s.OpiskeluoikeusID = T.opiskeluoikeus_id
inner join virta.dbo.Henkilon_patevyys hp on hp.OpintosuoritusID=s.ID
inner join virta.dbo.Patevyys p on p.ID=hp.PatevyysID
where p.Koodi='vo'
and lastu is null
;

Rahoituslähde (rahlahde)

Haetaan rahoituslähde-tieto sellaisenaan tiedetyn opiskeluoikeuden opiskeluoikeusjaksosta.

SQL
-- rahlahde :: Rahoituslähde
update T
set rahlahde=k.Koodi
from #tktutk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Rahoituslahde k on k.ID=oj.RahoituslahdeID
;

 

Maksullisen tilauskoulutuksen sijaintimaa (tilmaa)

Tietovarannossa ei ko. tietoa ole. 

Koulutuskoodi (koulk)

Haetaan tieto suoraan kullakin rivillä tunnetulle opintosuoritukselle.

SQL
-- koulk
update T
set koulk=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 1=1
-- jos ei jo ole
and koulk is null
;



 

Opintopisteet

Oma temp-taulu, joka nopeuttaa oleellisesti kyselyitä. Viedään temp-tauluun kaikki lehtitason muu opintosuoritus -lajiset opintosuoritukset, jotka liittyvät perusjoukkoon valittuun suoritettuun tutkintoon.

 

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

 

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

Suoritettavien opintojen laajuus (oplaaj) (laajuus)
  • Haetaan tutknto-opintosuoritukseen liittyvästä opiskeluoikeudesta tutkinnon laajuus, josta vähennetään tutkintoon liitettyjen ennen opiskeluoikeuden alkamista ja mahdollista siirtopäivää suoritettujen hyväksiluettujen opintosuoritusten opintopisteet.
  • Suoritettavien opintojen laajuus -tiedon mennessä negatiiviseksi, arvo muutetaan nollaksi (0).
Omassa ammattikorkeakoulussa suoritetut opintopisteet (opoamk)
  • Mikäli suoritusta ei luokitella muihin kategorioihin, tulee suorituksen opintopisteet tähän kategoriaan. Eli tämä on ns. oletuskategoria.
Muissa ammattikorkeakouluissa suoritetut opintopisteet (opmamk)
  • Mikäli suorituksen järjestävä-roolisista (tai hyväksiluetun opintosuorituksen tapauksessa lähde-roolin) organisaatioista löytyy oppilaitostyypiltään 41='Ammattikorkeakoulu' oleva organisaatio, joka ei ole sama kuin kenen tietoja nyt käsitellään, lasketaan opintopisteet tähän kategoriaan.
Yliopistoissa suoritetut opintopisteet (opylio)
  • Mikäli suorituksen järjestävä-roolisista (tai hyväksiluetun opintosuorituksen tapauksessa lähde-roolin) organisaatioista löytyy oppilaitostyypiltään 42='Yliopisto' tai 43='Sotilaskorkeakoulut' oleva organisaatio, joka ei ole sama kuin kenen tietoja nyt käsitellään, lasketaan opintopisteet tähän kategoriaan.
Yleissivistävissä ja ammatillisissa oppilaitoksissa suoritetut opintopisteet (opylei)
  • Mikäli suorituksen järjestävä-roolisista (tai hyväksiluetun opintosuorituksen tapauksessa lähde-roolin) organisaatioista löytyy oppilaitostyypiltään (63,64,66,99,Muu) oleva organisaatio, joka ei ole sama kuin kenen tietoja nyt käsitellään, lasketaan opintopisteet tähän kategoriaan.
  • 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' tai Muu='Pienet oppilaitokset ja koulutuksenjärjestäjät (alle 5 opiskelijaa)',
      ja
    • organisaatio ei ole sama kuin minkä tahansa korkeakoulun tiedot tietovarannossa (varmuuden vuoksi tarkistus)
      ja 
    • organisaatio itsessään ei ole 41='Ammattikorkeakoulu', 42='Yliopisto', 43='Sotilaskorkeakoulut', UK='Ulkomainen korkeakoulu' UM='Ulkomainen muu oppilaitos' tai XX='Muodollisen koulutuksen ulkopuolella',
      ja
    • organisaatio ei ole sama kuin kenen tietoja nyt käsitellään
    lasketaan opintopisteet tähän kategoriaan.
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.
Muualla suoritetut opintopisteet (opmuu)
  • Mikäli suorituksen järjestävä-roolisista (tai hyväksiluetun opintosuorituksen tapauksessa lähde-roolin) organisaatioista löytyy organisaatio XX='Muodollisen koulutuksen ulkopuolella', joka ei ole sama kuin kenen tietoja nyt käsitellään, lasketaan opintopisteet tähän kategoriaan.
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 "tiedoksi", koska sama tieto lisättiin myös yliopisto-puolelle.
  • Tieto on suoraan tutkinto-opintosuoritukselle (laji=1; eli tiedonkeruun perusjoukon rivin tieto) tallennettu laajuus-tieto.
SQL
-- 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,
	oamk bit,
	mamk bit,
	ylio bit,
	ylei 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,oamk,mamk,ylio,ylei,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 amk: käytetään halpaa logiikkaa, eli yhteensä miinus muut. tässä null kunnes..!
	, null as oamk
	--,(case when s.ID NOT IN (
	--	select sg.OpintosuoritusID
	--	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ä
	--	and g.Koodi<>(select kk from #param)
	--	)
	--  then 1 else 0 end
	-- ) as oamk
	--muu 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)
	  )
	  and(g.Koodi in (
			SELECT Organisaatio_koodi FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi
			WHERE Oppilaitostyyppi_koodi='41'
		)
	   or g.Koodi='41'
	  )
	  -- ei oma korkeakoulu
	  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 mamk
	--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)
	  )
	  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 korkeakoulu
	  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 ylio
	--ylei
	,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)
	  )
	  --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')
	  )
	  -- 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','XX')
	  --and g.Koodi in (select Koodi from Organisaatio g2 where len(Koodi)=5 or Koodi in('63','64','66','99','Muu') )
	  -- ei oma korkeakoulu
	  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 ylei
	--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)
	  )
	  --ei tarvita: and g.Koodi not in (SELECT Organisaatio_koodi FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi)
	  and g.Koodi in ('UK','UM')
	  -- ei oma korkeakoulu
	  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)
	  )
	  and g.Koodi in ('XX')
	  -- ei oma korkeakoulu
	  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 and sl.Koodi='2' --muu opintosuoritus
	-- HYVÄKSILUETTU
	left join Opsuorituksen_hyvaksiluku shl on shl.OpintosuoritusID=s.ID
	cross join #param -- saa olla vain yksi rivi!
	where 1=1
	-- 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')
		)
	)
	
;
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

-- laajuus :: Suoritettavien opintojen laajuus
--  Tilastokeskuksen ohjeen mukaanhan:
--  Suoritettavien opintojen laajuus opintopisteinä (=tutkinnon laajuudesta vähennetty aiemmin suoritetut hyväksilukujen laajuudet)
update T
set laajuus=cast(
	cast(o.Laajuus as int)
	-
	coalesce(
	(
		select cast(cast(round(sum(
			case
			when
			 s.suoritus_pvm <= o.Alkamispaivamaara and s.suoritus_pvm <= coalesce(o.Siirtopaivamaara,'9999-01-01')
			and s.hyvaksiluettu_pvm <= #param.vuosi+'-12-31' --#param.tilpvm
			then s.laajuus else 0 end),0) as int) as varchar)
		from #pisteet s
		inner join Opiskeluoikeus o on o.ID=s.opiskeluoikeus_id --voi tehdä amkeilla
		cross join #param -- saa olla vain yksi rivi!
		where s.opiskelija_id=T.opiskelija_id
		-- HYVÄKSILUETTU
		and s.hyvaksiluettu_pvm is not null
		and s.opiskeluoikeus_id=T.opiskeluoikeus_id
		group by s.opiskelija_id
	)
	,0)
 as varchar)
--select distinct o.Laajuus
from #tktutk T
inner join Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
where o.Laajuus is not null
;
-- NB! 20150129/LJ: Negatiiviset suoritettavien opintojen laajuus -tiedot nollaksi!
update T
set laajuus=cast(0 as varchar)
from #tktutk T
where laajuus is not null and cast(laajuus as int) < 0
;

---
-- Opinnot, suoritukset, opintopisteet
---
--

-- VIRTA-625: Lisätään tutkinto-opintosuorituksen opintopisteet mukaan tiedonkeruuseen.
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
;
-- kategoriat ja yhteensä (summa lehtitasolta)
update T
set opoamk=(
  select cast(cast(round(sum(
	--case when s.oamk = 1 then s.Laajuus else 0 end),0) as int) as varchar)
	case when s.mamk=0 and s.ylio=0 and s.ylei=0 and s.ulk=0 and s.muu=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
)
, opmamk=(
  select cast(cast(round(sum(
	case when s.mamk = 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
)
, opylio=(
  select cast(cast(round(sum(
	case when s.ylio = 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
)
, opylei=(
  select cast(cast(round(sum(
	case when s.ylei = 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
)
, opulk=(
  select cast(cast(round(sum(
	case when s.ulk = 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
)
, opmuu=(
  select cast(cast(round(sum(
	case when s.muu = 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
)
, 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 opyht is null
;
Kansainvälinen tutkinto (kvtutk)

Poimitaan opintosuorituksen luokittelu 9 tai 10 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
Kansallinen oppijanumero (oppijanumero)

Poimitaan mukaan kansallinenopid jos löytyy.

Tulos

SQL
select
	coalesce(tunn,'')    as "Oppilaitostunnus"
	,coalesce(koultyp,'')   "Koulutustyyppi"
	,coalesce(kiel,'')      "Koulutuksen opetuskieli" --kkieli
	,coalesce(kkun,'')      "Opiskelukunta" --kkun
	,coalesce(ht,'')        "Henkilötunnus" --ht
	,coalesce(snimi,'')     "Sukunimi" --snimi
	,coalesce(enimi,'')     "Etunimet" --enimi
	,coalesce(sp,'')        "Sukupuoli" --sp
	,coalesce(aikieli,'')   "Äidinkieli" --aikieli
	,coalesce(askunta,'')   "Opiskelijan vakinainen asuinkunta" --askun
	,coalesce(kansal,'')    "Opiskelijan kansalaisuus" --kansal
	,coalesce(kirtupv,'')   "Ko. tutkintoon kirjoihintuloajankohta ko. ammattikorkeakoulussa" --kirtupv
	,coalesce(suorpvm,'')   "Suorituspäivämäärä"
	,coalesce(opoamk,'')    "Omassa ammattikorkeakoulussa suoritetut opintopisteet"
	,coalesce(opmamk,'')    "Muissa ammattikorkeakouluissa suoritetut opintopisteet"
	,coalesce(opylio,'')    "Yliopistoissa suoritetut opintopisteet"
	,coalesce(opylei,'')    "Yleissivistävissä ja ammatillisissa oppilaitoksissa suoritetut opintopisteet"
	,coalesce(opulk,'')     "Ulkomailla suoritetut opintopisteet"
	,coalesce(opmuu,'')     "Muualla suoritetut opintopisteet"
	,coalesce(opyht,'')     "Opintopisteet yhteensä"
	,coalesce(lasnalk,'')   "Läsnäololukukaudet" --lasnalk
	,coalesce(poissalk,'')  "Poissaololukukaudet" --poissalk
	,coalesce(laajuus,'')   "Suoritettavien opintojen laajuus" --oplaaj
	,coalesce(kirtu1pv,'')  "Ensimmäinen kirjoihintuloajankohta ko. tutkintoon" --kirtu1pv
	,coalesce(lastu,'')     "Lastentarhanopettajan pätevyyteen tähtäävät opinnot" --opettaja
	,coalesce(rahlahde,'')  "Rahoituslähde"
	,coalesce(tilmaa,'')    "Maksullisen koulutuksen sijaintimaa"
	,coalesce(koulk,'')     "Koulutuskoodi"
	-- VIRTA-625: tutkinto-opintosuorituksen opintopisteet
	,coalesce(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 1=1
order by ht,suorpvm,koulk
;

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
---
/*
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),
  --pois: avain varchar(12),
  --pois: avain2 varchar(12),
  --pois: tutk
  --pois: ko
  --pois: sv
  --koultyp
  koultyp1 varchar(1),
  koultyp2 varchar(8),
  koultyp3 varchar(8),
  srhtup varchar(15),
  kkieli varchar(2),
  --pois: kkun varchar(3),
  kkun2 varchar(3),
  --ht varchar(11),
  ht1 varchar(11),
  ht2 varchar(11),
  ht3 varchar(11),
  ht4 varchar(11),
  aikieli varchar(2),
  askun varchar(3),
  kansal varchar(3),
  --kirtupv
  --pois: kirtupv1 varchar(8),
  kirtupv2 varchar(8),
  suoraik varchar(8),
  --tu
  tu1 varchar(1),
  tu2 varchar(1),
  --op
  op1 varchar(4),
  op2 varchar(4),
  op3 varchar(4),
  op4 varchar(4),
  op5 varchar(4),
  op6 varchar(4),
  op7 varchar(4),
  --lasnalk
  lasnalk varchar(10),
  --oplaaj
  oplaaj1 varchar(4),
  --pois: oplaaj2 varchar(4),
  kirtu1pv varchar(8),
  kirtu1p2 varchar(8),
  --opettaja
  opett1 varchar(2),
  opett2 varchar(8),
  rahlahde varchar(1),
  
  koulk_avain varchar(20),--Koulutuskoodi
  --pois: rahlahd4 varchar(1),
  --pois: tilmaa varchar(3),
  --
  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 tunn not in (select koodi from virta..Organisaatio) then tunn else '' end)
--,koultyp1 = (case when koultyp not in ('1','2','3','5','6') then koultyp else '' end)
,koultyp1 = (case when koultyp not in ('1','2','3','5','6','7') 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)
,srhtup = (case
  when koulk='671101' then --sairaanhoitaja (amk)
    case when exists (
    select 1 from #tktutk t2 where t2.opiskelijaavain=#tktutk.opiskelijaavain AND t2.tunn = #tktutk.tunn 
    and koulk in ('671106','671116','671103') --kätilö (amk), ensihoitaja (amk), terveydenhoitaja (amk)
  ) then '1' else '' end
  when koulk='771201' then --sairaanhoitaja (ylempi amk)
    case when exists (
    select 1 from #tktutk t2 where t2.opiskelijaavain=#tktutk.opiskelijaavain AND t2.tunn = #tktutk.tunn 
    and koulk in ('771206','771216','771203') --kätilö (ylempi amk), ensihoitaja (ylempi amk), terveydenhoitaja (ylempi amk)
  ) then '1' else '' end
  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?
  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 aikieli 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'))
  then aikieli
  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)
--,kirtupv1 --koulutusohjelmapäätös?
,kirtupv2 = (case
  when cast(substring(kirtupv,1,4) as int) > (select year(tilpvm) from #param) then kirtupv
  when cast(substring(kirtupv,5,2) as int) not between 1 and 12 then kirtupv
  when cast(substring(kirtupv,7,2) as int) not between 1 and 31 then kirtupv
  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.tunn = #tktutk.tunn
    group by tunn,koultyp,kiel,kkun,ht,snimi,enimi,sp,aikieli,askunta,kansal,kirtupv,suorpvm,
    opoamk,opmamk,opylio,opylei,opulk,opmuu,opyht,lasnalk,poissalk,laajuus,kirtu1pv,lastu,
    rahlahde,koulk,optutk
    having count(*)>1
  ) then '1'
  else '' end)
,tu2 = (case
  when exists (
    select 1 from #tktutk t2 where t2.opiskelijaavain=#tktutk.opiskelijaavain and t2.tunn = #tktutk.tunn
    group by tunn, koulk, suorpvm, ht, snimi, enimi, sp
    having count(*)>1
  ) then '1'
  else '' end)
,op1 = (case when cast(opoamk as int)>200 then opoamk else '' end)
,op2 = (case when cast(opmamk as int)>200 then opmamk else '' end)
,op3 = (case when cast(opylio as int)>200 then opylio else '' end)
,op4 = (case when cast(opylei as int)>200 then opylei else '' end)
,op5 = (case when cast(opulk as int)>200 then opulk else '' end)
,op6 = (case when cast(opmuu as int)>200 then opmuu else '' end)
,op7 = (case when cast(opyht as int)>400 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)
--,oplaaj2 --opm:n päätös?
,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)
,opett2 = (case when lastu in ('1') and koulk not in ('671201','771301') then koulk+'+'+lastu else '' end)
,rahlahde = (case when rahlahde not in ('1','2','3','4','5') then rahlahde else '' end)

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

--,rahlahd4 = --tilmaa tietoa ei enää ole
--
,opiskelijaavain
,opintosuoritusavain
,opiskeluoikeusavain
,opiskelija_id
,opintosuoritus_id
,opiskeluoikeus_id
,db = (select DB from Yhteiset.Asetukset.Instanssit where OrganisaatioKoodi=tunn)
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
delete from TK_tarkistus where tiedonkeruu='tk_tutk_amk';
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_amk' from #tarkistus where ''<>tunn
union select 'koultyp1', 'koulutustyyppi on virheellinen; muuttujan arvo ei ole 1, 2, 3, 5, 6 eikä 7',koultyp1,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_amk' 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_amk' 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_amk' from #tarkistus where ''<>koultyp3
union select 'srhtup', 'ilmoitus sairaanhoitajista, joilla myös kätilön, ensihoitajan tai terveydenhoitajan tutkinto (tuplasairaanhoitajat)',srhtup,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_amk' from #tarkistus where ''<>srhtup
union select 'kkieli', 'koulutuksen opetuskieli -koodiarvo on virheellinen',kkieli,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_amk' from #tarkistus where ''<>kkieli
union select 'kkun2', 'kunta-koodiarvo on virheellinen ( koulutustyyppi 2, 3 ja 5 )',kkun2,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_amk' 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_amk' 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_amk' from #tarkistus where ''<>ht2
union select 'ht3', 'sukupuolitieto virheellinen',ht3,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_amk' 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_amk' from #tarkistus where ''<>ht4
union select 'aikieli', 'äidinkielitieto virheellinen',aikieli,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_amk' from #tarkistus where ''<>aikieli
union select 'askun', 'asuinkuntatieto virheellinen',askun,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_amk' from #tarkistus where ''<>askun
union select 'kansal', 'kansalaisuustieto virheellinen',kansal,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_amk' 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_amk' from #tarkistus where ''<>kirtupv2
union select 'suoraik', 'tutkinnon suoritusajankohta on virheellinen',suoraik,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_amk' 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_amk' 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_amk' 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_amk' 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_amk' 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_amk' 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_amk' 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_amk' 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_amk' 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_amk' from #tarkistus where ''<>op7
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_amk' 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_amk' 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_amk' 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_amk' 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_amk' 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_amk' 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_amk' from #tarkistus where ''<>rahlahde
union select 'koulk_avain', 'Tutkintokoodi on virheellinen tai puuttuu',koulk_avain,opiskelijaavain,opiskeluoikeusavain,opintosuoritusavain,opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,db,'tk_tutk_amk' from #tarkistus where ''<>koulk_avain
;
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ää.

14.12.2018 puuttuvien lukukausi tietojen sarakkeen lisäys :: wiki-versio 35

  • Tiedostoon lisätty puuttuvat lukukaudet sarake, johon tuodaan lukukausi ilmoittautumisen tilan 4 lukumäärä niiltä korkeakouluilta jonka sen tuovat

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

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

20.1.2017 :: Ohjauksen ala ei-tutkintoonjohtaville, Siirto-opiskelijan läsnä-poissaolot, erikoistumiskoulutuskoodi :: wikiversio 34
  • Ohjausala sarake (1-2), 999999 koodeille (koulutustyyppiä 3=Erikoistumisopinnot, 5=Ammatillinen opettajankoulutus, 7=Erikoistumiskoulutus). Siirto-opiskelijoiden läsnä-poissaolot uuden tietomallin mukaan poimittua. Erikoistumiskoulutksen koulutuskoodi jos on.
4.9.2016 :: Tarkistusta korjattu :: wikiversio 33
  • Korjattu koultyp1 tarkistusta että tunnistaa myös uuden koodin 7=Erikoistumiskoulutukset (Virta koodi 19=Erikoistumiskoulutukset)
5.2.2016 :: Ammatillinen opettajankoulutus opiskeluoikeuksista :: wikiversio 32
  • Lisätty poimintaan poikkeus vuoden 2015 tiedonkeruuseen: Katsotaan nk. amk-ope -tiedot opiskeluoikeuksista
5.2.2016 :: Lukukausi-ilmoittautumisten poislukeminen tutkinnon suorittamisen jälkeen :: wikiversio 31
  • Lisätty ehto läsnä- ja poissaolojen lukumäärien laskemiseen, että tutkinnon suorittamisen jälkeen alkaneet lukukaudet eivät tule mukaan. Poistetaan toisin sanoen ns. "varmuuden vuoksi" ilmoittautumiset pois
29.1.2016 :: Opettajapätevyys koodiarvot :: wikiversio 30
  • Korjattu opettajapätevyyksien koodiarvot poiminnassa
22.1.2016 :: Erikoistumiskoulutus -tyyppiset päättyneet opiskeluoikeudet :: wikiversio 29
  • Poimitaan koulutuskoodilla 999999 mukaan myös erikoistumiskoulutukset
  • Lisäksi lisätty opettajapätevyyksiä
15.1.2016 :: Lukukausien lukumäärän laskennan kaksi poimintamallia :: wikiversio 27
  • Lisätty lukukausien laskentaan kaksi poimintamallia "välitila" ja "toinenkk".
22.9.2015 :: Lastentarhaopettajan pätevyystieto :: wiki-versio 21
  • Korjattu pätevyyskoodi ehtoa, vanha Virta pätevyyskoodi 2 => "iq"
11.2.2015 :: Erikoistumisopinnot ja ammatillinen opettajakoulutus päättymistieto :: wiki-versio 20
  • Korjattu opiskeluoikeuden tilan 'päättynyt' alkamishetken tarkastelu. Nyt myös 1.1.(tilastovuosi+1) käy.
29.1.2015 :: Suoritettavien opintojen laajuus -tieto: negatiiviset arvot nollaksi :: wiki-versio 19
26.1.2015 :: Opintosuoritusten lehtitason määrityksen korjaus :: wiki-versio 17
  • Muutettu vain opintosuoritusten opintopistelaskennassa nk. lehtitaso-tarkastelu olemaan ottamatta huomioon opintosuorituksen lajien 3 ja 4 opintosuorituksia.
15.1.2015 :: Pätevyydet katsotaan koko hierarkiasta
12.1.2015 :: Tutkinto-opintosuorituksen opintopisteet :: wiki-versio 15
  • Lisätty lastentarhanopettaja-tietoon (lastu) arvo 2=sairaanhoitaja, jolla voidaan hoitaa nk. tuplasairaanhoitaja-tapaukset.
  • Lisätty uusi sarake "Tutkinnon opintopisteet", jonka sisällöksi tuodaan juuri tutkinto-opintoon (laji=1) tallennettu laajuustieto.
19.12.2014 :: Opintopisteiden jaottelussa huomioitu oppilaitostyyppi sotilaskorkeakoulu :: wiki-versio 14
15.12.2014 :: Parannettu opintopisteiden jakautumisen poimintaa :: wiki-versio 13
  • Tuotu kattavampi aputaulu eri organisaatioiden (oppilaitosten) oppilaitostyypeistä mukaan
  • Tarkasteltu opintopisteiden jakautumisten kategoriointia paremmaksi
8.12.2014 :: Korjattu tiedonkeruun poimintaa :: wiki-versio 10
  • Lisätty ammatillisen opettajakoulutuksen suorittaneet
  • Korjattu opiskeluoikeuden valinta
  • Täydennetty dokumentaatiota
17.11.2014 :: Automaatin aika :: wiki-versio 8


  • No labels

1 Comment

  1. Lauri Jokipii

    Tiedonkeruun katselmointi 2.12.2014:

    Perusjoukon valinta

    • Lisätään joukkoon: Ammatillinen opettajakoulutus
    • Tuplatutkinnot (sairaanhoitaja-terveydenhoitaja, sairaanhoitaja-kätilö yms)
      • Saadaanko eroteltua tuplapätevyydet?
      • SUVA-koodit puuttuvat tiedonkeruusta, joten päättely tehtävä tiedonkäyttäjän puolella jotenkin toisin; viimekädessä kysyä korkeakoululta
      • Päivämäärät ovat tässä kriittisiä
        • Sairaanhoitajan tutkintoa ei saisi ilmoittaa ennen (eri aikaan) kuin terveydenhoitajan tutkinto
      • Kirjauskäytäntöihin mahdollisesti tehtävä muutoksia

    Erikoistumisopinnot

    • (ks. myös suva-koodit yllä)
    •  Opintoala2002
      • Korkeakoulut toimittavat erillisen CSV-tiedoston, jossa linkitetään opiskelija (hetu), suoritettu tutkinto (opintosuoritusavain) ja opintoala2002-arvo

    Ammatillinen opettajakoulutus

    • Tarkempi taso: tavalliset, pedagogiset erityisope, opo
      • Tarvitaan esim. erillinen CSV-tiedosto korkeakouluilta, jonka CSC edelleen toimittaa TK:lle