CSC:ltä (Korkeakoulujen tietovaranto - VIRTA) saatavien tietojen tiedosto- ja tietuekuvaus 1.9.2018 alkaen.

Alarajapäivä
Opintopisteet1
Opintopisteet2
Tietuekuvaus (taulukko)
Virta poiminta 

Huomioitavaa että Kelan edistymisenseurannassa poimitaan myös opintopisteet opintosuorituksista jossa on Virta opintosuorituslajin 3 (="ei huomioitava, osasuoritus vain Kelaa varten").


Korkeakouluilla tarkoitetaan suomalaisia korkeakouluja. 
CSC täydentää Kelan muodostamaan pyyntötiedostoon kustakin siinä olevasta opiskelijasta (yksi tietue opiskelijaa kohden) seuraavat VIRTA-opintotietopalvelusta poimittavat tiedot:

  1. Alarajapäivä: Korkeakouluopintojen aloituspäivä
    Tarkentavat ehdot:

    1. korkeakouluopintoja ovat tutkintoon johtavat suomalaisen korkeakoulun opinnot

      Aloituspäiväksi ei saa ilmoittaa muun kuin tutkintoon johtavan koulutuksen aloituspäivää eikä avoimen korkeakoulun opintojen aloituspäivää eikä Maanpuolustuskorkeakoulun aloituspäivää.

    2. aloituspäivä on ensimmäisen läsnäololukukauden ensimmäinen päivä (1.8. tai 1.1.), siirto-opiskelijoilla 1. korkeakoulun mukainen läsnäololukukauden ensimmäinen päivä

      Vaikka opiskelijalla olisi useita opiskeluoikeuksia, aloituspäivä määräytyy ensimmäisen tutkintoon johtavan läsnäolon mukaan (ellei valmistu, ks. ehto c.)

    3. jos opiskelija on valmistunut aloituspäiväksi poimitaan viimeisimmän valmistumisen valmistumiskuukautta seuraavan kuukauden ensimmäinen päivä. Myös Maanpuolustuskorkeakoulussa tehty tutkinto huomioidaan. Huom. muiden kuin yllämainittujen tutkintoon johtavien koulutusten valmistumista ei huomioida valmistumisena (Kelassa P ja E tutkinnontasot).

      Esim. opiskelijan tutkinnon suorittamispäivä on 14.3.2018. Ilmoitettava aloituspäivä on 1.4.2018.

      Esim. opiskelijan tutkinnon suorittamispäivä ammattikorkeakoulussa 14.3.2018, jonka jälkeen opiskelija on suorittanut erillisen ylemmän korkeakoulututkinnon (erillinen maisteri) 30.9.2020. Vuonna 2021 toteuttavassa lv. 2020 – 2021 edistymisen seurannassa ilmoitettava aloituspäivä on 1.10.2020.

      Jos henkilö on valmistunut kuluvan vuoden heinäkuussa, ilmoitettava aloituspäivä 1.8.kuluva vuosi.

    4. aloituspäivä ei saa olla aikaisempi kuin 1.8.2011 (jos on aikaisempi, ilmoitetaan 1.8.2011)

      Esim. jos opiskelija on aloittanut opintonsa 1.8.2009, ilmoitettavaaloituspäivä on 1.8.2011.

  2. Opintopisteet1: Koko opiskeluajan opintopisteiden määrä kaikista korkeakouluista (myös avoimen korkeakoulun suoritukset poimitaan)
    Tarkentavat ehdot:

    1. jos aloituspäivä (alarajapäivä) on tyhjä tai myöhempi 31.7. kuluva vuosi, opintopisteitä ei ilmoiteta (tieto on tyhjää)

    2. koko opiskeluajan opintopisteillä tarkoitetaan korkeakouluopintojen aloituspäivän (määrittely kohdassa 1) ja seurantalukuvuoden lopun (31.7.20xx) välillä suoritettuja opintopisteitä

    3. opintopisteet huomioidaan suorituspäivän mukaan

    4. jos suorituksella on VIRTA-opintotietopalvelussa hyväksi lukemisen päivämäärä toisessa kotimaisessa korkeakoulussa tehdystä suorituksesta, niin ko. suoritusta ei huomioida lainkaan. Mahdollisesti toisessa korkeakoulussa hyväksi luetut suoritukset huomioidaan alkuperäisen suorituspäivämäärän perusteella sen ajan suoritukseksi, milloin suoritus on tehty.

      1. Toisen asteen, kesäyliopiston, kansanopiston, ulkomaisen oppilaitoksen tms. hyväksi luettu suoritus huomioidaan, jos alkuperäinen suorituspäivä on korkeakouluopintojen ajalta (eli alarajapäivän jälkeen).

      2. Niitä hyväksi luettuja opintosuorituksia, jotka on suoritettu ennen tutkintoon tähtäävien korkeakouluopintojen aloittamista (ennen alarajapäivää), ei ilmoiteta lainkaan.

  3. Opintopisteet2: Seurantalukuvuoden opintopisteiden määrä kaikista korkeakouluista (myös avoimen korkeakoulun suoritukset poimitaan)
    Tarkentavat ehdot:

    1. Jos aloituspäivä (alarajapäivä) on tyhjä tai myöhempi 31.7., opintopisteitä ei ilmoiteta (tieto on tyhjää)

    2. seurantalukuvuosi on seurannan toteutusta edeltävä lukuvuosi 1.8.20xx – 31.7.20xx tai jos henkilö on valmistunut seurantalukuvuonna, niin seurantakausi on valmistumiskuukautta seuraavan kuukauden 1. päivä (alarajapäivä) –31.7.20xx

    3. opintopisteet huomioidaan suorituspäivän mukaan

    4. jos suorituksella on VIRTA-opintotietopalvelussa hyväksi lukemisen päivämäärä toisessa kotimaisessa korkeakoulussa tehdystä suorituksesta, niin ko. suoritusta ei huomioida lainkaan. Mahdollisesti toisessa korkeakoulussa hyväksi luetut suoritukset huomioidaan alkuperäisen suorituspäivämäärän perusteella sen ajan suoritukseksi, milloin suoritus on tehty.

      1. Toisen asteen, kesäyliopiston, kansanopiston, ulkomaisen oppilaitoksen tms. hyväksi luettu suoritus huomioidaan, jos alkuperäinen suorituspäivä on korkeakouluopintojen ajalta (eli alarajapäivän jälkeen).

      2. Niitä hyväksi luettuja opintosuorituksia, jotka on suoritettu ennen tutkintoon tähtäävien korkeakouluopintojen aloittamista (ennen alarajapäivää), ei ilmoiteta lainkaan.

        Esim. kurssin suorituspvm. on 2.5.2017 ja hyväksi lukemisen pvm.  20.9.2017. Ko. kurssin opintopisteet ilmoitetaan lv. 2016 – 2017 edistymisen seurannassa seurantalukuvuodelle (on mukana sekä Opintopisteet1 ja Opintopisteet2 tiedossa), mutta ei enää lv. 2017 – 2018 seurantalukuvuodelle (on mukana vain Opintopisteet1 tiedossa).





