View Source

Opetushallituksen liikkuvuusjaksotiedonkeruu

Kansainvälistä opiskelijaliikkuvuutta koskevan tilastotiedon keruu suomalaisista korkeakouluista
- Opetushallituksen tiedonkeruussa kerätään vuosittain tiedot yliopisto- ja ammattikorkeakouluopiskelijoiden kansainvälisistä liikkuvuusjaksoista.
- Tiedot julkaistaan Opetushallinnon tilastopalvelu Vipusessa.
- Tiedot opiskelijoiden kansainvälisistä liikkuvuusjaksoista poimitaan VIRTA-opintotietopalvelusta vuosittain maaliskuussa, jolloin poimitaan edellisen kalenterivuoden aikana alkaneet liikkuvuusjaksot.
- OPH:n liikkuvuustiedonkeruusta lähetetään korkeakouluille vuosittain syksyllä tiedote.
- Lisätietoja Tiedonkeruun käsikirjasta: https://wiki.eduuni.fi/x/HiMPCQ
Aikataulu
- Tiedot päivittyvät sivulle https://wiki.eduuni.fi/x/4DCpC
Tiedonkeruun muutokset koskien vuoden 2022 tietoja
- Liikkuvuusohjelmatieto kerätään vuonna 2022 alkavista liikkuvuusjaksoista myös lyhytkestoisista liikkuvuusjaksoista.
- Tiedonkeruussa kerätään tiedot myös osittain virtuaalisista liikkuvuusjaksoista (ns. blended mobility), joissa fyysiseen liikkuvuusjaksoon yhdistyy virtuaalisesti toteutettuja osioita.
  - Näiden kohdalla opiskelijan kansainväliseksi liikkuvuusjaksoksi alku- ja loppupäivän mukaan kirjataan vain fyysinen liikkuvuusjakso eli jakso, jonka opiskelija on fyysisesti toisessa maassa. Lisäksi osoitetaan liikkuvuuden luokitteluarvolla "b" (blended), että liikkuvuusjaksoon kuuluu myös virtuaalisesti toteutettu(ja) osio(ita). Osittain virtuaalisten liikkuvuusjaksojen kirjaaminen on uusi käytäntö ja ensimmäistä kertaa voimassa vuonna 2022 alkavien liikkuvuusjaksojen kohdalla, jotka raportoidaan vuonna 2023.

OPH:n liikkuvuustiedonkeruissa (kaikissa neljässä), haetaan opiskelijan ensisijainen opiskeluoikeus kyseiselle liikkuvuusjaksolle.

Pitkäkestoiset lähtevät liikkuvuusjaksot (väh. 3 kk)
Kerättävä tieto	Vanha tiedon muoto/koodisto	Tieto VIRTA-opintotietopalvelussa (tietovarannon koodistot)	Tiedonsiirtoskeeman tieto VIRTA:ssa
yliopiston tunnus TAI ammattikorkeakoulun numero	YO: TK:n 2-numeroinen yliopistokoodisto* / AMK: TK:n 5-numeroinen oppilaitoskoodisto	Organisaatio -> Oppilaitoskoodisto (5-numeroinen)	OrganisaatioKoodiTyyppi
opiskelijan henkilö- tai muu tunnus	vapaateksti	Henkilötunnus - jollei henkilötunnusta löydy, käytetään opiskelijaan liittyvää avainta.	Opiskelija.Henkilotunnus Opiskelija.avain
sukupuoli	1, 2, tai 9	Sukupuoli	Opiskelija.Sukupuoli 1,2 ja 9
koulutusala	1 - 12	Koulutusluokitus OKM korkeakoulujen ohjauksen alat	Opiskeluoikeus.koulutusala
koulutuskoodi	TK:n 6-numeroinen	Koulutusluokitus Tutkintonimikekoodi	Opiskeluoikeus.Jakso.Koulutuskoodi
opintoaste (vain YO)	1, 2 tai 3	Opiskeluoikeuden tyyppi (YO)	Opiskeluoikeus.tyyppi 1,2,3,4,6,7 Lähtevien saapuneiden vaihto-opiskelijoiden kohdalla, joilla on opiskeluoikeus.tyyppi 9 Liikkuvuusjakso.opiskeluoikeustyyppi 1,2,3,4,6,7
koulutuksen tyyppi (vain AMK)	1,2 tai 6	Opiskeluoikeuden tyyppi (AMK)	Opiskeluoikeus.tyyppi 1,2,3,4,6,7 Lähtevien saapuneiden vaihto-opiskelijoiden kohdalla, joilla on opiskeluoikeus.tyyppi 9 Liikkuvuusjakso.opiskeluoikeustyyppi 1,2,3,4,6,7
liikkuvuusohjelma	01-16 tai 99	Liikkuvuusohjelma	Liikkuvuusohjelma
kohdemaa	000-999	Maa	MaatunnusKoodiTyyppi
liikkuvuuden kesto kuukausina	3-n	Liikkuvuusjakson alkamispäivämäärän ja päättymispäivämäärän erotus - Vaihdon tulee olla kestänyt vähintään 87 päivää, jotta tulee poimituksi. Kokonaiskesto raportoidaan päivinä ja vaihdon aloituspäivämäärä raportointivuoden mukaan.	Pvmtyyppi AlkuPvm, LoppuPvm
liikkuvuuden tyyppi	1, 2	Tyyppi	LiikkuvuusTyyppiKoodiTyyppi 1,2
liikkuvuuden suunta	1, 2	Suunta	LiikkuvuusSuuntaKoodiTyyppi 1
ikä
aloitusvuosi
liikkuvuuden luokittelu	b	Luokittelu	Luokittelu

