/** Opiskeluoikeusviitte löytyy näiltä yliopistostoista */
USE [eurostat]
GO
/****** Object: View [dbo].[v_EuroSTAT_YO] Script Date: 12.4.2022 14:18:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_EuroSTAT_YO]
AS
SELECT DISTINCT
ORG.Organisaatio_koodi AS oppilaitos_koodi,
LJ.db AS oppilaitos,
O.avain AS opiskelijaAvain,
OO.avain AS opiskeluoikeusAvain,
LJ.Avain AS liikkuvuusjaksoAvain,
OS.Avain AS opintosuoritusAvain,
H.henkilotunnus,
H.etunimet,
H.sukunimi,
S.Nimi as sukupuoli,
CASE
WHEN (OO.KoulutusalaID IS NULL) THEN ('')
ELSE K.nimi
END AS koulutusala,
OT.Nimi as opiskeluoikeuden_tyyppi,
Tt.koodi as tutkintokoodi,
OS.Suorituspaivamaara as tutkinnon_suorituspaivamaara,
Tt.nimi AS tutkinto_suoritettu,
OO.Alkamispaivamaara opiskeluoikeuden_alkamispaivamaara,
OO.Paattymispaivamaara as opiskeluoikeuden_paattymispaivamaara,
LJ.Alkamispaivamaara as liikkuvuusjakson_alkamispaivamaara,
LJ.Paattymispaivamaara as liikkuvuusjakson_paattymispaivamaara,
(CASE
WHEN DATEDIFF(dd,LJ.Alkamispaivamaara,LJ.Paattymispaivamaara) >=86 then (cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1)
ELSE '' END) AS liikkuvuuden_kesto_yli_3kk,
(CASE
WHEN DATEDIFF(dd,LJ.Alkamispaivamaara,LJ.Paattymispaivamaara) < 86 then (cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1)
ELSE '' END) AS liikkuvuuden_kesto_alle_3kk,
LO.Nimi as liikkuvuusohjelma,
LT.Nimi as liikkuvuuden_tyyppi,
LS.Nimi as liikkuvuuden_suunta,
'Suomi' as lahtomaa,
'246' as lahtomaa_koodi,
M.Nimi as kohdemaa,
M.Koodi as kohdemaa_koodi,
CURRENT_TIMESTAMP as latauspaiva
FROM [virta].[dbo].Liikkuvuusjakso LJ
JOIN [virta].[dbo].Opiskeluoikeus OO ON OO.id= LJ.OpiskeluoikeusID
JOIN [virta].[dbo].Opiskelija O ON O.id = LJ.OpiskelijaID
JOIN [virta].[dbo].Opintosuoritus OS ON OS.OpiskeluoikeusID = OO.ID
JOIN [virta].[dbo].Opintosuorituksen_laji OL ON OL.id = OS.Opintosuorituksen_lajiID
JOIN [virta].[dbo].Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID
JOIN [virta].[dbo].Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
JOIN [virta].[dbo].Opiskeluoikeuden_tila OTT ON OTT.id = OOT.Op_oikeuden_tilaID
JOIN Yhteiset.Asetukset.Instanssit I ON OS.db = I.DB
JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi ORG ON ORG.Organisaatio_koodi = I.OrganisaatioKoodi
LEFT JOIN [virta].[dbo].Koulutusala as K on OO.KoulutusalaID = K.ID
JOIN [virta].[dbo].Liikkuvuusohjelma as LO on LJ.LiikkuvuusohjelmaID = lO.ID
JOIN [virta].[dbo].Henkilo as H on O.HenkiloID = H.ID
JOIN [virta].[dbo].Sukupuoli as S on H.SukupuoliID = S.ID
JOIN [virta].[dbo].Liikkuvuuden_tyyppi as LT on LJ.Liikkuvuuden_tyyppiID = LT.ID
JOIN [virta].[dbo].Liikkuvuuden_suunta as LS on LJ.Liikkuvuuden_suuntaID = LS.ID
JOIN [virta].[dbo].Maa as M on LJ.MaaID = m.ID
JOIN [virta].[dbo].Tutkintonimike Tt ON Tt.id = OS.TutkintonimikeID
WHERE OL.koodi = '1' -- Tutkinto
AND YEAR(OS.suorituspaivamaara) = 2021 -- Tutkinnon suoritusvuosi 2018
AND OT.Koodi IN ( '1','2','3','4','6','7') -- Opiskeluoikeuden tyyppi amk, alempi kk, ylempi amk, ylempi kk, erikoislääkäri, lisensiaatti, tohtori
AND OTT.Koodi = '3' -- Opiskeluoikeuden tila päättynyt (3) päättynyt tutkintoon kun on tutkintosuoritus (OL.koodi = 1)
AND YEAR(OOT.Alkamispaivamaara) >= 2021 -- Opiskeluoikeus päättynyt tutkinnon suorittamiseen, mahd. toinen oikeus jatkuu
AND LJ.Alkamispaivamaara BETWEEN OO.Alkamispaivamaara AND OS.Suorituspaivamaara -- Liikkuvuusjakso opiskeluoikeuden alkamisen ja tutkintosuoritus päivän välillä = "Tutkinnon suorittamisen aikana"
AND LJ.Alkamispaivamaara < '2022-01-01' -- Ei tulevaisuudessa "2022" alkavia liikkuvuusjaksoja
AND LJ.Paattymispaivamaara < OOT.Alkamispaivamaara -- Liikkuvuus päättynyt ennen opiskeluoikeutta jossa tutkinto suoritettu
AND ORG.Oppilaitostyyppi_koodi = 42 --YO
AND OO.Alkamispaivamaara <= LJ.Alkamispaivamaara
AND LS.Koodi = '1'
AND (LJ.db != ('AYO'))
UNION ALL
/* Näillä yliopistoilla ei löydy opiskeluoikeusviitettä */
SELECT DISTINCT
ORG.Organisaatio_koodi AS oppilaitos_koodi,
LJ.db AS oppilaitos,
O.avain AS opiskelijaAvain,
OO.avain AS opiskeluoikeusAvain,
LJ.Avain AS liikkuvuusjaksoAvain,
OS.Avain AS opintosuoritusAvain,
H.henkilotunnus,
H.etunimet,
H.sukunimi,
S.Nimi as sukupuoli,
CASE
WHEN (OO.KoulutusalaID IS NULL) THEN ('')
ELSE K.nimi
END AS koulutusala,
OT.Nimi as opiskeluoikeuden_tyyppi,
T.koodi as tutkintokoodi,
OS.Suorituspaivamaara as tutkinnon_suorituspaivamaara,
T.nimi AS tutkinto_suoritettu,
OO.Alkamispaivamaara opiskeluoikeuden_alkamispaivamaara,
OO.Paattymispaivamaara as opiskeluoikeuden_paattymispaivamaara,
LJ.Alkamispaivamaara as liikkuvuusjakson_alkamispaivamaara,
LJ.Paattymispaivamaara as liikkuvuusjakson_paattymispaivamaara,
(CASE
WHEN DATEDIFF(dd,LJ.Alkamispaivamaara,LJ.Paattymispaivamaara) >=86 then (cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1)
ELSE '' END) AS liikkuvuuden_kesto_yli_3kk,
(CASE
WHEN DATEDIFF(dd,LJ.Alkamispaivamaara,LJ.Paattymispaivamaara) < 86 then (cast(coalesce(DATEDIFF(day,lj.Alkamispaivamaara,lj.Paattymispaivamaara),9999) as varchar) +1)
ELSE '' END) AS liikkuvuuden_kesto_alle_3kk,
LO.Nimi as liikkuvuusohjelma,
LT.Nimi as liikkuvuuden_tyyppi,
LS.Nimi as liikkuvuuden_suunta,
'Suomi' as lahtomaa,
'246' as lahtomaa_koodi,
M.Nimi as kohdemaa,
M.Koodi as kohdemaa_koodi,
CURRENT_TIMESTAMP as latauspaiva
FROM [virta].[dbo].Liikkuvuusjakso LJ
JOIN [virta].[dbo].Opiskelija O ON O.id = LJ.OpiskelijaID
JOIN [virta].[dbo].Opiskeluoikeus OO ON OO.OpiskelijaID= O.id
LEFT JOIN [virta].[dbo].Koulutusala as K on OO.KoulutusalaID = K.ID
JOIN [virta].[dbo].Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID
JOIN [virta].[dbo].Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
JOIN [virta].[dbo].Opiskeluoikeuden_tila OTT ON OTT.id = OOT.Op_oikeuden_tilaID
JOIN Yhteiset.Asetukset.Instanssit I ON LJ.db = I.DB
JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi ORG ON ORG.Organisaatio_koodi = I.OrganisaatioKoodi
JOIN [virta].[dbo].Henkilo as H on O.HenkiloID = H.ID
JOIN [virta].[dbo].Sukupuoli as S on H.SukupuoliID = S.ID
JOIN [virta].[dbo].Opintosuoritus OS ON OS.OpiskeluoikeusID = OO.ID
JOIN [virta].[dbo].Opintosuorituksen_laji OL ON OL.id = OS.Opintosuorituksen_lajiID
JOIN [virta].[dbo].Tutkintonimike T ON T.id = OS.TutkintonimikeID
JOIN [virta].[dbo].Liikkuvuusohjelma as LO on LJ.LiikkuvuusohjelmaID = LO.ID
JOIN [virta].[dbo].Liikkuvuuden_tyyppi as LT on LJ.Liikkuvuuden_tyyppiID = LT.ID
JOIN [virta].[dbo].Liikkuvuuden_suunta as LS on LJ.Liikkuvuuden_suuntaID = LS.ID
JOIN [virta].[dbo].Maa as M on LJ.MaaID = m.ID
WHERE OL.koodi = '1' -- Tutkinto
AND YEAR(OS.suorituspaivamaara) = 2021 -- Tutkinnon suoritusvuosi 2021
AND OT.Koodi IN ( '1','2','3','4','6','7') -- Opiskeluoikeuden tyyppi amk, alempi kk, ylempi amk, ylempi kk, lisensiaatti, tohtori
AND OTT.Koodi = '3' -- Opiskeluoikeuden tila päättynyt (3) päättynyt tutkintoon kun on tutkintosuoritus (OL.koodi = 1)
AND YEAR(OOT.Alkamispaivamaara) >= 2021 -- Opiskeluoikeus päättynyt tutkinnon suorittamiseen, mahd. toinen oikeus jatkuu
AND LJ.Alkamispaivamaara BETWEEN OO.Alkamispaivamaara AND OS.Suorituspaivamaara -- Liikkuvuusjakso opiskeluoikeuden alkamisen ja tutkintosuoritus päivän välillä = "Tutkinnon suorittamisen aikana"
AND LJ.Alkamispaivamaara < '2022-01-01' -- Ei tulevaisuudessa "2017" alkavia liikkuvuusjaksoja
AND LJ.Paattymispaivamaara < OOT.Alkamispaivamaara -- Liikkuvuus päättynyt ennen opiskeluoikeutta jossa tutkinto suoritettu
AND OO.Alkamispaivamaara <= LJ.Alkamispaivamaara
AND ORG.Oppilaitostyyppi_koodi = 42 --YO
AND LS.Koodi = '1'
AND (LJ.db = ('AYO'))
GO