Alkuun

Tietuekuvaus (taulukko)

CSC:ltä (Korkeakoulujen tietovaranto - VIRTA) saatavien tietojen tiedosto- ja tietuekuvaus 1.9.2018 alkaen.

Tätä tietuekuvausta käytetään opiskelun edistymisen seurantaan tarvittavassa tietojenvälityksessä sekä Kelasta CSC:lle (VIRTAan) ja CSC:ltä (VIRTAsta) Kelaan.


Tiedon nimiSijaintiPituusKuvaus
Siirtotunnus115

Tiedoston siirtotunnus

- Kela ilmoittaa (vakio), CSC ei päivitä

- vakio ’SOSOVP’

- loppuosa tyhjää

- pakollinen

Tietuetyyppi161

T = opintosuoritustietue

- Kela ilmoittaa (vakio), CSC ei päivitä

- pakollinen

Poimintapäivämäärä178

PPKKVVVV

- Kela ilmoittaa, CSC päivittää

- poiminta-ajon ajopäivämäärä CSC:llä

- etunollat PP:ssä ja KK:ssa

esim. 20092018

- pakollinen

Siirtolaji255

Siirtoerän siirtolaji

- Kela ilmoittaa (vakio), CSC ei päivitä

- vakio ’OPVII’

- pakollinen

Lähettäjäryhmän

tunnus

302

Lähettäjäryhmän tunnus

- Kela ilmoittaa (vakio), CSC ei päivitä

- vakio ’OP’

- pakollinen

Varatila328Tyhjää
Henkilötunnus4011

PPKKVVZNNNX

- Kela ilmoittaa, CSC ei päivitä

- pakollinen tieto, esim. 010101A000X

Sukunimi5130

Opiskelijan sukunimi

- Kela ilmoittaa, CSC ei päivitä

- nimitiedot virheselvittelyä varten

- pakollinen

Etunimet8130

Opiskelijan etunimet

- Kela ilmoittaa, CSC ei päivitä

- nimitiedot virheselvittelyä varten

- pakollinen

Alarajapäivä1118

PPKKVVVV

- opintosuoritusten seuranta-ajan alkupäivä (koko

opiskeluajan alkupäivä) ks. ehdot muistiosta

- CSC päivittää

- etunollat PP:ssä ja KK:ssa, esim. 01082015

Ylärajapäivä1198

PPKKVVVV

- Kela ilmoittaa, CSC ei päivitä

- opintosuoritusten seuranta-ajan loppupäivä,

jonka Kela päivittää pyyntötiedostoon valmiiksi

- etunollat PP:ssä ja KK:ssa, esim. 31072018

- pakollinen

Opintopisteet11273

Em. rajapäivämäärien välisenä aikana (koko

opiskeluaikana) suoritettujen opintopisteiden

määrä ks. ehdot muistiosta

- CSC päivittää

- kokonaislukuna (pyöristyssääntö), tyhjä sallittu

- täytetään etunollilla, esim. 000, 005, 096

Varatila1303Tyhjää
Opintopisteet21333