Pitkäkestoiset saapuvat liikkuvuusjaksot (väh. 3 kk)
Kerättävä tieto	Tiedon muoto/koodisto	Tieto VIRTA-opintotietopalvelussa (tietovarannon koodistot)	Tiedonsiirtoskeeman tieto VIRTA:ssa
yliopiston tunnus TAI ammattikorkeakoulun numero	YO: TK:n 2-numeroinen yliopistokoodisto* / AMK: TK:n 5-numeroinen oppilaitoskoodisto	Organisaatio -> Oppilaitoskoodisto (5-numeroinen)	OrganisaatioKoodiTyyppi
opiskelijan henkilö- tai muu tunnus	vapaateksti	Henkilötunnus - jollei henkilötunnusta löydy, käytetään opiskelijaan liittyvää avainta.	Opiskelija.Henkilotunnus Opiskelija.avain
sukupuoli	1, 2, tai 9	Sukupuoli	Opiskelija.Sukupuoli 1,2 ja 9
koulutusala	1 - 12	Koulutusluokitus OKM korkeakoulujen ohjauksen alat	Opiskeluoikeus.koulutusala
koulutuskoodi	999999 tai tyhjä	Koulutusluokitus Tutkintonimikekoodi	Opiskeluoikeus.Jakso.Koulutuskoodi
opintoaste (vain YO)	1, 2 tai 3	Opiskelijan Opiskeluoikeuden tyyppi Liikkuvuusjakson Opiskeluoikeuden tyyppi	Opiskeluoikeustyyppi 9 Liikkuvuusjakso.opiskeluoikeustyyppi 1,2,3,4,6,7
koulutuksen tyyppi (vain AMK)	1,2 tai 6	Opiskelijan Opiskeluoikeuden tyyppi Liikkuvuusjakson Opiskeluoikeuden tyyppi	Opiskeluoikeustyyppi 9 Liikkuvuusjakso.opiskeluoikeustyyppi 1,2,3,4,6,7
liikkuvuusohjelma	01-16 tai 99	Liikkuvuusohjelma	Liikkuvuusohjelma
kansalaisuus	000-999	Maat ja valtiot	Opiskelija.Kansalaisuus
lähtömaa	000-999	Maa	MaatunnusKoodiTyyppi
liikkuvuuden kesto kuukausina	3-n	Liikkuvuusjakson AlkuPvm, LiikkuvuusjaksonLoppuPvm - Vaihdon tulee olla kestänyt vähintään 87 päivää, jotta tulee poimituksi. Kokonaiskesto raportoidaan päivinä ja vaihdon aloituspäivämäärä raportointivuoden mukaan.	PvmTyyppi
liikkuvuuden tyyppi	1, 2	Tyyppi	Tyyppi LiikkuvuusTyyppiKoodiTyyppi
liikkuvuuden suunta	1, 2	Suunta	LiikkuvuusSuuntaKoodiTyyppi 2
ikä
aloitusvuosi
liikkuvuuden luokittelu	b	Luokittelu	Luokittelu

Lyhytkestoiset lähtevät liikkuvuusjaksot (1 vk - alle 3 kk)
Kerättävä tieto	Tiedon muoto/koodisto	Tieto VIRTA-opintotietopalvelussa (tietovarannon koodistot)	Tiedonsiirtoskeeman tieto VIRTA:ssa
yliopiston tunnus TAI ammattikorkeakoulun numero	YO: TK:n 2-numeroinen yliopistokoodisto* / AMK: TK:n 5-numeroinen oppilaitoskoodisto	Organisaatio -> Oppilaitoskoodisto (5-numeroinen)	OrganisaatioKoodiTyyppi
opiskelijan henkilö- tai muu tunnus	vapaateksti	Henkilötunnus - jollei henkilötunnusta löydy, käytetään opiskelijaan liittyvää avainta.	Opiskelija.Henkilotunnus Opiskelija.avain
sukupuoli	1, 2, tai 9	Sukupuoli	Opiskelija.Sukupuoli 1,2 ja 9
koulutusala	1 - 12	Koulutusluokitus OKM korkeakoulujen ohjauksen alat	Opiskeluoikeus.koulutusala
koulutuskoodi	TK:n 6-numeroinen	Koulutusluokitus Tutkintonimikekoodi	Opiskeluoikeus.Jakso.Koulutuskoodi
opintoaste (vain YO)	1, 2 tai 3	Opiskeluoikeuden tyyppi (YO)	Opiskeluoikeus.Tyyppi 1,2,3,4,6,7 Lähtevien saapuneiden vaihto-opiskelijoiden kohdalla, joilla on opiskeluoikeus.tyyppi 9 Liikkuvuusjakso.opiskeluoikeustyyppi 1,2,3,4,6,7
koulutuksen tyyppi (vain AMK)	1,2 tai 6	Opiskeluoikeuden tyyppi (AMK)	Opiskeluoikeus.Tyyppi 1,2,3,4,6,7, Lähtevien saapuneiden vaihto-opiskelijoiden kohdalla, joilla on opiskeluoikeus.tyyppi 9 Liikkuvuusjakso.opiskeluoikeustyyppi 1,2,3,4,6,7
kohdemaa	000-999	Maa	MaatunnusKoodiTyyppi
liikkuvuuden kesto viikkoina	1-	Liikkuvuusjakson AlkuPvm, Liikkuvuusjakson LoppuPvm - Kokonaiskesto raportoidaan päivinä ja vaihdon aloituspäivämäärä määrää raportointivuoden.	PvmTyyppi
liikkuvuuden tyyppi	1, 2	Tyyppi	Tyyppi LiikkuvuusTyyppiKoodiTyyppi
liikkuvuuden suunta	1,2	Suunta	LiikkuvuusSuuntaKoodiTyyppi 1
ikä
aloitusvuosi
liikkuvuusohjelma	01-16 tai 99	Liikkuvuusohjelma	Liikkuvuusohjelma
liikkuvuuden luokittelu	b	Luokittelu	Luokittelu

Lyhytkestoiset saapuvat liikkuvuusjaksot (1 vk - alle 3 kk)
Kerättävä tieto	Tiedon muoto/koodisto	Tieto VIRTA-opintotietopalvelussa (tietovarannon koodistot)	TTiedonsiirtoskeeman tieto VIRTA:ssa
yliopiston tunnus TAI ammattikorkeakoulun numero	YO: TK:n 2-numeroinen yliopistokoodisto* / AMK: TK:n 5-numeroinen oppilaitoskoodisto	Organisaatio -> Oppilaitoskoodisto (5-numeroinen)	OrganisaatioKoodiTyyppi
opiskelijan henkilö- tai muu tunnus	vapaateksti	Henkilötunnus - jollei henkilötunnusta löydy, käytetään opiskelijaan liittyvää avainta.	Opiskelija.Henkilotunnus Opiskelija.avain
sukupuoli	1, 2, tai 9	Sukupuoli	Opiskelija.Sukupuoli 1,2 ja 9
koulutusala	1 - 12	Koulutusluokitus OKM korkeakoulujen ohjauksen alat	Opiskeluoikeus.koulutusala
koulutuskoodi ei koske saapuvia	999999 tai tyhjä	Koulutusluokitus Tutkintonimikekoodi	Opiskeluoikeus.Jakso.Koulutuskoodi
opintoaste (vain YO)	1, 2 tai 3	Opiskelijan Opiskeluoikeuden tyyppi Liikkuvuusjakson Opiskeluoikeuden tyyppi (YO)	Opiskeluoikeustyyppi 9 Liikkuvuusjakso.opiskeluoikeustyyppi 1,2,3,4,6,7
koulutuksen tyyppi (vain AMK)	1,2 tai 6	Opiskelijan Opiskeluoikeuden tyyppi Liikkuvuusjakson Opiskeluoikeuden tyyppi (AMK)	Opiskeluoikeustyyppi 9 Liikkuvuusjakso.opiskeluoikeustyyppi 1,2,3,4,6,7
lähtömaa	000-999	Maa	MaatunnusKoodiTyyppi
liikkuvuuden kesto viikkoina	1-	Liikkuvuusjakson AlkuPvm, Liikkuvuusjakson LoppuPvm - Kokonaiskesto raportoidaan päivinä ja vaihdon aloituspäivämäärä määrää raportointivuoden.	PvmTyyppi
liikkuvuuden tyyppi	1, 2	Tyyppi	LiikkuvuusTyyppiKoodiTyyppi
liikkuvuuden suunta	1,2	Suunta	LiikkuvuusSuuntaKoodiTyyppi 2
ikä
aloitusvuosi
liikkuvuusohjelma	01-16 tai 99	Liikkuvuusohjelma	Liikkuvuusohjelma
liikkuvuuden luokittelu	b	Luokittelu	Luokittelu

