Page tree
Skip to end of metadata
Go to start of metadata

 

 

Poiminta

Alustus

Dokumentaation tueksi on tuotu SQL-kielisiä kyselyitä, joissa taustalla on temp-tietokantataulu #tkopisk 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 oleellinen tilastointipäivä (20.9.VUOSI) ja lisäksi on erikseen vielä vuosi-tieto kyselyitä yksinkertaistamaan.

SQL
-- Temp-taulu tietojen valintaa varten
-- NB! param-taulussa saa olla vain yksi rivi!
IF OBJECT_ID(''tempdb..#param'') IS NOT NULL
BEGIN
	truncate table #param
	drop table #param
	if @debug<>0 print convert(varchar,getdate(),120)+'' DROP temp-taulu param''
END
;
create table #param (
	vuosi varchar(4),
	org varchar(30),--KK-lyhenne (DB-nimi)
	kk varchar(5),--Oppilaitos
	tilpvm date,--Tilastointipäivä
)
if @debug<>0 print convert(varchar,getdate(),120)+'' CREATE temp-taulu param''
;
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

IF OBJECT_ID(''tempdb..#tkopisk'') IS NOT NULL
BEGIN
	truncate table #tkopisk
	drop table #tkopisk
	if @debug<>0 print convert(varchar,getdate(),120)+'' DROP temp-taulu''
END
;
create table #tkopisk (
	tunn varchar(5),--Oppilaitostunnus
	koultyp varchar(4),--Koulutustyyppi
	kiel varchar(2),--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
	olotamm varchar(1),--Kirjoillaolo ammattikorkeakoulussa 1.1.-31.7. (ilmoittautumistieto)
	olosyys varchar(1),--Kirjoillaolo ammattikorkeakoulussa 20.9. (ilmoittautumistieto)
	opes varchar(4),--Edellisenä syyslukukautena (1.8. -31.12.) suoritettujen opintopisteiden määrä
	opek varchar(4),--Edellisenä kevätlukukautena (1.1. -31.7.) suoritettujen opintopisteiden määrä
	opker varchar(4),--Edellisen lukuvuoden loppuun (31.7.) mennessä suoritettujen kaikkien opintopisteiden määrä
	lasnalk varchar(4),--Läsnäololukukaudet
	poissalk varchar(4),--Poissaololukukaudet
	laajuus varchar(4),--Suoritettavien opintojen laajuus
	kirtu1pv varchar(8),--Ensimmäinen kirjoihintuloajankohta ko. tutkintoon
	lastu varchar(1),--Lastentarhanopettajan pätevyyteen tähtäävät opinnot
	rahlahde varchar(1),--Rahoituslähde
	--tilmaa varchar(3),--Maksullisen tilauskoulutuksen sijaintimaa
	koulk varchar(6),--Koulutuskoodi
    ohjauksenAla      varchar(3), -- OKM Ohjauksen ala
    ekkoodi         varchar(3), -- Erikoistumiskoulutuksen koodi
    puuttuulk varchar(4),--Puuttuu tieto 4 lukukaudet
 --
	opiskelijaavain varchar(100),
	opiskeluoikeusavain varchar(100),
	opiskelija_id int,
	opiskeluoikeus_id int,
	-- avuksi
	oikeus_alkamispaivamaara date,
	oikeus_paattymispaivamaara date,
	oikeus_siirtopaivamaara date
)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelija_id ON #tkopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskeluoikeus_id ON #tkopisk (opiskeluoikeus_id)
if @debug<>0 print convert(varchar,getdate(),120)+'' CREATE temp-taulu''

 

Perusjoukon valinta

Poimitaan aputauluun perustiedot opiskelijoista (henkilöistä) ja opiskeluoikeuksista. Poimituksi tulee, mikäli:

  • on tilastointipäivänä aktiivinen opiskeluoikeus, joka
  • on tutkintoon johtava opiskeluoikeus (opiskeluoikeuden tyyppi 1-7) tai erikoistumisopinnot (tyyppi 12) tai ammatillinen opettajakoulutus (15), sekä
  • on läsnä- tai poissaolo lukukausi-ilmoittautuminen syksylle (ilmoittautumispäivä 20.9. mennessä) ja
    • katsotaan lisäksi, ettei mukana ole tutkinnon suorittaneita ajalla 1.8.-19.9.
  • siirto-opiskelijan tapauksessa siirto on tapahtunut ennen tilastointipäivää

Samalla kerätään tiedot, joille tehdään heti perään muutos tuntemattomaksi, mikäli puuttuu:

  • Korkeakoulu (tunn)
  • Henkilötunnus (ht)
  • Sukupuoli (sp)
  • Sukunimi (snimi)
  • Etunimet (enimi)
  • Kansalaisuus (kansaly) :: nb! ensin vain suomalaiset, mutta heti perään muut kuin ei suomalaiset siten, että jos monta, valinta on "satunnainen"
  • Äidinkieli (aikieli)
  • Asuinkunta (askunta)