Kertyneet opintopisteet edeltävänä lukuvuotena

(seurantalukuvuosi) ks. ehdot muistiosta

- CSC päivittää

- kokonaislukuna (pyöristyssääntö), tyhjä sallittu

- ei saa olla suurempi kuin opintopisteet1

- täytetään etunollilla, esim. 000, 005, 096

Varatila13665Tyhjää


Yleistä

Kelaan lähetettävien tietojen muodollinen oikeellisuus on tarkistettava CSC:ssä ennen tietojen lähettämistä. Asiasisällön osalta oppilaitos vastaa siitä, että tiedot ovat VIRTA-opintotietopalvelussa mahdollisimman ajantasaiset.
Tietueen pituus pitää olla aina 200 merkkiä. Jokaisen tietueen on oltava omalla rivillään. Tiedoston pitää olla ISO 8859-1 standardin mukainen.

Alkuun



Virta poiminta


Yleiset perustiedot

CSC toimittaa Kelalle vuosittain edistymisenseurannan tulokset ns. vastaustiedostona Kelan lähettämään pyyntötiedostoon.  Edistymisen seuranta tehdään Kelan korkeakouluille ilmoittaman aikataulun mukaisesti. Opiskelijoiden opintopisteet lasketaan Kelan ilmoittaman määrittelyn mukaisesti Virta-tietovarannosta, jossa suoritetaan lopullinen laskenta sovituna poimintahetkenä, sen hetken Virta-tietojen mukaan. Korkeakoulujen vastuulla on toimittaa ajantasaiset tiedot Virtaan ajoissa ennen lopullista laskentaa.  Kela käyttää näitä laskennan tuloksia massa-ajoihin, joilla seurataan opintotukea saaneiden opiskelijoiden edistymistä nostettuun opintotukikuukausiin nähden. Vuodesta 2018 lähtien edistymisen seurannan tiedoista ei enään muodosteta korkeakouluille omien opiskelijoiden seurantaa varten korkeakoulukohtaisia tarkistustiedostoja.

Kaikki alla olevissa poimintalauseissa olevat taulut ovat sisäisiä tietovarannon kaikkien korkeakoulujen tietoja sisältäviä tauluja (yleistasolla on mahdollisuksien mukaan yritetty kuvata mitä taulut sisältävät). Kelan pyyntötiedoston opiskelijat on aluksi luettu tauluun, jotka muodostavat opiskelijajoukon, joiden tiedot kuuluvat edistymisen seurannan piiriin. Vain näiden opiskelijoiden tietoja käsitellään poiminnassa.

Ne pyyntötiedoston opiskelijat,  joita henkilötunnuksen avulla ei löydy Virta-tietovarannosta,  jäävät päivittämättä Kelalle lähetettävään vastaustiedostoon ja käsitellään Kelan toimesta erillistapauksina.


Alarajapäivä = Aloituspäivä = Korkeakouluopintojen aloituspäivä (tutkintoon johtavan koulutuksen aloituspäivä) .
Aloituspäivä on ensimmäisen läsnäololukukauden ensimmäinen päivä (1.8. tai 1.1.)  siirto-opiskelijoilla 1. korkeakoulun mukainen läsnäololukukauden ensimmäinen päivä.
Jos seurannassa oleva opiskelija on valmistunut, niin aloituspäiväksi poimitaan viimeisimmän valmistumisen valmistumiskuukautta seuraavan kuukauden ensimmäinen päivä. Muiden kuin tutkintoon johtavien koulutusten valmistumista ei huomioida valmistumisena.


KELAEDSE = kopio virta tietokannasta
KELA = kela kanta jossa tulokset käsitellään ja tallennetaan
kela.dbo.kela_edistymisen_tiedot_yhteen = seurattavat opiskelijat (hetut, nimet) vuoden edistymisenseurannassa
kela.dbo.kela_edistymisen_valmistuneet = opiskelijat jotka valmistuneet (tutkintoon)
kela_edistymisen_viimeisin_valmistumispvm = Viimeisin valmistuminen mistä tahansa korkeakoulusta
kela.dbo.kela_aikaisin_aloituspvm = laskettu alarajapäivämäärä opiskelijalle joka ei ole vielä valmistunut
kela.dbo.kela_opintopisteet_poimittu_per_hetu = laskettu opintopisteet seurantaajalle
rivinro = rivinro pyyntötiedostossa


  1. Valmistuneet (=>alarajapäivä)

    Poimitaan kaikki edistymisen seurannan joukosta valmistuneet.  Aloituspäivä määräytyy silloin ylläolevan mukaan eli valmistumisesta seuraavan kuukauden ensimmäinen päivä.

    sp_kela__poimi_edistymisen_valmistuneet 
    CREATE TABLE #kelaopiskelija (

		rivinro int,
		kk varchar(12),-- korkeakoulu
		ht varchar(11),--Henkilötunnus
		kirtu date,--Kirjoihintulopvm 	
		opevuosi float, --yhteensä viime lukuvuonna (esim. 1.8.2018 - 31.7.2019)	
		opeyhteensa float,	
		opoik date,--Opiskeluoikeuden alkaminen nykyisessä tutkinnossa	
		koulk varchar(6),--Koulutuskoodi		
		opiskelijaavain varchar(100),
		opiskeluoikeusavain varchar(100),	
		opiskelija_id int,
		opiskeluoikeus_id int,	
		aloituspvm  varchar(10), -- KELA alarajapäivä string
		aloituspvm_datum  date   -- KELA alarajapäivä date
)