*Paitsi Taideyliopisto, joka menee amk-mallin mukaan

CIMO-tiedostot poimintaehdot

CIMO lyhyt saapuva:

 -- CIMO_lyhyt_saapuva:

DECLARE    @vuosi varchar(4)
SET @vuosi = '2016'

create table #param (
    vuosi varchar(4),
)
;
insert into #param (vuosi)
    select
    @vuosi as vuosi
;

-- Temp-taulu tiedonkeruun tietoja varten
create table #cimopisk (
    oppilaitos varchar(5), -- Oppilaitoskoodi
    hetu varchar(100), --henkilö- tai muu tunnus
    sp varchar(1), -- sukupuoli
    koulutusala varchar(4), -- koulutusala
    opintoaste varchar(1), -- (vain YO)
    lahtomaa varchar(3),
    kesto varchar(4), --liikkuvuuden kesto
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK)
    tyyppi varchar(2), --liikkuvuuden tyyppi
    --
    jakso_alkupvm date,
    jakso_loppupvm date,
    koulutuskoodi varchar(6),
    --
    opiskelijaavain varchar(100),
    liikkuvuusjaksoavain varchar(100),
    opiskeluoikeusavain varchar(100),
    opiskelija_id int,
    liikkuvuusjakso_id int,
    opiskeluoikeus_id int
)
;
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskelija_id ON #cimopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_liikkuvuusjakso_id ON #cimopisk (liikkuvuusjakso_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskeluoikeus_id ON #cimopisk (opiskeluoikeus_id)
;

print convert(varchar,getdate(),120)+' CREATE temp-taulu param ja cimopisk'
;

---
-- Lisätään perustiedot (henkilö, opiskelija)
-- * täytyy olla liikkuvuusjakso joka on
--   - jonka (liikkuvuuden )suunta on 2=Saapuva
--   - kestänyt enintään [5,7] ja vähintään 86 (87-1) päivää
-- * jakson aloituspäivämäärän vuosi on tilastovuosi
---

INSERT into #cimopisk (
    -- kaikki sarakkeet (kopioitavaksi)
    oppilaitos,hetu,sp,koulutusala,opintoaste,lahtomaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,lahtomaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = lj.OpiskelijaID
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = OO.Op_oikeuden_tyyppiID  -- KV vaihto tyyppi , Ei Liikkuvuusjakson opiskeluoikeustyyppi
    
WHERE 1=1
AND otk.koodi = '9' -- Kansainvälinen vaihto

-- suunta 2=Saapuva
and 2 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- enintään [5,7] ja vähintään 86 päivää
and coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
    between 5 and 86
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)


-- Saapuva jolla on myös tutkinto-oikeus on double degree?
AND    p.id NOT IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','5','6','7','14','15','19')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')
            and year(lj.Alkamispaivamaara) = (select vuosi from #param)
            )
;

print convert(varchar,getdate(),120)+' liikkuvuus- ja opiskelijatiedot'
;


-- Lyhyt saapuva ilman opiskeluoikeutta

INSERT into #cimopisk (    
    oppilaitos,hetu,sp,koulutusala,opintoaste,lahtomaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT  DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,lahtomaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
        inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
        inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID    
WHERE 
        p.ID NOT IN (SELECT C.opiskelija_id FROM #cimopisk C)

-- suunta 2=Saapuva
and 2 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- enintään [5,7] ja vähintään 86 päivää
and coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
    between 5 and 86
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)

-- Saapuva jolla on myös tutkinto-oikeus on double degree?
AND    p.id NOT IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','5','6','7','14','15','19')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')
            and year(lj.Alkamispaivamaara) = (select vuosi from #param)
            )

;

---
-- Opiskeluoikeustiedot
---
/*
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
*/

-- valitaan yksi per opiskelija, jos ei löytynyt jo ID yllä!
update T
set opiskeluoikeus_id=o.ID, opiskeluoikeusavain=o.avain
from #cimopisk T
inner join virta.dbo.Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
inner join virta.dbo.Op_oikeuden_ensisijaisuus oe on oe.OpiskeluoikeusID=o.ID
where 1=1
-- ensisijainen alkamispäivänä
and T.jakso_alkupvm between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-9-9')
;

-- koulutuskoodi
update T
set koulutuskoodi=(select k.Koodi from virta.dbo.Tutkintonimike k where k.ID=oj.TutkintonimikeID)
from #cimopisk T
inner join virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
where T.opiskeluoikeus_id is not null
-- jakso voimassa alkupäivänä
and T.jakso_alkupvm between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
;

-- koulutusala
-- nb! OKM:n ohjauksen ala
update T
set koulutusala=k.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID AND OO.ID = T.opiskeluoikeus_id
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID    
where T.opiskeluoikeus_id is not null   AND T.opiskelija_id is not null  
AND K.Versio = 'ohjausala'
;

update T
set koulutusala=k.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID    
where T.koulutusala is null  AND T.opiskelija_id is not null  
AND K.Versio = 'ohjausala'
;



-- opintoaste, koultyp
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L on L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.opiskeluoikeus OO ON T.opiskeluoikeus_id = OO.id
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = L.Opiskeluoikeuden_tyyppiID 
where T.opiskeluoikeus_id is not null
AND     T.opiskeluoikeusAvain = OO.avain
AND     L.avain = T.liikkuvuusjaksoavain
;

-- Jos opintoaste on null edellisen jälkeen
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk ON otk.ID = L.Opiskeluoikeuden_tyyppiID
where T.opintoaste is null AND T.opiskeluoikeus_id is null 
;

-- Jos koultyp on null edellisen jälkeen, ei pitäisi vaikuttaa yliopistoihin mikäli ei ole väärällä tyypillä tuotu
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk ON otk.ID = L.Opiskeluoikeuden_tyyppiID
where T.koultyp is null AND T.opiskeluoikeus_id is null 
;

---
-- TULOS
---
select * from #cimopisk T
order by hetu

