- Created by Unknown User (eojala@csc.fi), last updated by Karoliina Sipovaara on Jul 30, 2021 50 minute read
Opiskelijatiedot
CSC on toimittanut huhtikuusta 2017 lähtien Virta-tietovarannosta opiskelutiedot kuukausittain Kelalle. Korkeakoulut tarkistavat VIRTA-aineiston ennen sen siirtämistä Kelaan Tiedonsiirrot tehdään Kelan ilmoittaman aikataulun mukaisesti. Kuukausiseurannan tietojen VIRTA-aineisto muodostuu kerran vuorokaudessa siten, että ammattikorkeakoulun aineiston ajo käynnistyy illalla klo 22.00 ja yliopistojen aineiston ajo tämän jälkeen klo 01.00. Aineistossa on mukana korkeakoulun vuorokautta aiemmin toimittamat tiedot.
Poimintaskriptejä ja -ehtojen kuvauksia päivitetään sitä mukaan jos korjauksia tulee. Ammattikorkeakouluilla ja yliopistoilla on hieman erillaiset tiedot.
Korkeakouluja pyydetään tarkistamaan omat tiedostot jotka muodostuvat päivittäin omaan Virta tiedonsiirron SFTP-hakemistoon (kela-hakemisto).
Läsnäolotiedoissa (L-koodin tiedot) on huomioitu nämä eri mahdolliset kombinaatiot. Vähintään yksi lukukausi-tieto lukuvuodessa täytyy löytyä joko läsnä tai poissaolotieto.
| LL | Läsnä | Läsnä |
| PP | Poissa | Poissa |
| LP | Läsnä | Poissa |
| PL | Poissa | Läsnä |
| _L | tieto puuttuu | Läsnä |
| _P | tieto puuttuu | Poissa |
| L_ | Läsnä | tieto puuttuu |
| P_ | Poissa | tieto puuttuu |
Ammattikorkeakouluopiskelijat haetaan opiskeluoikeuden perusteella, aktiivinen (läsnä/poissa). Jos useampi oikeus niin on kaksi tai useampi rivi.
Yliopistoopiskelijat aktiivinen (läsnä/poissa) ensisijaisuuden mukaan + koodit poimintaehtojen ja eri päättelyiden mukaan.
Opiskelutiedon poiminnat AMK
Valmistuneet AMK-tutkinnot (Tapahtumakoodi W)
AMK tutkinnon suorittaneet Expand source
DECLARE @YEAR varchar(4) = '2015'
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+'-08-01'
CREATE TABLE #Aloittanut (
oid int,
Alkamispaivamaara date,
Paattymispaivamaara date
)
INSERT INTO #Aloittanut (oid, Alkamispaivamaara, Paattymispaivamaara)
SELECT oo.ID
,l.Alkamispaivamaara
,l.Paattymispaivamaara
FROM dbo.Henkilo H
JOIN dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN dbo.Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
JOIN dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE LT.Koodi = 1 /* Läsnä */
CREATE TABLE #Min_aloituspvm (
oid int,
alk_pvm date
)
INSERT INTO #Min_aloituspvm (oid, alk_pvm)
SELECT oid, MIN(alkamispaivamaara) As alk_pvm
FROM #Aloittanut
GROUP BY oid
CREATE TABLE #KELA_paattynyt (
oid int,
tila_koodi int,
Alkamispaivamaara date
)
INSERT INTO #KELA_paattynyt (oid,tila_koodi,Alkamispaivamaara)
SELECT OO.ID AS oID
, OT.Koodi AS tila_koodi
, OOT.Alkamispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Alkamispaivamaara >= @Syys_alk
AND OT.Koodi = 3 /* Päättynyt */
SELECT DISTINCT
OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'W' AS Tapahtumakoodi
,REPLACE((CONVERT(varchar(12), OS.Suorituspaivamaara, 105)),'-','') AS Paivamaara
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, ' ' AS kevatlukukausi
,FLOOR(OO.Laajuus) AS tutkinnon_laajuus
,REPLACE((CONVERT(varchar(12), M.alk_pvm, 105)),'-','') AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Opintosuoritus OS ON OS.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
LEFT JOIN #Min_aloituspvm M ON M.oid = OO.ID
WHERE OO.ID IN (SELECT oid AS ID FROM #KELA_paattynyt)
AND OT.Koodi IN (1,3,12,14,15)
AND OS.Opintosuorituksen_lajiID = 1
AND OS.Suorituspaivamaara >= @Syys_alk
DROP TABLE #KELA_paattynyt
DROP TABLE #Aloittanut
DROP TABLE #Min_aloituspvm
Valmistuneet AMK - Ei tutkintoon johtava koulutus (E)
Opinnot loppuun suorittaneet - Ei tutkintoon johtava koulutus Expand source
/* Versio 1 Opiskeluoikeuden tila päättynyt (3) alkamispvm = päättymispäivämäärä */
DECLARE @YEAR varchar(4) = '2015'
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+'-08-01'
CREATE TABLE #Aloittanut (
oid int,
Alkamispaivamaara date,
Paattymispaivamaara date
)
INSERT INTO #Aloittanut (oid, Alkamispaivamaara, Paattymispaivamaara)
SELECT oo.ID
,l.Alkamispaivamaara
,l.Paattymispaivamaara
FROM dbo.Henkilo H
JOIN dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN dbo.Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
JOIN dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
JOIN dbo.[Opiskeluoikeuden_tyyppi] OOT ON OO.Op_oikeuden_tyyppiID = OOT.id
WHERE LT.Koodi = 1 /* Läsnä */
AND OOT.Koodi IN (12,14,15)
CREATE TABLE #Min_aloituspvm (
oid int,
alk_pvm date
)
INSERT INTO #Min_aloituspvm (oid, alk_pvm)
SELECT oid, MIN(alkamispaivamaara) As alk_pvm
FROM #Aloittanut
GROUP BY oid
CREATE TABLE #KELA_paattynyt (
oid int,
tila_koodi int,
Alkamispaivamaara date
)
INSERT INTO #KELA_paattynyt (oid,tila_koodi,Alkamispaivamaara)
SELECT OO.ID AS oID
, OT.Koodi AS tila_koodi
, OOT.Alkamispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
JOIN dbo.[Opiskeluoikeuden_tyyppi] OOTyyp ON OO.Op_oikeuden_tyyppiID = OOTyyp.id
WHERE OOT.Alkamispaivamaara >= @Syys_alk
AND OT.Koodi = 3 /* Päättynyt */
AND OOTyyp.koodi IN (12,14,15)
SELECT DISTINCT
OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'W' AS Tapahtumakoodi
,REPLACE((CONVERT(varchar(12), KP.Alkamispaivamaara, 105)),'-','') AS Paivamaara
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, ' ' AS kevatlukukausi
,FLOOR(OO.Laajuus) AS tutkinnon_laajuus
,REPLACE((CONVERT(varchar(12), M.alk_pvm, 105)),'-','') AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Opintosuoritus OS ON OS.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
LEFT JOIN #Min_aloituspvm M ON M.oid = OO.ID
LEFT JOIN #KELA_paattynyt KP ON KP.oid = OO.ID
WHERE OO.ID IN (SELECT oid AS ID FROM #KELA_paattynyt)
AND OT.Koodi IN (12,14,15)
DROP TABLE #KELA_paattynyt
DROP TABLE #Aloittanut
DROP TABLE #Min_aloituspvm
/* Versio 2 Opiskeluoikeuden tila päättynyt (3) aktiivisen (1) tilan päättymispäivämäärä = valmistumispäivämäärä */
DECLARE @YEAR varchar(4) = '2015'
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+''-08-01''
CREATE TABLE #Aloittanut (
oid int,
Alkamispaivamaara date,
Paattymispaivamaara date
)
INSERT INTO #Aloittanut (oid, Alkamispaivamaara, Paattymispaivamaara)
SELECT oo.ID
,l.Alkamispaivamaara
,l.Paattymispaivamaara
FROM dbo.Henkilo H
JOIN dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN dbo.Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
JOIN dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
JOIN dbo.[Opiskeluoikeuden_tyyppi] OOT ON OO.Op_oikeuden_tyyppiID = OOT.id
WHERE LT.Koodi = 1 /* Läsnä */
AND OOT.Koodi IN (12,14,15)
/* Jos ei läsnäolo ilmoituksia */
INSERT INTO #Aloittanut (oid, Alkamispaivamaara, Paattymispaivamaara)
SELECT oo.ID
,oo.Alkamispaivamaara
,oo.Paattymispaivamaara
FROM dbo.Henkilo H
JOIN dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN dbo.[Opiskeluoikeuden_tyyppi] OOT ON OO.Op_oikeuden_tyyppiID = OOT.id
WHERE OO.id NOT IN (SELECT OpiskeluoikeusID AS id FROM dbo.Lukukausi_ilmoittautuminen)
AND OOT.Koodi IN (12,14,15)
AND OO.Paattymispaivamaara >= @Syys_alk
CREATE TABLE #Min_aloituspvm (
oid int,
alk_pvm date
)
INSERT INTO #Min_aloituspvm (oid, alk_pvm)
SELECT oid, MIN(alkamispaivamaara) As alk_pvm
FROM #Aloittanut
GROUP BY oid
CREATE TABLE #KELA_paattynyt (
oid int,
tila_koodi int,
Alkamispaivamaara date
)
INSERT INTO #KELA_paattynyt (oid,tila_koodi,Alkamispaivamaara)
SELECT OO.ID AS oID
, OT.Koodi AS tila_koodi
, max(OOT.Paattymispaivamaara) AS Alkamispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
JOIN dbo.[Opiskeluoikeuden_tyyppi] OOTyyp ON OO.Op_oikeuden_tyyppiID = OOTyyp.id
WHERE (OOT.Paattymispaivamaara >= @Syys_alk)
AND OT.Koodi = 1 /* Aktiivinen */
AND OOTyyp.koodi IN (12,14,15)
AND OO.ID IN (
SELECT DISTINCT OO.ID
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Alkamispaivamaara>= @Syys_alk
AND OT.Koodi = 3 /* Valmistunut */
AND LEN(LTRIM(H.Henkilotunnus))>7
)
GROUP BY OO.ID, OT.Koodi
SELECT DISTINCT
OO.ID AS opiskeluoikeus_ID
,''L''+ORG.Koodi AS siirtotunnus
,''T'' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),''-'','''') AS poimintapaivamaara
,''OPISK'' AS siirtolaji
,''OP'' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN ''T''
WHEN OT.Koodi = 3 THEN ''H''
WHEN OT.Koodi IN (12,14,15) THEN ''E''
END
AS opinnot_johon_hyvaksytty
,''W'' AS Tapahtumakoodi
,REPLACE((CONVERT(varchar(12), KP.Alkamispaivamaara, 105)),''-'','''') AS Paivamaara
,CASE WHEN OT.Koodi = 1 THEN ''T''
WHEN OT.Koodi = 3 THEN ''H''
WHEN OT.Koodi IN (12,14,15) THEN ''E''
END
AS valmistunut_tutkinto
, '' '' AS syyslukukausi
, '' '' AS kevatlukukausi
,FLOOR(OO.Laajuus) AS tutkinnon_laajuus
,REPLACE((CONVERT(varchar(12), M.alk_pvm, 105)),''-'','''') AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN ''M''
WHEN H.SukupuoliID = 2 THEN ''N''
END
AS Sukupuoli
, '' '' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Opintosuoritus OS ON OS.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
LEFT JOIN #Min_aloituspvm M ON M.oid = OO.ID
LEFT JOIN #KELA_paattynyt KP ON KP.oid = OO.ID
WHERE OO.ID IN (SELECT oid AS ID FROM #KELA_paattynyt)
AND OT.Koodi IN (12,14,15)
DROP TABLE #KELA_paattynyt
DROP TABLE #Aloittanut
DROP TABLE #Min_aloituspvm
Läsnä- ja poissaolevat AMK (Tapahtumakoodi L)
Jos opiskelijalla on useampi opiskeluoikeus muodostuu hänelle tiedostoon kaksi tai useampi rivi, mahdollisesti eri läsnäolotiedolla, eli voi olla saman aikaisesti läsnä ja poissa.
Läsnä-Poissa AMK Expand source
DECLARE @YEAR varchar(4) = '2015'
DECLARE @plus_vuosi varchar(4)
SET @plus_vuosi = convert(int,@YEAR) + 1
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+'-08-01'
DECLARE @Syys_lop date
SET @Syys_lop = @YEAR+'-12-31'
DECLARE @Kevat_alk date
SET @Kevat_alk = @plus_vuosi + '-01-01'
DECLARE @Kevat_lop date
SET @Kevat_lop = @plus_vuosi + '-07-31'
DECLARE @Paivamaara varchar(8)
SET @Paivamaara = '0108'+@YEAR
/* Jos on uudestaan aktiivinen ja ollut päättyneessä tilassa */
CREATE TABLE #reactivated ( oid int )
INSERT INTO #reactivated (oid)
SELECT DISTINCT OO.ID AS oid
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Alkamispaivamaara >= @Syys_alk
AND OT.Koodi IN ( 3, 4, 5 ) /* Päättynyt jotenkin */
AND OO.ID IN (
SELECT DISTINCT OO.ID AS oID
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Paattymispaivamaara > GETDATE()
AND OT.Koodi = 1 /* Taas aktiivinen */
AND LEN(LTRIM(H.Henkilotunnus))>7
)
CREATE TABLE #KELA_aktiivinen (
oid int,
tila_koodi int
)
INSERT INTO #KELA_aktiivinen (oid,tila_koodi)
SELECT DISTINCT OO.ID AS oID
, OT.Koodi AS tila_koodi
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE (OOT.Paattymispaivamaara > @Syys_alk OR OOT.Paattymispaivamaara IS NULL)
AND OT.Koodi = 1 /* Aktiivinen */
AND LEN(LTRIM(H.Henkilotunnus))>7
AND OO.ID NOT IN ( SELECT DISTINCT OO.ID
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Alkamispaivamaara >= @Syys_alk
AND OT.Koodi IN ( 3, 4, 5) /* Päättynyt jotenkin */
AND OO.ID NOT IN ( SELECT oid AS id FROM #reactivated ) /* Uudestaan aktiivi */
)
CREATE TABLE #KELA_lasnaolo (
oid int,
tila_koodi int,
lukukausi char,
Alkamispaivamaara date,
Paattymispaivamaara date
)
/* Syksy Läsnä L */
INSERT INTO #KELA_lasnaolo (oid,tila_koodi,lukukausi,Alkamispaivamaara,Paattymispaivamaara)
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
, LT.Koodi AS tila_koodi
, 'S' AS lukukausi
, L.Alkamispaivamaara
, L.Paattymispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE L.Alkamispaivamaara >= @Syys_alk
AND L.Paattymispaivamaara <= @Syys_lop
AND LT.Koodi = 1 /* Läsnä */
AND LEN(LTRIM(H.Henkilotunnus))>7
/* Syksy Poissa P */
INSERT INTO #KELA_lasnaolo (oid,tila_koodi,lukukausi,Alkamispaivamaara,Paattymispaivamaara)
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
, LT.Koodi AS tila_koodi
, 'S' AS lukukausi
, L.Alkamispaivamaara
, L.Paattymispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE L.Alkamispaivamaara >= @Syys_alk
AND L.Paattymispaivamaara <= @Syys_lop
AND LT.Koodi IN (2,3) /* Poissa */
AND LEN(LTRIM(H.Henkilotunnus))>7
/* Kevat Läsnä L */
INSERT INTO #KELA_lasnaolo (oid,tila_koodi,lukukausi,Alkamispaivamaara,Paattymispaivamaara)
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
, LT.Koodi AS tila_koodi
, 'K' AS lukukausi
, L.Alkamispaivamaara
, L.Paattymispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE L.Alkamispaivamaara >= @Kevat_alk
AND L.Paattymispaivamaara <= @Kevat_lop
AND LT.Koodi = 1 /* Läsnä */
AND LEN(LTRIM(H.Henkilotunnus))>7
/* Kevat Poissa P */
INSERT INTO #KELA_lasnaolo (oid,tila_koodi,lukukausi,Alkamispaivamaara,Paattymispaivamaara)
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
, LT.Koodi AS tila_koodi
, 'K' AS lukukausi
, L.Alkamispaivamaara
, L.Paattymispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE L.Alkamispaivamaara >= @Kevat_alk
AND L.Paattymispaivamaara <= @Kevat_lop
AND LT.Koodi IN (2,3) /* Poissa */
AND LEN(LTRIM(H.Henkilotunnus))>7
/* LL */
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'L' AS syyslukukausi
, 'L' AS kevatlukukausi
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE
OT.Koodi IN (1,3,12,14,15)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_aktiivinen)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'S')
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))>7
/* PP */
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'P' AS syyslukukausi
, 'P' AS kevatlukukausi
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE
OT.Koodi IN (1,3,12,14,15)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_aktiivinen)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'S')
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))>7
/* L */
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
/* ,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS valmistunut_tutkinto */
, ' ' AS valmistunut_tutkinto
, 'L' AS syyslukukausi
, ' ' AS kevatlukukausi
-- Kevät puuttuu
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE
OT.Koodi IN (1,3,12,14,15)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_aktiivinen)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'S')
AND OO.ID NOT IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))>7
/* P */
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'P' AS syyslukukausi
, ' ' AS kevatlukukausi
-- Kevät puuttuu
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE
OT.Koodi IN (1,3,12,14,15)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_aktiivinen)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'S')
AND OO.ID NOT IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))>7
/* LP */
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'L' AS syyslukukausi
, 'P' AS kevatlukukausi
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE
OT.Koodi IN (1,3,12,14,15)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_aktiivinen)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'S')
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))>7
/* PL */
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'P' AS syyslukukausi
, 'L' AS kevatlukukausi
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE
OT.Koodi IN (1,3,12,14,15)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_aktiivinen)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'S')
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))>7
/* _L */
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
/* ,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS valmistunut_tutkinto */
, ' ' AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, 'L' AS kevatlukukausi
-- syys puuttuu
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE
OT.Koodi IN (1,3,12,14,15)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_aktiivinen)
AND OO.ID NOT IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE lukukausi = 'S')
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))>7
/* _P */
SELECT DISTINCT OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OT.Koodi = 1 THEN 'T'
WHEN OT.Koodi = 3 THEN 'H'
WHEN OT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, 'P' AS kevatlukukausi
-- Syys puuttuu
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE
OT.Koodi IN (1,3,12,14,15)
AND OO.ID IN (SELECT oid AS ID FROM #KELA_aktiivinen)
AND OO.ID NOT IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE lukukausi = 'S')
AND OO.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))>7
DROP TABLE #KELA_lasnaolo
DROP TABLE #KELA_aktiivinen
DROP TABLE #reactivated
Keskeyttäneet AMK (Tapahtumakoodi K - K)
Jos opiskelijalla on aktiivinen voimassa oleva opiskeluoikeus (tapahtumakoodi L) keskeyttämistietoa ei poimita.
Eronnut omasta ilmoituksesta K Expand source
/* Versio 1. niille korkeakouluille joiden päättymispäivämäärä on opiskelutilan päättynyt (5) alkamispäivämäärä */
/* Katso alempana versio 2. poiminta korkeakouluille joiden päättymistpäivämäärä on viimeinen aktiivinen (1) päivä */
DECLARE @YEAR varchar(4) = '2015'
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+'-08-01'
/* Jos on vielä aktivinen vaikka on keskeytys merkintä */
CREATE TABLE #KELA_viela_aktiivinen (
oid int,
tila_koodi int
)
INSERT INTO #KELA_viela_aktiivinen (oid,tila_koodi)
SELECT DISTINCT O.ID AS oID
, OT.Koodi AS tila_koodi
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
JOIN dbo.[Opiskeluoikeuden_tyyppi] OOOT ON OO.Op_oikeuden_tyyppiID = OOOT.id
WHERE (OOT.Paattymispaivamaara > GETDATE() OR OOT.Paattymispaivamaara IS NULL )
AND OT.Koodi = 1 /* Aktiivinen */
AND LEN(LTRIM(H.Henkilotunnus))>7
AND OOOT.Koodi NOT IN (9,13,17,18) /* ei avoimen, vaihto, valmentava, erillinen */
/* Opiskelujen päättymispaiva */
CREATE TABLE #KELA_paattynyt (
oid int,
tila_koodi int,
Alkamispaivamaara date
)
INSERT INTO #KELA_paattynyt (oid,tila_koodi,Alkamispaivamaara)
SELECT OO.ID AS oID
, OT.Koodi AS tila_koodi
, OOT.Alkamispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Alkamispaivamaara >= @Syys_alk
AND OT.Koodi = 5 /* Luopunut */
SELECT DISTINCT
OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,H.Henkilotunnus AS henkilotunnus
,H.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OTT.Koodi = 1 THEN 'T'
WHEN OTT.Koodi = 3 THEN 'H'
WHEN OTT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'K' AS Tapahtumakoodi
/* Viimeinen aktiivinen on päättymispäivä */
,REPLACE((CONVERT(varchar(12), KP.Alkamispaivamaara, 105)),'-','') AS Paivamaara
,' ' AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, ' ' AS kevatlukukausi
,' ' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, 'K' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
JOIN Opiskelija O ON O.HenkiloID = H.ID
JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
JOIN #KELA_paattynyt KP ON KP.oid = OO.ID
JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
JOIN Opiskeluoikeuden_tyyppi OTT ON OTT.ID = OO.Op_oikeuden_tyyppiID
WHERE O.ID NOT IN (SELECT oid AS ID FROM #KELA_viela_aktiivinen)
AND OTT.Koodi IN (1,3,12,14,15)
AND OT.Koodi = 5 /* Luopunut */
AND OOT.Alkamispaivamaara >= @Syys_alk
AND LEN(LTRIM(H.Henkilotunnus))>7
DROP TABLE #KELA_viela_aktiivinen
DROP TABLE #KELA_paattynyt
/* Versio 2. Opiskeluoikeuden viimeinen aktiivinen päivä on päättymispäivä */
DECLARE @YEAR varchar(4) = '2015'
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+'-08-01'
/* Jos on vielä aktivinen vaikka on keskeytys merkintä */
CREATE TABLE #KELA_viela_aktiivinen (
oid int,
tila_koodi int
)
INSERT INTO #KELA_viela_aktiivinen (oid,tila_koodi)
SELECT DISTINCT O.ID AS oID
, OT.Koodi AS tila_koodi
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
JOIN dbo.[Opiskeluoikeuden_tyyppi] OOOT ON OO.Op_oikeuden_tyyppiID = OOOT.id
WHERE (OOT.Paattymispaivamaara > GETDATE() OR OOT.Paattymispaivamaara IS NULL )
AND OT.Koodi = 1 /* Aktiivinen */
AND LEN(LTRIM(H.Henkilotunnus))>7
AND OOOT.Koodi NOT IN (9,13,17,18) /* ei avoimen, vaihto, valmentava, erillinen */
/* Opiskelujen viimeinen aktiivinen päivä on päättymispaiva */
CREATE TABLE #KELA_paattynyt (
oid int,
tila_koodi int,
Alkamispaivamaara date
)
INSERT INTO #KELA_paattynyt (oid,tila_koodi,Alkamispaivamaara)
SELECT OO.ID AS oID
, OT.Koodi AS tila_koodi
, max(OOT.Paattymispaivamaara) AS Alkamispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Paattymispaivamaara >= @Syys_alk
AND OT.Koodi = 1 /* Aktiivinen */
AND OO.ID IN (
SELECT DISTINCT OO.ID
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Alkamispaivamaara>= @Syys_alk
AND OT.Koodi = 5 /* Luopunut */
AND LEN(LTRIM(H.Henkilotunnus))>7
)
GROUP BY OO.ID, OT.Koodi
SELECT DISTINCT
OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,H.Henkilotunnus AS henkilotunnus
,H.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OTT.Koodi = 1 THEN 'T'
WHEN OTT.Koodi = 3 THEN 'H'
WHEN OTT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'K' AS Tapahtumakoodi
/* Viimeinen aktiivinen on päättymispäivä */
,REPLACE((CONVERT(varchar(12), KP.Alkamispaivamaara, 105)),'-','') AS Paivamaara
,' ' AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, ' ' AS kevatlukukausi
,' ' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, 'K' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
JOIN Opiskelija O ON O.HenkiloID = H.ID
JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
JOIN #KELA_paattynyt KP ON KP.oid = OO.ID
JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
JOIN Opiskeluoikeuden_tyyppi OTT ON OTT.ID = OO.Op_oikeuden_tyyppiID
WHERE O.ID NOT IN (SELECT oid AS ID FROM #KELA_viela_aktiivinen)
AND OTT.Koodi IN (1,3,12,14,15)
AND OT.Koodi = 5 /* Luopunut */
AND OOT.Alkamispaivamaara >= @Syys_alk
AND LEN(LTRIM(H.Henkilotunnus))>7
DROP TABLE #KELA_viela_aktiivinen
DROP TABLE #KELA_paattynyt
Keskeyttämispäivämäärän poiminta
Kela opintojen keskeyttämispäivän poimintaan Virrasta on kaksi tapaa riippuen siitä kuinka opiskeluoikeuden tilan muutoksen päivämäärä on tuotu Virtaan ja miten keskeyttämispäivä on ilmoitettu Kelaan nykyisillä poiminnoilla.
Kela keskeyttämispäivä (päivämäärä tieto Kelan tietuekuvauksen posiitiossa 113) =
1.) opiskelutilan päättynyt (5) alkamispäivämäärä (ei siis opiskeluoikeuden viimeinen aktiivinen päivä)
Esim. jos keskeyttämispäivä on 10.8.2015. Virtaan tuotu opiskeluoikeuden viimeinen aktiivinen (1) päivä on 9.8.2015.
Opiskeluoikeuden tila päättynyt (5) alkamispäivämäärä 10.8.2015 (tilan (5) päättymispvm 10.8.2015 tai NULL).
Poimitaan siis keskeyttämispäivä = alkamispäivämäärä 10.8.2015
2.) opiskeluoikeuden viimeinen aktiivinen (1) päivä = päättymispäivämäärä
Esim. jos keskeyttämispäivä on 31.12.2014. Virtaan tuotu opiskeluoikeuden viimeinen aktiivinen (1) päivä 31.12.2014, opiskeluoikeuden tila päättynyt tila alkaa 1.1.2015.
Poimitaan siis keskeyttämispäivä = päättymispäivämäärä 31.12.2014
Katso kuva opiskeluoikeuden tila.
Keskeyttäneet AMK (Tapahtumakoodi K - E)
Jos opiskelijalla on aktiivinen voimassa oleva opiskeluoikeus (tapahtumakoodi L) keskeyttämistietoa ei poimita.
Eronnut - ei omasta ilmoituksesta (E) Expand source
/* Versio 1. niille korkeakouluille joiden päättymispäivämäärä on opiskelutilan passivoitu (4) alkamispäivämäärä */
/* Katso alempana versio 2. poiminta korkeakouluille joiden päättymispäivämäärä on viimeinen aktiivinen (1) päivä */
DECLARE @YEAR varchar(4) = '2015'
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+'-08-01'
/* Jos on vielä aktivinen vaikka on E keskeytys merkintä */
CREATE TABLE #KELA_viela_aktiivinen (
oid int,
tila_koodi int,
hetu varchar(12)
)
INSERT INTO #KELA_viela_aktiivinen (oid,tila_koodi,hetu)
SELECT DISTINCT O.ID AS oID
, OT.Koodi AS tila_koodi
, H.Henkilotunnus AS hetu
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
JOIN dbo.[Opiskeluoikeuden_tyyppi] OOOT ON OO.Op_oikeuden_tyyppiID = OOOT.id
WHERE (OOT.Paattymispaivamaara > GETDATE() OR OOT.Paattymispaivamaara IS NULL )
AND OT.Koodi = 1 /* Aktiivinen */
AND LEN(LTRIM(H.Henkilotunnus))>7
AND OOOT.Koodi NOT IN (9,13,17,18) /* ei avoimen, vaihto, valmentava, erillinen */
/* Opiskelujen viimeinen aktiivinen päivä on päättymispaiva */
CREATE TABLE #KELA_paattynyt (
oid int,
tila_koodi int,
Alkamispaivamaara date
)
INSERT INTO #KELA_paattynyt (oid,tila_koodi,Alkamispaivamaara)
SELECT OO.ID AS oID
, OT.Koodi AS tila_koodi
, OOT.Alkamispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Alkamispaivamaara >= @Syys_alk
AND OT.Koodi = 4 /* Passivoitu */
/* Eikä saa olla valmistuneiden joukossa vaikka on keskytysmerkintä ennen sitä */
AND OO.ID NOT IN ( SELECT DISTINCT OO.ID
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Alkamispaivamaara>= @Syys_alk
AND OT.Koodi = 3 /* Päättynyt */
AND OO.id IN ( SELECT opiskeluoikeusid AS id
FROM Opintosuoritus
WHERE Opintosuorituksen_lajiID = 1 )
)
SELECT DISTINCT
OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,H.Henkilotunnus AS henkilotunnus
,H.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OTT.Koodi = 1 THEN 'T'
WHEN OTT.Koodi = 3 THEN 'H'
WHEN OTT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'K' AS Tapahtumakoodi
/* Viimeinen aktiivinen on päättymispäivä */
,REPLACE((CONVERT(varchar(12), KP.Alkamispaivamaara, 105)),'-','') AS Paivamaara
,' ' AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, ' ' AS kevatlukukausi
,' ' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, 'E' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
LEFT JOIN #KELA_paattynyt KP ON KP.oid = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OTT ON OTT.ID = OO.Op_oikeuden_tyyppiID
WHERE
H.Henkilotunnus NOT IN (SELECT hetu AS henkilotunnus FROM #KELA_viela_aktiivinen)
AND OT.Koodi = 4 /* Passivoitu */
AND OTT.Koodi IN (1,3,12,14,15)
AND OOT.Alkamispaivamaara >= @Syys_alk
AND LEN(LTRIM(H.Henkilotunnus))>7
AND KP.Alkamispaivamaara IS NOT NULL
DROP TABLE #KELA_viela_aktiivinen
DROP TABLE #KELA_paattynyt
/* Versio 2. */
/* Opiskelujen viimeinen aktiivinen (1) päivä on päättymispaiva */
DECLARE @YEAR varchar(4) = '2015'
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+'-08-01'
/* Jos on vielä aktivinen vaikka on E keskeytys merkintä */
CREATE TABLE #KELA_viela_aktiivinen (
oid int,
tila_koodi int,
hetu varchar(12)
)
INSERT INTO #KELA_viela_aktiivinen (oid,tila_koodi,hetu)
SELECT DISTINCT O.ID AS oID
, OT.Koodi AS tila_koodi
, H.Henkilotunnus AS hetu
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
JOIN dbo.[Opiskeluoikeuden_tyyppi] OOOT ON OO.Op_oikeuden_tyyppiID = OOOT.id
WHERE (OOT.Paattymispaivamaara > GETDATE() OR OOT.Paattymispaivamaara IS NULL )
AND OT.Koodi = 1 /* Aktiivinen */
AND LEN(LTRIM(H.Henkilotunnus))>7
AND OOOT.Koodi NOT IN (9,13,17,18) /* ei avoimen, vaihto, valmentava, erillinen */
/* Opiskelujen viimeinen aktiivinen päivä on päättymispaiva */
CREATE TABLE #KELA_paattynyt (
oid int,
tila_koodi int,
Alkamispaivamaara date
)
INSERT INTO #KELA_paattynyt (oid,tila_koodi,Alkamispaivamaara)
SELECT OO.ID AS oID
, OT.Koodi AS tila_koodi
, max(OOT.Paattymispaivamaara) AS Alkamispaivamaara
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Paattymispaivamaara >= @Syys_alk
AND OT.Koodi = 1 /* Aktiivinen */
AND OO.ID IN (
SELECT DISTINCT OO.ID
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Alkamispaivamaara>= @Syys_alk
AND OT.Koodi = 4 /* Passivoitu */
AND LEN(LTRIM(H.Henkilotunnus))>7
)
/* Eikä saa olla valmistuneiden joukossa vaikka on keskytysmerkintä ennen sitä */
AND OO.ID NOT IN ( SELECT DISTINCT OO.ID
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE OOT.Alkamispaivamaara>= @Syys_alk
AND OT.Koodi = 3 /* Päättynyt */
AND OO.id IN ( SELECT opiskeluoikeusid AS id
FROM Opintosuoritus
WHERE Opintosuorituksen_lajiID = 1 )
)
GROUP BY OO.ID, OT.Koodi
SELECT DISTINCT
OO.ID AS opiskeluoikeus_ID
,'L'+ORG.Koodi AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,ORG.Koodi AS oppilaitos
,H.Henkilotunnus AS henkilotunnus
,H.Sukunimi
,substring(h.Etunimet,1,30) AS Etunimet
,CASE WHEN OTT.Koodi = 1 THEN 'T'
WHEN OTT.Koodi = 3 THEN 'H'
WHEN OTT.Koodi IN (12,14,15) THEN 'E'
END
AS opinnot_johon_hyvaksytty
,'K' AS Tapahtumakoodi
/* Viimeinen aktiivinen on päättymispäivä */
,REPLACE((CONVERT(varchar(12), KP.Alkamispaivamaara, 105)),'-','') AS Paivamaara
,' ' AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, ' ' AS kevatlukukausi
,' ' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, 'E' AS keskeyttamisen_ilmoittaja
, GETDATE()
, substring(ORG.Nimi,1,20) AS organisaatio
FROM Henkilo H
LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
LEFT JOIN Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
LEFT JOIN #KELA_paattynyt KP ON KP.oid = OO.ID
LEFT JOIN Op_oikeuden_organisaatio OOO ON OOO.OpiskeluoikeusID = oo.ID
LEFT JOIN Organisaatio ORG ON ORG.ID = OOO.OrganisaatioID
LEFT JOIN Opiskeluoikeuden_tyyppi OTT ON OTT.ID = OO.Op_oikeuden_tyyppiID
WHERE
H.Henkilotunnus NOT IN (SELECT hetu AS henkilotunnus FROM #KELA_viela_aktiivinen)
AND OT.Koodi = 4 /* Passivoitu */
AND OTT.Koodi IN (1,3,12,14,15)
AND OOT.Alkamispaivamaara >= @Syys_alk
AND LEN(LTRIM(H.Henkilotunnus))>7
AND KP.Alkamispaivamaara IS NOT NULL
DROP TABLE #KELA_viela_aktiivinen
DROP TABLE #KELA_paattynyt
Opiskelutiedon poiminnat YO
Yliopistojen opiskelutiedot kerätään KELA_opiskelutiedot tauluun.
Läsnäolo-olot (L), valmistumistiedot (W)
KELA Opiskelutiedot taulu Expand source
CREATE TABLE [KELA_opiskelutiedot](
[opiskeluoikeus_ID] [int] NOT NULL,
[siirtotunnus] [varchar](6) NOT NULL,
[tietuetyyppi] [char](1) NOT NULL,
[poimintapaivamaara] [varchar](8) NOT NULL,
[siirtolaji] [varchar](5) NOT NULL,
[lahettajaryhman_tunnus] [varchar](2) NOT NULL,
[oppilaitos] [varchar](5) NOT NULL,
[henkilotunnus] [varchar](11) NOT NULL,
[sukunimi] [varchar](30) NOT NULL,
[etunimet] [varchar](30) NOT NULL,
[opinnot_johon_hyvaksytty] [char](1) NOT NULL,
[tapahtumakoodi] [char](1) NOT NULL,
[paivamaara] [varchar](8) NOT NULL,
[valmistunut_tutkinto] [char](1) NOT NULL,
[syyslukukausi] [char](1) NOT NULL,
[kevatlukukausi] [char](1) NOT NULL,
[tutkinnon_laajuus] [varchar](3) NOT NULL,
[opintojen_aloituspaiva] [varchar](8) NULL,
[sukupuoli] [char](1) NOT NULL,
[keskeyttamisen_ilmoittaja] [char](1) NOT NULL,
[reg_date] [smalldatetime] NOT NULL,
[organisaatio] [varchar](20) NULL,
[tilastokeskuksen_koulutuskoodi] [varchar](6) NULL,
[kela_tutkinnon_taso] [varchar](3) NULL,
[kk] [varchar](20) NULL,
[opiskelijaAvain] [varchar](100) NULL,
[opiskeluOikeusAvain] [varchar](100) NULL
)
Valmistuneet YO-tutkinnot (Tapahtumakoodi W)
Valmistuneet YO tutkinnot (Tapahtumakoodi W) Expand source
DECLARE @mydb varchar(20) /* Korkeakoulu lyhenne Virrassa esim @mydb = 'HY' */
DECLARE @vuosi varchar(4) /* Ajankohtainen lukuvuosi esim @vuosi = 2016 */
DECLARE @YEAR varchar(4) = @vuosi
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+'-08-01'
/* Päättyneet oikeudet */
CREATE TABLE #KELA_paattynyt (
opiskeluoikeusid int,
opiskelijaid int,
tila_koodi int,
Tilan_Alkamispaivamaara date,
opiskelijaAvain varchar(100),
opiskeluoikeusAvain varchar(100),
hetu varchar(15),
oo_paattynyt date
)
/* Syksystä alkaen päättyneet (tilakoodilla 3) opiskeluoikeudet */
INSERT INTO #KELA_paattynyt (opiskeluoikeusid,opiskelijaid,tila_koodi,Tilan_Alkamispaivamaara,opiskelijaAvain,opiskeluoikeusAvain, hetu,oo_paattynyt)
SELECT DISTINCT OO.ID AS opiskeluoikeusid
, O.id AS opiskelijaid
, OT.Koodi AS tila_koodi
, OOT.Alkamispaivamaara AS Tilan_Alkamispaivamaara
, O.avain
, OO.avain
, H.henkilotunnus
, OO.Paattymispaivamaara
FROM virta.dbo.Henkilo H
JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
JOIN virta.dbo.Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
JOIN virta.dbo.Opiskeluoikeuden_tyyppi OTYP ON OO.Op_oikeuden_tyyppiID = OTYP.id
WHERE
H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND OOT.db = @mydb
AND OOT.Alkamispaivamaara >= @Syys_alk
AND OOT.Alkamispaivamaara < getdate()
AND OT.Koodi = 3 /* Päättynyt */
AND OTYP.Koodi NOT IN (8,9,10,13,14,18) /* Ei kotimainen tai ulkomaa opiskelijaliikkuvuus,täydennyskoulutus, avoimet, erillinen opiskeluoikeus */
AND OO.id NOT IN (SELECT KP.opiskeluoikeusid FROM #KELA_paattynyt KP)
/* Opiskeluoikeudet Kela koodeilla */
CREATE TABLE #opiskelu_oikeudet_yo (
opiskelijaid int,
kela_opinto_koodi char(1),
virta_koodi smallint,
opiskeluoikeusid int,
opiskeluoikeus_paattymispvm date,
hetu varchar(15),
tutkinto_laajuus int
)
INSERT INTO #opiskelu_oikeudet_yo (opiskelijaid,kela_opinto_koodi,virta_koodi,opiskeluoikeusid,opiskeluoikeus_paattymispvm,hetu,tutkinto_laajuus )
SELECT DISTINCT O.ID AS opiskelijaid
, CASE WHEN OT.Koodi = 2 THEN 'A'
WHEN OT.Koodi = 4 THEN 'Y'
WHEN OT.Koodi IN ( 6,7) THEN 'J'
END
AS kela_opinto_koodi
, OT.koodi AS virta_koodi
, OO.id AS opiskeluoikeusid
, OO.Paattymispaivamaara AS opiskeluoikeus_paattymispvm
, H.henkilotunnus
, FLOOR(OO.Laajuus) AS tutkinto_laajuus
FROM virta.dbo.Opiskeluoikeus OO
JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OO.Op_oikeuden_tyyppiID = OT.id
JOIN virta.dbo.Opiskelija O ON O.id = OO.OpiskelijaID
JOIN virta.dbo.Henkilo H ON H.id = o.HenkiloID
WHERE OO.ID IN (SELECT opiskeluoikeusid AS ID FROM #KELA_paattynyt)
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND OT.Koodi NOT IN (8,9,10,13,14,18) /* Ei kotimainen tai ulkomaa opiskelijaliikkuvuus,täydennyskoulutus, avoimet, erillinen opiskeluoikeus */
ORDER BY O.ID
UPDATE OYO
SET OYO.kela_opinto_koodi = 'Y'
FROM #opiskelu_oikeudet_yo OYO
WHERE OYO.kela_opinto_koodi = 'A'
AND OYO.opiskelijaid IN ( SELECT OO2.OpiskelijaID
FROM virta.dbo.Opiskeluoikeus OO2
JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT2 ON OO2.Op_oikeuden_tyyppiID = OT2.id
WHERE OO2.OpiskelijaID = OYO.opiskelijaid
AND OT2.koodi = '4'
AND OO2.db = @mydb
)
UPDATE OYO
SET OYO.kela_opinto_koodi = 'R'
FROM #opiskelu_oikeudet_yo OYO
WHERE OYO.kela_opinto_koodi = 'Y'
AND OYO.opiskelijaid NOT IN ( SELECT OO2.OpiskelijaID
FROM virta.dbo.Opiskeluoikeus OO2
JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT2 ON OO2.Op_oikeuden_tyyppiID = OT2.id
WHERE OO2.OpiskelijaID = OYO.opiskelijaid
AND OT2.koodi = '2'
AND OO2.db = @mydb
)
CREATE TABLE #kela_A_tutkinto (
opiskelijaid int,
opiskeluoikeusid int,
kela_opinto_koodi char(1),
valmistunut_tutkinto char(1) null,
tutkinto_laajuus int null ,
jatkamistieto char(1),
tutkintokoodi int
)
CREATE TABLE #kela_tutkinto (
opiskelijaid int,
opiskeluoikeusid int,
kela_opinto_koodi char(1),
valmistunut_tutkinto char(1) null,
tutkinto_laajuus int null,
jatkamistieto char(1),
tutkintokoodi int
)
CREATE TABLE #kela_tutkinto_speciale (
opiskelijaid int,
opiskeluoikeusid int,
kela_opinto_koodi char(1),
valmistunut_tutkinto char(1) null,
tutkinto_laajuus int null,
jatkamistieto char(1),
tutkintokoodi int
)
INSERT INTO #kela_tutkinto ( opiskelijaid, opiskeluoikeusid, kela_opinto_koodi, valmistunut_tutkinto, tutkinto_laajuus, jatkamistieto, tutkintokoodi )
SELECT DISTINCT KOY.opiskelijaid
, KOY.opiskeluoikeusid
, KOY.kela_opinto_koodi
, CASE WHEN KOY.virta_koodi = 2 THEN 'A'
WHEN KOY.virta_koodi = 4 AND KOY.kela_opinto_koodi = 'Y' THEN 'Y'
WHEN KOY.virta_koodi IN (6,7) THEN 'J'
WHEN KOY.kela_opinto_koodi = 'R' THEN 'R'
END
AS valmistunut_tutkinto
, CASE WHEN KOY.virta_koodi = 2 THEN 180
WHEN KOY.virta_koodi = 4 AND KOY.kela_opinto_koodi = 'Y' THEN 300
WHEN KOY.virta_koodi IN (6,7) THEN CEILING(OS.laajuus)
WHEN KOY.kela_opinto_koodi = 'R' THEN 120
END
AS tutkinto_laajuus
, CASE WHEN KOY.virta_koodi = 2 THEN 'K'
ELSE 'E'
END
AS jatkamistieto
, T.koodi
FROM #opiskelu_oikeudet_yo KOY
JOIN virta.dbo.Opintosuoritus OS ON OS.OpiskeluoikeusID = KOY.opiskeluoikeusid
JOIN virta.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID
JOIN virta.dbo.Tutkintonimike T ON T.id = OS.TutkintonimikeID
WHERE OS.db = @mydb
AND OSL.koodi = '1'
AND OS.Suorituspaivamaara >= @Syys_alk
AND OS.Suorituspaivamaara <= getdate()
/* Jos opiskeluoikeutta ei ole kytkeytty tutkintosuoritukseen mutta tutkintonimike on */
IF NOT EXISTS ( SELECT *
FROM #opiskelu_oikeudet_yo KOY
JOIN virta.dbo.Opintosuoritus OS ON OS.OpiskeluoikeusID = KOY.opiskeluoikeusid
JOIN virta.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID
WHERE OS.db = @mydb
AND OSL.koodi = '1'
AND OS.Suorituspaivamaara >= @Syys_alk
AND OS.Suorituspaivamaara <= getdate()
)
BEGIN
INSERT INTO #kela_tutkinto_speciale ( opiskelijaid, opiskeluoikeusid, kela_opinto_koodi, valmistunut_tutkinto, tutkinto_laajuus, jatkamistieto,tutkintokoodi )
SELECT DISTINCT KOY.opiskelijaid
, KOY.opiskeluoikeusid
, KOY.kela_opinto_koodi
, CASE WHEN KOY.virta_koodi = 2 THEN 'A'
WHEN KOY.virta_koodi = 4 AND KOY.kela_opinto_koodi = 'Y' THEN 'Y'
WHEN KOY.virta_koodi IN (6,7) THEN 'J'
WHEN KOY.kela_opinto_koodi = 'R' THEN 'R'
END
AS valmistunut_tutkinto
, CASE WHEN KOY.virta_koodi = 2 THEN 180
WHEN KOY.virta_koodi = 4 AND KOY.kela_opinto_koodi = 'Y' THEN 300
WHEN KOY.virta_koodi IN (6,7) THEN CEILING(OS.laajuus)
WHEN KOY.kela_opinto_koodi = 'R' THEN 120
END
AS tutkinto_laajuus
, CASE WHEN KOY.virta_koodi = 2 THEN 'K'
ELSE 'E'
END
AS jatkamistieto
, T.koodi
FROM #opiskelu_oikeudet_yo KOY
JOIN virta.dbo.Opintosuoritus OS ON OS.opiskelijaid = KOY.opiskelijaid
JOIN virta.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID
JOIN virta.dbo.Tutkintonimike T ON T.id = OS.TutkintonimikeID
WHERE OS.db = @mydb
AND OSL.koodi = '1'
AND OS.Suorituspaivamaara >= @Syys_alk
AND OS.Suorituspaivamaara = KOY.opiskeluoikeus_paattymispvm
AND OS.Suorituspaivamaara <= getdate()
END
INSERT INTO #kela_A_tutkinto ( opiskelijaid, opiskeluoikeusid, kela_opinto_koodi, valmistunut_tutkinto, tutkinto_laajuus, jatkamistieto, tutkintokoodi )
SELECT DISTINCT opiskelijaid, opiskeluoikeusid, kela_opinto_koodi, valmistunut_tutkinto, tutkinto_laajuus, jatkamistieto, tutkintokoodi FROM #kela_tutkinto
WHERE valmistunut_tutkinto = 'A'
AND opiskelijaid IN ( SELECT KT.opiskelijaid FROM #kela_tutkinto KT WHERE KT.valmistunut_tutkinto = 'Y' )
IF EXISTS (SELECT * FROM #kela_tutkinto_speciale)
BEGIN
INSERT INTO #kela_A_tutkinto ( opiskelijaid, opiskeluoikeusid, kela_opinto_koodi, valmistunut_tutkinto, tutkinto_laajuus, jatkamistieto, tutkintokoodi )
SELECT DISTINCT opiskelijaid, opiskeluoikeusid, kela_opinto_koodi, valmistunut_tutkinto, tutkinto_laajuus, jatkamistieto, tutkintokoodi FROM #kela_tutkinto_speciale
WHERE valmistunut_tutkinto = 'A'
AND opiskelijaid IN ( SELECT KT.opiskelijaid FROM #kela_tutkinto_speciale KT WHERE KT.valmistunut_tutkinto = 'Y' )
END
DELETE FROM #kela_tutkinto
WHERE valmistunut_tutkinto = 'A'
AND opiskelijaid IN ( SELECT KT.opiskelijaid FROM #kela_tutkinto KT WHERE KT.valmistunut_tutkinto = 'Y' )
IF EXISTS (SELECT * FROM #kela_tutkinto_speciale)
BEGIN
DELETE FROM #kela_tutkinto_speciale
WHERE valmistunut_tutkinto = 'A'
AND opiskelijaid IN ( SELECT KT.opiskelijaid FROM #kela_tutkinto_speciale KT WHERE KT.valmistunut_tutkinto = 'Y' )
END
UPDATE #kela_tutkinto
SET kela_opinto_koodi = 'R'
,tutkinto_laajuus = 120
WHERE kela_opinto_koodi = 'Y'
AND opiskeluoikeusid NOT IN ( SELECT OpiskeluoikeusID2 FROM virta.dbo.Opiskeluoikeuden_liittyvyys WHERE db = @mydb )
AND opiskelijaid IN ( SELECT KT.opiskelijaid FROM #kela_tutkinto KT WHERE KT.valmistunut_tutkinto = 'Y' )
/* Kuvataiteen kandidaatti, kuvataiteen tutkinto (Kuvataideakatemia) 622301 (210 op) */
UPDATE #kela_tutkinto
SET kela_opinto_koodi = 'A'
,tutkinto_laajuus = 210
, jatkamistieto = 'E'
WHERE kela_opinto_koodi = 'Y'
AND tutkintokoodi = 622301
AND opiskeluoikeusid NOT IN ( SELECT OpiskeluoikeusID2 FROM virta.dbo.Opiskeluoikeuden_liittyvyys WHERE db = @mydb )
AND opiskelijaid IN ( SELECT KT.opiskelijaid FROM #kela_tutkinto KT WHERE KT.valmistunut_tutkinto = 'A' )
IF EXISTS (SELECT * FROM #kela_tutkinto_speciale)
BEGIN
UPDATE #kela_tutkinto_speciale
SET kela_opinto_koodi = 'R'
,tutkinto_laajuus = 120
WHERE kela_opinto_koodi = 'Y'
AND opiskeluoikeusid NOT IN ( SELECT OpiskeluoikeusID2 FROM virta.dbo.Opiskeluoikeuden_liittyvyys WHERE db = @mydb )
AND opiskelijaid IN ( SELECT KT.opiskelijaid FROM #kela_tutkinto_speciale KT WHERE KT.valmistunut_tutkinto = 'Y' )
END
/* Lääketieteen lisensiaatti (360 op) */
UPDATE #kela_tutkinto
SET kela_opinto_koodi = 'Y'
, valmistunut_tutkinto = 'Y'
,tutkinto_laajuus = 360
WHERE
tutkintokoodi = 772101
/* Hammaslääketieteen lisensiaatti (330 op) */
UPDATE #kela_tutkinto
SET kela_opinto_koodi = 'Y'
, valmistunut_tutkinto = 'Y'
,tutkinto_laajuus = 330
WHERE
tutkintokoodi = 772201
/* Eläinlääketieteen lisensiaatti (360 op) */
UPDATE #kela_tutkinto
SET kela_opinto_koodi = 'Y'
, valmistunut_tutkinto = 'Y'
,tutkinto_laajuus = 360
WHERE tutkintokoodi = 772301
/* Psykologian maisteri 733501 (330 op) */
UPDATE #kela_tutkinto
SET kela_opinto_koodi = 'Y'
, valmistunut_tutkinto = 'Y'
, tutkinto_laajuus = 330
WHERE tutkintokoodi = 733501
/* Musiikin maisteri 723111 (330 op) */
UPDATE #kela_tutkinto
SET kela_opinto_koodi = 'Y'
, valmistunut_tutkinto = 'Y'
, tutkinto_laajuus = 330
WHERE tutkintokoodi = 723111
/* Kuvataiteen maisteri 724101 (330 op) */
UPDATE #kela_tutkinto
SET kela_opinto_koodi = 'Y'
, valmistunut_tutkinto = 'Y'
, tutkinto_laajuus = 330
WHERE tutkintokoodi = 724101
INSERT INTO #kela_tutkinto ( opiskelijaid, opiskeluoikeusid, kela_opinto_koodi, valmistunut_tutkinto, tutkinto_laajuus, jatkamistieto, tutkintokoodi )
SELECT DISTINCT opiskelijaid, opiskeluoikeusid, kela_opinto_koodi, valmistunut_tutkinto, tutkinto_laajuus, jatkamistieto, tutkintokoodi
FROM #kela_A_tutkinto
IF EXISTS (SELECT * FROM #kela_tutkinto_speciale)
BEGIN
INSERT INTO #kela_tutkinto_speciale ( opiskelijaid, opiskeluoikeusid, kela_opinto_koodi, valmistunut_tutkinto, tutkinto_laajuus, jatkamistieto, tutkintokoodi )
SELECT DISTINCT opiskelijaid, opiskeluoikeusid, kela_opinto_koodi, valmistunut_tutkinto, tutkinto_laajuus, jatkamistieto, tutkintokoodi
FROM #kela_A_tutkinto
END
/* Lasketaan aloituspvm ensimmäisen läsnäolon mukaan */
CREATE TABLE #Aloittanut (
oid int,
Alkamispaivamaara date
)
INSERT INTO #Aloittanut (oid, Alkamispaivamaara )
SELECT DISTINCT oo.ID,
CASE WHEN MIN(L.Alkamispaivamaara) >= OJ.Alkamispaivamaara THEN MIN(L.Alkamispaivamaara)
ELSE
(convert(varchar(4), YEAR(OJ.Alkamispaivamaara)) + CASE WHEN MONTH(OJ.Alkamispaivamaara)>=8 THEN '-08-01' ELSE '-01-01' END)
END
AS Alkamispaivamaara
FROM virta.dbo.Henkilo H
JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN virta.dbo.Opiskeluoikeusjakso OJ ON OJ.opiskeluoikeusid = OO.id
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
JOIN virta.dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE
H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND OJ.db = @mydb
AND L.db = @mydb
AND LT.Koodi = 1 /* Läsnä */
AND H.Henkilotunnus IS NOT NULL
AND OO.ID IN (SELECT opiskeluoikeusid AS ID FROM #KELA_paattynyt)
GROUP BY L.Alkamispaivamaara, OJ.Alkamispaivamaara, oo.ID
CREATE TABLE #Min_aloituspvm (
oid int,
alk_pvm date
)
INSERT INTO #Min_aloituspvm (oid, alk_pvm)
SELECT oid, MIN(alkamispaivamaara) As alk_pvm
FROM #Aloittanut
GROUP BY oid
DELETE FROM KELA.dbo.KELA_opiskelutiedot WHERE kk = @mydb AND Tapahtumakoodi = 'W'
IF EXISTS (SELECT * FROM #kela_tutkinto_speciale)
BEGIN
INSERT INTO KELA.dbo.KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, tilastokeskuksen_koulutuskoodi, kk,opiskelijaAvain, opiskeluOikeusAvain, jatkamistieto )
SELECT DISTINCT
OO.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,H.Henkilotunnus AS henkilotunnus
,substring(h.Sukunimi,1,30) AS Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
, EO.kela_opinto_koodi AS opinnot_johon_hyvaksytty
,'W' AS Tapahtumakoodi
, REPLACE((CONVERT(varchar(12), KP.oo_paattynyt, 105)),'-','') AS Paivamaara
, EO.valmistunut_tutkinto AS valmistunut_tutkinto
--, EO.jatkamistieto
, ' ' AS syyslukukausi
, ' ' AS kevatlukukausi
, EO.tutkinto_laajuus AS tutkinnon_laajuus
, REPLACE((CONVERT(varchar(12), M.alk_pvm, 105)),'-','') AS Opintojen_aloituspaiva
, CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE() AS regDatum
,(SELECT substring(OROT.Organisaatio_nimi,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, EO.tutkintokoodi
, @mydb AS kk
, O.avain AS opiskelijaAvain
, OO.avain AS opiskeluOikeusAvain
, EO.jatkamistieto
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
INNER JOIN #kela_tutkinto_speciale EO ON EO.OpiskeluoikeusID = OO.ID
INNER JOIN #Min_aloituspvm M ON M.oid = EO.OpiskeluoikeusID AND M.oid = OO.ID
INNER JOIN #KELA_paattynyt KP ON KP.OpiskeluoikeusID = EO.OpiskeluoikeusID
WHERE H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND KP.oo_paattynyt >= @Syys_alk
AND EO.kela_opinto_koodi IS NOT NULL
ORDER BY H.henkilotunnus
END
ELSE
BEGIN
INSERT INTO KELA.dbo.KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, tilastokeskuksen_koulutuskoodi, kk,opiskelijaAvain, opiskeluOikeusAvain, jatkamistieto )
SELECT DISTINCT
OO.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,H.Henkilotunnus AS henkilotunnus
,substring(h.Sukunimi,1,30) AS Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
, EO.kela_opinto_koodi AS opinnot_johon_hyvaksytty
,'W' AS Tapahtumakoodi
, REPLACE((CONVERT(varchar(12), OS.Suorituspaivamaara, 105)),'-','') AS Paivamaara
, EO.valmistunut_tutkinto AS valmistunut_tutkinto
--, EO.jatkamistieto
, ' ' AS syyslukukausi
, ' ' AS kevatlukukausi
, EO.tutkinto_laajuus AS tutkinnon_laajuus
, REPLACE((CONVERT(varchar(12), M.alk_pvm, 105)),'-','') AS Opintojen_aloituspaiva
, CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE() AS regDatum
,(SELECT substring(OROT.Organisaatio_nimi,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, EO.tutkintokoodi
, @mydb AS kk
, O.avain AS opiskelijaAvain
, OO.avain AS opiskeluOikeusAvain
, EO.jatkamistieto
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
INNER JOIN #kela_tutkinto EO ON EO.OpiskeluoikeusID = OO.ID
INNER JOIN virta.dbo.Opintosuoritus OS ON OS.OpiskeluoikeusID = EO.OpiskeluoikeusID AND OS.OpiskeluoikeusID = OO.ID
INNER JOIN #Min_aloituspvm M ON M.oid = EO.OpiskeluoikeusID AND M.oid = OO.ID
INNER JOIN #KELA_paattynyt KP ON KP.OpiskeluoikeusID = EO.OpiskeluoikeusID
WHERE H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND OS.db = @mydb
AND OS.Suorituspaivamaara >= @Syys_alk
AND OS.Suorituspaivamaara >= ISNULL(KP.oo_paattynyt,@Syys_alk)
AND EO.kela_opinto_koodi IS NOT NULL
ORDER BY H.henkilotunnus
END
DROP TABLE #KELA_paattynyt
DROP TABLE #opiskelu_oikeudet_yo
DROP TABLE #kela_tutkinto
DROP TABLE #kela_A_tutkinto
DROP TABLE #Aloittanut
DROP TABLE #Min_aloituspvm
DROP TABLE #kela_tutkinto_speciale
Valmistuneet YO ei-tutkintoon johtavat (P) (Tapahtumakoodi W)
YO ei-tutkinto Expand source
DECLARE @YEAR varchar(4) = @vuosi
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+'-08-01'
/* Päättyneet oikeudet */
CREATE TABLE #KELA_paattynyt (
opiskeluoikeusid int,
opiskelijaid int,
tila_koodi int,
Tilan_Alkamispaivamaara date,
opiskelijaAvain varchar(100),
opiskeluoikeusAvain varchar(100),
hetu varchar(15),
oo_paattynyt date
)
/* Syksystä alkaen valmistuneet ei Tutkintoon johtava viimeinen aktiivinen päivä päättymispvm */
INSERT INTO #KELA_paattynyt (opiskeluoikeusid,opiskelijaid,tila_koodi,Tilan_Alkamispaivamaara,opiskelijaAvain,opiskeluoikeusAvain, hetu,oo_paattynyt)
SELECT DISTINCT OO.ID AS opiskeluoikeusid
, O.id AS opiskelijaid
, OT.Koodi AS tila_koodi
, max(OOT.Paattymispaivamaara) AS Tilan_Alkamispaivamaara
, O.avain
, OO.avain
, H.henkilotunnus
, OO.Paattymispaivamaara
FROM virta.dbo.Henkilo H
JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
JOIN virta.dbo.Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
JOIN virta.dbo.Opiskeluoikeuden_tyyppi OTYP ON OO.Op_oikeuden_tyyppiID = OTYP.id
WHERE
H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND OOT.db = @mydb
AND OTYP.Koodi IN ('5','10','14','16','17','18','19')
AND LEN(LTRIM(H.Henkilotunnus))>7
AND OOT.Paattymispaivamaara >= @Syys_alk
AND OT.Koodi = 1 /* Viimeinen Aktiivinen päivä */
AND OO.ID IN ( SELECT DISTINCT OO2.ID
FROM virta.dbo.Henkilo H2
LEFT JOIN virta.dbo.Opiskelija O2 ON O2.HenkiloID = H2.ID
LEFT JOIN virta.dbo.Opiskeluoikeus OO2 ON OO2.OpiskelijaID = O2.ID
LEFT JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOT2 ON OO2.ID = OOT2.OpiskeluoikeusID
LEFT JOIN virta.dbo.Opiskeluoikeuden_tila OT2 ON OT2.ID = OOT2.Op_oikeuden_tilaID
WHERE OOT2.Alkamispaivamaara >= @Syys_alk
AND OOT2.Alkamispaivamaara < getdate()
AND OT2.Koodi = 3 /* Valmistunut */
)
AND OO.id NOT IN (SELECT KP.opiskeluoikeusid FROM #KELA_paattynyt KP)
GROUP BY OOT.Paattymispaivamaara, OO.ID, OT.Koodi,O.id,O.avain,OO.avain, H.henkilotunnus, OO.Paattymispaivamaara
DELETE FROM KELA.dbo.KELA_opiskelutiedot WHERE kk = @mydb AND Tapahtumakoodi = 'W' AND valmistunut_tutkinto = 'P' AND opinnot_johon_hyvaksytty = 'P'
/* Summa summarum */
INSERT INTO KELA.dbo.KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, kk,opiskelijaAvain, opiskeluOikeusAvain, jatkamistieto )
SELECT DISTINCT
OO.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,h.Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
, CASE WHEN OT.Koodi = '2' THEN 'A'
WHEN OT.Koodi = '4' THEN 'Y'
WHEN OT.Koodi IN ('6','7') THEN 'J'
WHEN OT.Koodi IN ('5','10','14','16','17','18','19') THEN 'P'
END
AS opinnot_johon_hyvaksytty
,'W' AS Tapahtumakoodi
,REPLACE((CONVERT(varchar(12), KP.oo_paattynyt, 105)),'-','') AS Paivamaara
, CASE WHEN OT.Koodi = '2' THEN 'A'
WHEN OT.Koodi = '4' THEN 'Y'
WHEN OT.Koodi IN ('6','7') THEN 'J'
WHEN OT.Koodi IN ('5','10','14','16','17','18','19') THEN 'P'
END
AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, ' ' AS kevatlukukausi
,0 AS tutkinnon_laajuus
,' ' AS Opintojen_aloituspaiva
--,REPLACE((CONVERT(varchar(12), M.alk_pvm, 105)),'-','') AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE() AS regDatum
,(SELECT substring(OROT.Organisaatio_nimi,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, @mydb AS kk
, O.avain AS opiskelijaAvain
, OO.avain AS opiskeluOikeusAvain
, 'E' AS jatkamistieto
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
--INNER JOIN #Min_aloituspvm M ON M.oid = OO.ID
INNER JOIN #KELA_paattynyt KP ON KP.OpiskeluoikeusID = OO.ID
WHERE H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND OT.Koodi IN ('5','10','14','16','17','18','19')
AND KP.oo_paattynyt < getdate()
DROP TABLE #KELA_paattynyt
Läsnä- ja poissaolevat YO (Tapahtumakoodi L)
Läsnä- ja poissaolevat YO (Tapahtumakoodi L) Expand source
DECLARE @mydb varchar(20) /* Korkeakoulu lyhenne Virrassa esim @mydb = 'HY' */
DECLARE @vuosi varchar(4) /* Ajankohtainen lukuvuosi esim @vuosi = 2016 */
DECLARE @YEAR varchar(4) = @vuosi
DECLARE @plus_vuosi varchar(4)
SET @plus_vuosi = convert(int,@YEAR) + 1
DECLARE @Syys_alk date
SET @Syys_alk = @YEAR+'-08-01'
DECLARE @Syys_lop date
SET @Syys_lop = @YEAR+'-12-31'
DECLARE @Kevat_alk date
SET @Kevat_alk = @plus_vuosi + '-01-01'
DECLARE @Kevat_lop date
SET @Kevat_lop = @plus_vuosi + '-07-31'
DECLARE @Paivamaara varchar(8)
SET @Paivamaara = '0108'+@YEAR
/* Aktiiviset (+ passiviset) johon opiskelijalla on opiskeluoikeus */
CREATE TABLE #KELA_aktiivinen (
oid int,
ooid int,
tila_koodi int,
opiskelijaAvain varchar(100),
opiskeluOikeusAvain varchar(100),
)
INSERT INTO #KELA_aktiivinen (oid, ooid, tila_koodi,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT O.ID AS oid
, OO.id AS oodi
, OT.Koodi AS tila_koodi
, O.avain AS opiskelijaAvain
, OO.avain AS opiskeluOikeusAvain
FROM virta.dbo.Henkilo H
JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
JOIN virta.dbo.Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND OOT.db = @mydb
AND (OOT.Paattymispaivamaara >= @Syys_alk OR OOT.Paattymispaivamaara IS NULL)
AND OT.Koodi IN ('1','2') /* Aktiivinen ja passivisessa tilassa voimassa olevat */
AND LEN(LTRIM(H.Henkilotunnus))>7
AND OO.ID NOT IN ( SELECT DISTINCT OO.ID
FROM virta.dbo.Henkilo H
JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOT ON OO.ID = OOT.OpiskeluoikeusID
JOIN virta.dbo.Opiskeluoikeuden_tila OT ON OT.ID = OOT.Op_oikeuden_tilaID
WHERE H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND OOT.db = @mydb
AND OOT.Alkamispaivamaara >= @Syys_alk
AND OT.Koodi IN ( '3', '4', '5' ) /* Opiskeluoikeus päättynyt */
)
/* Mihin on opiskeluoikeus */
CREATE TABLE #tutkinnot_johon_valittu (
opiskelijaid int,
opiskeluoikeusid int,
opiskeluoikeustyyppi int,
kela_opinto_koodi char(1),
opiskelijaAvain varchar(100),
opiskeluOikeusAvain varchar(100)
)
INSERT INTO #tutkinnot_johon_valittu( opiskelijaid,opiskeluoikeusid,opiskeluoikeustyyppi,kela_opinto_koodi,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT
O.id AS opiskelijaid
, OO.id AS opiskeluoikeusid
, OT.koodi AS opiskeluoikeustyyppi
, CASE WHEN OT.Koodi = '2' THEN 'A'
WHEN OT.Koodi = '4' THEN 'Y'
WHEN OT.Koodi IN ('6','7') THEN 'J'
END
AS kela_opinto_koodi
, O.avain
, OO.avain
FROM virta.dbo.Henkilo H
JOIN virta.dbo.Opiskelija O ON H.id = O.HenkiloID
JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OO.Op_oikeuden_tyyppiID = OT.id
JOIN virta.dbo.Opiskeluoikeusjakso OJ ON OJ.OpiskeluoikeusID = OO.id
JOIN virta.dbo.Tutkintonimike T ON T.id = OJ.TutkintonimikeID
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
WHERE O.id IN (SELECT oid FROM #KELA_aktiivinen)
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND OJ.db = @mydb
AND OOT.db = @mydb
AND @Syys_alk < COALESCE(OJ.Paattymispaivamaara,'2022-11-11')
CREATE TABLE #kela_tutkinto (
opiskelijaid int,
opiskeluoikeusid int,
kela_opinto_koodi char(1),
opiskelijaAvain varchar(100),
opiskeluOikeusAvain varchar(100)
)
/* Maisterit (Y) */
INSERT INTO #kela_tutkinto ( opiskelijaid, opiskeluoikeusid, kela_opinto_koodi,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT opiskelijaid, opiskeluoikeusid, kela_opinto_koodi,opiskelijaAvain,opiskeluOikeusAvain
FROM #tutkinnot_johon_valittu
WHERE opiskeluoikeustyyppi = 4
/* Muut kuin maisterit (A, J) */
INSERT INTO #kela_tutkinto ( opiskelijaid, opiskeluoikeusid, kela_opinto_koodi,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT opiskelijaid, opiskeluoikeusid, kela_opinto_koodi,opiskelijaAvain,opiskeluOikeusAvain
FROM #tutkinnot_johon_valittu
WHERE opiskeluoikeustyyppi <> 4
AND opiskelijaid NOT IN ( SELECT opiskelijaid FROM #kela_tutkinto )
/* Kaikki opiskeluoikeudet Kela koodeilla */
CREATE TABLE #opiskelu_oikeudet_yo (
opiskelijaid int,
kela_opinto_koodi char(1),
virta_koodi smallint,
opiskeluoikeusid int,
opiskeluoikeus_paattymispvm date,
opiskelijaAvain varchar(100),
opiskeluOikeusAvain varchar(100)
)
INSERT INTO #opiskelu_oikeudet_yo (opiskelijaid,kela_opinto_koodi,virta_koodi,opiskeluoikeusid,opiskeluoikeus_paattymispvm,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT O.ID AS opiskelijaid
, CASE WHEN OT.Koodi = '2' THEN 'A'
WHEN OT.Koodi = '4' THEN 'Y'
WHEN OT.Koodi IN ( '6','7') THEN 'J'
END
AS kela_opinto_koodi
, OT.koodi AS virta_koodi
, OO.id AS opiskeluoikeusid
, OO.Paattymispaivamaara AS opiskeluoikeus_paattymispvm
, O.avain AS opiskelijaAvain
, OO.avain AS opiskeluOikeusAvain
FROM virta.dbo.Opiskeluoikeus OO
JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OO.Op_oikeuden_tyyppiID = OT.id
JOIN virta.dbo.Opiskelija O ON O.id = OO.OpiskelijaID
JOIN virta.dbo.Henkilo H ON H.id = o.HenkiloID
WHERE
O.ID IN ( SELECT oid AS ID FROM #KELA_aktiivinen )
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND OT.Koodi NOT IN ('8', '9', '10', '13', '18') /* Ei kotimainen tai ulkomaa opiskelijaliikkuvuus,täydennyskoulutus, avoimet, erillinen opiskeluoikeus */
ORDER BY O.ID
/* R */
/* Jos tiedoksi tulisi Y, pitää vielä tehdä lisäpäättely: Jos ylempään tutkintoon liittyvälle opiskeluoikeudelle
ei löydy liittyvää alempaa opiskeluoikeutta, tiedoksi tulee Y:n sijasta R (erillinen ylempi korkeakoulututkinto).
*/
UPDATE #kela_tutkinto
SET kela_opinto_koodi = 'R'
WHERE kela_opinto_koodi = 'Y'
AND opiskeluoikeusid NOT IN ( SELECT OpiskeluoikeusID2 FROM virta.dbo.Opiskeluoikeuden_liittyvyys WHERE db = @mydb )
UPDATE #kela_tutkinto
SET kela_opinto_koodi = 'R'
WHERE kela_opinto_koodi = 'Y'
AND opiskelijaid NOT IN ( SELECT opiskelijaid FROM #opiskelu_oikeudet_yo WHERE kela_opinto_koodi = 'A')
AND opiskelijaid NOT IN ( SELECT opiskelijaid FROM #opiskelu_oikeudet_yo WHERE kela_opinto_koodi = 'J')
/* Ensisijaisuus */
CREATE TABLE #ensisijaisuus (
opiskelijaid int,
opiskeluoikeusid int,
oikeus_nimi varchar(50),
alkpvm date,
lopppvm date,
opiskelijaAvain varchar(100),
opiskeluOikeusAvain varchar(100)
)
INSERT INTO #ensisijaisuus (opiskelijaid , opiskeluoikeusid,oikeus_nimi,alkpvm,lopppvm,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT OO.OpiskelijaID, OO.id AS OpiskeluoikeusID, OT.nimi, OO.Alkamispaivamaara, OO.Paattymispaivamaara, K.opiskelijaAvain, OO.avain AS opiskeluOikeusAvain
FROM #KELA_aktiivinen K
JOIN virta.dbo.Opiskeluoikeus OO ON OO.id = K.ooid
JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID
JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOT ON OOT.OpiskeluoikeusID = OO.ID
JOIN virta.dbo.Op_oikeuden_ensisijaisuus OE ON OO.id = OE.OpiskeluoikeusID
WHERE
OO.db = @mydb
AND OE.db = @mydb
AND OOT.db = @mydb
AND @Syys_alk < COALESCE(OE.Paattymispaivamaara,'2022-11-11')
CREATE TABLE #kela_tutkinto_final (
opiskelijaid int,
opiskeluoikeusid int,
kela_opinto_koodi char(1),
opiskelijaAvain varchar(100),
opiskeluOikeusAvain varchar(100)
)
INSERT INTO #kela_tutkinto_final ( opiskelijaid, opiskeluoikeusid, kela_opinto_koodi,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT K.opiskelijaid, K.opiskeluoikeusid, K.kela_opinto_koodi,E.opiskelijaAvain,E.opiskeluOikeusAvain
FROM #kela_tutkinto K
JOIN #ensisijaisuus E ON K.opiskelijaid = E.opiskelijaid AND E.opiskeluoikeusid = K.opiskeluoikeusid
ORDER BY K.opiskelijaid
--Loput ei ensisijaisuutta
INSERT INTO #kela_tutkinto_final ( opiskelijaid, opiskeluoikeusid, kela_opinto_koodi,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT Ko.opiskelijaid, Ko.opiskeluoikeusid, Ko.kela_opinto_koodi, Ko.opiskelijaAvain, Ko.opiskeluOikeusAvain
FROM #kela_tutkinto Ko
WHERE Ko.opiskelijaid NOT IN (
SELECT K.opiskelijaid
FROM #ensisijaisuus E
JOIN #kela_tutkinto K ON K.opiskelijaid = E.opiskelijaid AND E.opiskeluoikeusid = K.opiskeluoikeusid
)
ORDER BY Ko.OpiskelijaID
/* Läsnäoloilmoitukset, kaikki mahdolliset kombinaatiot */
CREATE TABLE #KELA_lasnaolo (
oid int,
tila_koodi int,
lukukausi char,
Alkamispaivamaara date,
Paattymispaivamaara date
)
/* Syksyllä Läsnä L */
INSERT INTO #KELA_lasnaolo (oid,tila_koodi,lukukausi,Alkamispaivamaara,Paattymispaivamaara)
SELECT DISTINCT O.ID AS oid
, LT.Koodi AS tila_koodi
, 'S' AS lukukausi
, L.Alkamispaivamaara
, L.Paattymispaivamaara
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE H.db = @mydb
AND O.db = @mydb
AND L.db = @mydb
AND L.Alkamispaivamaara >= @Syys_alk
AND L.Paattymispaivamaara <= @Syys_lop
AND LT.Koodi = '1' /* Läsnä */
AND LEN(LTRIM(H.Henkilotunnus))>7
/* Syksyllä Poissa P */
INSERT INTO #KELA_lasnaolo (oid,tila_koodi,lukukausi,Alkamispaivamaara,Paattymispaivamaara)
SELECT DISTINCT O.ID AS oid
, LT.Koodi AS tila_koodi
, 'S' AS lukukausi
, L.Alkamispaivamaara
, L.Paattymispaivamaara
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE H.db = @mydb
AND O.db = @mydb
AND L.db = @mydb
AND L.Alkamispaivamaara >= @Syys_alk
AND L.Paattymispaivamaara <= @Syys_lop
AND LT.Koodi IN ('2','3') /* Poissa */
AND LEN(LTRIM(H.Henkilotunnus))>7
/* Kevällä Läsnä L */
INSERT INTO #KELA_lasnaolo (oid,tila_koodi,lukukausi,Alkamispaivamaara,Paattymispaivamaara)
SELECT DISTINCT O.ID AS oid
, LT.Koodi AS tila_koodi
, 'K' AS lukukausi
, L.Alkamispaivamaara
, L.Paattymispaivamaara
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE H.db = @mydb
AND O.db = @mydb
AND L.db = @mydb
AND L.Alkamispaivamaara >= @Kevat_alk
AND L.Paattymispaivamaara <= @Kevat_lop
AND LT.Koodi = '1' /* Läsnä */
AND LEN(LTRIM(H.Henkilotunnus))>7
/* Kevällä Poissa P */
INSERT INTO #KELA_lasnaolo (oid,tila_koodi,lukukausi,Alkamispaivamaara,Paattymispaivamaara)
SELECT DISTINCT O.ID AS oid
, LT.Koodi AS tila_koodi
, 'K' AS lukukausi
, L.Alkamispaivamaara
, L.Paattymispaivamaara
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE H.db = @mydb
AND O.db = @mydb
AND L.db = @mydb
AND L.Alkamispaivamaara >= @Kevat_alk
AND L.Paattymispaivamaara <= @Kevat_lop
AND LT.Koodi IN ('2','3') /* Poissa */
AND LEN(LTRIM(H.Henkilotunnus))>7
DELETE FROM KELA_opiskelutiedot WHERE kk = @mydb AND Tapahtumakoodi = 'L'
/* LL */
INSERT INTO KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, kela_tutkinnon_taso,tilastokeskuksen_koulutuskoodi, kk,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT O.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,substring(h.Sukunimi,1,30) AS Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
,EO.kela_opinto_koodi AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'L' AS syyslukukausi
, 'L' AS kevatlukukausi
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
,(SELECT substring(OROT.Organisaatio_nimi,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, NULL --EO.kela_tutkinnon_taso
, NULL --EO.tilastokeskuksen_koulutuskoodi
, @mydb AS kk
, EO.opiskelijaAvain AS opiskelijaAvain
, EO.opiskeluOikeusAvain AS opiskeluOikeusAvain
FROM virta.dbo.Henkilo H
JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN #kela_tutkinto_final EO ON EO.opiskelijaid = O.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE OO.ID IN (SELECT ooid AS ID FROM #KELA_aktiivinen)
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND L.db = @mydb
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'S')
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))> 7
AND EO.kela_opinto_koodi IS NOT NULL
/* PP */
INSERT INTO KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, kela_tutkinnon_taso,tilastokeskuksen_koulutuskoodi, kk,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT O.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,substring(h.Sukunimi,1,30) AS Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
,EO.kela_opinto_koodi AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'P' AS syyslukukausi
, 'P' AS kevatlukukausi
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
,(SELECT substring(OROT.Organisaatio_nimi,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, NULL --EO.kela_tutkinnon_taso
, NULL --EO.tilastokeskuksen_koulutuskoodi
, @mydb AS kk
, EO.opiskelijaAvain AS opiskelijaAvain
, EO.opiskeluOikeusAvain AS opiskeluOikeusAvain
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN #kela_tutkinto_final EO ON EO.opiskelijaid = O.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE OO.ID IN (SELECT ooid AS ID FROM #KELA_aktiivinen)
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND L.db = @mydb
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'S')
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))> 7
AND EO.kela_opinto_koodi IS NOT NULL
/* L */
INSERT INTO KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, kela_tutkinnon_taso,tilastokeskuksen_koulutuskoodi, kk,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT O.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,substring(h.Sukunimi,1,30) AS Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
,EO.kela_opinto_koodi AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'L' AS syyslukukausi
, ' ' AS kevatlukukausi
-- Kevät puuttuu
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
,(SELECT substring(OROT.Organisaatio_nimi,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, NULL --EO.kela_tutkinnon_taso
, NULL --EO.tilastokeskuksen_koulutuskoodi
, @mydb AS kk
, EO.opiskelijaAvain AS opiskelijaAvain
, EO.opiskeluOikeusAvain AS opiskeluOikeusAvain
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN #kela_tutkinto_final EO ON EO.opiskelijaid = O.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE OO.ID IN (SELECT ooid AS ID FROM #KELA_aktiivinen)
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND L.db = @mydb
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'S')
AND O.ID NOT IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))> 7
AND EO.kela_opinto_koodi IS NOT NULL
/* P */
INSERT INTO KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, kela_tutkinnon_taso,tilastokeskuksen_koulutuskoodi, kk,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT O.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,substring(h.Sukunimi,1,30) AS Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
,EO.kela_opinto_koodi AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'P' AS syyslukukausi
, ' ' AS kevatlukukausi
-- Kevät puuttuu
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
,(SELECT substring(OROT.Organisaatio_nimi,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, NULL --EO.kela_tutkinnon_taso
, NULL --EO.tilastokeskuksen_koulutuskoodi
, @mydb AS kk
, EO.opiskelijaAvain AS opiskelijaAvain
, EO.opiskeluOikeusAvain AS opiskeluOikeusAvain
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN #kela_tutkinto_final EO ON EO.opiskelijaid = O.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE OO.ID IN (SELECT ooid AS ID FROM #KELA_aktiivinen)
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND L.db = @mydb
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'S')
AND O.ID NOT IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))> 7
AND EO.kela_opinto_koodi IS NOT NULL
/* LP */
INSERT INTO KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, kela_tutkinnon_taso,tilastokeskuksen_koulutuskoodi, kk,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT O.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,substring(h.Sukunimi,1,30) AS Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
,EO.kela_opinto_koodi AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'L' AS syyslukukausi
, 'P' AS kevatlukukausi
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
,(SELECT substring(OROT.Organisaatio_nimi,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, NULL --EO.kela_tutkinnon_taso
, NULL --EO.tilastokeskuksen_koulutuskoodi
, @mydb AS kk
, EO.opiskelijaAvain AS opiskelijaAvain
, EO.opiskeluOikeusAvain AS opiskeluOikeusAvain
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN #kela_tutkinto_final EO ON EO.opiskelijaid = O.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE OO.ID IN (SELECT ooid AS ID FROM #KELA_aktiivinen)
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND L.db = @mydb
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'S')
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))> 7
AND EO.kela_opinto_koodi IS NOT NULL
/* PL */
INSERT INTO KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, kela_tutkinnon_taso,tilastokeskuksen_koulutuskoodi, kk,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT O.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,substring(h.Sukunimi,1,30) AS Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
,EO.kela_opinto_koodi AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, 'P' AS syyslukukausi
, 'L' AS kevatlukukausi
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
,(SELECT substring(OROT.Organisaatio_nimi,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, NULL --EO.kela_tutkinnon_taso
, NULL --EO.tilastokeskuksen_koulutuskoodi
, @mydb AS kk
, EO.opiskelijaAvain AS opiskelijaAvain
, EO.opiskeluOikeusAvain AS opiskeluOikeusAvain
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN #kela_tutkinto_final EO ON EO.opiskelijaid = O.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE OO.ID IN (SELECT ooid AS ID FROM #KELA_aktiivinen)
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND L.db = @mydb
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'S')
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))> 7
AND EO.kela_opinto_koodi IS NOT NULL
/* _L */
INSERT INTO KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, kela_tutkinnon_taso,tilastokeskuksen_koulutuskoodi, kk,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT O.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,substring(h.Sukunimi,1,30) AS Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
,EO.kela_opinto_koodi AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, 'L' AS kevatlukukausi
-- Syys puuttuu
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
,(SELECT substring(OROT.Organisaatio_nimi,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, NULL --EO.kela_tutkinnon_taso
, NULL --EO.tilastokeskuksen_koulutuskoodi
, @mydb AS kk
, EO.opiskelijaAvain AS opiskelijaAvain
, EO.opiskeluOikeusAvain AS opiskeluOikeusAvain
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN #kela_tutkinto_final EO ON EO.opiskelijaid = O.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE OO.ID IN (SELECT ooid AS ID FROM #KELA_aktiivinen)
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND L.db = @mydb
AND O.ID NOT IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE lukukausi = 'S')
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi = 1 AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))> 7
AND EO.kela_opinto_koodi IS NOT NULL
/* _P */
INSERT INTO KELA_opiskelutiedot ( opiskeluoikeus_ID, siirtotunnus, tietuetyyppi, poimintapaivamaara, siirtolaji, lahettajaryhman_tunnus, oppilaitos, henkilotunnus, sukunimi, etunimet, opinnot_johon_hyvaksytty, tapahtumakoodi, paivamaara, valmistunut_tutkinto, syyslukukausi, kevatlukukausi, tutkinnon_laajuus, opintojen_aloituspaiva, sukupuoli, keskeyttamisen_ilmoittaja, reg_date, organisaatio, kela_tutkinnon_taso,tilastokeskuksen_koulutuskoodi, kk,opiskelijaAvain,opiskeluOikeusAvain )
SELECT DISTINCT O.ID AS opiskeluoikeus_ID
,'L'+(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb) AS siirtotunnus
,'T' AS tietuetyyppi
,REPLACE((CONVERT(varchar(12), GETDATE(), 105)),'-','') AS poimintapaivamaara
,'OPISK' AS siirtolaji
,'OP' AS lahettajaryhman_tunnus
,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = @mydb ) AS oppilaitos
,h.Henkilotunnus AS henkilotunnus
,substring(h.Sukunimi,1,30) AS Sukunimi
,ISNULL (substring(h.Etunimet,1,30),'') AS Etunimet
,EO.kela_opinto_koodi AS opinnot_johon_hyvaksytty
,'L' AS Tapahtumakoodi
,@Paivamaara AS Paivamaara
, ' ' AS valmistunut_tutkinto
, ' ' AS syyslukukausi
, 'P' AS kevatlukukausi
-- Syys puuttuu
,'' AS tutkinnon_laajuus
, ' ' AS Opintojen_aloituspaiva
,CASE WHEN H.SukupuoliID = 1 THEN 'M'
WHEN H.SukupuoliID = 2 THEN 'N'
END
AS Sukupuoli
, ' ' AS keskeyttamisen_ilmoittaja
, GETDATE()
,(SELECT substring(substring(OROT.Organisaatio_nimi,1,20) ,1,20) FROM Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi OROT JOIN Yhteiset.Asetukset.Instanssit I ON I.OrganisaatioKoodi = OROT.Organisaatio_koodi WHERE I.DB = @mydb AND OROT.Oppilaitostyyppi_koodi IN (41,42) ) AS organisaatio
, NULL --EO.kela_tutkinnon_taso
, NULL --EO.tilastokeskuksen_koulutuskoodi
, @mydb AS kk
, EO.opiskelijaAvain AS opiskelijaAvain
, EO.opiskeluOikeusAvain AS opiskeluOikeusAvain
FROM virta.dbo.Henkilo H
INNER JOIN virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
JOIN #kela_tutkinto_final EO ON EO.opiskelijaid = O.ID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
LEFT JOIN virta.dbo.Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
INNER JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.ID = OO.Op_oikeuden_tyyppiID
WHERE OO.ID IN (SELECT ooid AS ID FROM #KELA_aktiivinen)
AND H.db = @mydb
AND O.db = @mydb
AND OO.db = @mydb
AND L.db = @mydb
AND O.ID NOT IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE lukukausi = 'S')
AND O.ID IN (SELECT oid AS ID FROM #KELA_lasnaolo WHERE tila_koodi IN (2,3) AND lukukausi = 'K')
AND LEN(LTRIM(H.Henkilotunnus))> 7
AND EO.kela_opinto_koodi IS NOT NULL
DROP TABLE #KELA_lasnaolo
DROP TABLE #KELA_aktiivinen
DROP TABLE #ensisijaisuus
DROP TABLE #opiskelu_oikeudet_yo
DROP TABLE #kela_tutkinto
DROP TABLE #tutkinnot_johon_valittu
DROP TABLE #kela_tutkinto_final
Päättely jos on sekä läsnä ja poissa, läsnäolo tieto ensisijaisesti. Jos useampi sama läsnä-/poissaolo, yksi läsnä-/poissaolo (L-rivi) riittää.
Ylimääräiset Expand source
DECLARE @mydb varchar(20) /* Korkeakoulu lyhenne Virrassa esim @mydb = 'HY' */
/* Vain läsnäolo jos on myös poissaolo */
DELETE FROM KELA_opiskelutiedot WHERE kk = @mydb AND opiskeluoikeus_ID IN
(SELECT opiskeluoikeus_ID
FROM KELA_opiskelutiedot
WHERE kk = @mydb AND tapahtumakoodi = 'L'
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'L' AND kevatlukukausi = 'L' )
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'P' AND kevatlukukausi = 'P' )
AND syyslukukausi = 'P')
/* Vain läsnäolo jos on myös poissaolo */
DELETE FROM KELA_opiskelutiedot WHERE kk = @mydb AND opiskeluoikeus_ID IN
(SELECT opiskeluoikeus_ID
FROM KELA_opiskelutiedot
WHERE kk = @mydb AND tapahtumakoodi = 'L'
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'L' )
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = ' ' )
AND syyslukukausi = ' ')
/* Vain yksi poissaolo riittää */
DELETE FROM KELA_opiskelutiedot WHERE kk = @mydb AND opiskeluoikeus_ID IN (
SELECT opiskeluoikeus_ID
FROM KELA_opiskelutiedot
WHERE kk = @mydb AND tapahtumakoodi = 'L'
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'P' AND kevatlukukausi = 'P' )
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'P' AND kevatlukukausi = 'L' )
AND kevatlukukausi = 'L')
/* Vain yksi läsnäolo riittää */
DELETE FROM KELA_opiskelutiedot WHERE kk = @mydb AND opiskeluoikeus_ID IN (
SELECT opiskeluoikeus_ID
FROM KELA_opiskelutiedot
WHERE kk = @mydb AND tapahtumakoodi = 'L'
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'L' AND kevatlukukausi = 'L' )
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'L' AND kevatlukukausi = ' ' )
AND kevatlukukausi = ' ')
/* Vain läsnäolo jos on myös poissaolo */
DELETE FROM KELA_opiskelutiedot WHERE kk = @mydb AND opiskeluoikeus_ID IN (
SELECT opiskeluoikeus_ID
FROM KELA_opiskelutiedot
WHERE kk = @mydb AND tapahtumakoodi = 'L'
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'L' AND kevatlukukausi = ' ' )
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'P' AND kevatlukukausi = 'L' )
AND syyslukukausi = 'P')
/* Vain läsnäolo jos on myös poissaolo */
DELETE FROM KELA_opiskelutiedot WHERE kk = @mydb AND opiskeluoikeus_ID IN (
SELECT opiskeluoikeus_ID
FROM KELA_opiskelutiedot
WHERE kk = @mydb AND tapahtumakoodi = 'L'
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'L' AND kevatlukukausi = 'L' )
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'P' AND kevatlukukausi = 'L' )
AND syyslukukausi = 'P')
/* Vain yksi läsnäolo riittää */
DELETE FROM KELA_opiskelutiedot WHERE kk = @mydb AND opiskeluoikeus_ID IN (
SELECT opiskeluoikeus_ID
FROM KELA_opiskelutiedot
WHERE kk = @mydb AND tapahtumakoodi = 'L'
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'L' AND kevatlukukausi = 'L' )
AND henkilotunnus IN (SELECT henkilotunnus FROM KELA_opiskelutiedot WHERE kk = @mydb AND tapahtumakoodi = 'L' AND syyslukukausi = 'L' AND kevatlukukausi = 'P' )
AND kevatlukukausi = 'P')