-- Valmistunut joskus jostain, tutkintosuoritus, tutkintoon johtava opiskeluoikeus
INSERT INTO #kelaopiskelija (rivinro, ht, kk, opiskelija_id, opiskelijaavain)

SELECT DISTINCT Y.rivinro, H.Henkilotunnus, H.db, O.id, O.Avain 
FROM 	kela.dbo.kela_edistymisen_tiedot_yhteen Y
		JOIN KELAEDSE.dbo.Henkilo  H ON H.henkilotunnus = Y.henkilotunnus
		JOIN KELAEDSE.dbo.Opiskelija O ON O.henkiloid = H.id		
		JOIN KELAEDSE.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
		JOIN KELAEDSE.dbo.Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
		JOIN KELAEDSE.dbo.Opintosuoritus OS ON OS.OpiskelijaID = O.id AND OO.ID = OS.OpiskeluoikeusID
		JOIN KELAEDSE.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID AND OSL.Koodi = '1'				
WHERE 	OT.Koodi IN ( '1', '2', '3', '4', '6', '7' ) --Tutkinto oikeus, tutktinto suoriutus laji 1


CREATE NONCLUSTERED INDEX IX_kelaopiskelija_opiskelija_id ON #kelaopiskelija (opiskelija_id) INCLUDE (ht, rivinro);


-- Tulos
DELETE FROM kela.dbo.kela_edistymisen_valmistuneet


INSERT INTO kela.dbo.kela_edistymisen_valmistuneet(opiskelijaid,opiskelijaAvain,opiskeluoikeusid,opiskeluoikeusavain,OAlkamispaivamaara,kk,hetu,tutkinnonsuorituspvm,seuraava_alarajapaiva, rivinro )

SELECT 	T.opiskelija_id AS opiskelijaid
		, o.avain AS opiskelijaavain
		, oo.id AS opiskeluoikeusid
		, oo.avain	AS opiskeluoikeusavain	
		, oo.Alkamispaivamaara	AS OAlkamispaivamaara												
		, T.kk
		, T.ht hetu
		, OS.Suorituspaivamaara tutkinnonsuorituspvm
		, DATEADD(d, 1, EOMONTH(OS.Suorituspaivamaara)) AS seuraava_alarajapaiva
		, T.rivinro						
FROM 	#kelaopiskelija T 
		JOIN KELAEDSE.dbo.Opiskelija O ON O.ID = T.opiskelija_id
		JOIN KELAEDSE.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
		JOIN KELAEDSE.dbo.Opiskeluoikeuden_tyyppi ot on ot.ID=oo.Op_oikeuden_tyyppiID
		JOIN KELAEDSE.dbo.Opintosuoritus OS ON OS.OpiskelijaID = O.id AND OO.ID = OS.OpiskeluoikeusID
		JOIN KELAEDSE.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID AND OSL.Koodi = '1'									
WHERE 	ot.Koodi IN ( '1', '2', '3', '4', '6', '7' )


DELETE FROM kela.dbo.kela_edistymisen_viimeisin_valmistumispvm

-- Viimeisin valmistuminen mistä tahansa korkeakoulusta
INSERT INTO kela.dbo.kela_edistymisen_viimeisin_valmistumispvm ( hetu, seuraava_alarajapaiva )
SELECT hetu, max(seuraava_alarajapaiva) seuraava_alarajapaiva
FROM kela.dbo.kela_edistymisen_valmistuneet
GROUP BY hetu
ORDER BY seuraava_alarajapaiva

-- Ennen 1.8.2011 valmistuneet
UPDATE V
SET V.seuraava_alarajapaiva = '2011-08-01'
FROM kela.dbo.kela_edistymisen_viimeisin_valmistumispvm  V
WHERE V.seuraava_alarajapaiva < '2011-08-01'


DROP TABLE #kelaopiskelija

  2. Alarajapäivä (=>alarajapäivä)

    Alarajapäivän muodostus läsnäolon mukaan mikäli opiskelija ei ole valmistunut. Aloituspäivä ei saa olla aikaisempi kuin 1.8.2011 (jos on aikaisempi, ilmoitetaan 1.8.2011).

    sp_kela__poimi_edistymisen_aikaisin_aloituspvm 
    DECLARE @alarajapvm DATE
DECLARE @helpstr varchar(20)

SET @helpstr = YEAR(getdate())
SET @helpstr = @helpstr+'-08-01' 

SET @alarajapvm = @helpstr


CREATE TABLE #aloituspaivamaara (
			OOalkamispaivamaara date,
			Henkilotunnus varchar(12),
			kk varchar(10),
			OOAvain varchar(100),
			OTkoodi int,
			OpiskelijaAvain varchar(100)
			);