Kerätään kaikki liikkuvuusjaksot joiden opiskeluoikeus tyyppi on 9 (=Kansainvälinen vaihto) tauluun #cimopisk: oppilaitos, hetu, sukupuoli, liikkuvuusohjelma, kansalaisuus, lähtömaa, liikkuvuuden kesto, liikkuvuuden tyyppi, liikkuvuuden alkamispvm, liikkuvuuden päättymispvm

Liikkuvuuden suunta 2 (=Saapuva)
Liikkuvusjaksot pituudeltaan 5 - 86 päivää.
Liikkuvusjakso alkamispäivämäärä tilastovuonna (nyt tilastovuosi 2016)
Saapuvalla opiskelijalla ei saa olla tutkintoon johtavaa opiskeluoikeutta saman aikaisesti liikkuvuusjakson kanssa ao. korkeakoulussa. Eikä erikoistumiskoulutus tai opettajakoulutus-oikeutta.

Kerätään mukaan myös kaikki opiskelijat joilla ei ole opiskeluoikeutta mutta on olemaassa ajalta liikkuuvuusjaksoja.
Liikkuvuuden suunta 2 (=Saapuva)
Liikkuvusjaksot pituudeltaan 5 - 86 päivää.
Liikkuvusjakso alkamispäivämäärä tilastovuonna (jakson aloituspäivämäärän vuosi on tilastovuosi, nyt tilastovuosi 2016)
Saapuvalla opiskelijalla ei saa olla tutkintoon johtavaa opiskeluoikeutta saman aikaisesti liikkuvuusjakson kanssa ao. korkeakoulussa. Eikä erikoistumiskoulutus tai opettajakoulutus-oikeutta.

CIMO pitkä saapuva:

 --CIMO_pitka_saapuva:

DECLARE    @vuosi varchar(4)
SET @vuosi = '2016'

create table #param (
    vuosi varchar(4),
)
;
insert into #param (vuosi)
    select
    @vuosi as vuosi
;

-- Temp-taulu tiedonkeruun tietoja varten
create table #cimopisk (
    oppilaitos varchar(5), -- Oppilaitoskoodi
    hetu varchar(100), --henkilö- tai muu tunnus
    sp varchar(1), -- sukupuoli
    koulutusala varchar(4), -- koulutusala
    opintoaste varchar(1), -- (vain YO)
    liikkuvuusohjelma varchar(3),
    kansalaisuus varchar(3),
    lahtomaa varchar(3),
    kesto varchar(4), --liikkuvuuden kesto
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK)
    tyyppi varchar(2), --liikkuvuuden tyyppi
    --
    jakso_alkupvm date,
    jakso_loppupvm date,
    koulutuskoodi varchar(6),
    --
    opiskelijaavain varchar(100),
    liikkuvuusjaksoavain varchar(100),
    opiskeluoikeusavain varchar(100),
    opiskelija_id int,
    liikkuvuusjakso_id int,
    opiskeluoikeus_id int
)
;
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskelija_id ON #cimopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_liikkuvuusjakso_id ON #cimopisk (liikkuvuusjakso_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskeluoikeus_id ON #cimopisk (opiskeluoikeus_id)
;

print convert(varchar,getdate(),120)+' CREATE temp-taulu param ja cimopisk'
;

---
-- Lisätään perustiedot (henkilö, opiskelija)
-- * täytyy olla liikkuvuusjakso joka on
--   - jonka (liikkuvuuden )suunta on 2=Saapuva
--   - kestänyt vähintään 87 päivää
-- * jakson aloituspäivämäärän vuosi on tilastovuosi
---

INSERT into #cimopisk (
    -- kaikki sarakkeet (kopioitavaksi)
    oppilaitos,hetu,sp,koulutusala,opintoaste,liikkuvuusohjelma,kansalaisuus,lahtomaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT  DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,liikkuvuusohjelma = (select top 1 k.Koodi from virta.dbo.Liikkuvuusohjelma k where k.ID=lj.LiikkuvuusohjelmaID)
,kansalaisuus = (
    -- TODO: jos monta?
    select top 1 k.Koodi
    from virta.dbo.Henkilon_maa hm
    join virta.dbo.Maan_rooli mr on mr.ID=hm.Maan_rooliID and mr.Koodi='1'--Kansalaisuus
    join virta.dbo.Maa k on k.ID=hm.MaaID
    where hm.HenkiloID=h.ID
    )
,lahtomaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!


FROM virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = lj.OpiskelijaID
    INNER JOIN virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=OO.id
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = OO.Op_oikeuden_tyyppiID  -- KV vaihto tyyppi , Ei Liikkuvuusjakson opiskeluoikeustyyppi
WHERE 1=1
AND otk.koodi = '9' -- Kansainvälinen vaihto
-- suunta 2=Saapuva
and 2 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- vähintään 87 päivää
and 87 <= coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)

AND lj.Alkamispaivamaara BETWEEN oj.Alkamispaivamaara AND coalesce(oj.Paattymispaivamaara,'9999-01-01')

-- Saapuva jolla on myös tutkinto-oikeus on double degree?
AND    p.id NOT IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','5','6','7','14','15','19')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')
                            
            and year(lj.Alkamispaivamaara) = (select vuosi from #param)
            )
;
print convert(varchar,getdate(),120)+' liikkuvuus- ja opiskelijatiedot'
;


-- Pitkä saapuva ilman opiskeluoikeutta

INSERT into #cimopisk (    
    oppilaitos,hetu,sp,koulutusala,opintoaste,liikkuvuusohjelma,kansalaisuus,lahtomaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT  DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,liikkuvuusohjelma = (select top 1 k.Koodi from virta.dbo.Liikkuvuusohjelma k where k.ID=lj.LiikkuvuusohjelmaID)
,kansalaisuus = (
    -- TODO: jos monta?
    select top 1 k.Koodi
    from virta.dbo.Henkilon_maa hm
    join virta.dbo.Maan_rooli mr on mr.ID=hm.Maan_rooliID and mr.Koodi='1'--Kansalaisuus
    join virta.dbo.Maa k on k.ID=hm.MaaID
    where hm.HenkiloID=h.ID
    )
,lahtomaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
        inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
        inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID    
WHERE 
        p.ID NOT IN (SELECT C.opiskelija_id FROM #cimopisk C)

-- suunta 2=Saapuva
and 2 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- vähintään 87 päivää
and 87 <= coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)

-- Saapuva jolla on myös tutkinto-oikeus on double degree?
AND    p.id NOT IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','5','6','7','14','15','19')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')
            and year(lj.Alkamispaivamaara) = (select vuosi from #param)
            )