NB! Enää ei ole mukana "OKM aloittaneet"
SQL
---
-- Lisätään perustiedot (henkilö, opiskelija, opiskeluoikeus)
-- * täytyy olla tutkinto-oikeus
-- * täytyy olla ilmoittautuminen syksylle (20.9. mennessä)
-- NB 2014-09-24: EI OKM ALOITTANEITA ENÄÄ MUKAAN
--  \TO DONE: opiskeluoikeuden tila. tutkinnon suorittaneet 1.8.-20.9. EI MUKAAN
--            ja jopa karsitaan pois ne, joilla opiskeluoikeuden tila on päivittämättä.
---
-- kaikki 20.9. läsnä- tai poissaoleviksi ilmoittautuneet
insert into #tkopisk (tunn,ht,sp,snimi,enimi,kansal,aikieli,askunta,opiskelijaavain,opiskelija_id,opiskeluoikeus_id,opiskeluoikeusavain,oikeus_alkamispaivamaara,oikeus_paattymispaivamaara,oikeus_siirtopaivamaara)
select
 (select top 1 kk from #param) as tunn
,h.Henkilotunnus ht
,(select top 1 k.Koodi from Sukupuoli k where k.ID=h.SukupuoliID) sp
,h.Sukunimi snimi
,h.Etunimet enimi
--nb! vain suomalaiset tässä
,(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
 ) kansal
,(select top 1 k.Koodi from Kieli k where k.ID=h.KieliID) aikieli
,(select top 1 k.Koodi from Kunta k where k.ID=h.KuntaID) askunta
,p.Avain opiskelijaavain
,p.ID opiskelija_id
--
,o.id opiskeluoikeus_id
,o.avain opiskeluoikeusavain
--
,o.Alkamispaivamaara
,coalesce(o.Paattymispaivamaara,''9999-01-01'')
,o.Siirtopaivamaara
from Henkilo h
inner join dbo.Opiskelija p on p.HenkiloID=h.ID
inner join Opiskeluoikeus o on o.OpiskelijaID=p.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
-- tutkinto-tyyppinen, erikoistumisopinnot, ammatillinen opettajakoulutus ja Erikoistumiskoulutus
and ot.Koodi in (''1'',''3'',''12'',''15'',''19'')
-- voimassa tilpvm
and (select tilpvm from #param) between o.Alkamispaivamaara and coalesce(o.Paattymispaivamaara,''9999-01-01'')
-- tila
and kt.Koodi = ''1'' --aktiivinen
and (select tilpvm from #param) between lt.Alkamispaivamaara and coalesce(lt.Paattymispaivamaara,''9999-01-01'')
-- EI tutkinnon suorittaneita mukaan, vaikka opiskeluoikeuden tila olisikin jäänyt päivittämättä
-- (tämän opiskeluoikeuden)
and o.ID NOT IN (
	select s.OpiskeluoikeusID
	from Opintosuoritus s
	inner join Tutkintonimike sk on sk.ID=s.TutkintonimikeID
	-- liitetään mukaan myös opiskeluoikeusjakso, jotta tiedetään kyseessä olevan SAMA tutkinto
	inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=s.OpiskeluoikeusID
	inner join Tutkintonimike ok on ok.ID=oj.TutkintonimikeID
	where s.OpiskelijaID=p.ID
	and s.Opintosuorituksen_lajiID in (select id from Opintosuorituksen_laji where Koodi=''1'')--tutkinto
	and s.Suorituspaivamaara between (select vuosi from #param)+''-08-01'' and (select vuosi from #param)+''-09-19''
	--sama tutkinto
	and sk.ID=ok.ID
)
-- ilmo / opiskeluoikeus
and o.ID in (
	select OpiskeluoikeusID
	from Lukukausi_ilmoittautuminen l
	inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
	where lt.Koodi in (''1'',''2'',''3'') --läsnä, poissa, poissa ei kuluta
	and l.Ilmoittautumispaivamaara <= (select tilpvm from #param)
	and (select tilpvm from #param) between l.Alkamispaivamaara and coalesce(l.Paattymispaivamaara,''9999-01-01'')
)
-- siirto-opiskelija / siirto tapahtunut viimeistään tilastointipäivänä (puuttuminen siis sallitaan!)
and (select tilpvm from #param) >= coalesce(o.Siirtopaivamaara,''1900-01-01'')
-- varmuuden vuoksi; ei tuplata rivejä
and o.ID not in (select opiskeluoikeus_id from #tkopisk)
;
 
-- : kansal
-- muut kuin suomi
update T
set kansal=(
  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 #tkopisk T
where kansal is null
;
-- tuntemattomia
-- : kansal
update T
set kansal=''999''
from #tkopisk T
where kansal is null
;
-- : askunta
update T
set askunta=''999''
from #tkopisk T
where askunta is null
;
-- : aikieli
update T
set aikieli=''99''
from #tkopisk 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/vuoden 2015 uusilla opiskelijoilla monimuotototeutus
    • muuten koulutustyyppi saa arvon 1=Amk-tutkintoon johtava nuorten koulutus/vuoden 2015 uusilla opiskelijoilla päivätoteutus
    • Opiskeluoikeusluokittelun koodin 3 mukaan saa arvon 2=Amk-tutkintoon
  • 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=Erikoistumiskoulutukset, koulutustyyppi saa arvon 7=Erikoistumiskoulutukset (uudet 2015 alkavat koulutukset)
     
Erikoistumiskoulutus

ja muu ei-tutkintoon johtavien ( 999999) koulutukselle ohjauksenala-tieto ( OKM koodit 1-12)

 


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 ''2''--	Alempi korkeakoulututkinto
	when ''3''--	Ylempi ammattikorkeakoulututkinto
	 then ''6''--=Ylempi ammattikorkeakoulututkinto
	when ''12''--	Erikoistumisopinnot
	 then ''3''--=Erikoistumisopinnot
	when ''15''--	Ammatillinen opettajankoulutus
	 then ''5''--=Ammatillinen opettajankoulutus
	when ''19'' 
	 then ''7''--=Erikoistumiskoulutus


 end
)
--select distinct ot.Koodi
from #tkopisk T
inner join Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
inner join Opiskeluoikeuden_tyyppi ot on ot.ID=o.Op_oikeuden_tyyppiID
;

/* Tähän  lisäys uuden  koodi 3 Aikuiskoulutus luokittelun mukaan */



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

FROM #tkopisk 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 #tkopisk 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)

Haetaan opetuskieli-tieto tiedetyn opiskeluoikeuden opiskeluoikeusjaksosta.

SQL
-- kiel :: Opetuskieli
update T
set kiel=k.Koodi
from #tkopisk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Kieli k on k.ID=oj.KieliID
where 1=1
--voimassa tilannepäivänä:
and (select tilpvm from #param) 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 #tkopisk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Kunta k on k.ID=oj.KuntaID
where 1=1
--voimassa tilannepäivänä:
and (select tilpvm from #param) between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,''9999-01-01'')
;
Henkilötunnus (ht)

Ks. alkuperäinen poiminta.

Sukunimi (snimi)

Ks. alkuperäinen poiminta.

Etunimet (enimi)

Ks. alkuperäinen poiminta.

Sukupuoli (sp)

Ks. alkuperäinen poiminta.

Äidinkieli (aikieli)

Ks. alkuperäinen poiminta.

Opiskelijan vakinainen asuinkunta (askunta)

Ks. alkuperäinen poiminta.

Opiskelijan kansalaisuus (kansal)

Ks. alkuperäinen poiminta.

Ko. tutkintoon kirjoihintuloajankohta ko. ammattikorkeakoulussa (kirtupv)

Haetaan opiskeluoikeuden alkamispäivä.

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

Kirjoillaolo ammattikorkeakoulussa 1.1.-31.7.2016 (ilmoittautumistieto) (olotamm)

Haetaan ilmoittautumistieto kuten se on tietovarannossa käsittelyssä olevan lukukauden kohdalla liittyen ko. opiskeluoikeuteen. Poissaoloa kuvaavia koodiarvoja on tietovarannossa kaksi. Muutetaan 0-arvoksi, mikäli mitään tieto ei löydy.

Kevätlukukausi päätellään lukukauden viimeisen päivän eli 31. heinäkuuta perusteella.

SQL
-- olotamm :: kevään ilmo tilanne
update T
set olotamm=''1''
from #tkopisk T
where opiskeluoikeus_id in (
	select OpiskeluoikeusID
	from Lukukausi_ilmoittautuminen l
	inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
	where lt.Koodi=''1'' --läsnä
	and (select vuosi from #param)+''-07-31'' between l.Alkamispaivamaara and coalesce(l.Paattymispaivamaara,''9999-01-01'')
	-- siirto-opiskelija; ei aiemman kk:n ilmoja tähän (vertailu lk:n alku vai loppu?)
	and coalesce(l.Paattymispaivamaara,''9999-01-01'') >= coalesce(T.oikeus_siirtopaivamaara,''1900-01-01'')
	and coalesce(l.Paattymispaivamaara,''9999-01-01'') >= coalesce(T.oikeus_alkamispaivamaara,''1900-01-01'')
	-- EI tilpvm
)
and olotamm is null
;
update T
set olotamm=''2''
from #tkopisk T
where opiskeluoikeus_id in (
	select OpiskeluoikeusID
	from Lukukausi_ilmoittautuminen l
	inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
	where lt.Koodi in (''2'',''3'') --poissa, poissa ei kuluta
	and (select vuosi from #param)+''-07-31'' between l.Alkamispaivamaara and coalesce(l.Paattymispaivamaara,''9999-01-01'')
	-- siirto-opiskelija; ei aiemman kk:n ilmoja tähän (vertailu lk:n alku vai loppu?)
	and coalesce(l.Paattymispaivamaara,''9999-01-01'') >= coalesce(T.oikeus_siirtopaivamaara,''1900-01-01'')
	and coalesce(l.Paattymispaivamaara,''9999-01-01'') >= coalesce(T.oikeus_alkamispaivamaara,''1900-01-01'')
	-- EI tilpvm
)
and olotamm is null
;
-- ei ilmoa keväällä, nollaksi
update T
set olotamm=''0''
from #tkopisk T
where olotamm is null
;

Kirjoillaolo ammattikorkeakoulussa 20.9.2016 (ilmoittautumistieto) (olosyys)

Haetaan ilmoittautumistieto kuten se on tietovarannossa käsittelyssä olevan lukukauden kohdalla liittyen ko. opiskeluoikeuteen. Poissaoloa kuvaavia koodiarvoja on tietovarannossa kaksi. Muutetaan 0-arvoksi, mikäli mitään tieto ei löydy.

Syyslukukausi päätellään 20. syyskuuta päivämäärän perusteella.

SQL
-- olosyys :: syksyn ilmo tilanne
update T
set olosyys=''1''
from #tkopisk T
where opiskeluoikeus_id in (
	select OpiskeluoikeusID
	from Lukukausi_ilmoittautuminen l
	inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
	where lt.Koodi=''1'' --läsnä
	and (select tilpvm from #param) between l.Alkamispaivamaara and coalesce(l.Paattymispaivamaara,''9999-01-01'')
	-- siirto-opiskelija; ei aiemman kk:n ilmoja tähän (vertailu lk:n alku vai loppu?)
	and coalesce(l.Paattymispaivamaara,''9999-01-01'') >= coalesce(T.oikeus_siirtopaivamaara,''1900-01-01'')
	and coalesce(l.Paattymispaivamaara,''9999-01-01'') >= coalesce(T.oikeus_alkamispaivamaara,''1900-01-01'')
	-- tilpvm
	and l.Ilmoittautumispaivamaara <= (select tilpvm from #param)
)
and olosyys is null
;
update T
set olosyys=''2''
from #tkopisk T
where opiskeluoikeus_id in (
	select OpiskeluoikeusID
	from Lukukausi_ilmoittautuminen l
	inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
	where lt.Koodi in (''2'',''3'') --poissa, poissa ei kuluta
	and (select tilpvm from #param) between l.Alkamispaivamaara and coalesce(l.Paattymispaivamaara,''9999-01-01'')
	-- siirto-opiskelija; ei aiemman kk:n ilmoja tähän (vertailu lk:n alku vai loppu?)
	and coalesce(l.Paattymispaivamaara,''9999-01-01'') >= coalesce(T.oikeus_siirtopaivamaara,''1900-01-01'')
	and coalesce(l.Paattymispaivamaara,''9999-01-01'') >= coalesce(T.oikeus_alkamispaivamaara,''1900-01-01'')
	-- tilpvm
	and l.Ilmoittautumispaivamaara <= (select tilpvm from #param)
)
and olosyys is null
;
-- ei ilmoa syksyllä, nollaksi
update T
set olosyys=''0''
from #tkopisk T
where olosyys is null
;

 

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 #tkopisk 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 #tkopisk 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 mennessä mukaan lukien siirto-opiskelijoiden käyttämät lukukaudet aiemmassa korkeakoulussa.

Siirto-opiskelijat

  • 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ä
    • tai 2016 alkaen mikäli tiedossa siirto-opiskelijan liittyvät lukukausimäärät uuden tietomallin mukaan

 

Kaksi poimintamallia vanhan ja uuden tietomallin mukaan

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
        )
    ,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 #tkopisk T
WHERE   T.opiskeluoikeus_id NOT IN (SELECT OPL.OpiskeluoikeusID FROM  Op_oik_siir_opisk_liittyva_lukukausi OPL )
-- 2016 muutos  


Poissaololukukaudet (poissalk)

Käytettyjen poissaololukausien määrä tilastointivuoden loppuun mennessä mukaan lukien siirto-opiskelijoiden käyttämät lukukaudet aiemmassa korkeakoulussa.

 

 

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
        )
    ,0)
    + coalesce((select lkm from #poissaolo_maara_ennen where opiskeluoikeusid=T.opiskeluoikeus_id),0)
from #tkopisk T
WHERE   T.opiskeluoikeus_id NOT IN (SELECT OPL.OpiskeluoikeusID FROM  Op_oik_siir_opisk_liittyva_lukukausi OPL )
-- 2016 muutos  


-- Katsotaan onko Op_oik_siir_opisk_liittyva_lukukausi taulussa poissa ja läsnä-olot siirto-opiskelijalle
 -- 2016 muutos siirto-opiskelijat
 
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
        )
    ,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 #tkopisk T    
WHERE   T.opiskeluoikeus_id IN  (SELECT OPL.OpiskeluoikeusID FROM  Op_oik_siir_opisk_liittyva_lukukausi OPL  )


-- 2016 muutos poissaolo siirto-opiskelijat
 

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
        )
    ,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 #tkopisk T
WHERE   T.opiskeluoikeus_id  IN (    SELECT OPL.OpiskeluoikeusID  FROM  Op_oik_siir_opisk_liittyva_lukukausi OPL )







 
Puuttuvat lukukaudet (puuttulk)
 

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

 

-- puuttuulk :: Puuttuuvat poissalukukaudet
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') --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
        )
    ,0)
    
from #tkopisk T
WHERE   T.opiskeluoikeus_id NOT IN (SELECT OPL.OpiskeluoikeusID FROM  virta.dbo.Op_oik_siir_opisk_liittyva_lukukausi OPL )

Ensimmäinen kirjoihintuloajankohta ko. tutkintoon (kirtu1pv)

Haetaan opiskelun alkamispäivä ko tutkintoon siten, että otetaan pienin päiväys päivämääristä siirtopäivämäärä ja opiskeluoikeuden alkamispäivä.

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

Haetaan tiedetyn opiskeluoikeuden opiskeluoikeusjaksosta pätevyys-tiedon perusteella.

Pätevyyteen tähtäävät opinnot koodit.

1=Opiskelijalle, joka suorittaa sosionomi (amk) -tutkintoa kuuluu aineyhdistelmään opintoja, jotka tähtäävät lastentarhanopettajan pätevyyteen

2=Sairaanhoitajan pätevyys (tutkintokeruu)

3=Ammatillinen opettajankoulutus, pedagogiset opinnot

4=Ammatillinen opettajankoulutus, opinto-ohjaaja

5=Ammatillinen opettajankoulutus, erityisopettaja

 



SQL
-- lastu :: Lastentarhanopettajan pätevyyteen tähtäävät opinnot
update T
set lastu=''1''
from #tkopisk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Opiskeluoikeusjakson_patevyys jp on jp.OpiskeluoikeusjaksoID=oj.ID
inner join Patevyys k on k.ID=jp.PatevyysID
where k.Koodi=''iq''
--voimassa tilannepäivänä:
and (select tilpvm from #param) between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,''9999-01-01'')
--myös pätevyys-tieto
and (select tilpvm from #param) between jp.Alkamispaivamaara and coalesce(jp.Paattymispaivamaara,''9999-01-01'')
; 

-- lastu :: Sairaanhoitajan pätevyys (tutkintokeruu) Pätevyyteen tähtäävät opinnot
update T
set lastu=''2''
from #tkopisk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Opiskeluoikeusjakson_patevyys jp on jp.OpiskeluoikeusjaksoID=oj.ID
inner join Patevyys k on k.ID=jp.PatevyysID
where k.Koodi=''12''
--voimassa tilannepäivänä:
and (select tilpvm from #param) between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,''9999-01-01'')
--myös pätevyys-tieto
and (select tilpvm from #param) between jp.Alkamispaivamaara and coalesce(jp.Paattymispaivamaara,''9999-01-01'')
--TIE-90: Sairaanhoitajan tutkintoon ei saa liittyä sairaanhoitajan pätevyyttä
and oj.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 #tkopisk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Opiskeluoikeusjakson_patevyys jp on jp.OpiskeluoikeusjaksoID=oj.ID
inner join Patevyys k on k.ID=jp.PatevyysID
where k.Koodi=''ja''
--voimassa tilannepäivänä:
and (select tilpvm from #param) between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,''9999-01-01'')
--myös pätevyys-tieto
and (select tilpvm from #param) between jp.Alkamispaivamaara and coalesce(jp.Paattymispaivamaara,''9999-01-01'')
;

-- lastu :: Ammatillinen opettajankoulutus, opinto-ohjaaja Pätevyyteen tähtäävät opinnot
update T
set lastu=''4''
from #tkopisk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Opiskeluoikeusjakson_patevyys jp on jp.OpiskeluoikeusjaksoID=oj.ID
inner join Patevyys k on k.ID=jp.PatevyysID
where k.Koodi=''jc''
--voimassa tilannepäivänä:
and (select tilpvm from #param) between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,''9999-01-01'')
--myös pätevyys-tieto
and (select tilpvm from #param) between jp.Alkamispaivamaara and coalesce(jp.Paattymispaivamaara,''9999-01-01'')
;

-- lastu :: Ammatillinen opettajankoulutus, erityisopettaja Pätevyyteen tähtäävät opinnot
update T
set lastu=''5''
from #tkopisk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Opiskeluoikeusjakson_patevyys jp on jp.OpiskeluoikeusjaksoID=oj.ID
inner join Patevyys k on k.ID=jp.PatevyysID
where k.Koodi=''jb''
--voimassa tilannepäivänä:
and (select tilpvm from #param) between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,''9999-01-01'')
--myös pätevyys-tieto
and (select tilpvm from #param) between jp.Alkamispaivamaara and coalesce(jp.Paattymispaivamaara,''9999-01-01'')
;
 

Rahoituslähde (rahlahde)

Haetaan rahoituslähde-tieto tiedetyn opiskeluoikeuden opiskeluoikeusjaksosta. 

SQL
-- rahlahde :: Rahoituslähde
update T
set rahlahde=k.Koodi
from #tkopisk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Rahoituslahde k on k.ID=oj.RahoituslahdeID
--select * from Rahoituslahde
where 1=1
--voimassa tilannepäivänä:
and (select tilpvm from #param) between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,''9999-01-01'')
;
Maksullisen tilauskoulutuksen sijaintimaa (tilmaa)

Tietovarannossa ei ko. tietoa ole.

Koulutuskoodi (koulk)

Haetaan tieto suoraan kullakin rivillä tunnetun opiskeluoikeuden opiskeluoikeusjaksosta, joka on voimassa tilastointipäivänä.

SQL
update T
set koulk=k.Koodi
from #tkopisk T
inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
inner join Tutkintonimike k on k.ID=oj.TutkintonimikeID
where 1=1
--voimassa tilannepäivänä:
and (select tilpvm from #param) between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,''9999-01-01'')
and koulk is null
;

 

Opintopisteet (laajuus, opes, opek, opker)

Oma temp-taulu, joka nopeuttaa oleellisesti kyselyitä. Viedään temp-tauluun kaikki lehtitason muu opintosuoritus -lajiset opintosuoritukset ja samalla tarkistetaan suorituspäivämäärän perusteella minkä kauden kertymään suoritus kuuluu. Myös hyväksiluetut opintosuorituket viedään mukaan siinä missä muutkin suoritukset, sillä poikkeuksella, että hyväksilukeminen (päivämäärä) tulee olla tehty viimeistään tilannepäivänä.

Päivitetään kullekin poiminnan riville summat opintosuorituksista opiskeluoikeus-viittauksen perusteella.

Neljä tietoa käyttää opintosuorituslaajuuksia:

Suoritettavien opintojen laajuus (oplaaj) (laajuus)

  • Vähennetään opiskeluoikeuden laajuus-tiedosta summa niiden suoritusten laajuuksilla, joille pätee: Mikäli suoritus on ennen opiskeluoikeuden alkua ja suoritus on hyväksiluettu suoritus (ennen tilastointipäivää), lasketaan suoritus mukaan.

Edellisenä syyslukukautena (1.8.2015-31.12.2015) suoritettujen varsinaisten opintopisteiden määrä (opes)

  • Mikäli aputaulupoiminnassa katsottiin suorituspäivän osuvan Edellisen Syksyn (es) aikavälille, lasketaan suorituksen laajuus mukaan
Edellisenä kevätlukukautena (1.1.2016-31.7.2016) suoritettujen varsinaisten opintopisteiden määrä (opek)
  • Mikäli aputaulupoiminnassa katsottiin suorituspäivän osuvan Edellisen Kevään (ek) aikavälille, lasketaan suorituksen laajuus mukaan
Edellisen lukuvuoden loppuun (31.7.2016) mennessä suoritettujen kaikkien opintopisteiden määrä (opker)
  • Mikäli aputaulupoiminnassa katsottiin suorituspäivän osuvan KERtymän (ker) aikavälille, eli määrättyä päivää aiemmin suoritetuksi, lasketaan suorituksen laajuus mukaan
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,
	es bit,
	ek bit,
	ker bit
)
if @debug<>0 print convert(varchar,getdate(),120)+'' CREATE temp-taulu pisteet''
;
insert into #pisteet (opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm,hyvaksiluettu_pvm,es,ek,ker)
	select
	 opiskelija_id = s.OpiskelijaID
	,opiskeluoikeus_id = s.OpiskeluoikeusID
	,opintosuoritus_id = s.ID
	,laajuus = s.Laajuus
	,suoritus_pvm = s.Suorituspaivamaara
	,hyvaksiluettu_pvm = shl.Hyvaksilukupaivamaara
	,es = case when s.Suorituspaivamaara
		between cast(cast(#param.vuosi as int)-1 as varchar)+''-08-01'' and cast(cast(#param.vuosi as int)-1 as varchar)+''-12-31''
	  then 1 else 0 end
	,ek = case when s.Suorituspaivamaara between #param.vuosi+''-01-01'' and #param.vuosi+''-07-31''
	  then 1 else 0 end
	,ker = case when s.Suorituspaivamaara <= #param.vuosi+''-07-31''
	  then 1 else 0 end
	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
	-- Muutos 2017-09-16 Poisluettu lajin 3,4 suoritukset
    AND s.ID NOT IN (
        
        SELECT     ss.OpintosuoritusID 
        FROM     virta.dbo.Op_suor_sisaltyvyys ss
       
        --  Sisältyvyydet Lajeja 3 ja 4 ei huomioida       
        WHERE         
                 ss.OpintosuoritusID2 NOT IN ( 
        
            SELECT sss.ID            
            FROM virta.dbo.Opintosuoritus sss
            
                INNER JOIN virta.dbo.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 <= o2.Alkamispaivamaara and s.suoritus_pvm <= coalesce(o2.Siirtopaivamaara,''9999-01-01'')
			and s.hyvaksiluettu_pvm <= #param.tilpvm
			then s.laajuus else 0 end),0) as int) as varchar)
		from #pisteet s
		inner join Opiskeluoikeus o2 on o2.ID=T.opiskeluoikeus_id
		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 #tkopisk T
inner join Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
where o.Laajuus is not null
;

---
-- Opinnot, suoritukset, opintopisteet
---
--
-- TO DONE: "kyseistä tutkintoa varten"!? - opiskeluoikeusid
update T
set opes=(
  select cast(cast(round(sum(
	case when s.es = 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
)
from #tkopisk T
where opes is null
update T
set opek=(
  select cast(cast(round(sum(
	case when s.ek = 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
)
from #tkopisk T
where opek is null
update T
set opker=(
  select cast(cast(round(sum(
	case when s.ker = 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
)
from #tkopisk T
where opker is null
;
update T
set opes=''0''
from #tkopisk T
where opes is null
update T
set opek=''0''
from #tkopisk T
where opek is null
update T
set opker=''0''
from #tkopisk T
where opker is null
;

Tulos

Haetaan tehdystä aputaulusta rivit CSV-tiedostoon kokolailla sellaisenaan.

SQL
select
	 tunn                  as "Oppilaitostunnus" --oppil
	,coalesce(koultyp,'''')   "Koulutustyyppi" --koultyp
	,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(olotamm,'''')   "Kirjoillaolo ammattikorkeakoulussa  1.1.-31.7. (ilmoittautumistieto)" --olotamm
	,coalesce(olosyys,'''')   "Kirjoillaolo ammattikorkeakoulussa 20.9. (ilmoittautumistieto)" --olosyys
	,coalesce(opes,'''')      "Edellisenä syyslukukautena (1.8.-31.12.) suoritettujen varsinaisten opintopisteiden määrä" --opes
	,coalesce(opek,'''')      "Edellisenä kevätlukukautena (1.1.-31.7.) suoritettujen varsinaisten opintopisteiden määrä" --opek
	,coalesce(opker,'''')     "Edellisen lukuvuoden loppuun (31.7.) mennessä suoritettujen kaikkien opintopisteiden määrä" --opker
	,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" --rahlahde
	,coalesce(koulk,'''')     "Koulutuskoodi" --koulk
    ,coalesce(koulk,'''')      "ohjauksenala" --Ohjauksenala
    ,coalesce(koulk,'''')      "ekkoodi" --ekkoodi
    ,coalesce(koulk,'''')      "Puuttuvatlukukaudet" --puuttuulk
-- korkeakoulujen pyynnöstä, lisäkentät
	,T.opiskelijaavain      "Opiskelija-avain"
	,T.opiskeluoikeusavain  "Opiskeluoikeusavain"
from #tkopisk T

where T.opiskeluoikeus_id is not null
order by ht
'

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
---
/*
  Tiedonkeruuaineiston tarkistusohjelman suorittamat tarkistukset


  Amk-opiskelijatiedonkeruun aikana 20.9.-30.9. TK:n palvelimella toimii automaattinen
  tarkistusohjelma, joka tarkistaa aineistot yleisimpien virheiden osalta ja toimittaa tarkistuksesta
  palautetulosteen amk:n yhdyshenkilölle. Seuraavassa on kuvattu ohjelman tekemät tarkistukset ja
  niiden nimet palautetulosteessa. Kaikki tulosteessa mainitut kohdat eivät ole välttämättä virheitä,
  mutta ne tulevat listalle mahdollisina virheellisinä tietoina. Sellaisia ovat mm. epäilyttävän suuret
  opintopistekertymät tai puutteelliset henkilötunnukset.

  TUNN = tunn (oppilaitostunnus) on virheellinen
  onko oppilaitostunnus jokin voimassaolevista 5-numeroisista tunnuksista

  AVAIN = koulk (koulutuskoodi) on virheellinen
  Verrataan koulutusluokituksen koulutuskoodeihin

  AVAIN2 = oltunn - koulk (ammattikorkeakoulu - koulutuskoodi) -
  koodikombinaatio on virheellinen
  Ammattikorkeakoulu - koulutuskoodi -kombinaatiota verrataan vuoden 2013 kombinaatioihin

  AVAIN3 = koultyp on 3 tai 5 ja koulutusohjelma puuttuu

  KOULTYP = koulutustyyppi on virheellinen

  KOULTYP1 = muuttujan arvo ei ole 1, 2, 3, 5 eikä 6

  KOULTYP2 = jos koultyp on 1, 2 tai 6, niin koulutuskoodi ei saa olla 999999

  KOULTYP3= jos koultyp on 3 tai 5, niin koulutuskoodin täytyy olla 999999 ja koulutusohjelman tulee
  olla sallittu

  KKIELI = kkieli (koulutuksen opetuskieli) -koodiarvo on virheellinen
  koulutuksen opetuskieli suomi (fi), ruotsi (sv), englanti (en) tai saksa (de)

  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 ). 
  
  KIRTUPV = kirjoihintuloajankohta on virheellinen

  KIRTUPV2 = kirjoihintulovuosi ei saa olla suurempi kuin tilastovuosi, kirjoihintulokuukausi saa arvon
  1-12, päivä arvon 1-31

  OLOTAMM = olotamm (kevään kirjoillaolo ) on virheellinen

  OLOTAMM1 = kevään läsnäolotieto voi olla 0, 1 tai 2

  OLOTAMM2 = jos tilastovuosi=kirjoihintulovuosi ja kirjoihintulolukukausi on syksy, kevään
  läsnäolotiedon on oltava 0

  OLOTAMM3 = kevään läsnäolotieto voi olla amk:n vanhoilla opiskelijoilla joko 1 tai 2

  OLOTAMM4 = kirjoihintulolukukausi on kevät, mutta kevään läsnäolotieto on 0 (olotamm=0)

  OLOSYYS = olosyys (läsnäolo 20.9.) on virheellinen
  syksyn läsnäolotieto voi olla 0, 1 tai 2

  TU = sama opiskelija on aineistossa kahteen kertaan

  TU1 = tarkistetaan onko joillakin havainnoilla samat arvot joka muuttujalla - täydellinen tuplatietue

  TU2 = tarkistetaan onko joillakin havainnoilla samat oppilaitostunnus-koulutustyyppi-koulutuskoodi -
  kombinaatiot

  OPES = ed. syyslukukaudella suoritetut opintopisteet on virheellinen
  lukukauden aikana suoritettu yli 99 opintopistettä

  OPEK = ed. kevätlukukaudella suoritetut opintopisteet on virheellinen
  lukukauden aikana suoritettu yli 99 opintopistettä

  OPKER = opker (ed. kevätlukukauden loppuun mennessä kertyneet
  opintopisteet) on virheellinen
  opintopistekertymä yli 300 tai ed. kevät- ja syyslukukausien opintopisteet yhteensä ovat enemmän
  kuin kertymä plus yksi (=pyöristykset voivat nostaa summaa yhdellä).

  HT = henkilötunnus (tai sukupuoli) on virheellinen

  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

  LASNALK = läsnäolo- tai poissaololukukausissa virhe

  LASNALK1 = (läsnäololukukaudet + poissaololukukaudet) <= (3 - 1.kirjoihintulolukukausi) +
  (tilastovuosi - 1.kirjoihintulovuosi) * 2
  Esim. jos opiskelija on tullut kirjoille 1. kerran syyslukukaudella 2010, ja tilastovuosi on 2012:
  läsnä+poissa yhteensä voi olla maksimissaan: (3 - 2) + (2012-2010) * 2 = 5

  LASNALK2 = läsnä- tai poissaolokausia täytyy olla vähintään 1.

  OPLAAJ = opintojen laajuus virheellinen

  OPLAAJ1 = muodollinen virhe (voi olla 1 - 300)

  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

  OPETTAJA = Lastentarhanpettajan koulutus muodoltaan virheellinen

  OPETT1 = voi olla 1, 2, tai tyhjä.

  OPETT2 = opettajankoulutus ei ole sosionomi-tutkinnolla

  OPETT3 = opettajatieto syksyn uudella opiskelijalla

  RAHLAHDE = Rahoituslähde muodoltaan virheellinen

  RAHLAHDE= voi olla 1, 2, 3, 4 tai 5.

  RAHLAHD4= rahoituslähde on 4 ja maksullisen tilauskoulutuksen sijaintimaatieto puuttuu

  EIMUKAAN = Opiskelijalla ei ilmoittautumistietoa 20.9. eikä ole aloittanut
  vuonna 2014
  Ei kuulu perusjoukkoon
  
  TILMAA = Maksullisen tilauskoulutuksen sijaintimaatieto virheellinen
  Verrataan sijaintimaatietoa sallittujen maiden listaan
  
  OHJAUKSENALA= voi olla 1 - 12 jos koulutuskoodi 999999 
  
*/
--/*
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),
  avain varchar(20),
  --pois: avain2 varchar(12),
  --pois: avain3 varchar(1),
  --koultyp varchar(4),
  koultyp1 varchar(1),
  koultyp2 varchar(8),
  --pois: koultyp3 varchar(1),
  kkieli varchar(2),
  kkun varchar(10), --muutettu
  kkun2 varchar(3),
  kirtupv varchar(10), --muutettu
  kirtupv2 varchar(8),
  olotamm varchar(10), --muutettu
  olotamm1 varchar(8),
  olotamm2 varchar(8),
  olotamm3 varchar(8),
  olotamm4 varchar(8),
  olosyys varchar(10), --muutettu(lisäys)
  --tu varchar(1),
  tu1 varchar(1),
  tu2 varchar(1),
  opes varchar(4),
  opek varchar(4),
  opker varchar(4),
  --ht varchar(11),
  ht1 varchar(11),
  ht2 varchar(11),
  ht3 varchar(11),
  ht4 varchar(11),
  aikieli varchar(2),
  askun varchar(3),
  kansal varchar(3),
  --lasnalk varchar(4),
  lasnalk1 varchar(10),
  lasnalk2 varchar(10),
  --oplaaj varchar(4),
  oplaaj1 varchar(4),
  kirtu1pv varchar(8),
  kirtu1p2 varchar(8),
  --opettaja varchar(1),
  opett1 varchar(2),
  opett2 varchar(8),
  opett3 varchar(10),
  rahlahde varchar(1),
  --pois: rahlahd4 varchar(1),
  eimukaan varchar(1),
  --pois: tilmaa varchar(3),
  --
  opoik varchar(10), --lisäys
  ohjauksenAla varchar(2), 
  --
  opiskelijaavain varchar(100),
  opiskeluoikeusavain varchar(100),
  opiskelija_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)

,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
-- lisätty tarkistus että tutkintonimike alkaa numerolla 6,7,8 tai 9
               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
         
         )
--,avain2 = --amk-koulk kombinaatiot?
--,avain3 = --koulutusohjelma?
--,koultyp = --otsikko?
--,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 ('1','2','6') and koulk='999999' then koultyp+' '+koulk else '' end)
--,koultyp3 = --koulutusohjelma?
,kkieli = (case when kiel not in ('fi','sv','en','de') then kiel else '' end)
,kkun = (case when coalesce(kkun,'')='' then '#puuttuu#' else '' end)
,kkun2 = (case
  when koultyp in ('2','3','5')
  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)
,kirtupv = (case when coalesce(kirtupv,'')='' then '#puuttuu#' else '' end)
,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)
,olotamm = (case when coalesce(olotamm,'')='' then '#puuttuu#' else '' end)
,olotamm1 = (case when olotamm not in ('0','1','2') then olotamm else '' end)
,olotamm2 = (case
  when cast(substring(kirtupv,1,4) as int) = (select year(tilpvm) from #param)
  and cast(substring(kirtupv,5,2) as int) >= 8
  and olotamm<>'0' then kirtupv
  else '' end)
,olotamm3 = (case
  when(
      (cast(substring(kirtupv,1,4) as int) < (select year(tilpvm) from #param))
   or (cast(substring(kirtupv,1,4) as int) = (select year(tilpvm) from #param) and cast(substring(kirtupv,5,2) as int) < 8))
  and olotamm not in ('0','1','2') then kirtupv
  else '' end)
,olotamm4 = (case
  when (cast(substring(kirtupv,1,4) as int) = (select year(tilpvm) from #param) and cast(substring(kirtupv,5,2) as int) < 8)
  and olotamm = '0' then kirtupv
  else '' end)
,olosyys = (case
  when coalesce(olosyys,'')='' then '#puuttuu#'
    when olosyys not in ('0','1','2') then olosyys
  else '' end)
--,tu = --otsikko?
,tu1 = (case
  when exists (
    select 1 from #tkopisk t2 where t2.opiskelijaavain=#tkopisk.opiskelijaavain and t2.tunn = #tkopisk.tunn 
    group by tunn, koultyp, kiel, kkun, ht, snimi, enimi, sp, aikieli, askunta, kansal, kirtupv, olotamm, olosyys
    , opes, opek, opker, lasnalk, poissalk, laajuus, kirtu1pv, lastu, rahlahde, koulk
    having count(*)>1
  ) then '1'
  else '' end)
,tu2 = (case
  when exists (
    select 1 from #tkopisk t2 where t2.opiskelijaavain=#tkopisk.opiskelijaavain and t2.tunn = #tkopisk.tunn 
    group by tunn, koultyp, koulk, ht, snimi, enimi, sp
    having count(*)>1
  ) then '1'
  else '' end)
,opes = (case when cast(opes as int) > 200 then opes else '' end)
,opek = (case when cast(opek as int) > 200 then opek else '' end)
,opker = (case when cast(opker as int) > 400 then opker else '' end)
--,ht = --otsikko?
,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)
--,lasnalk = --otsikko?
,lasnalk1 = (case
  when cast(lasnalk as int)+cast(poissalk as int)
     > ((3 - (case when cast(substring(kirtu1pv,5,2) as int)<8 then 1 else 2 end))
      + (((select year(tilpvm) from #param) - cast(substring(kirtu1pv,1,4) as int)) * 2))
  then lasnalk+'+'+poissalk+'<'+
      cast((3 - (case when cast(substring(kirtu1pv,5,2) as int)<8 then 1 else 2 end))
      + (((select year(tilpvm) from #param) - cast(substring(kirtu1pv,1,4) as int)) * 2) as varchar)
  else '' end)
,lasnalk2 = (case
  when cast(lasnalk as int)+cast(poissalk as int) < 1 then lasnalk+'+'+poissalk+'<1'
  else '' end)
--,oplaaj = --otsikko?
,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 else '' end)
--,opettaja = --otsikko?
,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') then koulk+'+'+lastu else '' end)
,opett2 = (case when lastu in ('1') and koulk not in ('671201','771301') then koulk+'+'+lastu else '' end)
--,opett3 = (case
--  when lastu in ('1' ,'3','4','5')
--  and substring(kirtupv,1,4) = (select vuosi from #param) and cast(substring(kirtupv,5,2) as int) >= 8
--  then kirtupv+'+'+lastu
--  else '' end)
,opett3 = '' 
,rahlahde = (case when rahlahde not in ('1','2','3','4','5') then rahlahde else '' end)
--,rahlahd4 = --tilmaa tietoa ei enää ole
,eimukaan = '' --pitäisi toistaa perusjoukon poiminta
--
,opoik = ''--(case when coalesce(opoik,'')='' then '#puuttuu#' else '' end)

,ohjauksenAla = (CASE WHEN koulk='999999' AND ohjauksenAla NOT IN ('1','2','3','4','5','6','7','8','9','10','11','12')  THEN ohjauksenAla +' '+ohjauksenAla 
                      WHEN koulk='999999' AND ohjauksenAla IS NULL   THEN '99' ELSE '' END)
--
--
,opiskelijaavain
,opiskeluoikeusavain
,opiskelija_id
,opiskeluoikeus_id
,db = (select DB from Yhteiset.Asetukset.Instanssit where OrganisaatioKoodi=tunn)
from #tkopisk
;

/*
select * from #tarkistus
where ''<>
tunn+avain+koultyp1+koultyp2+kkieli+kkun2+kirtupv2+
olotamm1+olotamm2+olotamm3+olotamm4+olosyys+tu1+tu2+
opes+opek+opker+ht1+ht2+ht3+aikieli+askun+kansal+
lasnalk1+lasnalk2+oplaaj1+kirtu1pv+kirtu1p2+
opett1+opett2+opett3+
rahlahde+eimukaan+opoik
;
--*/

-- transponoi
delete from TK_tarkistus where tiedonkeruu='tk_opisk_amk';
insert into TK_tarkistus (virhekoodi,virhe,arvo,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,tiedonkeruu)
select 'tunn' as VIRHEKOODI, 'oppilaitostunnus on virheellinen' as VIRHE,tunn AS ARVO,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>tunn
union select 'avain', 'koulutuskoodi on virheellinen tai puuttuu',avain,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>avain
union select 'koultyp1', 'koulutustyyppi on virheellinen; muuttujan arvo ei ole 1, 2, 3, 5, 6 eikä 7',koultyp1,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>koultyp1
union select 'koultyp2', 'jos koulutustyyppi on 1, 2 tai 6, niin koulutuskoodi ei saa olla 999999',koultyp2,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>koultyp2
union select 'kkieli', 'koulutuksen opetuskieli -koodiarvo on virheellinen',kkieli,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>kkieli
union select 'kkun', 'koulutuksen sijaintikunta -koodiarvo on virheellinen',kkun,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>kkun
union select 'kkun2', 'kunta-koodiarvo on virheellinen ( koulutustyyppi 2, 3 ja 5 )',kkun2,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>kkun2
union select 'kirtupv', 'kirjoihintuloajankohta on virheellinen',kirtupv,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>kirtupv
union select 'kirtupv2', 'kirjoihintulovuosi ei saa olla suurempi kuin tilastovuosi, kirjoihintulokuukausi ei ole 1-12 tai päivä ei ole 1-31',kirtupv2,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>kirtupv2
union select 'olotamm', 'kevään kirjoillaolo on virheellinen',olotamm,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>olotamm
union select 'olotamm1', 'kevään läsnäolotieto voi olla 0, 1 tai 2',olotamm1,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>olotamm1
union select 'olotamm2', 'jos tilastovuosi=kirjoihintulovuosi ja kirjoihintulolukukausi on syksy, kevään läsnäolotiedon on oltava 0',olotamm2,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>olotamm2
union select 'olotamm3', 'kevään läsnäolotieto voi olla vanhoilla opiskelijoilla joko 1 tai 2',olotamm3,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>olotamm3
union select 'olotamm4', 'kirjoihintulolukukausi on kevät, mutta kevään läsnäolotieto on 0',olotamm4,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>olotamm4
union select 'olosyys', 'olosyys (läsnäolo 20.9.) on virheellinen. syksyn läsnäolotieto voi olla 0, 1 tai 2',olosyys,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>olosyys
union select 'tu1', 'joillakin havainnoilla samat arvot joka muuttujalla - täydellinen tuplatietue',tu1,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>tu1
union select 'tu2', 'sama oppilaitostunnus-koulutustyyppi-koulutuskoodi -kombinaatio useita kertoja',tu2,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>tu2
union select 'opes', 'ed. syyslukukaudella suoritettu liikaa opintopisteitä',opes,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>opes
union select 'opek', 'ed. kevätlukukaudella suoritettu liikaa opintopisteitä',opek,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>opek
union select 'opker', 'opintopistekertymä liian suuri tai ed. kevät- ja syyslukukausien opintopisteet yhteensä ovat enemmän kuin kertymä plus yksi (=pyöristykset voivat nostaa summaa yhdellä).',opker,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>opker
union select 'ht1', 'Puutteellinen henkilötunnus - loppuosa puuttuu',ht1,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>ht1
union select 'ht2', 'henkilötunnus, jonka jakojäännös ei täsmää - loppuosa virheellinen',ht2,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>ht2
union select 'ht3', 'sukupuolitieto virheellinen',ht3,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>ht3
union select 'ht4', 'Väärä välimerkki lopussa. Jos 2000 luvulla syntynyt tulee olla A ei -',ht4,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>ht4
union select 'aikieli', 'äidinkielitieto virheellinen',aikieli,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>aikieli
union select 'askun', 'asuinkuntatieto virheellinen',askun,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>askun
union select 'kansal', 'kansalaisuustieto virheellinen',kansal,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>kansal
union select 'lasnalk1', '(läsnäololukukaudet + poissaololukukaudet) <= (3 - 1.kirjoihintulolukukausi) + (tilastovuosi - 1.kirjoihintulovuosi) * 2',lasnalk1,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>lasnalk1
union select 'lasnalk2', 'läsnä- tai poissaolokausia täytyy olla vähintään 1.',lasnalk2,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>lasnalk2
union select 'oplaaj1', 'Opintojen laajuus virheellinen; muodollinen virhe (voi olla 1 - 300)',oplaaj1,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_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,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_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',kirtu1p2,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>kirtu1p2
union select 'opett1', 'Lastentarhanpettajan koulutus muodoltaan virheellinen; voi olla 1, 2, 3, 4, 5 tai tyhjä.',opett1,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>opett1
union select 'opett2', 'opettajankoulutus ei ole sosionomi-tutkinnolla',opett2,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>opett2
union select 'opett3', 'opettajatieto syksyn uudella opiskelijalla',opett3,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>opett3
union select 'rahlahde', 'Rahoituslähde muodoltaan virheellinen. Voi olla 1, 2, 3, 4 tai 5.',rahlahde,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>rahlahde
---union select eimukaan,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>eimukaan
union select 'opoik', 'OPOIK puuttuu',opoik,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' from #tarkistus where ''<>opoik
union SELECT 'ohjauksenAla', 'jos koulutuskoodi 999999 ohjauksenala on annettava koodiarvolla 1 - 12 ja se ei voi olla tyhjä',ohjauksenAla,opiskelijaavain,opiskeluoikeusavain,opiskelija_id,opiskeluoikeus_id,db,'tk_opisk_amk' FROM #tarkistus WHERE ''<>ohjauksenAla
;
print convert(varchar,getdate(),120)+' tarkistustiedot viety tauluun TK_tarkistus'
;

 

Muutoshistoria

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

  • 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

12.10.2018:: Ohjauksenala tyhjä ja tutkintokoodin tarkistus ja puuttuvien lukukausi tietojen sarakkeen lisäys :: wiki-versio 42

Tarkistuskäsittelyyn lisätty avain muuttujaan tutkintokoodin ensimmäisen numeron tarkastelu että alkaa numeroilla 6,7,8 tai 9 ja ohjauksenala päättelyyn koulk = '999999' niin ohjauksenala ei saa olla tyhjä.

Tiedostoon lisätty myös puuttuvat lukukaudet sarake, johon tuodaan lukukausi ilmoittautumisen tilan 4 lukumäärä niiltä korkeakouluilta jotka sen tuovat.

11.10.2017 ::olotamm3 käsittelyyn muutos tarkistuksessa :: wiki-versio 41

  • lisätty olotamm3 tarkistukseen myös olotamm= 0 vertailu, jos kevät lukukausi-ilmoittautumista ei ole ja olotamm on 0.

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

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

17.10.2016 :: Erikoistumiskoulutus. Siirto-opiskelijoiden läsnä-poissaolot :: wikiversio 39
4.9.2016 :: Tarkistusta korjattu :: wikiversio 38

Korjattu koultyp1 tarkistusta että tunnistaa myös uuden koodin 7=Erikoistumiskoulutukset (Virta koodi 19=Erikoistumiskoulutukset) ja opett3 tarkistus poistettu.

23.11.2015 :: Lastentarha (lastu) koodit :: wikiversio 37
  • Korjattu AMK puolen "lastu" muuttujan koodiarvoja 3 - 5 (väärät YO puolen im, iy,iu => ja, jc, jb).
22.10.2015 :: Lastentarha (lastu) koodit :: wikiversio 36
  • Lisätty uuden Tilastokeskuksen tietuemallin mukaisia "lastu" muuttujan koodiarvoja 2 - 5. Huom! vain koodi 1 (= opintoja, jotka tähtäävät lastentarhanopettajan pätevyyteen) käytössä toistaiseksi.
15.10.2015 :: Lukukausien lukumäärän laskennan kaksi poimintamallia :: wikiversio 35
  • Lisätty lukukausien laskentaan kaksi poimintamallia "välitila" ja "toinenkk".
8.10.2015 :: Läsnä- ja poissaolojen lukumäärä :: wikiversio 31
  • Lasketaan aiemmassa korkeakoulussa käytetyt lukukaudet yhteen aikaväliltä ja päätellään läsnäoloksi ne joita ei ole ilmoitettu poissaoloksi LasnaPoissaLukukausiMaara-XML-kentassa.
14.09.2015 :: Erikoistumiskoulutus Koulutustyyppi:: wikiversio 25
  • Erikoistumiskoulutus, 7=Erikoistumiskoulutukset (uudet 2015 alkavat koulutukset)
09.09.2015 :: Lastentarhanopettajan pätevyyteen tähtäävät opinnot :: wikiversio 24
  • Muutettu vanha pätevyyskoodi 2 => 'iq'

 

17.11.2014 :: Poistettu turhia sarakkeita ja lisätty avaintiedot :: wikiversio 23
  • Poistetut sarakkeet
    • Tutkintokoodi TUTK
    • Koulutusohjelma KO
    • Suuntautumisvaihtoehto SV
    • Oikeutettu valtionosuusrahoitukseen (Huom. Vanhentunut kysymys) DEP_OIK_VALTOSRAH
    • Erityisopiskelija EROPISK
  • Lisätty avaintiedot
    • Opiskelija-avain
    • Opiskeluoikeusavain
30.9.2014 :: Opiskelijatiedonkeruu 2014 :: wikiversio 21

 

 

  • No labels

3 Comments

  1. 1.8-19.9 eronneita ei tarvita mukaan poimintoihin /OKM

  2. Kirjoilletulopäivämäärä on kerätty tieto, alvv_kk opiskelija-aineistojen perusteella päätelty OKM:n aineistoihin@TK, korjataan muuttujien epämääräisyyksiä

  3. Pekka Harjula, Humak: Jä tässä ei siis ollut merkitystä sillä että hyväksilukemisen pvm on elo-syyskuussa mikäli suorituspv oli esim. edellisen kevään pvm? Tämä oli se minkä Hanne sanoi ensin hieman väärin ja joka sitten korjattiin Kota-seminaarissa tauon jälkeen (eikö niin). Eli näiden kahden päivämäärän ei tarvitse ajoittua samalle lukuvuodelle.

    Pekka Harjula, Humak: esim kolmatta vuotta aloittavilla tällä on merkitystä 55 op osalta, eli jos on ollut kevään 2014 vaihdossa ulkom oppilaitoksessa ja siitä hyväksiluetaan 30 op niin se noteerataan 55 op tarkastelussa

    Jukka Haapamäki: Pekka, noin on tarkoituskin

    Hanne Mikkonen: Kyllä, menee noin eli kevään ulkomaan opinnot kerryttävät 55 op kertymää..