-- Ei valmistuneet. Valmistuneet ovat kela_edistymisen_viimeisin_valmistumispvm taulussa
INSERT INTO #aloituspaivamaara (OOalkamispaivamaara, Henkilotunnus, kk, OOAvain, OTkoodi, OpiskelijaAvain   )
SELECT 	 	DISTINCT OO.alkamispaivamaara, KE.Henkilotunnus, OO.db, OO.avain, OT.koodi, O.Avain
FROM		kela.dbo.kela_edistymisen_tiedot_yhteen KE
			JOIN KELAEDSE.dbo.Henkilo h ON h.henkilotunnus = KE.henkilotunnus
			JOIN KELAEDSE.dbo.Opiskelija O ON O.henkiloid = H.id
			JOIN KELAEDSE.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
			JOIN KELAEDSE.dbo.Opiskeluoikeuden_tyyppi ot on ot.ID=oo.Op_oikeuden_tyyppiID			
											-- Ei valmistuneita mukaan 					
WHERE   KE.henkilotunnus NOT IN (SELECT P.hetu FROM kela.dbo.kela_edistymisen_viimeisin_valmistumispvm  P WHERE  P.hetu = KE.henkilotunnus)
AND 	ot.Koodi IN ( '1', '2', '3', '4', '6', '7' )
AND 	OO.alkamispaivamaara < @alarajapvm

CREATE NONCLUSTERED INDEX IX_hetu ON #aloituspaivamaara (henkilotunnus)

 
-- Tulos
DELETE FROM kela.dbo.kela_aikaisin_aloituspvm

INSERT INTO kela_aikaisin_aloituspvm ( hetu, aikaisin_aloituspvm )
SELECT Henkilotunnus, min(OOalkamispaivamaara) OOalkamispaivamaara
FROM #aloituspaivamaara
GROUP BY Henkilotunnus
ORDER BY OOalkamispaivamaara
 


-- Ennen 2011-08-01 kaikki 2011-08-01
UPDATE A
SET A.aikaisin_aloituspvm = '2011-08-01'
FROM kela_aikaisin_aloituspvm A
WHERE A.aikaisin_aloituspvm < '2011-08-01'

-- Poissaolot ei tutkintoon johtava opiskeluoikeus (NOT IN) 
DELETE FROM  KELAEDSE.dbo.Lukukausi_ilmoittautuminen  
WHERE opiskeluoikeusid IN (
		SELECT DISTINCT LA.opiskeluoikeusid 
		FROM    KELAEDSE.dbo.Lukukausi_ilmoittautuminen LA 
					INNER JOIN KELAEDSE.dbo.Lukukausi_ilm_tila LTA ON LTA.id = LA.Lukukausi_ilmoittautumisen_tilaID
					INNER JOIN KELAEDSE.dbo.Opiskeluoikeus OO ON OO.id = LA.opiskeluoikeusid
					INNER JOIN KELAEDSE.dbo.Opiskeluoikeuden_tyyppi OT on ot.ID=oo.Op_oikeuden_tyyppiID	
		WHERE   LTA.Koodi IN ( '2' , '3' )
		AND     OT.Koodi NOT IN ( '1', '2', '3', '4', '6', '7' )   -- Ei  tutkintoon johtava oikeus jolla poissaolo 
)



-- Läsnäolot ei tutkintoon johtava
DELETE FROM KELAEDSE.dbo.Lukukausi_ilmoittautuminen 
WHERE opiskeluoikeusid IN (
	SELECT DISTINCT LA.opiskeluoikeusid 
	FROM    KELAEDSE.dbo.Lukukausi_ilmoittautuminen LA 
				INNER JOIN KELAEDSE.dbo.Lukukausi_ilm_tila LTA ON LTA.id = LA.Lukukausi_ilmoittautumisen_tilaID
				INNER JOIN KELAEDSE.dbo.Opiskeluoikeus OO ON OO.id = LA.opiskeluoikeusid
				INNER JOIN KELAEDSE.dbo.Opiskeluoikeuden_tyyppi OT on ot.ID=oo.Op_oikeuden_tyyppiID	
	WHERE   LTA.Koodi IN ( '1' )
	AND     OT.Koodi NOT IN ( '1', '2', '3', '4', '6', '7' )   -- Ei  tutkintoon johtava oikeus jolla poissaolo  
)

 



-- Kiinnostaa onko ollut poissa
CREATE TABLE #poissaolo_pvm (
	Poissaalkamispaivamaara date,
	henkilotunnus varchar(12),
	kk varchar(10)
	)

 
INSERT INTO #poissaolo_pvm( Poissaalkamispaivamaara, henkilotunnus, kk )
SELECT 	 	DISTINCT min(L.alkamispaivamaara), KE.Henkilotunnus, O.db 

FROM		kela.dbo.kela_edistymisen_tiedot_yhteen KE
			
			JOIN #aloituspaivamaara A ON A.Henkilotunnus  = KE.henkilotunnus
			
			JOIN KELAEDSE.dbo.Henkilo h ON h.henkilotunnus = KE.henkilotunnus
			JOIN KELAEDSE.dbo.Opiskelija O ON O.henkiloid = H.id

			JOIN KELAEDSE.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
			JOIN KELAEDSE.dbo.Lukukausi_ilm_tila LT ON LT.id = L.Lukukausi_ilmoittautumisen_tilaID
			
			