AND    p.id NOT IN (
  
            SELECT OO2.opiskelijaid 
            FROM virta.dbo.Opiskeluoikeus OO2                 
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
                INNER JOIN virta.dbo.Op_oik_jaks_liittyva_luokittelu OL ON OL.OpiskeluoikeusjaksoID = OJ2.id
                INNER JOIN virta.dbo.Opiskeluoikeuden_luokittelu OOL ON OOL.id = OL.Opiskeluoikeuden_luokitteluID
            WHERE     OOL.koodi = '7'            
            AND OJ2.Alkamispaivamaara BETWEEN  lj.Alkamispaivamaara  AND COALESCE(OJ2.Paattymispaivamaara, @vuosi + '-12-31')
            and year(lj.Alkamispaivamaara) = (select vuosi from #param)
            
            )


 ; 




---
-- Opiskeluoikeustiedot
---
/*
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
*/

-- valitaan yksi per opiskelija, jos ei löytynyt jo ID yllä!
update T
set opiskeluoikeus_id=o.ID, opiskeluoikeusavain=o.avain
from #cimopisk T
inner join virta.dbo.Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
inner join virta.dbo.Op_oikeuden_ensisijaisuus oe on oe.OpiskeluoikeusID=o.ID
where 1=1
-- ensisijainen alkamispäivänä
and T.jakso_alkupvm between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-9-9')
;

-- koulutuskoodi
update T
set koulutuskoodi=(select k.Koodi from virta.dbo.Tutkintonimike k where k.ID=oj.TutkintonimikeID)
from #cimopisk T
inner join virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
where T.opiskeluoikeus_id is not null
-- jakso voimassa alkupäivänä
and T.jakso_alkupvm between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
;


-- koulutusala
-- nb! OKM:n ohjauksen ala
update T
set koulutusala=K.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID AND OO.ID = T.opiskeluoikeus_id
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID    
    INNER JOIN virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=OO.id
where T.opiskeluoikeus_id is not null  AND T.opiskelija_id is not null    
and T.jakso_alkupvm between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
AND K.Versio = 'ohjausala'
;


update T
set koulutusala=K.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID    
    
where T.koulutusala IS NULL  AND T.opiskelija_id is not null  
AND K.Versio = 'ohjausala'
;



-- opintoaste, koultyp jos on opiskeluoikeusid ensisijaisen mukaan eli ei NULL
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.opiskeluoikeus OO ON T.opiskeluoikeus_id = OO.id
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk ON otk.ID = L.Opiskeluoikeuden_tyyppiID
where T.opiskeluoikeus_id is not null
AND     T.opiskeluoikeusAvain = OO.avain
AND     L.avain = T.liikkuvuusjaksoavain
;


-- Jos opintoaste on null edellisen jälkeen
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk ON otk.ID = L.Opiskeluoikeuden_tyyppiID
where T.opintoaste is null AND T.opiskeluoikeus_id is null
;


-- Jos koultyp on null edellisen jälkeen, ei pitäisi vaikuttaa yliopistoihin mikäli ei ole väärällä tyypillä tuotu
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
     
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk ON otk.ID = L.Opiskeluoikeuden_tyyppiID
where T.koultyp is null AND T.opiskeluoikeus_id is null
;

---
-- TULOS
---
select * 
from #cimopisk T
order by hetu
;

Kerätään kaikki liikkuvuusjaksot joiden opiskeluoikeus tyyppi on 9(=Kansainvälinen vaihto ) tauluun #cimopisk: oppilaitos, hetu, sukupuoli, liikkuvuusohjelma, kansalaisuus, lähtömaa, liikkuvuuden kesto, liikkuvuuden tyyppi, liikkuvuuden alkamispvm, liikkuvuuden päättymispvm
Liikkuvuuden suunta 2 (=Saapuva)
Liikkuvusjaksot pituudeltaan >= 87 päivää.
Liikkuvusjakso alkamispäivämäärä tilastovuonna (jakson aloituspäivämäärän vuosi on tilastovuosi, nyt tilastovuosi 2016)
Saapuvalla opiskelijalla ei saa olla tutkintoon johtavaa opiskeluoikeutta saman aikaisesti liikkuvuusjakson kanssa ao. korkeakoulussa.Eikä erikoistumiskoulutus tai opettajakoulutus-oikeutta.

Kerätään mukaan myös kaikki opiskelijat joilla ei ole opiskeluoikeutta mutta on olemaassa ajalta liikkuuvuusjaksoja.
Liikkuvuuden suunta 2 (=Saapuva)
Liikkuvusjaksot pituudeltaan >= 87 päivää.Liikkuvusjakso alkamispäivämäärä tilastovuonna (jakson aloituspäivämäärän vuosi on tilastovuosi, nyt tilastovuosi 2016)
Saapuvalla opiskelijalla ei saa olla tutkintoon johtavaa opiskeluoikeutta saman aikaisesti liikkuvuusjakson kanssa ao. korkeakoulussa. Eikä erikoistumiskoulutus tai opettajakoulutus-oikeutta.

CIMO lyhyt lähteva:

 --CIMO_lyhyt_lahteva:

DECLARE    @vuosi varchar(4)
SET @vuosi = '2016'

create table #param (
    vuosi varchar(4),
)
;
insert into #param (vuosi)
    select
    @vuosi as vuosi
;

-- Temp-taulu tiedonkeruun tietoja varten
create table #cimopisk (
    oppilaitos varchar(5), -- Oppilaitoskoodi // 5-numeroinen
    hetu varchar(100), --henkilö- tai muu tunnus    vapaateksti // jollei henkilötunnusta löydy, käytetään Opiskelija.avain
    sp varchar(1), -- sukupuoli // 1, 2, tai 9
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    kohdemaa varchar(3), --// 000-999
    kesto varchar(4), --liikkuvuuden kesto // päiviä
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
    tyyppi varchar(2), --liikkuvuuden tyyppi // 1, 2
    --
    jakso_alkupvm date,
    jakso_loppupvm date,
    koulutuskoodi varchar(6),
    --
    opiskelijaavain varchar(100),
    liikkuvuusjaksoavain varchar(100),
    opiskeluoikeusavain varchar(100),
    opiskelija_id int,
    liikkuvuusjakso_id int,
    opiskeluoikeus_id int
)
;
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskelija_id ON #cimopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_liikkuvuusjakso_id ON #cimopisk (liikkuvuusjakso_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskeluoikeus_id ON #cimopisk (opiskeluoikeus_id)
;


