-- @kk = YO-lyhenne
CREATE TABLE #Lasna (
opiskelijaAvain varchar(100),
lukukaudet int,
kuukaudet int,
Suorituspaivamaara date,
tyyppi smallint,
oid int
)
-- 612101 Kasvatustieteiden kandidaatti (alempi), lastentarhanopettaja, 612204 Kasvatustieteiden kandidaatti (alempi), varhaiskasvatus, 672401 Farmaseutti
INSERT INTO #Lasna ( opiskelijaAvain, lukukaudet, kuukaudet, Suorituspaivamaara, tyyppi, oid )
SELECT DISTINCT O.avain
, count(*) AS lukukaudet
, DATEDIFF(MM , oo.Alkamispaivamaara, os.Suorituspaivamaara) AS kuukaudet
, OS.Suorituspaivamaara
, OT.Koodi
, OO.id
FROM Virta.dbo.Opiskelija O
JOIN Virta.dbo.Henkilo H ON O.HenkiloID = H.ID
JOIN Virta.dbo.Opintosuoritus OS ON O.id = OS.opiskelijaid
JOIN Virta.dbo.Tutkintonimike T ON T.id = OS.TutkintonimikeID
JOIN Virta.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID
JOIN Virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID AND OO.id = OS.OpiskeluoikeusID
JOIN Virta.dbo.Opiskeluoikeuden_tyyppi OT ON OO.Op_oikeuden_tyyppiID = OT.id
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 O.db IN (@kk)
AND H.db IN (@kk)
AND OS.db IN (@kk)
AND OO.db IN (@kk)
AND OT.Koodi IN ('2')
AND T.koodi IN ('612101','612204','672401') -- 612101 Kasvatustieteiden kandidaatti (alempi), lastentarhanopettaja, 612204 Kasvatustieteiden kandidaatti (alempi), varhaiskasvatus, 672401 Farmaseutti
AND OSL.Koodi = '1'
AND LT.Koodi = 1
AND YEAR(OS.suorituspaivamaara) BETWEEN @start AND @slut
GROUP BY O.avain, oo.Alkamispaivamaara, OO.id, OT.Koodi, os.Suorituspaivamaara
CREATE clustered INDEX tmp_idx ON #Lasna (opiskelijaAvain);
SELECT DISTINCT H.db AS kk
, (SELECT ORG.organisaatio_koodi FROM VirtaVIE.dbo.v_Oppilaitos_DB_Organisaatio_Koodi_Nimi ORG WHERE ORG.db = @kk ) AS Yliopisto_Oppilaitoskoodi
, (SELECT ORG.organisaatio_nimi FROM VirtaVIE.dbo.v_Oppilaitos_DB_Organisaatio_Koodi_Nimi ORG WHERE ORG.db = @kk ) AS Yliopisto_nimi
, YEAR(OS.suorituspaivamaara) as Valmistumisvuosi
, O.Avain AS Opiskelijatunnus
, H.Sukunimi
, H.Etunimet
, H.henkilotunnus
, H.SukupuoliID AS sukupuoli
, DATEDIFF ( yy , H.Syntymaaika , OS.suorituspaivamaara ) AS ika_valmistuessa
, (SELECT TOP 1 M.Koodi FROM Virta.dbo.Henkilon_maa HM JOIN Virta.dbo.Maa M ON M.id = HM.MaaID WHERE HM.HenkiloID = H.id) AS kansalaisuus -- H.id Henkilo_maa
, (SELECT TOP 1 K.koodi FROM Virta.dbo.Kieli K WHERE K.id = H.kieliid) AS aidinkieli -- Äidinkieli
, KALA.koodi AS koulutusalakoodi
, ( SELECT TOP 1 ka.Nimi FROM Virta.dbo.Koulutusala ka WHERE ka.koodi = Kala.koodi AND KA.Versio = 'ohjausala' ) AS koulutusala
, ( SELECT TOP 1 OJJ.Koulutusmoduulitunniste FROM Virta.dbo.Opiskeluoikeusjakso OJJ WHERE OJJ.opiskeluoikeusID = OJ.opiskeluoikeusID ORDER BY OJJ.Alkamispaivamaara DESC ) AS Aine
, OT.Koodi AS tutkinnon_taso --Tutkinnon taso
, D.koulutusluokitus_koodi AS tutkinto_koulutuskoodi
, D.koulutusluokitus_fi AS tutkinto_nimi
, OS.laajuus -- Tutkinnonlaajuus (op)
, OO.Alkamispaivamaara AS valintavuosi
, (SELECT TOP 1 kunt.koodi FROM Virta.dbo.kunta kunt WHERE kunt.id = H.kuntaid ) AS asuinkunta_koodi -- Kotikunta
, (SELECT TOP 1 kunt.nimi FROM Virta.dbo.kunta kunt WHERE kunt.id = H.kuntaid ) AS asuinkunta_nimi
, OS.suorituspaivamaara AS Valmistumisajankohta
, (SELECT TOP 1 kunt2.koodi FROM Virta.dbo.kunta kunt2 WHERE kunt2.id = OJ.kuntaid) AS Opiskelupaikkakunta_koodi -- Opiskelupaikkakunta
, (SELECT TOP 1 kunt2.nimi FROM Virta.dbo.kunta kunt2 WHERE kunt2.id = OJ.kuntaid) AS Opiskelupaikkakunta_nimi
, (SELECT max(L1.kuukaudet) FROM #Lasna L1 WHERE L1.opiskelijaAvain = O.Avain AND L1.oid = OS.OpiskeluoikeusID ) AS Kirjoilla_olo_kuukausia
, (SELECT max(L2.lukukaudet) FROM #Lasna L2 WHERE L2.opiskelijaAvain = O.Avain AND L2.oid = OS.OpiskeluoikeusID ) AS Lasnaolo_lukukausia
, ( SELECT TOP 1 A.Arvosana FROM Virta.dbo.Arvosana A WHERE A.id = OS.ArvosanaID) AS arvosana
, ( SELECT TOP 1 AK.Nimi FROM Virta.dbo.ARvosana_asteikko AK WHERE AK.id IN (SELECT Arvosana_asteikkoID FROM Virta.dbo.Arvosana A WHERE A.id = OS.ArvosanaID)) AS asteikko
, (SELECT TOP 1 R.Koodi FROM Virta.dbo.Rahoituslahde R WHERE R.ID = OJ.RahoituslahdeID ORDER BY OJ.Alkamispaivamaara DESC ) AS rahoituslahde
FROM Virta.dbo.Opiskelija O
JOIN Virta.dbo.Henkilo H ON O.HenkiloID = H.ID
JOIN Virta.dbo.Opintosuoritus OS ON O.id = OS.opiskelijaid
LEFT JOIN Virta.dbo.Op_suor_koulutusala OK ON OK.OpintosuoritusID = OS.ID
LEFT JOIN Virta.dbo.Koulutusala KALA ON KALA.id = OK.KoulutusalaID
JOIN Virta.dbo.Tutkintonimike T ON T.id = OS.TutkintonimikeID
JOIN Virta.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID
JOIN Virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID AND OO.id = OS.OpiskeluoikeusID
JOIN Virta.dbo.Opiskeluoikeusjakso OJ ON OJ.opiskeluoikeusID = OO.id
JOIN Virta.dbo.Opiskeluoikeuden_tyyppi OT ON OO.Op_oikeuden_tyyppiID = OT.id
JOIN ANTERO.dw.d_koulutusluokitus D ON D.koulutusluokitus_koodi = T.koodi
LEFT JOIN Virta.dbo.Rahoituslahde R ON R.ID = OJ.RahoituslahdeID
WHERE O.db IN (@kk)
AND H.db IN (@kk)
AND OS.db IN (@kk)
AND OO.db IN (@kk)
AND OJ.db IN (@kk)
AND OSL.Koodi = '1' -- Tutkinto suoritus laji 1
AND OT.Koodi IN ('2') -- Alempi korkeakoulututkintotyyppi 2
AND YEAR(OS.suorituspaivamaara) BETWEEN @start AND @slut
AND T.koodi IN ('612101','612204','672401') -- 612101 Kasvatustieteiden kandidaatti (alempi), lastentarhanopettaja, 612204 Kasvatustieteiden kandidaatti (alempi), varhaiskasvatus, 672401 Farmaseutti
-- Ylemmät
INSERT INTO #Lasna ( opiskelijaAvain, lukukaudet, kuukaudet, Suorituspaivamaara, tyyppi, oid )
SELECT DISTINCT O.avain
, count(*) AS lukukaudet
, DATEDIFF(MM , oo.Alkamispaivamaara, os.Suorituspaivamaara) AS kuukaudet
, OS.Suorituspaivamaara
, OT.Koodi
, OO.id
FROM Virta.dbo.Opiskelija O
JOIN Virta.dbo.Henkilo H ON O.HenkiloID = H.ID
JOIN Virta.dbo.Opintosuoritus OS ON O.id = OS.opiskelijaid
JOIN Virta.dbo.Tutkintonimike T ON T.id = OS.TutkintonimikeID
JOIN Virta.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID
JOIN Virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID AND OO.id = OS.OpiskeluoikeusID
JOIN Virta.dbo.Opiskeluoikeuden_tyyppi OT ON OO.Op_oikeuden_tyyppiID = OT.id
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 O.db IN (@kk)
AND H.db IN (@kk)
AND OS.db IN (@kk)
AND OO.db IN (@kk)
AND OT.Koodi IN ('4')
AND OSL.Koodi = '1'
AND LT.Koodi = 1
AND YEAR(OS.suorituspaivamaara) BETWEEN @start AND @slut
GROUP BY O.avain, oo.Alkamispaivamaara, OO.id, OT.Koodi, os.Suorituspaivamaara
CREATE TABLE #tmp_Uraseuranta (
[kk] [varchar](20) NULL,
[yliopisto_oppilaitoskoodi] [varchar](10) NULL,
[yliopisto_nimi] [varchar](50) NULL,
[valmistumisvuosi] [int] NULL,
[opiskelijatunnus] [varchar](250) NULL,
[sukunimi] [varchar](200) NULL,
[etunimet] [varchar](200) NULL,
[henkilotunnus] [varchar](15) NULL,
[sukupuoli] [smallint] NULL,
[ika_valmistuessa] [int] NULL,
[kansalaisuus] [varchar](3) NULL,
[aidinkieli] [varchar](2) NULL,
[koulutusalakoodi] [varchar](3) NULL,
[koulutusala] [varchar](200) NULL,
[aine] [varchar](250) NULL,
[tutkinnon_taso] [smallint] NULL,
[tutkinto_koulutuskoodi] [varchar](10) NULL,
[tutkinto_nimi] [varchar](200) NULL,
[laajuus] [int] NULL,
[valintavuosi] [date] NULL,
[asuinkunta_koodi] [varchar](3) NULL,
[asuinkunta_nimi] [varchar](100) NULL,
[valmistumisajankohta] [date] NULL,
[opiskelupaikkakunta_koodi] [varchar](3) NULL,
[opiskelupaikkakunta_nimi] [varchar](200) NULL,
[kirjoilla_olo_kuukausia] [int] NULL,
[lasnaolo_lukukausia] [int] NULL,
[arvosana] [varchar](100) NULL,
[asteikko] [varchar](200) NULL,
[rahoituslahde] [varchar] (20) NULL
)
INSERT INTO #tmp_Uraseuranta ( kk,yliopisto_oppilaitoskoodi,yliopisto_nimi,valmistumisvuosi,opiskelijatunnus,sukunimi,etunimet,henkilotunnus,sukupuoli,ika_valmistuessa,kansalaisuus,aidinkieli,koulutusalakoodi,koulutusala,aine,tutkinnon_taso,tutkinto_koulutuskoodi,tutkinto_nimi,laajuus,valintavuosi,asuinkunta_koodi,asuinkunta_nimi,valmistumisajankohta,opiskelupaikkakunta_koodi,opiskelupaikkakunta_nimi,kirjoilla_olo_kuukausia,lasnaolo_lukukausia,arvosana,asteikko,rahoituslahde )
SELECT DISTINCT H.db AS kk
, (SELECT ORG.organisaatio_koodi FROM VirtaVIE.dbo.v_Oppilaitos_DB_Organisaatio_Koodi_Nimi ORG WHERE ORG.db = @kk ) AS Yliopisto_Oppilaitoskoodi
, (SELECT ORG.organisaatio_nimi FROM VirtaVIE.dbo.v_Oppilaitos_DB_Organisaatio_Koodi_Nimi ORG WHERE ORG.db = @kk ) AS Yliopisto_nimi
, YEAR(OS.suorituspaivamaara) as Valmistumisvuosi
, O.Avain AS Opiskelijatunnus
, H.Sukunimi
, H.Etunimet
, H.henkilotunnus
, H.SukupuoliID AS sukupuoli
, DATEDIFF ( yy , H.Syntymaaika , OS.suorituspaivamaara ) AS ikä_valmistuessa
, (SELECT TOP 1 M.Koodi FROM Virta.dbo.Henkilon_maa HM JOIN Virta.dbo.Maa M ON M.id = HM.MaaID WHERE HM.HenkiloID = H.id) AS kansalaisuus -- H.id Henkilo_maa
, (SELECT TOP 1 K.koodi FROM Virta.dbo.Kieli K WHERE K.id = H.kieliid) AS aidinkieli -- Äidinkieli
, KALA.koodi AS koulutusalakoodi
, ( SELECT TOP 1 ka.Nimi FROM Virta.dbo.Koulutusala ka WHERE ka.koodi = Kala.koodi AND KA.Versio = 'ohjausala') AS koulutusala
, ( SELECT TOP 1 OJJ.Koulutusmoduulitunniste FROM Virta.dbo.Opiskeluoikeusjakso OJJ WHERE OJJ.opiskeluoikeusID = OJ.opiskeluoikeusID ORDER BY OJJ.Alkamispaivamaara DESC ) AS Aine
, OT.Koodi AS tutkinnon_taso --Tutkinnon taso
, D.koulutusluokitus_koodi AS tutkinto_koulutuskoodi
, D.koulutusluokitus_fi AS tutkinto_nimi
, OS.laajuus -- Tutkinnonlaajuus (op)
, OO.Alkamispaivamaara AS valintavuosi
, (SELECT TOP 1 kunt.koodi FROM Virta.dbo.kunta kunt WHERE kunt.id = H.kuntaid ) AS asuinkunta_koodi -- Kotikunta
, (SELECT TOP 1 kunt.nimi FROM Virta.dbo.kunta kunt WHERE kunt.id = H.kuntaid ) AS asuinkunta_nimi
, OS.suorituspaivamaara AS Valmistumisajankohta
, (SELECT TOP 1 kunt2.koodi FROM Virta.dbo.kunta kunt2 WHERE kunt2.id = OJ.kuntaid ORDER BY kunt2.nimi DESC ) AS Opiskelupaikkakunta_koodi -- Opiskelupaikkakunta
, (SELECT TOP 1 kunt2.nimi FROM Virta.dbo.kunta kunt2 WHERE kunt2.id = OJ.kuntaid ORDER BY kunt2.nimi DESC ) AS Opiskelupaikkakunta_nimi
, (SELECT max(L1.kuukaudet) FROM #Lasna L1 WHERE L1.opiskelijaAvain = O.Avain AND L1.oid = OS.OpiskeluoikeusID ) AS Kirjoilla_olo_kuukausia
, (SELECT max(L2.lukukaudet) FROM #Lasna L2 WHERE L2.opiskelijaAvain = O.Avain AND L2.oid = OS.OpiskeluoikeusID ) AS Lasnaolo_lukukausia
, ( SELECT TOP 1 A.Arvosana FROM Virta.dbo.Arvosana A WHERE A.id = OS.ArvosanaID) AS arvosana
, ( SELECT TOP 1 AK.Nimi FROM Virta.dbo.ARvosana_asteikko AK WHERE AK.id IN (SELECT Arvosana_asteikkoID FROM Virta.dbo.Arvosana A WHERE A.id = OS.ArvosanaID)) AS asteikko
, (SELECT TOP 1 R.Koodi FROM Virta.dbo.Rahoituslahde R WHERE R.ID = OJ.RahoituslahdeID ORDER BY OJ.Alkamispaivamaara DESC ) AS rahoituslahde
FROM Virta.dbo.Opiskelija O
JOIN Virta.dbo.Henkilo H ON O.HenkiloID = H.ID
JOIN Virta.dbo.Opintosuoritus OS ON O.id = OS.opiskelijaid
LEFT JOIN Virta.dbo.Op_suor_koulutusala OK ON OK.OpintosuoritusID = OS.ID
LEFT JOIN Virta.dbo.Koulutusala KALA ON KALA.id = OK.KoulutusalaID
JOIN Virta.dbo.Tutkintonimike T ON T.id = OS.TutkintonimikeID
JOIN Virta.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID
JOIN Virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID AND OO.id = OS.OpiskeluoikeusID
JOIN Virta.dbo.Opiskeluoikeusjakso OJ ON OJ.opiskeluoikeusID = OO.id
JOIN Virta.dbo.Opiskeluoikeuden_tyyppi OT ON OO.Op_oikeuden_tyyppiID = OT.id
JOIN ANTERO.dw.d_koulutusluokitus D ON D.koulutusluokitus_koodi = T.koodi
LEFT JOIN Virta.dbo.Rahoituslahde R ON R.ID = OJ.RahoituslahdeID
WHERE O.db IN (@kk)
AND H.db IN (@kk)
AND OS.db IN (@kk)
AND OO.db IN (@kk)
AND OJ.db IN (@kk)
AND OSL.Koodi = '1'
AND OT.Koodi IN ('4')
AND YEAR(OS.suorituspaivamaara) BETWEEN @start AND @slut
-- Tohtorit
INSERT INTO #Lasna ( opiskelijaAvain, lukukaudet, kuukaudet, Suorituspaivamaara, tyyppi, oid )
SELECT DISTINCT O.avain
, count(*) AS lukukaudet
, DATEDIFF(MM , oo.Alkamispaivamaara, os.Suorituspaivamaara) AS kuukaudet
, OS.Suorituspaivamaara
, OT.Koodi
, OO.id
FROM Virta.dbo.Opiskelija O
JOIN Virta.dbo.Henkilo H ON O.HenkiloID = H.ID
JOIN Virta.dbo.Opintosuoritus OS ON O.id = OS.opiskelijaid
JOIN Virta.dbo.Tutkintonimike T ON T.id = OS.TutkintonimikeID
JOIN Virta.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID
JOIN Virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID AND OO.id = OS.OpiskeluoikeusID
JOIN Virta.dbo.Opiskeluoikeuden_tyyppi OT ON OO.Op_oikeuden_tyyppiID = OT.id
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 O.db IN (@kk)
AND H.db IN (@kk)
AND OS.db IN (@kk)
AND OO.db IN (@kk)
AND OT.Koodi IN ('7')
AND OSL.Koodi = '1'
AND LT.Koodi = 1
AND YEAR(OS.suorituspaivamaara) BETWEEN @start AND @slut
--AND YEAR(OO.Paattymispaivamaara) = YEAR(OS.suorituspaivamaara)
GROUP BY O.avain, oo.Alkamispaivamaara, OO.id, OT.Koodi, os.Suorituspaivamaara
CREATE clustered INDEX tmp_idx ON #Lasna (opiskelijaAvain);
CREATE TABLE #tmp_Uraseuranta (
[kk] [varchar](20) NULL,
[yliopisto_oppilaitoskoodi] [varchar](10) NULL,
[yliopisto_nimi] [varchar](50) NULL,
[valmistumisvuosi] [int] NULL,
[opiskelijatunnus] [varchar](250) NULL,
[sukunimi] [varchar](200) NULL,
[etunimet] [varchar](200) NULL,
[henkilotunnus] [varchar](15) NULL,
[sukupuoli] [smallint] NULL,
[ika_valmistuessa] [int] NULL,
[kansalaisuus] [varchar](3) NULL,
[aidinkieli] [varchar](2) NULL,
[koulutusalakoodi] [varchar](3) NULL,
[koulutusala] [varchar](200) NULL,
[aine] [varchar](250) NULL,
[tutkinnon_taso] [smallint] NULL,
[tutkinto_koulutuskoodi] [varchar](10) NULL,
[tutkinto_nimi] [varchar](200) NULL,
[laajuus] [int] NULL,
[valintavuosi] [date] NULL,
[asuinkunta_koodi] [varchar](3) NULL,
[asuinkunta_nimi] [varchar](100) NULL,
[valmistumisajankohta] [date] NULL,
[opiskelupaikkakunta_koodi] [varchar](3) NULL,
[opiskelupaikkakunta_nimi] [varchar](200) NULL,
[kirjoilla_olo_kuukausia] [int] NULL,
[lasnaolo_lukukausia] [int] NULL,
[arvosana] [varchar](100) NULL,
[asteikko] [varchar](200) NULL,
[rahoituslahde] [varchar] (20) NULL
)
INSERT INTO #tmp_Uraseuranta ( kk,yliopisto_oppilaitoskoodi,yliopisto_nimi,valmistumisvuosi,opiskelijatunnus,sukunimi,etunimet,henkilotunnus,sukupuoli,ika_valmistuessa,kansalaisuus,aidinkieli,koulutusalakoodi,koulutusala,aine,tutkinnon_taso,tutkinto_koulutuskoodi,tutkinto_nimi,laajuus,valintavuosi,asuinkunta_koodi,asuinkunta_nimi,valmistumisajankohta,opiskelupaikkakunta_koodi,opiskelupaikkakunta_nimi,kirjoilla_olo_kuukausia,lasnaolo_lukukausia,arvosana,asteikko,rahoituslahde )
SELECT DISTINCT H.db AS kk
, (SELECT ORG.organisaatio_koodi FROM VirtaVIE.dbo.v_Oppilaitos_DB_Organisaatio_Koodi_Nimi ORG WHERE ORG.db = @kk ) AS Yliopisto_Oppilaitoskoodi
, (SELECT ORG.organisaatio_nimi FROM VirtaVIE.dbo.v_Oppilaitos_DB_Organisaatio_Koodi_Nimi ORG WHERE ORG.db = @kk ) AS Yliopisto_nimi
, YEAR(OS.suorituspaivamaara) as Valmistumisvuosi
, O.Avain AS Opiskelijatunnus
, H.Sukunimi
, H.Etunimet
, H.henkilotunnus
, H.SukupuoliID AS sukupuoli
, DATEDIFF ( yy , H.Syntymaaika , OS.suorituspaivamaara ) AS ika_valmistuessa
, (SELECT TOP 1 M.Koodi FROM Virta.dbo.Henkilon_maa HM JOIN Virta.dbo.Maa M ON M.id = HM.MaaID WHERE HM.HenkiloID = H.id) AS kansalaisuus -- H.id Henkilo_maa
, (SELECT TOP 1 K.koodi FROM Virta.dbo.Kieli K WHERE K.id = H.kieliid) AS aidinkieli -- Äidinkieli
, KALA.koodi AS koulutusalakoodi
, (SELECT TOP 1 ka.Nimi FROM Virta.dbo.Koulutusala ka WHERE ka.koodi = Kala.koodi AND KA.Versio = 'ohjausala') AS koulutusala
, ( SELECT TOP 1 OJJ.Koulutusmoduulitunniste FROM Virta.dbo.Opiskeluoikeusjakso OJJ WHERE OJJ.opiskeluoikeusID = OJ.opiskeluoikeusID ORDER BY OJJ.Alkamispaivamaara DESC ) AS Aine
, OT.Koodi AS tutkinnon_taso --Tutkinnon taso
, D.koulutusluokitus_koodi AS tutkinto_koulutuskoodi
, D.koulutusluokitus_fi AS tutkinto_nimi
, OS.laajuus -- Tutkinnonlaajuus (op)
, OO.Alkamispaivamaara AS valintavuosi
, (SELECT TOP 1 kunt.koodi FROM Virta.dbo.kunta kunt WHERE kunt.id = H.kuntaid ) AS asuinkunta_koodi -- Kotikunta
, (SELECT TOP 1 kunt.nimi FROM Virta.dbo.kunta kunt WHERE kunt.id = H.kuntaid ) AS asuinkunta_nimi
, OS.suorituspaivamaara AS Valmistumisajankohta
, (SELECT TOP 1 kunt2.koodi FROM Virta.dbo.kunta kunt2 WHERE kunt2.id = OJ.kuntaid ORDER BY kunt2.nimi DESC ) AS Opiskelupaikkakunta_koodi -- Opiskelupaikkakunta
, (SELECT TOP 1 kunt2.nimi FROM Virta.dbo.kunta kunt2 WHERE kunt2.id = OJ.kuntaid ORDER BY kunt2.nimi DESC ) AS Opiskelupaikkakunta_nimi
, (SELECT max(L1.kuukaudet) FROM #Lasna L1 WHERE L1.opiskelijaAvain = O.Avain AND L1.oid = OS.OpiskeluoikeusID ) AS Kirjoilla_olo_kuukausia
, (SELECT max(L2.lukukaudet) FROM #Lasna L2 WHERE L2.opiskelijaAvain = O.Avain AND L2.oid = OS.OpiskeluoikeusID ) AS Lasnaolo_lukukausia
, (SELECT TOP 1 A.Arvosana FROM Virta.dbo.Arvosana A WHERE A.id = OS.ArvosanaID) AS arvosana
, (SELECT TOP 1 AK.Nimi FROM Virta.dbo.ARvosana_asteikko AK WHERE AK.id IN (SELECT Arvosana_asteikkoID FROM Virta.dbo.Arvosana A WHERE A.id = OS.ArvosanaID)) AS asteikko
FROM Virta.dbo.Opiskelija O
JOIN Virta.dbo.Henkilo H ON O.HenkiloID = H.ID
JOIN Virta.dbo.Opintosuoritus OS ON O.id = OS.opiskelijaid
LEFT JOIN Virta.dbo.Op_suor_koulutusala OK ON OK.OpintosuoritusID = OS.ID
LEFT JOIN Virta.dbo.Koulutusala KALA ON KALA.id = OK.KoulutusalaID
JOIN Virta.dbo.Tutkintonimike T ON T.id = OS.TutkintonimikeID
JOIN Virta.dbo.Opintosuorituksen_laji OSL ON OSL.id = OS.Opintosuorituksen_lajiID
JOIN Virta.dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID AND OO.id = OS.OpiskeluoikeusID
JOIN Virta.dbo.Opiskeluoikeusjakso OJ ON OJ.opiskeluoikeusID = OO.id
JOIN Virta.dbo.Opiskeluoikeuden_tyyppi OT ON OO.Op_oikeuden_tyyppiID = OT.id
JOIN ANTERO.dw.d_koulutusluokitus D ON D.koulutusluokitus_koodi = T.koodi
WHERE O.db IN (@kk)
AND H.db IN (@kk)
AND OS.db IN (@kk)
AND OO.db IN (@kk)
AND OT.Koodi IN ('7')
AND OSL.Koodi = '1'
AND YEAR(OS.suorituspaivamaara) BETWEEN @start AND @slut
AND H.henkilotunnus NOT IN
( SELECT U.henkilotunnus
FROM #tmp_Uraseuranta U
WHERE U.henkilotunnus = H.henkilotunnus
AND U.tutkinto_koulutuskoodi = T.koodi
AND U.opiskelijatunnus = O.avain )
DROP TABLE #Lasna
DROP TABLE #tmp_Uraseuranta