WHERE   LT.Koodi IN ( '2' , '3' ) 
AND		L.alkamispaivamaara < @alarajapvm
AND 	L.alkamispaivamaara <= A.OOalkamispaivamaara

-- Aloituspäiväksi ei saa ilmoittaa muun kuin tutkintoon johtavan koulutuksen aloituspäivää
AND 	O.avain IN ( SELECT A.OpiskelijaAvain FROM #aloituspaivamaara A  WHERE A.Henkilotunnus = KE.Henkilotunnus AND O.db = A.kk AND A.OTkoodi IN ( '1', '2', '3', '4', '6', '7' )   )

GROUP BY  KE.Henkilotunnus, O.db 

CREATE NONCLUSTERED INDEX IX_poissaolo_pvm_hetu ON #poissaolo_pvm (henkilotunnus)


-- Millon ensimmäinen läsnäolo	
CREATE TABLE #lasnaolo_pvm (
	LasnaAlkamispaivamaara date,
	henkilotunnus varchar(12) 
	 
	)

 
INSERT INTO #lasnaolo_pvm( LasnaAlkamispaivamaara, henkilotunnus )

SELECT 	 	DISTINCT min(L.alkamispaivamaara), KE.Henkilotunnus 

FROM		kela.dbo.kela_edistymisen_tiedot_yhteen KE
			JOIN KELAEDSE.dbo.Henkilo h ON h.henkilotunnus = KE.henkilotunnus
			JOIN KELAEDSE.dbo.Opiskelija O ON O.henkiloid = H.id
			JOIN KELAEDSE.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
			JOIN KELAEDSE.dbo.Lukukausi_ilm_tila LT ON LT.id = L.Lukukausi_ilmoittautumisen_tilaID

WHERE	KE.Henkilotunnus IN  ( SELECT P.henkilotunnus FROM #poissaolo_pvm P WHERE P.henkilotunnus = KE.henkilotunnus )
AND		LT.Koodi IN ( '1' )
AND		L.alkamispaivamaara < @alarajapvm

-- Aloituspäiväksi ei saa ilmoittaa muun kuin tutkintoon johtavan koulutuksen aloituspäivää
AND 	O.avain IN ( SELECT A.OpiskelijaAvain FROM #aloituspaivamaara A  WHERE A.Henkilotunnus = KE.Henkilotunnus AND O.db = A.kk AND A.OTkoodi IN ( '1', '2', '3', '4', '5', '6', '7' )   )
GROUP BY  KE.Henkilotunnus 

CREATE NONCLUSTERED INDEX IX_lasnaolo_pvm_hetu ON #lasnaolo_pvm (henkilotunnus)


UPDATE A
SET A.aikaisin_aloituspvm =  L.LasnaAlkamispaivamaara
FROM kela_aikaisin_aloituspvm A
			JOIN #lasnaolo_pvm L ON L.henkilotunnus = A.hetu
WHERE L.LasnaAlkamispaivamaara < @alarajapvm

-- Varmuuden vuoksi vielä
UPDATE A
SET A.aikaisin_aloituspvm = '2011-08-01'
FROM kela_aikaisin_aloituspvm A
WHERE A.aikaisin_aloituspvm < '2011-08-01'


CREATE TABLE #ei_loydy ( 
		henkilotunnus varchar(12) 
		)
		
INSERT INTO #ei_loydy (henkilotunnus)
SELECT DISTINCT Y.Henkilotunnus
FROM		kela.dbo.kela_edistymisen_tiedot_yhteen Y
	WHERE  Y.Henkilotunnus 
					NOT IN (SELECT A.hetu FROM kela_aikaisin_aloituspvm A WHERE A.hetu = Y.Henkilotunnus)
					
AND Y.henkilotunnus 
					NOT IN (SELECT P.hetu FROM kela.[dbo].[kela_edistymisen_viimeisin_valmistumispvm] P WHERE  P.hetu = Y.henkilotunnus) 	
	

UPDATE A				
SET	 A.aikaisin_aloituspvm	 = (convert(varchar(4), YEAR(A.aikaisin_aloituspvm)) +  CASE WHEN MONTH(A.aikaisin_aloituspvm)>=8 THEN '-08-01' ELSE '-01-01' END)
FROM kela.dbo.kela_aikaisin_aloituspvm A 



DROP TABLE #aloituspaivamaara 
DROP TABLE #poissaolo_pvm 
DROP TABLE #lasnaolo_pvm 
DROP TABLE #ei_loydy

  3. Opintopisteet (=> Opintopisteet 1 (= koko opiskeluajalta) ja Opintopisteet 2 (=seurantalukuvuoden, ts. edellisen lukuvuoden pisteet))

    Lasketaan opintopisteet rajapäivämäärien välisenä aikana.  Opintopisteet lasketaan lehtitasolta, kaikki Virta opintosuorituslaji 2 ja laji 3 suoritukset. 
    Lehtitaso = Opintosuoritukseen ei sisälly muita opintosuorituksia.

    Lasketaan opintopisteet suorituspäivämäärän mukaan. Hyväksiluetut joissa lähde on suomalainen korkeakoulu ei lasketa mukaan.
    Alkuperäisen suoritustiedon tulisi löytyä suorituksen alkuperäisen korkeakoulun tuomana Virrasta, ei hyväksiluettuna alkuperäisenä suorituksena.
    Mikäli opintosuorituksen suorituspäivämäärä on seurannan rajapäivämäärien välisenä aikana tulee sen opintopisteet mukaan pistelaskentaan.

    sp_kela__poimi_edistymisen_opintopisteet_per_hetu 
    DECLARE @Ylarajapvm DATE
DECLARE @AlarajapvmViimevuosi DATE

DECLARE @helpstr varchar(20)
DECLARE @helpstrAiempivuosi varchar(20)

SET @helpstr = YEAR(getdate())
SET @helpstr = @helpstr+'-07-31' 

-- esim. syksyllä 2019 on  2019 
SET @Ylarajapvm = @helpstr

SET @helpstrAiempivuosi = YEAR(getdate())-1
SET @helpstrAiempivuosi = @helpstrAiempivuosi+'-08-01' 

--  esim. syksyllä 2019 aiempi 2018 
SET @AlarajapvmViimevuosi = @helpstrAiempivuosi


CREATE TABLE #kela_op ( 
		rivinro int,
		henkilotunnus varchar(12),
		kk varchar(12),
		opiskelijaid int,
		OpiskelijaAvain varchar(100),
		alarajapvm date,
		op1 float,
		op2 float
)