create table #cimopisk_pitka (
    oppilaitos varchar(5), -- Oppilaitoskoodi // 5-numeroinen
    hetu varchar(100), --henkilö- tai muu tunnus    vapaateksti // jollei henkilötunnusta löydy, käytetään Opiskelija.avain
    sp varchar(1), -- sukupuoli // 1, 2, tai 9
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    kohdemaa varchar(3), --// 000-999
    kesto varchar(4), --liikkuvuuden kesto // päiviä
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
    tyyppi varchar(2), --liikkuvuuden tyyppi // 1, 2
    --
    jakso_alkupvm date,
    jakso_loppupvm date,
    koulutuskoodi varchar(6),
    --
    opiskelijaavain varchar(100),
    liikkuvuusjaksoavain varchar(100),
    opiskeluoikeusavain varchar(100),
    opiskelija_id int,
    liikkuvuusjakso_id int,
    opiskeluoikeus_id int
)
;
CREATE NONCLUSTERED INDEX IX_cimopisk_pitka_opiskelija_id ON #cimopisk_pitka (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_pitka_liikkuvuusjakso_id ON #cimopisk_pitka (liikkuvuusjakso_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_pitka_opiskeluoikeus_id ON #cimopisk_pitka (opiskeluoikeus_id)
;


-- Pitkät saapuneet jotka lähtee lyhyelle vaihdolle
INSERT into #cimopisk_pitka  (
    -- kaikki sarakkeet (kopioitavaksi)
    oppilaitos,hetu,sp,koulutusala,opintoaste,kohdemaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,kohdemaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = lj.OpiskelijaID
    INNER JOIN virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=OO.id
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = OO.Op_oikeuden_tyyppiID  -- KV vaihto tyyppi , Ei Liikkuvuusjakson opiskeluoikeustyyppi
WHERE 1=1
AND otk.koodi IN ('9') -- Kansainvälinen vaihto
-- suunta 2=Saapuva
and 2 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- vähintään 87 päivää
and 87 <= coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)
AND lj.Alkamispaivamaara BETWEEN oj.Alkamispaivamaara AND coalesce(oj.Paattymispaivamaara,'9999-01-01')





print convert(varchar,getdate(),120)+' CREATE temp-taulu param ja cimopisk'
;

---
-- Lisätään perustiedot (henkilö, opiskelija)
-- * täytyy olla liikkuvuusjakso joka on
--   - jonka (liikkuvuuden )suunta on 1=Lähtevä
--   - kestänyt vähintään [5,7] ja enintään 86 (87-1) päivää
-- * jakson aloituspäivämäärän vuosi on tilastovuosi
---

INSERT into #cimopisk (
    -- kaikki sarakkeet (kopioitavaksi)
    oppilaitos,hetu,sp,koulutusala,opintoaste,kohdemaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT  DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,kohdemaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID

WHERE 1=1
-- suunta 1=Lähtevä
and 1 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- vähintään [5,7] ja enintään 86 päivää
and coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
    between 5 and 86
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)

-- Lähtevillä vain tutkinto ei muita
AND    p.id IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','6','7')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')

            and year(lj.Alkamispaivamaara) = (select vuosi from #param)

            )

;


-- Lyhyt lähtö pitkä saapuva
INSERT into #cimopisk (
    -- kaikki sarakkeet (kopioitavaksi)
    oppilaitos,hetu,sp,koulutusala,opintoaste,kohdemaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT  DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,kohdemaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID

WHERE p.id IN ( SELECT C2.opiskelija_id FROM #cimopisk_pitka C2 )
-- suunta 1=Lähtevä
and 1 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- vähintään [5,7] ja enintään 86 päivää
and coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
    between 5 and 86
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)
;


print convert(varchar,getdate(),120)+' liikkuvuus- ja opiskelijatiedot'
;

---
-- Opiskeluoikeustiedot
---
/*
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
*/

-- valitaan yksi per opiskelija, jos ei löytynyt jo ID yllä!
update T
set opiskeluoikeus_id=o.ID, opiskeluoikeusavain=o.avain
from #cimopisk T
inner join virta.dbo.Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
inner join virta.dbo.Op_oikeuden_ensisijaisuus oe on oe.OpiskeluoikeusID=o.ID
where 1=1
-- ensisijainen alkamispäivänä
and T.jakso_alkupvm between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-9-9')
;

-- koulutuskoodi
update T
set koulutuskoodi=(select k.Koodi from virta.dbo.Tutkintonimike k where k.ID=oj.TutkintonimikeID)
from #cimopisk T
inner join virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
where T.opiskeluoikeus_id is not null
-- jakso voimassa alkupäivänä
and T.jakso_alkupvm between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
;

-- koulutusala
-- nb! OKM:n ohjauksen ala
update T
set koulutusala=k.okmohjausala
from #cimopisk T
inner join Yhteiset.Koodistot.Koulutusluokitus k on k.Koodi=T.koulutuskoodi
where T.opiskeluoikeus_id is not null and T.koulutuskoodi is not null
-- voimassa alkupäivänä
and T.jakso_alkupvm between k.Alkamispaivamaara and coalesce(k.Paattymispaivamaara,'9999-01-01')
;


-- Lyhyt lähtevä joka on alunperin saapuva Suomeen, tai kotimainen vaihto
-- Jos on Saapunut opiskeluoikeustyyppi = 9 ja  Lyhyt lähtevä
-- koulutusala (ohjausala)  lähtevän opiskeluoikeudesta (opiskeluoikeustyyppi = 9)
-- koulutusala
-- nb! OKM:n ohjauksen ala
update T
set koulutusala=k.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID            -- koulutusala (ohjausala)  lähtevän opiskeluoikeudesta (opiskeluoikeustyyppi = 9)
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = OO.Op_oikeuden_tyyppiID  -- !!

where T.opiskelija_id is not null  
AND otk.koodi IN ('9','8')  -- Kansainvälinen vaihto ja kotimainen vaihto  -- !!
AND T.koulutuskoodi IS NULL 
AND K.Versio = 'ohjausala'
;


-- opintoaste, koultyp
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    when '1'--Ammattikorkeakoulututkinto
     then case
        when o.Aikuiskoulutus='1' then '2'--=Amk-tutkintoon johtava aikuiskoulutus
        when o.Aikuiskoulutus='2' then '1'--=Amk-tutkintoon johtava nuorten koulutus
        else '1' end
    when '3'  then '6'--=Ylempi ammattikorkeakoulututkinto
    --when '12' then '3'--=Erikoistumisopinnot
    --when '15' then '5'--=Ammatillinen opettajankoulutus
    --when '19' then '7'--=Erikoistumiskoulutus
    end
)
from #cimopisk T
inner join virta.dbo.Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID=o.Op_oikeuden_tyyppiID
where T.opiskeluoikeus_id is not null
;


-- opintoaste, koultyp
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
    
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L on L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = L.Opiskeluoikeuden_tyyppiID   -- koultyp lähtevän jakson opiskeluoikeudesta (1)
    
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = T.opiskelija_id    -- !!
    inner join virta.dbo.Opiskeluoikeuden_tyyppi oootk on oootk.ID = OO.Op_oikeuden_tyyppiID  -- !!    