CREATE TABLE #kelapisteet (
		opiskelija_id int not null,		 
		opintosuoritus_id int not null,
		laajuus float not null,
		suoritus_pvm date not null		
)
		
		
-- Pyyntötiedoston kela_edistymisen_tiedot_yhteen haetaan opiskelijat		
INSERT INTO #kela_op ( henkilotunnus, kk, opiskelijaid, OpiskelijaAvain )
SELECT H.Henkilotunnus, H.db, O.id, O.Avain 
FROM KELAEDSE.dbo.Henkilo  H
		JOIN KELAEDSE.dbo.Opiskelija O ON O.henkiloid = H.id

WHERE H.Henkilotunnus IN 
(			SELECT [henkilotunnus]
			FROM [kela].[dbo].[kela_edistymisen_tiedot_yhteen] 
 )

-- Valmistuneet kela_edistymisen_viimeisin_valmistumispvm  seuraava_alarajapaiva on alarajapvm
UPDATE K
SET K.alarajapvm = V.seuraava_alarajapaiva
FROM #kela_op K
	JOIN kela.dbo.kela_edistymisen_viimeisin_valmistumispvm V ON V.hetu = K.henkilotunnus

	
-- Loput ei valmistuneet, aikaisin mahdollinen aloituspvm alarajapvm
UPDATE K
SET K.alarajapvm = A.aikaisin_aloituspvm
FROM #kela_op K
	JOIN kela.dbo.kela_aikaisin_aloituspvm A ON A.hetu = K.henkilotunnus
WHERE K.alarajapvm IS NULL


-- Rivinro missä hetu pyyntötiedostosta löytyy
UPDATE K
SET K.rivinro = Y.rivinro
FROM #kela_op K
	JOIN kela.dbo.kela_edistymisen_tiedot_yhteen Y ON Y.henkilotunnus = K.henkilotunnus

 
-- Pistelaskenta alkaa ...				
DELETE FROM  #kelapisteet 

INSERT INTO #kelapisteet ( opiskelija_id, opintosuoritus_id, laajuus, suoritus_pvm )
SELECT	   opiskelija_id = s.OpiskelijaID
		,  opintosuoritus_id = s.ID
		,  laajuus = s.Laajuus
		,  suoritus_pvm = s.Suorituspaivamaara	
FROM KELAEDSE.dbo.Opintosuoritus s
	INNER JOIN KELAEDSE.dbo.Opintosuorituksen_laji sl ON sl.ID=s.Opintosuorituksen_lajiID AND sl.Koodi IN ('2','3')  --muu opintosuoritus, Kela suoritus	