where T.opiskelija_id is not null
AND oootk.koodi IN ('9','8')  -- Kansainvälinen vaihto ja kotimainen vaihto  -- !!
AND T.koulutuskoodi IS NULL 


---
-- TULOS
---

select * from #cimopisk T
order by hetu

Haetaan ensin mukaan myös CIMO pitkä saapuva liikkuvuusjaksot joiden opiskeluoikeus tyyppi on 9 (=Kansainvälinen vaihto ) tauluun #cimopisk_pitka

Kerätään sen jälkeen kaikki opiskeluoikeudet tutkintoon johtavan tyyppiä ('1','2','3','4','6','7') tauluun #cimopisk: oppilaitos, hetu, sukupuoli, liikkuvuusohjelma, kohdemaa, liikkuvuuden kesto, liikkuvuuden tyyppi, liikkuvuuden alkamispvm, liikkuvuuden päättymispvm
Liikkuvuuden suunta 1 (=Lähtevä)
Liikkuvusjaksot pituudeltaan 5 - 86 päivää.
Liikkuvusjakso alkamispäivämäärä tilastovuonna (jakson aloituspäivämäärän vuosi on tilastovuosi, nyt tilastovuosi 2016)

Lisätään joukkoon pitkät saapuvat joilla löytyy lyhyt liikkuvuusjakso taulusta #cimopisk_pitka
Liikkuvuuden suunta 1 (=Lähtevä)
Liikkuvusjaksot pituudeltaan 5 - 86 päivää.

Liikkuvusjakso alkamispäivämäärä tilastovuonna (jakson aloituspäivämäärän vuosi on tilastovuosi, nyt tilastovuosi 2016)

CIMO pitkä lähteva:

 --CIMO_pitka_lahteva:

DECLARE    @vuosi varchar(4)
SET @vuosi = '2016'

create table #param (
    vuosi varchar(4),
)
;
insert into #param (vuosi)
    select
    @vuosi as vuosi
;
--select * from #param

-- Temp-taulu tiedonkeruun tietoja varten
create table #cimopisk (
    oppilaitos varchar(5), -- Oppilaitoskoodi // 5-numeroinen
    hetu varchar(100), --henkilö- tai muu tunnus    vapaateksti // jollei henkilötunnusta löydy, käytetään Opiskelija.avain
    sp varchar(1), -- sukupuoli // 1, 2, tai 9
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    liikkuvuusohjelma varchar(3), --// 01-16 tai 99
    kohdemaa varchar(3), --// 000-999
    kesto varchar(4), --liikkuvuuden kesto // päiviä // Vaihdon tulee olla kestänyt vähintään 87 päivää, jotta tulee poimituksi. Kokonaiskesto raportoidaan päivinä ja vaihdon aloituspäivämäärä määrä raportointivuoden.
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
    tyyppi varchar(2), --liikkuvuuden tyyppi // 1, 2
    --
    jakso_alkupvm date,
    jakso_loppupvm date,
    koulutuskoodi varchar(6),
    --
    opiskelijaavain varchar(100),
    liikkuvuusjaksoavain varchar(100),
    opiskeluoikeusavain varchar(100),
    opiskelija_id int,
    liikkuvuusjakso_id int,
    opiskeluoikeus_id int
)
;
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskelija_id ON #cimopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_liikkuvuusjakso_id ON #cimopisk (liikkuvuusjakso_id)
CREATE NONCLUSTERED INDEX IX_cimopisk_opiskeluoikeus_id ON #cimopisk (opiskeluoikeus_id)
;

print convert(varchar,getdate(),120)+' CREATE temp-taulu param ja cimopisk'
;

---
-- Lisätään perustiedot (henkilö, opiskelija)
-- * täytyy olla liikkuvuusjakso joka on
--   - jonka (liikkuvuuden )suunta on 1=Lähtevä
--   - kestänyt vähintään 87 päivää
-- * jakson aloituspäivämäärän vuosi on tilastovuosi
---

INSERT into #cimopisk (
    -- kaikki sarakkeet (kopioitavaksi)
    oppilaitos,hetu,sp,koulutusala,opintoaste,liikkuvuusohjelma,kohdemaa,kesto,koultyp,tyyppi
    ,jakso_alkupvm,jakso_loppupvm,koulutuskoodi
    ,opiskelijaavain,liikkuvuusjaksoavain,opiskeluoikeusavain,opiskelija_id,liikkuvuusjakso_id,opiskeluoikeus_id
)

SELECT  DISTINCT 
 oppilaitos = (select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB=h.DB)
,hetu = h.Henkilotunnus
,sp = (select top 1 k.Koodi from virta.dbo.Sukupuoli k where k.ID=h.SukupuoliID)
,koulutusala = null
,opintoaste = null
,liikkuvuusohjelma = (select top 1 k.Koodi from virta.dbo.Liikkuvuusohjelma k where k.ID=lj.LiikkuvuusohjelmaID)
,kohdemaa = (select top 1 k.Koodi from virta.dbo.Maa k where k.ID=lj.MaaID)
,kesto = cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1 -- alku- ja loppupvm kuuluu mukaan
,koultyp = null
,tyyppi = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_tyyppi k where k.ID=lj.Liikkuvuuden_tyyppiID)
--
,jakso_alkupvm = lj.Alkamispaivamaara
,jakso_loppupvm = lj.Paattymispaivamaara
,koulutuskoodi = null
--
,opiskelijaavain = p.Avain
,liikkuvuusjaksoavain = lj.Avain
,opiskeluoikeusavain = (select avain from virta.dbo.Opiskeluoikeus where ID=lj.OpiskeluoikeusID)
,opiskelija_id = p.ID
,liikkuvuusjakso_id = lj.ID
,opiskeluoikeus_id = lj.OpiskeluoikeusID -- voi olla null!

FROM virta.dbo.Henkilo h
inner join virta.dbo.Opiskelija p on p.HenkiloID=h.ID
inner join virta.dbo.Liikkuvuusjakso lj on lj.OpiskelijaID=p.ID