WHERE   s.opiskelijaid IN (SELECT T.opiskelijaid FROM #kela_op T  WHERE T.alarajapvm IS NOT NULL )		
		
-- Lehtitaso = ei sisällä muita suorituksia
AND s.ID NOT IN (SELECT ss.OpintosuoritusID FROM KELAEDSE.dbo.Op_suor_sisaltyvyys ss)		
					
-- Hyväksiluettu? Toivottavasti on rekisteröity lähdekorkeakoulussa
AND S.ID NOT IN ( SELECT sh.OpintosuoritusID 
					FROM KELAEDSE.dbo.Opsuorituksen_hyvaksiluku sh 
					
					-- Ei hyväksiluettuja jos lähde kotimainen korkeakoulu
					-- Oletetaan että lähdekorkeakoulu on rekistöröinnyt suorituksen
					 WHERE  sh.OpintosuoritusID   IN (					
								SELECT OSORG.OpintosuoritusID	
								FROM 	KELAEDSE.dbo.Op_suorituksen_organisaatio OSORG
										JOIN KELAEDSE.dbo.organisaatio ORG ON ORG.ID = OSORG.OrganisaatioID
										JOIN KELAEDSE.dbo.Organisaation_rooli ORGROL ON ORGROL.id = OSORG.Organisaation_rooliID										
								WHERE  
								 ORGROL.koodi = 3   -- Lähde
								AND	ORG.koodi IN ( SELECT Organisaatio_koodi FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi   WHERE Oppilaitostyyppi_koodi IN ('41','42')  
-- 41, 42 YO ja AMK tyypin oppilaitos
)								
					)				
				)
	
CREATE NONCLUSTERED INDEX IX_pisteet_opiskelija_id ON #kelapisteet (opiskelija_id)
	INCLUDE ( opintosuoritus_id, laajuus, suoritus_pvm)
	;	 
	
	
-- Alaraja ja yläraja edellinen lukuvuosi, esim. 1.8.2018  -  31.7.2019
UPDATE T
SET op2 = (	SELECT sum(laajuus) 
					FROM  #kelapisteet s 
					WHERE 		s.opiskelija_id=T.opiskelijaid
					AND s.suoritus_pvm >= @AlarajapvmViimevuosi AND s.suoritus_pvm <= @Ylarajapvm
					AND s.suoritus_pvm >=  T.alarajapvm
					
					GROUP BY s.opiskelija_id )
FROM #kela_op T

--  Esim. vuonna 2019 yläraja on 31.7.2019	
UPDATE T
SET op1= (	SELECT sum(laajuus) 
					FROM  #kelapisteet s 
					WHERE 		s.opiskelija_id=T.opiskelijaid
					AND 		s.suoritus_pvm >= T.alarajapvm AND s.suoritus_pvm <= @Ylarajapvm					 
					
					GROUP BY s.opiskelija_id )								
FROM #kela_op T

-- Jos ei mitään on 0
UPDATE T
SET op1 = 0 
FROM #kela_op T
WHERE op1 IS NULL

UPDATE T
SET op2 = 0 
FROM #kela_op T
WHERE op2 IS NULL

-- Tulos
DELETE FROM [dbo].[kela_opintopisteet_poimittu_per_hetu]

INSERT INTO [dbo].[kela_opintopisteet_poimittu_per_hetu]
           (
		    rivinro
		   ,[henkilotunnus]
		   ,alarajapvm
           ,[opintopisteet1]
           ,[opintopisteet2]
           ,[opintopisteet1_num]
           ,[opintopisteet2_num]
           )


SELECT	  rivinro
		, henkilotunnus
		, alarajapvm
		
		, RIGHT('000' + ISNULL(CAST(CEILING(ROUND(ROUND(SUM(op1),1),0)) AS varchar(8)),''), 3) AS opintopisteet1
		, RIGHT('000' + ISNULL(CAST(CEILING(ROUND(ROUND(SUM(op2),1),0)) AS varchar(8)),''), 3) AS opintopisteet2
		, CEILING(ROUND(ROUND(SUM(op1),1),0)) opintopisteet1_num
		, CEILING(ROUND(ROUND(SUM(op2),1),0)) opintopisteet2_num

FROM #kela_op
GROUP BY henkilotunnus,rivinro,alarajapvm
ORDER BY rivinro, henkilotunnus,alarajapvm		   


-- Jos aloituspäivä (alarajapäivä) on tyhjä tai myöhempi 31.7. kuluva vuosi, opintopisteitä ei ilmoiteta (tieto on tyhjää)
UPDATE K
SET K.opintopisteet1 = NULL,
	K.opintopisteet2 = NULL,
	K.[opintopisteet1_num] = NULL,
	K.[opintopisteet2_num] = NULL 
	
FROM kela_opintopisteet_poimittu_per_hetu K
WHERE K.alarajapvm IS NULL

-- Jos alaraja esim. 31.7.2019 jälkeen,   1.8.2019 ei pisteitä
UPDATE K
SET K.opintopisteet1 = NULL,
	K.opintopisteet2 = NULL,
	K.[opintopisteet1_num] = NULL,
	K.[opintopisteet2_num] = NULL 
	
FROM kela_opintopisteet_poimittu_per_hetu K
WHERE K.alarajapvm > @Ylarajapvm

-- Jos opintopisteet on yli 1000 op => 999, vain 3 merkkiä varattu tilaa Kelatietuemallissa
UPDATE K
SET K.opintopisteet1 = '999'
FROM kela_opintopisteet_poimittu_per_hetu K
WHERE K.opintopisteet1_num > 1000

UPDATE K
SET K.opintopisteet2 = '999'
FROM kela_opintopisteet_poimittu_per_hetu K
WHERE K.opintopisteet2_num > 1000

 

DROP TABLE #kela_op
DROP TABLE #kelapisteet


Alkuun


















  • No labels