WHERE 1=1
-- suunta 1=Lähtevä
and 1 = (select top 1 k.Koodi from virta.dbo.Liikkuvuuden_suunta k where k.ID=lj.Liikkuvuuden_suuntaID)
-- vähintään 87 päivää
and 87 <= coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) +1 -- alku- ja loppupvm kuuluu mukaan
-- tilastovuosi
and year(lj.Alkamispaivamaara) = (select vuosi from #param)

-- Lähtevillä vain tutkinto ei muita
AND    p.id IN (

            SELECT OO2.opiskelijaid     
            FROM virta.dbo.Opiskeluoikeus OO2 
                inner join virta.dbo.Opiskeluoikeuden_tyyppi otk2 on otk2.ID = OO2.Op_oikeuden_tyyppiID  
                INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ2 on OJ2.OpiskeluoikeusID=OO2.id
            WHERE     otk2.koodi IN ('1','2','3','4','6','7')
            
            AND lj.Alkamispaivamaara BETWEEN OO2.Alkamispaivamaara AND COALESCE(OO2.Paattymispaivamaara, @vuosi + '-12-31')

            and year(lj.Alkamispaivamaara) = (select vuosi from #param)

            )
;

print convert(varchar,getdate(),120)+' liikkuvuus- ja opiskelijatiedot'
;

---
-- Opiskeluoikeustiedot
---
/*
    koulutusala varchar(4), -- koulutusala // OKMOhjausala
    opintoaste varchar(1), -- (vain YO) // 1, 2 tai 3 // Opiskeluoikeus.Tyyppi 2,4,6,7
    koultyp varchar(1), --koulutuksen tyyppi (vain AMK) // 1,2, tai 6 // OpiskeluoikeusTyyppi 1,3
*/

-- valitaan yksi per opiskelija, jos ei löytynyt jo ID yllä!
update T
set opiskeluoikeus_id=o.ID, opiskeluoikeusavain=o.avain
from #cimopisk T
inner join virta.dbo.Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
inner join virta.dbo.Op_oikeuden_ensisijaisuus oe on oe.OpiskeluoikeusID=o.ID
where 1=1
-- ensisijainen alkamispäivänä
and T.jakso_alkupvm between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-9-9')
;

-- koulutuskoodi
update T
set koulutuskoodi=(select k.Koodi from virta.dbo.Tutkintonimike k where k.ID=oj.TutkintonimikeID)
from #cimopisk T
inner join virta.dbo.Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=T.opiskeluoikeus_id
where T.opiskeluoikeus_id is not null
-- jakso voimassa alkupäivänä
and T.jakso_alkupvm between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01')
;

-- koulutusala
-- nb! OKM:n ohjauksen ala
update T
set koulutusala=k.okmohjausala
from #cimopisk T
inner join Yhteiset.Koodistot.Koulutusluokitus k on k.Koodi=T.koulutuskoodi
where T.opiskeluoikeus_id is not null and T.koulutuskoodi is not null
-- voimassa alkupäivänä
and T.jakso_alkupvm between k.Alkamispaivamaara and coalesce(k.Paattymispaivamaara,'9999-01-01')
;

-- Lyhyt lähtevä joka on alunperin saapuva vaihto Suomeen, tai kotimainen vaihto
-- Jos on Saapunut opiskeluoikeustyyppi = 9 ja  Lyhyt lähtevä
-- koulutusala (ohjausala)  lähtevän opiskeluoikeudesta (opiskeluoikeustyyppi = 9)
-- koulutusala
-- nb! OKM:n ohjauksen ala
update T
set koulutusala=k.Koodi
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L ON L.OpiskelijaID =T.opiskelija_id
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = L.OpiskelijaID
    inner join virta.dbo.Koulutusala K ON K.id = OO.KoulutusalaID            -- koulutusala (ohjausala)  lähtevän opiskeluoikeudesta (opiskeluoikeustyyppi = 9)
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = OO.Op_oikeuden_tyyppiID  -- !!

where T.opiskelija_id is not null  
AND otk.koodi IN ('9','8')  -- Kansainvälinen vaihto ja kotimainen vaihto  -- !!
AND T.koulutuskoodi IS NULL 
AND K.Versio = 'ohjausala'
;


-- opintoaste, koultyp
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    when '1'--Ammattikorkeakoulututkinto
     then case
        when o.Aikuiskoulutus='1' then '2'--=Amk-tutkintoon johtava aikuiskoulutus
        when o.Aikuiskoulutus='2' then '1'--=Amk-tutkintoon johtava nuorten koulutus
        else '1' end
    when '3'  then '6'--=Ylempi ammattikorkeakoulututkinto
    --when '12' then '3'--=Erikoistumisopinnot
    --when '15' then '5'--=Ammatillinen opettajankoulutus
    --when '19' then '7'--=Erikoistumiskoulutus
    end
)
from #cimopisk T
inner join virta.dbo.Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID=o.Op_oikeuden_tyyppiID
where T.opiskeluoikeus_id is not null
;


-- opintoaste, koultyp
update T
set opintoaste=(
    case otk.Koodi
    when '2' then '1'
    when '4' then '2'
    when '6' then '3'
    when '7' then '3'
    end
)
,koultyp=(
    case otk.Koodi
    
    when '1' then '1' -- Ammattikorkeakoulututkinto
    when '3' then '6' -- Ylempi ammattikorkeakoulututkinto
    
    end
)
from #cimopisk T
    inner join virta.dbo.Liikkuvuusjakso L on L.OpiskelijaID = T.opiskelija_id    
    inner join virta.dbo.Opiskeluoikeuden_tyyppi otk on otk.ID = L.Opiskeluoikeuden_tyyppiID   -- koultyp lähtevän jakson opiskeluoikeudesta (1)    
    inner join virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = T.opiskelija_id    -- !!
    inner join virta.dbo.Opiskeluoikeuden_tyyppi oootk on oootk.ID = OO.Op_oikeuden_tyyppiID  -- !!    
where T.opiskelija_id is not null
AND oootk.koodi IN ('9','8')  -- Kansainvälinen vaihto ja kotimainen vaihto  -- !!
AND T.koulutuskoodi IS NULL 

---
-- TULOS
---
select * from #cimopisk T
order by hetu
;

Kerätään kaikki liikkuvuusjaksot joiden opiskeluoikeudet tutkintoon johtavan tyyppiä ('1','2','3','4','6','7') tauluun #cimopisk
Liikkuvuuden suunta 1 (=Lähtevä)
Liikkuvusjaksot pituudeltaan >= 87 päivää.
Liikkuvusjakso alkamispäivämäärä tilastovuonna (jakson aloituspäivämäärän vuosi on tilastovuosi, nyt tilastovuosi 2016)

Yhteiset kaikkiin neljään poimintaan:

opiskeluoikeus tieto YO ensisijaisuuden mukaan, jos on.
AMK liikkuvuusjakson jo opiskeluoikeuden kautta tiedossa #cimopisk taulussa.

koulutuskoodi opiskeluoikeusjaksosta, jos on.

koulutusala (ohjausala) opiskeluoikeudesta.

opintoaste (YO):

opiskeluoikeuden tyyppi => opintoaste

2    => 1   -- Alempi korkeakoulututkinto
4    => 2   -- Ylempi korkeakoulututkinto
6    => 3   -- Lisensiaatintutkinto tai tohtorintutkinto
7    => 3   -- Lisensiaatintutkinto tai tohtorintutkinto

koulutus typpi (AMK):

opiskeluoikeuden tyyppi => koultyp

1 => 1 -- Ammattikorkeakoulututkinto
3 => 6 -- Ylempi ammattikorkeakoulututkinto