Kerätään ensin perusjoukko, opiskelijat, pisteet, läsnäolotiedot omina osajoukkoina Virran tiedoista ja nämä osat yhdistellään
jokaisen opiskelijan vuosijanaksi jotta hänen kohdalla voi suorittaa tilastovuosittain pankkilaskelmat vuosi vuodelta opiskelujen alusta .
Lyhyt kuvaus kokonaislaskentaprosessista:
- Lasketaan pisteet 2006 - 2016
- Haetaan läsnäolotiedot 2006 - 2016
- Haetaan opiskeluoikeustiedot 2006 - 2016
- Poimitaan keväällä aloittaneet
- Poimitaan kaikki tilastovuoden uudet opiskelijat
- Alustetaan pankkitiedot
- Lasketaan kevällä aloittaneiden >= 27 op keränneet
- Lasketaan kumulatiivinen pankkitili
- Pankkisaldot kumulatiivisen laskennan jälkeen
- Kerätään yhteen opiskelijat
- Loppuraportti ylläolevasta per tilastovuosi
Tilastovuosi: 20.9.Tilastovuosi
Edellinen lukuvuosi: 1.8.tilastovuosi-1 - 31.7.tilastovuosi
Aloittanut keväällä: Tilastovuoden aloituspäivämäärän kuukausi <= 7
Raportti Extravipusessa
Esimerkki pankkilaskelma
Tarkistustiedostot
Mittarit
Kysymykset ja vastaukset
- Perusjoukko Opintopisteet
Kaikki Virta lajin 2 (=muu opintosuoritus) on valmiiksi poimittu näkymässä jossa opintopisteille poimitaan hyväksymispäivämäärä jos suoritukselle sellainen on, muuten päivämäärä on suorituksen päivämäärää.
Pisteet lasketaan lukuvuosittain (edellinen lukuvuosi) :
syys 1.8.TILASTOVUOSI-1 - 31.12.TILASTOVUOSI-1
kevät 1.1.TILASTOVUOSI - 31.7.TILASTOVUOSI
Opiskelijoille joille tälle ajanjaksolle olemassa aktiivinen alempi/ylempi korkeakoulututkinto oikeus (opiskeluoikeus tyyppiä 2, 4) tai AMK opiskelija (opiskeluoikeustyyppi 1) ja tältä ajalta kertyneet opintopisteet per lukukausi ja niiden summa.
Vain lehtitason suorituksia.
Näkymä (View) kaikista Virran lajin 2 (muu suoritus) josta poimitaan opintosuoritukselle sen hyväksilukupäivämäärä, jos sellainen on, muuten poimitaan suorituspäivämäärä
CREATE VIEW [Opintosuoritus_pvm]
AS
SELECT CASE WHEN OH.Hyvaksilukupaivamaara IS NOT NULL THEN OH.Hyvaksilukupaivamaara
ELSE
OS.Suorituspaivamaara
END
AS suorpvm
,
OS.Suorituspaivamaara, OH.Hyvaksilukupaivamaara,
OS.id AS opintosuoritusid, OS.OpiskelijaID
, OS.Laajuus
, OL.Koodi
, OS.db
FROM Opintosuoritus OS
JOIN Opintosuorituksen_laji OL ON OL.id = OS.Opintosuorituksen_lajiID
LEFT JOIN Opsuorituksen_hyvaksiluku OH ON OH.OpintosuoritusID = OS.id
WHERE OL.Koodi = '2'
Taulu johon kerätään opintopisteet lukukausittain (edellinen syys ja kevät) tilastovuosittain per opiskelija:
CREATE TABLE [Pisteet55](
[oppilaitos] [varchar](20) NOT NULL,
[oppilaitostunnus] [varchar](10) NOT NULL,
[hetu] [varchar](11) NOT NULL,
[opiskelijaAvain] [varchar](100) NOT NULL,
[opiskeluoikeusAvain] [varchar](100) NULL,
[vuosi] [int] NOT NULL,
[edellinen_lv_syys_pisteet] [int] NULL CONSTRAINT [DF_Pisteet55_edellinen_lv_syys_pisteet] DEFAULT ((0)),
[edellinen_lv_kevat_pisteet] [int] NULL CONSTRAINT [DF_Pisteet55_edellinen_lv_kevat_pisteet] DEFAULT ((0)),
[summa] [int] NULL CONSTRAINT [DF_Pisteet55_summa] DEFAULT ((0)),
[reg_datum] [smalldatetime] NULL CONSTRAINT [DF_Pisteet55_reg_datum] DEFAULT (getdate())
)
Alkuun
AMK pisteet per tilastovuosi (@vuosi_para), per korkeakoulu (@korkeakoulu):
DECLARE @vuosi varchar(4) = @vuosi_para
DECLARE @db varchar(20) = @korkeakoulu
IF OBJECT_ID('tempdb..#param') IS NOT NULL
BEGIN
TRUNCATE TABLE #param
DROP TABLE #param
END
IF OBJECT_ID('tempdb..#tkopisk') IS NOT NULL
BEGIN
TRUNCATE TABLE #tkopisk
DROP TABLE #tkopisk
END
CREATE TABLE #param (
vuosi varchar(4),
org varchar(30),--KK-lyhenne
kk varchar(6) --Oppilaitos
)
TRUNCATE TABLE #param
INSERT INTO #param (vuosi,org,kk)
SELECT
@vuosi as vuosi
,@db as org
,(SELECT OrganisaatioKoodi FROM Yhteiset.Asetukset.Instanssit WHERE DB = @db) as kk
CREATE TABLE #tkopisk (
oppilaitostunnus varchar(6),
hetu varchar(11), -- Henkilötunnus
opiskelijaavain varchar(100),
opiskeluoikeusavain varchar(100),
opiskelija_id int,
opiskeluoikeus_id int,
opes int, -- Edellisenä syyslukukautena (1.8.-31.12.) opintopisteiden määrä
opek int -- Edellisenä kevätlukukautena (1.1.-31.07.) opintopisteiden määrä
)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelija_id ON #tkopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelijaavain ON #tkopisk (opiskelijaavain)
TRUNCATE TABLE #tkopisk
INSERT into #tkopisk (oppilaitostunnus,hetu,opiskelijaavain,opiskelija_id,opiskeluoikeusavain,opiskeluoikeus_id)
SELECT DISTINCT
oppilaitostunnus = (SELECT TOP 1 kk FROM #param)
, hetu = h.Henkilotunnus
, opiskelijaavain = p.Avain
, opiskelija_id = p.ID
, opiskeluoikeusavain = oo.avain
, opiskeluoikeus_id = oo.id
FROM Henkilo h
INNER JOIN dbo.Opiskelija p ON p.HenkiloID = h.ID
INNER JOIN Opiskeluoikeus oo ON oo.OpiskelijaID = p.id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = oo.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=oo.ID
INNER JOIN Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
WHERE 1=1
AND h.db = @db
AND p.db = @db
AND oo.db = @db
AND lt.db = @db
AND ot.Koodi IN ('1') -- tutkinto-oikeus
AND kt.Koodi IN ('1') --aktiivinen
AND p.ID IN (
SELECT OS.OpiskelijaID
FROM Opintosuoritus_pvm OS
WHERE 1 = 1
AND os.db = @db
AND OS.suorpvm BETWEEN cast(cast(@vuosi as int)-1 AS varchar)+'-08-01' AND cast(@vuosi AS varchar) +'-07-31'
AND OS.suorpvm >= OO.Alkamispaivamaara
)
AND oo.ID NOT IN (SELECT opiskeluoikeus_id FROM #tkopisk)
AND oo.ID NOT IN (SELECT OOP.id FROM Opiskeluoikeus OOP WHERE OOP.Paattymispaivamaara <= cast(cast(@vuosi as int)-1 AS varchar)+'-08-01' AND OOP.db = @db )
INSERT into #tkopisk (oppilaitostunnus,hetu,opiskelijaavain,opiskelija_id,opiskeluoikeusavain,opiskeluoikeus_id)
SELECT DISTINCT
oppilaitostunnus = (SELECT TOP 1 kk FROM #param)
, hetu = h.Henkilotunnus
, opiskelijaavain = p.Avain
, opiskelija_id = p.ID
, opiskeluoikeusavain = oo.avain
, opiskeluoikeus_id = oo.id
FROM Henkilo h
INNER JOIN dbo.Opiskelija p ON p.HenkiloID = h.ID
INNER JOIN Opiskeluoikeus oo ON oo.OpiskelijaID = p.id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = oo.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=oo.ID
INNER JOIN Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
WHERE 1=1
AND h.db = @db
AND p.db = @db
AND oo.db = @db
AND lt.db = @db
AND ot.Koodi IN ('1') -- tutkinto-oikeus
AND kt.Koodi IN ('1') --aktiivinen
AND cast(cast(@vuosi as int)-1 AS varchar) + '-08-01' BETWEEN lt.Alkamispaivamaara AND coalesce(lt.Paattymispaivamaara,'9999-01-01')
AND p.ID NOT IN (
SELECT OS.OpiskelijaID
FROM Opintosuoritus_pvm OS
WHERE 1 = 1
AND os.db = @db
AND OS.suorpvm BETWEEN cast(cast(@vuosi as int)-1 AS varchar)+'-08-01' AND cast(@vuosi AS varchar) +'-07-31'
-- Tässä lisäys Opiskeluoikeuden alkamisen jälkeen
AND OS.suorpvm >= OO.Alkamispaivamaara
)
AND oo.ID NOT IN (SELECT opiskeluoikeus_id FROM #tkopisk)
AND oo.ID NOT IN (SELECT OOP.id FROM Opiskeluoikeus OOP WHERE OOP.Paattymispaivamaara <= cast(cast(@vuosi as int)-1 AS varchar)+'-08-01' AND OOP.db = @db )
IF OBJECT_ID('tempdb..#pisteet') IS NOT NULL
BEGIN
TRUNCATE TABLE #pisteet
DROP TABLE #pisteet
END
CREATE TABLE #pisteet (
opiskelija_id int NOT NULL,
opiskeluoikeus_id int,
opintosuoritus_id int NOT NULL,
laajuus decimal(18,6) NOT NULL,
suoritus_pvm date NOT NULL,
es bit,
ek bit
)
CREATE NONCLUSTERED INDEX IX_pisteet_opiskelija_id ON #pisteet (opiskelija_id) INCLUDE (opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm)
CREATE NONCLUSTERED INDEX IX_pisteet_suoritus_pvm ON #pisteet (suoritus_pvm) INCLUDE ([opiskelija_id],[opiskeluoikeus_id],[laajuus])
INSERT INTO #pisteet (opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm,es,ek)
SELECT
opiskelija_id = s.OpiskelijaID
,opiskeluoikeus_id = s.OpiskeluoikeusID
,opintosuoritus_id = s.ID
,laajuus = s.Laajuus
,suoritus_pvm = OS.suorpvm
,es = CASE WHEN OS.suorpvm BETWEEN CAST(cast(@vuosi as int)-1 AS varchar)+'-08-01' AND CAST(cast(@vuosi AS int)-1 AS varchar)+'-12-31'
THEN 1 ELSE 0 END
,ek = CASE WHEN OS.suorpvm BETWEEN CAST(@vuosi AS varchar)+'-01-01' AND CAST(@vuosi AS varchar)+'-07-31'
THEN 1 ELSE 0 END
FROM Opintosuoritus s
INNER JOIN Opintosuoritus_pvm OS ON OS.opintosuoritusid = s.ID
WHERE 1=1
AND s.db = @db
AND OS.db = @db
AND s.ID NOT IN (SELECT ss.OpintosuoritusID FROM Op_suor_sisaltyvyys ss WHERE ss.db = @db )
UPDATE T
SET opes=(
SELECT cast(round(sum(
CASE WHEN s.es = 1 THEN s.Laajuus ELSE 0 END),0) AS int)
FROM #pisteet s
WHERE s.opiskelija_id=T.opiskelija_id
AND s.opiskeluoikeus_id=T.opiskeluoikeus_id
GROUP BY s.opiskelija_id
)
FROM #tkopisk T
WHERE opes IS NULL
UPDATE T
SET opek=(
SELECT cast(round(sum(
CASE WHEN s.ek = 1 THEN s.Laajuus ELSE 0 END),0) AS int)
FROM #pisteet s
WHERE s.opiskelija_id=T.opiskelija_id
AND s.opiskeluoikeus_id=T.opiskeluoikeus_id
GROUP BY s.opiskelija_id
)
FROM #tkopisk T
WHERE opek IS NULL
UPDATE T
set opes=0
FROM #tkopisk T
WHERE opes IS NULL
UPDATE T
set opek=0
FROM #tkopisk T
WHERE opek IS NULL
-- opiskeluoikeusavain
UPDATE T
SET opiskeluoikeus_id = o.ID
, opiskeluoikeusavain = o.avain
FROM #tkopisk T
INNER JOIN Opiskeluoikeus o ON o.OpiskelijaID=T.opiskelija_id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID=o.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt ON lt.OpiskeluoikeusID=o.ID
INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID=lt.Op_oikeuden_tilaID
INNER JOIN Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID = O.id
WHERE 1=1
AND o.db = @db
AND lt.db = @db
AND oj.db = @db
-- tutkinto-tyyppinen
AND ot.Koodi IN ('1')
AND kt.Koodi IN ('1') --aktiivinen
AND cast(cast(@vuosi as int)-1 AS varchar) + '-08-01' BETWEEN oj.Alkamispaivamaara AND coalesce(oj.Paattymispaivamaara,'9999-01-01')
SELECT DISTINCT
@db AS oppilaitos
,T.oppilaitostunnus
,COALESCE(T.hetu,'') "hetu"
, T.opiskelijaAvain
, T.opiskeluoikeusAvain
,@vuosi AS vuosi
,coalesce(T.opes,0) "edellinen_lv_syys_pisteet" -- (1.8-31.12.)
,coalesce(T.opek,0) "edellinen_lv_kevat_pisteet" -- (1.1-31.07.)
,(T.opes + T.opek) "Summa" -- opes + opek
FROM #tkopisk T
DROP TABLE #tkopisk
DROP TABLE #pisteet
DROP TABLE #param
DECLARE @vuosi varchar(4) = @vuosi_para
DECLARE @db varchar(20) = @korkeakoulu
IF OBJECT_ID('tempdb..#param') IS NOT NULL
BEGIN
TRUNCATE TABLE #param
DROP TABLE #param
END
IF OBJECT_ID('tempdb..#tkopisk') IS NOT NULL
BEGIN
TRUNCATE TABLE #tkopisk
DROP TABLE #tkopisk
END
CREATE TABLE #param (
vuosi varchar(4),
org varchar(30),--KK-lyhenne
kk varchar(6) --Yliopisto
)
INSERT INTO #param (vuosi,org,kk)
SELECT
@vuosi as vuosi
,@db as org
,(SELECT OrganisaatioKoodi FROM Yhteiset.Asetukset.Instanssit WHERE DB = @db) as kk
CREATE TABLE #tkopisk (
oppilaitostunnus varchar(6),
hetu varchar(11), -- Henkilötunnus
opiskelijaavain varchar(100),
opiskeluoikeusavain varchar(100),
opiskelija_id int,
opiskeluoikeus_id int,
opes int, -- Edellisenä syyslukukautena (1.8.-31.12.) opintopisteiden määrä
opek int -- Edellisenä kevätlukukautena (1.1.-31.07.) opintopisteiden määrä
)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelija_id ON #tkopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelijaavain ON #tkopisk (opiskelijaavain)
INSERT into #tkopisk (oppilaitostunnus,hetu,opiskelijaavain,opiskelija_id)
SELECT
oppilaitostunnus = (SELECT TOP 1 kk FROM #param)
, hetu = h.Henkilotunnus
, opiskelijaavain = p.Avain
, opiskelija_id = p.ID
FROM Henkilo h
INNER JOIN dbo.Opiskelija p ON p.HenkiloID = h.ID
WHERE 1=1
AND h.db = @db
AND p.db = @db
-- tutkinto-oikeus
AND p.ID IN (
SELECT oo.OpiskelijaID
FROM Opiskeluoikeus oo
INNER JOIN Opiskeluoikeuden_tyyppi ot on ot.ID = oo.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=oo.ID
INNER JOIN Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
WHERE 1=1
AND oo.db = @db
AND lt.db = @db
-- tutkinto-tyyppinen opiskeluoikeus
AND ot.Koodi IN ('2','4')
AND kt.Koodi IN ('1') -- aktiivinen
)
AND p.ID in (
SELECT OS.OpiskelijaID
FROM Opintosuoritus_pvm OS
JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = OS.OpiskelijaID
WHERE 1 = 1
AND os.db = @db
AND OS.suorpvm BETWEEN cast(cast(@vuosi as int)-1 AS varchar)+'-08-01' AND cast(@vuosi AS varchar) +'-07-31'
AND OS.suorpvm >= oo.Alkamispaivamaara
)
AND p.ID NOT IN (SELECT opiskelija_id FROM #tkopisk)
INSERT into #tkopisk (oppilaitostunnus,hetu,opiskelijaavain,opiskelija_id)
SELECT
oppilaitostunnus = (SELECT TOP 1 kk FROM #param)
, hetu = h.Henkilotunnus
, opiskelijaavain = p.Avain
, opiskelija_id = p.ID
FROM Henkilo h
INNER JOIN dbo.Opiskelija p ON p.HenkiloID = h.ID
WHERE 1=1
AND h.db = @db
AND p.db = @db
-- tutkinto-oikeus
AND p.ID IN (
SELECT oo.OpiskelijaID
FROM Opiskeluoikeus oo
INNER JOIN Opiskeluoikeuden_tyyppi ot on ot.ID = oo.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=oo.ID
INNER JOIN Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
WHERE 1=1
AND oo.db = @db
AND lt.db = @db
-- tutkinto-tyyppinen opiskeluoikeus
AND ot.Koodi IN ('2','4')
AND kt.Koodi IN ('1') -- aktiivinen
AND cast(cast(@vuosi as int)-1 AS varchar) + '-08-01' BETWEEN lt.Alkamispaivamaara AND coalesce(lt.Paattymispaivamaara,'9999-01-01')
AND oo.ID NOT IN (SELECT OOP.id FROM Opiskeluoikeus OOP WHERE OOP.Paattymispaivamaara <= cast(cast(@vuosi as int)-1 AS varchar)+'-08-01' AND OOP.db = @db )
)
AND p.ID NOT IN (
SELECT OS.OpiskelijaID
FROM Opintosuoritus_pvm OS
JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = OS.OpiskelijaID
WHERE 1 = 1
AND os.db = @db
AND OS.suorpvm BETWEEN cast(cast(@vuosi as int)-1 AS varchar)+'-08-01' AND cast(@vuosi AS varchar) +'-07-31'
AND OS.suorpvm >= oo.Alkamispaivamaara
)
AND p.ID NOT IN (SELECT opiskelija_id FROM #tkopisk)
IF OBJECT_ID('tempdb..#pisteet') IS NOT NULL
BEGIN
TRUNCATE TABLE #pisteet
DROP TABLE #pisteet
END
CREATE TABLE #pisteet (
opiskelija_id int NOT NULL,
opiskeluoikeus_id int,
opintosuoritus_id int NOT NULL,
laajuus decimal(18,6) NOT NULL,
suoritus_pvm date NOT NULL,
es bit,
ek bit
)
CREATE NONCLUSTERED INDEX IX_pisteet_opiskelija_id ON #pisteet (opiskelija_id) INCLUDE (opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm)
CREATE NONCLUSTERED INDEX IX_pisteet_suoritus_pvm ON #pisteet (suoritus_pvm) INCLUDE ([opiskelija_id],[opiskeluoikeus_id],[laajuus])
INSERT INTO #pisteet (opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm,es,ek)
SELECT
opiskelija_id = s.OpiskelijaID
,opiskeluoikeus_id = s.OpiskeluoikeusID
,opintosuoritus_id = s.ID
,laajuus = s.Laajuus
,suoritus_pvm = OS.suorpvm
,es = CASE WHEN OS.suorpvm BETWEEN CAST(cast(@vuosi as int)-1 AS varchar)+'-08-01' AND CAST(cast(@vuosi AS int)-1 AS varchar)+'-12-31'
THEN 1 ELSE 0 END
,ek = CASE WHEN OS.suorpvm BETWEEN CAST(@vuosi AS varchar)+'-01-01' AND CAST(@vuosi AS varchar)+'-07-31'
THEN 1 ELSE 0 END
FROM Opintosuoritus s
INNER JOIN Opintosuoritus_pvm OS ON OS.opintosuoritusid = s.ID
WHERE 1=1
AND s.db = @db
AND OS.db = @db
AND s.ID NOT IN (SELECT ss.OpintosuoritusID FROM Op_suor_sisaltyvyys ss WHERE ss.db = @db )
UPDATE T
SET opes=(
SELECT cast(round(sum(
CASE WHEN s.es = 1 THEN s.Laajuus ELSE 0 END),0) AS int)
FROM #pisteet s
WHERE s.opiskelija_id=T.opiskelija_id
-- and s.opiskeluoikeus_id=T.opiskeluoikeus_id
GROUP BY s.opiskelija_id
)
FROM #tkopisk T
WHERE opes IS NULL
UPDATE T
SET opek=(
SELECT cast(round(sum(
CASE WHEN s.ek = 1 THEN s.Laajuus ELSE 0 END),0) AS int)
FROM #pisteet s
WHERE s.opiskelija_id=T.opiskelija_id
-- and s.opiskeluoikeus_id=T.opiskeluoikeus_id
GROUP BY s.opiskelija_id
)
FROM #tkopisk T
WHERE opek IS NULL
UPDATE T
set opes=0
FROM #tkopisk T
WHERE opes IS NULL
UPDATE T
set opek=0
FROM #tkopisk T
WHERE opek IS NULL
-- opiskeluoikeusavain
UPDATE T
SET opiskeluoikeus_id = o.ID
, opiskeluoikeusavain = o.avain
FROM #tkopisk T
INNER JOIN Opiskeluoikeus o ON o.OpiskelijaID=T.opiskelija_id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID=o.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt ON lt.OpiskeluoikeusID=o.ID
INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID=lt.Op_oikeuden_tilaID
INNER JOIN Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID = O.id
WHERE 1=1
AND o.db = @db
AND lt.db = @db
AND oj.db = @db
-- tutkinto-tyyppinen
AND ot.Koodi IN ('2','4')
AND kt.Koodi IN ('1') --aktiivinen
AND cast(cast(@vuosi as int)-1 AS varchar) + '-08-01' BETWEEN oj.Alkamispaivamaara AND coalesce(oj.Paattymispaivamaara,'9999-01-01')
SELECT DISTINCT
@db AS oppilaitos
,T.oppilaitostunnus
,COALESCE(T.hetu,'') "hetu"
, T.opiskelijaAvain
, T.opiskeluoikeusAvain
,@vuosi AS vuosi
,coalesce(T.opes,0) "edellinen_lv_syys_pisteet" -- (1.8-31.12.)
,coalesce(T.opek,0) "edellinen_lv_kevat_pisteet" -- (1.1-31.07.)
,(T.opes + T.opek) "Summa" -- opes + opek
FROM #tkopisk T
DROP TABLE #tkopisk
DROP TABLE #pisteet
DROP TABLE #param
Alkuun
- Perusjoukko Läsnäolot
Lukuvuosi-ilmoittaumiset (läsnä/poissa/ei kirjoilla, 1/2/0). Poissa (2) sisältää sekä 2=Poissa, 3=Poissa ei kuluta opintoaikaa
syys 1.8.TILASTOVUOSI-1 - 31.12.TILASTOVUOSI-1 (edellinen_syys_olo)
kevät 1.1.TILASTOVUOSI- 31.7.TILASTOVUOSI(kevat_olo)
syys 1.8.TILASTOVUOSI- 31.12.TILASTOVUOSI(syys_olo)
Opiskelijoille joilla on ollut aikajaksolla aktiivinen opiskeluoikeus ja jolta löytyy läsnolotietoja.
Taulu johon kerätään läsnäolot syys, kevät ja edellinen syys tilastovuosittain per opiskelija
CREATE TABLE [Lasnaolo55](
[oppilaitos] [varchar](20) NOT NULL,
[oppilaitostunnus] [varchar](10) NOT NULL,
[hetu] [varchar](11) NOT NULL,
[opiskelijaAvain] [varchar](100) NOT NULL,
[opiskeluoikeusAvain] [varchar](100) NULL,
[vuosi] [int] NOT NULL,
[syys_olo] [smallint] NULL CONSTRAINT [DF_Lasnaolo55_syys_olo] DEFAULT ((0)),
[kevat_olo] [smallint] NULL CONSTRAINT [DF_LasnaOlo55_kevat_olo] DEFAULT ((0)),
[edellinen_syys_olo] [smallint] NULL CONSTRAINT [DF_Lasnaolo55_edellinen_syys_olo] DEFAULT ((0)),
[uusi_opisk_kevat] [smallint] NULL CONSTRAINT [DF_Lasnaolo55_uusi_opisk_kevat] DEFAULT ((0)),
[uusi_opisk] [smallint] NULL CONSTRAINT [DF_Lasnaolo55_uusi_opisk] DEFAULT ((0)),
[reg_datum] [smalldatetime] NULL CONSTRAINT [DF_Lasnaolo55_reg_datum] DEFAULT (getdate())
)
Alkuun
Läsnäolotiedot per tilastovuosi (@vuosi_para), per korkeakoulu (@korkeakoulu):
AMK
DECLARE @vuosi varchar(4) = @vuosi_para
DECLARE @db varchar(20) = @korkeakoulu
IF OBJECT_ID('tempdb..#param') IS NOT NULL
BEGIN
TRUNCATE TABLE #param
DROP TABLE #param
END
IF OBJECT_ID('tempdb..#tkopisk') IS NOT NULL
BEGIN
TRUNCATE TABLE #tkopisk
DROP TABLE #tkopisk
END
CREATE TABLE #param (
vuosi varchar(4),
org varchar(30),--KK-lyhenne
kk varchar(6) --Yliopisto
)
INSERT INTO #param (vuosi,org,kk)
SELECT
@vuosi as vuosi
,@db as org
,(SELECT OrganisaatioKoodi FROM Yhteiset.Asetukset.Instanssit WHERE DB = @db) as kk
CREATE TABLE #tkopisk (
oppilaitostunnus varchar(6),
hetu varchar(11), -- Henkilötunnus
opiskelijaavain varchar(100),
opiskeluoikeusavain varchar(100),
opiskelija_id int,
opiskeluoikeus_id int,
olotamm smallint, -- Ilmoittautunut keväällä
olosyys smallint, -- Ilmoittautunut syksyllä
edellinen_syysolo smallint -- Edellinen syysolotilanne
)
TRUNCATE TABLE #tkopisk
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelija_id ON #tkopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelijaavain ON #tkopisk (opiskelijaavain)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskeluoikeus_id ON #tkopisk (opiskeluoikeus_id)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskeluoikeusavain ON #tkopisk (opiskeluoikeusavain)
INSERT into #tkopisk (oppilaitostunnus,hetu,opiskelijaavain,opiskelija_id,opiskeluoikeus_id,opiskeluoikeusavain)
SELECT DISTINCT
oppilaitostunnus = (SELECT TOP 1 kk FROM #param)
, hetu = h.Henkilotunnus
, opiskelijaavain = p.Avain
, opiskelija_id = p.ID
, opiskeluoikeus_id = oo.id
, opiskeluoikeusavain = oo.avain
FROM Henkilo h
INNER JOIN dbo.Opiskelija p ON p.HenkiloID = h.ID
INNER JOIN Opiskeluoikeus oo ON oo.OpiskelijaID = p.id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = oo.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt ON lt.OpiskeluoikeusID = oo.ID
INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID = lt.Op_oikeuden_tilaID
WHERE 1 = 1
AND h.db = @db
AND p.db = @db
AND oo.db = @db
AND lt.db = @db
AND ot.Koodi IN ('1') -- tutkinto-oikeus
AND kt.Koodi IN ('1') -- aktiivinen
AND cast(cast(@vuosi as int)-1 AS varchar) + '-08-01' BETWEEN lt.Alkamispaivamaara AND coalesce(lt.Paattymispaivamaara,'9999-01-01')
AND oo.ID IN (
SELECT OpiskeluoikeusID
FROM Lukukausi_ilmoittautuminen l
INNER JOIN Lukukausi_ilm_tila lt ON lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('1','2','3')
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int) as varchar)+'-12-31'
)
-- Viimeinen tilaisuus tulla mukaan perusjoukkoon
INSERT into #tkopisk (oppilaitostunnus,hetu,opiskelijaavain,opiskelija_id,opiskeluoikeus_id,opiskeluoikeusavain)
SELECT DISTINCT
oppilaitostunnus = (SELECT TOP 1 kk FROM #param)
, hetu = h.Henkilotunnus
, opiskelijaavain = p.Avain
, opiskelija_id = p.ID
, opiskeluoikeus_id = oo.id
, opiskeluoikeusavain = oo.avain
FROM Henkilo h
INNER JOIN dbo.Opiskelija p ON p.HenkiloID = h.ID
INNER JOIN Opiskeluoikeus oo ON oo.OpiskelijaID = p.id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = oo.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=oo.ID
INNER JOIN Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
WHERE 1=1
AND oo.id NOT IN (SELECT opiskeluoikeus_id AS ID FROM #tkopisk) -- Ei edellisessä
AND h.db = @db
AND p.db = @db
AND oo.db = @db
AND lt.db = @db
AND ot.Koodi IN ('1') -- tutkinto-oikeus
AND kt.Koodi IN ('1') -- aktiivinen
--AND cast(cast(@vuosi as int) AS varchar) + '-01-01' BETWEEN lt.Alkamispaivamaara AND coalesce(lt.Paattymispaivamaara,'9999-01-01')
-- jolla on läsnäoloja
AND oo.ID IN (
SELECT OpiskeluoikeusID AS ID
FROM Lukukausi_ilmoittautuminen l
INNER JOIN Lukukausi_ilm_tila lt ON lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('1','2','3')
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) + '-01-01' AND cast(@vuosi AS varchar) +'-12-31'
)
-- Läsnäolotiedot
-- olotamm :: kevään ilmo tilanne
UPDATE T
set T.olotamm = 1
FROM #tkopisk T
WHERE T.opiskeluoikeus_id IN (
SELECT OpiskeluoikeusID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('1') -- läsnä
-- 2015-01-01
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) +'-01-01' AND cast(@vuosi AS varchar) +'-07-31'
)
and T.olotamm is null
;
UPDATE T
set T.olotamm = 2
FROM #tkopisk T
WHERE T.opiskeluoikeus_id IN (
SELECT OpiskeluoikeusID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('2','3') -- poissa, poissa ei kuluta
-- "2015-01-01"
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) + '-01-01' AND cast(@vuosi AS varchar) + '-07-31'
)
and T.olotamm is null
;
-- ei ilmoa keväällä, nollaksi
UPDATE T
set T.olotamm=0
FROM #tkopisk T
WHERE T.olotamm is null
;
-- olosyys
UPDATE T
set T.olosyys = 1
FROM #tkopisk T
WHERE T.opiskeluoikeus_id IN (
SELECT OpiskeluoikeusID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('1') -- läsnä
-- 2015-08-01
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) + '-08-01' AND cast(@vuosi AS varchar) + '-12-31'
)
and T.olosyys is null
;
UPDATE T
set T.olosyys = 2
FROM #tkopisk T
WHERE T.opiskeluoikeus_id IN (
SELECT OpiskeluoikeusID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('2','3') -- poissa, poissa ei kuluta
-- 2015-08-01
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) + '-08-01' AND cast(@vuosi AS varchar) + '-12-31'
)
and T.olosyys is null
;
-- ei ilmoa syksyllä, nollaksi
UPDATE T
set T.olosyys=0
FROM #tkopisk T
WHERE T.olosyys is null
;
-- edellinen olosyys
UPDATE T
set T.edellinen_syysolo = 1
FROM #tkopisk T
WHERE opiskeluoikeus_id IN (
SELECT OpiskeluoikeusID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('1') -- läsnä
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int)-1 as varchar)+'-12-31'
)
and T.edellinen_syysolo is null
;
UPDATE T
set T.edellinen_syysolo = 2
FROM #tkopisk T
WHERE opiskeluoikeus_id IN (
SELECT OpiskeluoikeusID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('2','3') -- poissa, poissa ei kuluta
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int)-1 as varchar)+'-12-31'
)
and T.edellinen_syysolo is null
;
-- ei ilmoa syksyllä, nollaksi
UPDATE T
set T.edellinen_syysolo=0
FROM #tkopisk T
WHERE T.edellinen_syysolo is null
;
SELECT DISTINCT
@db AS oppilaitos
, T.oppilaitostunnus
, COALESCE(T.hetu,'') "hetu"
, T.opiskelijaAvain
, T.opiskeluoikeusAvain
, @vuosi AS vuosi
, COALESCE(T.olosyys,0) "syys_olo"
, COALESCE(T.olotamm,0) "kevat_olo"
, T.edellinen_syysolo "edellinen_syys_olo"
FROM #tkopisk T
WHERE (T.olotamm + T.olosyys + COALESCE(T.edellinen_syysolo,0) ) > 0
DROP TABLE #tkopisk
DROP TABLE #param
YO
DECLARE @vuosi varchar(4) = @vuosi_para
DECLARE @db varchar(20) = @korkeakoulu
IF OBJECT_ID('tempdb..#param') IS NOT NULL
BEGIN
TRUNCATE TABLE #param
DROP TABLE #param
END
IF OBJECT_ID('tempdb..#tkopisk') IS NOT NULL
BEGIN
TRUNCATE TABLE #tkopisk
DROP TABLE #tkopisk
END
CREATE TABLE #param (
vuosi varchar(4),
org varchar(30),--KK-lyhenne
kk varchar(6) --Yliopisto
)
INSERT INTO #param (vuosi,org,kk)
SELECT
@vuosi as vuosi
,@db as org
,(SELECT OrganisaatioKoodi FROM Yhteiset.Asetukset.Instanssit WHERE DB = @db) as kk
CREATE TABLE #tkopisk (
oppilaitostunnus varchar(6),
hetu varchar(11), -- Henkilötunnus
opiskelijaavain varchar(100),
opiskeluoikeusavain varchar(100),
opiskelija_id int,
opiskeluoikeus_id int,
olotamm smallint, -- Ilmoittautunut keväällä
olosyys smallint, -- Ilmoittautunut syksyllä
edellinen_syysolo smallint -- Edellinen syysolotilanne
)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelija_id ON #tkopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelijaavain ON #tkopisk (opiskelijaavain)
INSERT into #tkopisk (oppilaitostunnus,hetu,opiskelijaavain,opiskelija_id)
SELECT
oppilaitostunnus = (SELECT TOP 1 kk FROM #param)
, hetu = h.Henkilotunnus
, opiskelijaavain = p.Avain
, opiskelija_id = p.ID
FROM Henkilo h
INNER JOIN dbo.Opiskelija p ON p.HenkiloID = h.ID
WHERE 1=1
AND h.db = @db
AND p.db = @db
-- tutkinto-oikeus
AND p.ID IN (
SELECT oo.OpiskelijaID
FROM Opiskeluoikeus oo
INNER JOIN Opiskeluoikeuden_tyyppi ot on ot.ID = oo.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=oo.ID
INNER JOIN Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
WHERE 1=1
AND oo.db = @db
AND lt.db = @db
-- tutkinto-tyyppinen opiskeluoikeus
AND ot.Koodi IN ('2','4')
AND kt.Koodi IN ('1') -- aktiivinen
)
-- ja jolla läsnäoloja
AND p.ID in (
SELECT OpiskelijaID
FROM Lukukausi_ilmoittautuminen l
INNER JOIN Lukukausi_ilm_tila lt on lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE lt.Koodi IN ('1','2','3')
AND l.db = @db
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int) AS varchar)+'-12-31'
)
AND p.ID NOT IN (SELECT opiskelija_id FROM #tkopisk)
-- opiskeluoikeusavain
UPDATE T
SET opiskeluoikeus_id = o.ID
, opiskeluoikeusavain = o.avain
FROM #tkopisk T
INNER JOIN Opiskeluoikeus o ON o.OpiskelijaID=T.opiskelija_id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID=o.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt ON lt.OpiskeluoikeusID=o.ID
INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID=lt.Op_oikeuden_tilaID
INNER JOIN Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID = O.id
WHERE 1=1
AND o.db = @db
AND lt.db = @db
AND oj.db = @db
-- tutkinto-tyyppinen
AND ot.Koodi IN ('2','4')
AND kt.Koodi IN ('1') --aktiivinen
AND cast(cast(@vuosi as int)-1 AS varchar) + '-08-01' BETWEEN oj.Alkamispaivamaara AND coalesce(oj.Paattymispaivamaara,'9999-01-01')
AND o.OpiskelijaID in (
SELECT OpiskelijaID
FROM Lukukausi_ilmoittautuminen l
INNER JOIN Lukukausi_ilm_tila lt on lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE lt.Koodi IN ('1','2','3')
AND l.db = @db
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int) AS varchar)+'-12-31'
)
-- Läsnäolotiedot
UPDATE T
SET olotamm = 1
FROM #tkopisk T
WHERE opiskelija_id in (
SELECT OpiskelijaID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE lt.Koodi IN ('1') --läsnä
AND l.db = @db
-- Esim. 2015-01-01
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) +'-01-01' AND cast(@vuosi AS varchar) +'-07-31'
)
AND olotamm is null
UPDATE T
set olotamm = 2
FROM #tkopisk T
WHERE opiskelija_id in (
SELECT OpiskelijaID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE lt.Koodi in ('2','3') --poissa, poissa ei kuluta
AND l.db = @db
-- Esim. 2015-01-01
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) +'-01-01' AND cast(@vuosi AS varchar) +'-07-31'
)
and olotamm is null
-- ei ilmoa keväällä, tyhjäksi
UPDATE T
set olotamm = 0
FROM #tkopisk T
WHERE olotamm is null
-- olosyys :: syksyn ilmo tilanne
UPDATE T
set olosyys= 1
FROM #tkopisk T
WHERE opiskelija_id in (
SELECT OpiskelijaID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE lt.Koodi IN ('1') --läsnä
AND l.db = @db
-- Esim. 2015-08-01
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) + '-08-01' AND cast(@vuosi AS varchar) + '-12-31'
)
AND olosyys is null
UPDATE T
set olosyys = 2
FROM #tkopisk T
WHERE opiskelija_id in (
SELECT OpiskelijaID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE lt.Koodi in ('2','3') --poissa, poissa ei kuluta
AND l.db = @db
-- Esim. 2015-08-01
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) + '-08-01' AND cast(@vuosi AS varchar) + '-12-31'
)
AND olosyys is null
-- edellinen olosyys
UPDATE T
set edellinen_syysolo = 1
FROM #tkopisk T
WHERE opiskelija_id in (
SELECT OpiskelijaID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('1') -- läsnä
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int)-1 as varchar)+'-12-31'
)
and edellinen_syysolo is null
UPDATE T
set edellinen_syysolo = 2
FROM #tkopisk T
WHERE opiskelija_id in (
SELECT OpiskelijaID
FROM Lukukausi_ilmoittautuminen l
inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('2','3') -- poissa, poissa ei kuluta
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int)-1 as varchar)+'-12-31'
)
and edellinen_syysolo is null
-- ei ilmoa syksyllä, nollaksi
UPDATE T
set edellinen_syysolo=0
FROM #tkopisk T
WHERE edellinen_syysolo is null
SELECT DISTINCT
@db AS oppilaitos
, T.oppilaitostunnus
, COALESCE(T.hetu,'') "hetu"
, T.opiskelijaAvain
, T.opiskeluoikeusAvain
, @vuosi AS vuosi
, COALESCE(T.olosyys,0) "syys_olo"
, COALESCE(T.olotamm,0) "kevat_olo"
, T.edellinen_syysolo "edellinen_syys_olo"
FROM #tkopisk T
WHERE (T.olotamm + T.olosyys + COALESCE(T.edellinen_syysolo,0) ) > 0
DROP TABLE #tkopisk
DROP TABLE #param
Alkuun
- Perusjoukko Opiskeluoikeudet
Ajanjaksoille
syys 1.8.TILASTOVUOSI-1 - 31.12.TILASTOVUOSI-1
kevät 1.1.TILASTOVUOSI- 31.7.TILASTOVUOSI
Poimintaan opiskeluoikeuden alkamis päivämäärä ja opiskelujen aloituspäivä ensimmäisen läsnäolotiedon mukaan.
Lisäksi opiskeluoikeusjaksosta Tilastokeskuksen 6-numeroinen tutkinto koodi.
Taulu johon kerätään opiskeluoikeudet tilastovuosittain per opiskelija:
CREATE TABLE [Opiskeluoikeus55](
[oppilaitos] [varchar](20) NOT NULL,
[oppilaitostunnus] [varchar](10) NOT NULL,
[hetu] [varchar](11) NOT NULL,
[opiskelijaAvain] [varchar](100) NOT NULL,
[opiskeluoikeusAvain] [varchar](100) NULL,
[vuosi] [int] NOT NULL,
[tila] [smallint] NULL,
[lo_aloituspvm] [date] NULL,
[oo_aloituspvm] [date] NULL,
[Tkkoodi] [varchar](10) NULL,
[reg_datum] [smalldatetime] NULL CONSTRAINT [DF_Opiskeluoikeus55_reg_datum] DEFAULT (getdate())
)
DECLARE @vuosi varchar(4) = @vuosi_para
DECLARE @db varchar(20) = @korkeakoulu
IF OBJECT_ID('tempdb..#param') IS NOT NULL
BEGIN
TRUNCATE TABLE #param
DROP TABLE #param
END
IF OBJECT_ID('tempdb..#tkopisk') IS NOT NULL
BEGIN
TRUNCATE TABLE #tkopisk
DROP TABLE #tkopisk
END
CREATE TABLE #param (
vuosi varchar(4),
org varchar(30),--KK-lyhenne
kk varchar(6) --Oppilaitos
)
TRUNCATE TABLE #param
INSERT INTO #param (vuosi,org,kk)
SELECT
@vuosi as vuosi
,@db as org
,(SELECT OrganisaatioKoodi FROM Yhteiset.Asetukset.Instanssit WHERE DB = @db) as kk
CREATE TABLE #Aloittanut (
oid int,
minAlkamispaivamaara date,
LAlkamispaivamaara date,
OAlkamispaivamaara date,
opiskelija_id int not null
)
CREATE TABLE #Min_aloituspvm (
oid int,
alk_pvm date
)
CREATE TABLE #tkopisk (
oppilaitostunnus varchar(6),
hetu varchar(11),
opiskelijaavain varchar(100),
opiskeluoikeusavain varchar(100),
opiskelija_id int,
opiskeluoikeus_id int,
tila smallint ,
lo_aloituspvm date ,
oo_aloituspvm date ,
Tkkoodi varchar(10)
)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelija_id ON #tkopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskeluoikeus_id ON #tkopisk (opiskeluoikeus_id)
INSERT into #tkopisk (oppilaitostunnus,hetu,opiskelijaavain,opiskelija_id,opiskeluoikeus_id,opiskeluoikeusavain, oo_aloituspvm)
SELECT DISTINCT
oppilaitostunnus = (SELECT TOP 1 kk FROM #param)
, hetu = h.Henkilotunnus
, opiskelijaavain = p.Avain
, opiskelija_id = p.ID
, opiskeluoikeus_id = oo.id
, opiskeluoikeusavain = oo.avain
, oo_aloituspvm = oo.Alkamispaivamaara
FROM Henkilo h
INNER JOIN dbo.Opiskelija p ON p.HenkiloID = h.ID
INNER JOIN Opiskeluoikeus oo ON oo.OpiskelijaID = p.id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = oo.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt ON lt.OpiskeluoikeusID = oo.ID
INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID = lt.Op_oikeuden_tilaID
WHERE 1 = 1
AND h.db = @db
AND p.db = @db
AND oo.db = @db
AND lt.db = @db
AND ot.Koodi IN ('1') -- tutkinto-oikeus
AND kt.Koodi IN ('1') -- aktiivinen
AND cast(cast(@vuosi as int)-1 AS varchar) + '-08-01' BETWEEN lt.Alkamispaivamaara AND coalesce(lt.Paattymispaivamaara,'9999-01-01')
-- jolla on läsnäoloja
AND oo.ID IN (
SELECT OpiskeluoikeusID
FROM Lukukausi_ilmoittautuminen l
INNER JOIN Lukukausi_ilm_tila lt ON lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('1','2','3')
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int) as varchar)+'-12-31'
)
-- Viimeinen tilaisuus tulla mukaan perusjoukkoon
INSERT into #tkopisk (oppilaitostunnus,hetu,opiskelijaavain,opiskelija_id,opiskeluoikeus_id,opiskeluoikeusavain, oo_aloituspvm)
SELECT DISTINCT
oppilaitostunnus = (SELECT TOP 1 kk FROM #param)
, hetu = h.Henkilotunnus
, opiskelijaavain = p.Avain
, opiskelija_id = p.ID
, opiskeluoikeus_id = oo.id
, opiskeluoikeusavain = oo.avain
, oo_aloituspvm = oo.Alkamispaivamaara
FROM Henkilo h
INNER JOIN dbo.Opiskelija p ON p.HenkiloID = h.ID
INNER JOIN Opiskeluoikeus oo ON oo.OpiskelijaID = p.id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = oo.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=oo.ID
INNER JOIN Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
WHERE 1=1
AND oo.ID NOT IN (SELECT opiskeluoikeus_id FROM #tkopisk) -- Ei edellisessä
AND h.db = @db
AND p.db = @db
AND oo.db = @db
AND lt.db = @db
AND ot.Koodi IN ('1') -- tutkinto-oikeus
AND kt.Koodi IN ('1') -- aktiivinen
--AND cast(cast(@vuosi as int) AS varchar) + '-01-01' BETWEEN lt.Alkamispaivamaara AND coalesce(lt.Paattymispaivamaara,'9999-01-01')
-- jolla on läsnäoloja
AND oo.ID IN (
SELECT OpiskeluoikeusID AS ID
FROM Lukukausi_ilmoittautuminen l
INNER JOIN Lukukausi_ilm_tila lt ON lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('1','2','3')
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) + '-01-01' AND cast(@vuosi AS varchar) +'-12-31'
)
-- opiskeluoikeusavain
UPDATE T
SET
tila = 1
, oo_aloituspvm = oj.Alkamispaivamaara
, Tkkoodi = tn.Koodi
FROM #tkopisk T
INNER JOIN Opiskeluoikeus o ON o.OpiskelijaID=T.opiskelija_id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID=o.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt ON lt.OpiskeluoikeusID=o.ID
INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID=lt.Op_oikeuden_tilaID
INNER JOIN Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID = O.id
INNER JOIN Tutkintonimike tn on tn.ID = oj.TutkintonimikeID
WHERE 1 = 1
AND o.db = @db
AND lt.db = @db
AND oj.db = @db
AND ot.Koodi IN ('1') -- tutkinto-oikeus
AND kt.Koodi IN ('1') -- aktiivinen
AND cast(cast(@vuosi as int)-1 AS varchar) + '-08-01' BETWEEN oj.Alkamispaivamaara AND coalesce(oj.Paattymispaivamaara,'9999-01-01')
-- jolla on läsnäoloja
AND o.ID IN (
SELECT OpiskeluoikeusID
FROM Lukukausi_ilmoittautuminen l
INNER JOIN Lukukausi_ilm_tila lt ON lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('1','2','3')
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int) as varchar)+'-12-31'
)
-- opiskeluoikeusavain
UPDATE T
SET
tila = 1
, oo_aloituspvm = oj.Alkamispaivamaara
, Tkkoodi = tn.Koodi
FROM #tkopisk T
INNER JOIN Opiskeluoikeus o ON o.OpiskelijaID=T.opiskelija_id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID=o.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt ON lt.OpiskeluoikeusID=o.ID
INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID=lt.Op_oikeuden_tilaID
INNER JOIN Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID = O.id
INNER JOIN Tutkintonimike tn on tn.ID = oj.TutkintonimikeID
WHERE 1=1
AND o.db = @db
AND lt.db = @db
AND oj.db = @db
AND ot.Koodi IN ('1') -- tutkinto-oikeus
AND kt.Koodi IN ('1') -- aktiivinen
--AND cast(cast(@vuosi as int) AS varchar) + '-01-01' BETWEEN oj.Alkamispaivamaara AND coalesce(oj.Paattymispaivamaara,'9999-01-01')
-- jolla on läsnäoloja
AND o.ID IN (
SELECT OpiskeluoikeusID AS ID
FROM Lukukausi_ilmoittautuminen l
INNER JOIN Lukukausi_ilm_tila lt ON lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE
l.db = @db
AND lt.Koodi IN ('1','2','3')
AND l.Alkamispaivamaara BETWEEN cast(@vuosi AS varchar) + '-01-01' AND cast(@vuosi AS varchar) +'-12-31'
)
TRUNCATE TABLE #Aloittanut
INSERT INTO #Aloittanut (oid, minAlkamispaivamaara, LAlkamispaivamaara, OAlkamispaivamaara,opiskelija_id )
SELECT oo.ID,
CASE WHEN MIN(L.Alkamispaivamaara) >= OO.Alkamispaivamaara THEN MIN(L.Alkamispaivamaara)
ELSE OO.Alkamispaivamaara
END AS minAlkamispaivamaara
,l.Alkamispaivamaara AS LAlkamispaivamaara
,oo.Alkamispaivamaara AS OAlkamispaivamaara
, o.id
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 ot on ot.ID=oo.Op_oikeuden_tyyppiID
JOIN dbo.Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
JOIN dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
WHERE OO.id IN (SELECT opiskeluoikeus_id FROM #tkopisk)
AND h.db = @db
AND o.db = @db
AND oo.db = @db
AND L.db = @db
AND LT.Koodi IN ('1')
AND ot.Koodi IN ('1')
GROUP BY L.Alkamispaivamaara, oo.Alkamispaivamaara, oo.ID, o.id
TRUNCATE TABLE #Min_aloituspvm
INSERT INTO #Min_aloituspvm (oid, alk_pvm)
SELECT oid, MIN(minAlkamispaivamaara) As alk_pvm
FROM #Aloittanut
GROUP BY oid
CREATE TABLE #Min_aloituspvm_siirto (
oid int,
alk_pvm date
)
INSERT INTO #Min_aloituspvm_siirto (oid, alk_pvm)
SELECT MA.oid,
CASE WHEN MA.alk_pvm >= OO.Siirtopaivamaara THEN OO.Siirtopaivamaara
ELSE MA.alk_pvm
END
AS alk_pvm
FROM #Min_aloituspvm MA
JOIN dbo.Opiskeluoikeus OO ON MA.oid = OO.id AND oo.db = @db
WHERE OO.Alkamispaivamaara <= cast(@vuosi AS varchar) + '-12-31'
UPDATE #Min_aloituspvm
SET alk_pvm = MT.alk_pvm
FROM #Min_aloituspvm_siirto MT
WHERE #Min_aloituspvm.oid = MT.oid
UPDATE T
SET
lo_aloituspvm = M.alk_pvm
FROM #tkopisk T
join Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
join #Min_aloituspvm M ON M.oid = o.id
WHERE o.db = @db
UPDATE T
set T.lo_aloituspvm = T.oo_aloituspvm
FROM #tkopisk T
WHERE T.oo_aloituspvm > T.lo_aloituspvm
SELECT DISTINCT
@db AS oppilaitos
, T.oppilaitostunnus
, COALESCE(T.hetu,'') "hetu"
, T.opiskelijaAvain
, T.opiskeluoikeusAvain
, @vuosi AS vuosi
, T.tila
, T.lo_aloituspvm
, T.oo_aloituspvm
, T.Tkkoodi
FROM #tkopisk T
--WHERE T.tila IS NOT NULL
DROP TABLE #tkopisk
DROP TABLE #param
DROP TABLE #Aloittanut
DROP TABLE #Min_aloituspvm
DROP TABLE #Min_aloituspvm_siirto
DECLARE @vuosi varchar(4) = @vuosi_para
DECLARE @db varchar(20) = @korkeakoulu
IF OBJECT_ID('tempdb..#param') IS NOT NULL
BEGIN
TRUNCATE TABLE #param
DROP TABLE #param
END
IF OBJECT_ID('tempdb..#tkopisk') IS NOT NULL
BEGIN
TRUNCATE TABLE #tkopisk
DROP TABLE #tkopisk
END
CREATE TABLE #param (
vuosi varchar(4),
org varchar(30),--KK-lyhenne
kk varchar(6) --Yliopisto
)
INSERT INTO #param (vuosi,org,kk)
SELECT
@vuosi as vuosi
,@db as org
,(SELECT OrganisaatioKoodi FROM Yhteiset.Asetukset.Instanssit WHERE DB = @db) as kk
CREATE TABLE #Aloittanut (
oid int,
minAlkamispaivamaara date,
LAlkamispaivamaara date,
OAlkamispaivamaara date,
opiskelija_id int not null
)
CREATE TABLE #Min_aloituspvm (
oid int,
alk_pvm date
)
CREATE TABLE #tkopisk (
oppilaitostunnus varchar(6),
hetu varchar(11), -- Henkilötunnus
opiskelijaavain varchar(100),
opiskeluoikeusavain varchar(100),
opiskelija_id int,
opiskeluoikeus_id int,
tila smallint ,
lo_aloituspvm date ,
oo_aloituspvm date ,
Tkkoodi varchar(10)
)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelija_id ON #tkopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelijaavain ON #tkopisk (opiskelijaavain)
INSERT into #tkopisk (oppilaitostunnus,hetu,opiskelijaavain,opiskelija_id)
SELECT
oppilaitostunnus = (SELECT TOP 1 kk FROM #param)
, hetu = h.Henkilotunnus
, opiskelijaavain = p.Avain
, opiskelija_id = p.ID
FROM Henkilo h
INNER JOIN dbo.Opiskelija p ON p.HenkiloID = h.ID
WHERE 1=1
AND h.db = @db
AND p.db = @db
-- tutkinto-oikeus
AND p.ID IN (
SELECT oo.OpiskelijaID
FROM Opiskeluoikeus oo
INNER JOIN Opiskeluoikeuden_tyyppi ot on ot.ID = oo.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=oo.ID
INNER JOIN Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
WHERE 1=1
AND oo.db = @db
AND lt.db = @db
-- tutkinto-tyyppinen opiskeluoikeus
AND ot.Koodi IN ('2','4')
AND kt.Koodi IN ('1') -- aktiivinen
)
-- ja jolla läsnäoloja
AND p.ID in (
SELECT OpiskelijaID
FROM Lukukausi_ilmoittautuminen l
INNER JOIN Lukukausi_ilm_tila lt on lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE lt.Koodi IN ('1','2','3')
AND l.db = @db
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int) AS varchar)+'-12-31'
)
AND p.ID NOT IN (SELECT opiskelija_id FROM #tkopisk)
-- opiskeluoikeusavain
UPDATE T
SET opiskeluoikeus_id = o.ID
, opiskeluoikeusavain = o.avain
, tila = 1
, oo_aloituspvm = oj.Alkamispaivamaara
, Tkkoodi = tn.Koodi
FROM #tkopisk T
INNER JOIN Opiskeluoikeus o ON o.OpiskelijaID=T.opiskelija_id
INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID=o.Op_oikeuden_tyyppiID
INNER JOIN Op_oikeuteen_liittyva_tila lt ON lt.OpiskeluoikeusID=o.ID
INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID=lt.Op_oikeuden_tilaID
INNER JOIN Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID = O.id
INNER JOIN Tutkintonimike tn on tn.ID = oj.TutkintonimikeID
WHERE 1=1
AND o.db = @db
AND lt.db = @db
AND oj.db = @db
-- tutkinto-tyyppinen
AND ot.Koodi IN ('2','4')
AND kt.Koodi IN ('1') --aktiivinen
AND cast(cast(@vuosi as int)-1 AS varchar) + '-08-01' BETWEEN oj.Alkamispaivamaara AND coalesce(oj.Paattymispaivamaara,'9999-01-01')
AND o.OpiskelijaID in (
SELECT OpiskelijaID
FROM Lukukausi_ilmoittautuminen l
INNER JOIN Lukukausi_ilm_tila lt on lt.ID = l.Lukukausi_ilmoittautumisen_tilaID
WHERE lt.Koodi IN ('1','2','3')
AND l.db = @db
AND l.Alkamispaivamaara BETWEEN cast(cast(@vuosi as int)-1 as varchar)+'-08-01' AND cast(cast(@vuosi as int) AS varchar)+'-12-31'
)
TRUNCATE TABLE #Aloittanut
INSERT INTO #Aloittanut (oid, minAlkamispaivamaara, LAlkamispaivamaara, OAlkamispaivamaara,opiskelija_id )
SELECT oo.ID,
CASE WHEN MIN(L.Alkamispaivamaara) >= OO.Alkamispaivamaara THEN MIN(L.Alkamispaivamaara)
ELSE OO.Alkamispaivamaara
END AS minAlkamispaivamaara
,l.Alkamispaivamaara AS LAlkamispaivamaara
,oo.Alkamispaivamaara AS OAlkamispaivamaara
,o.id
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 ot on ot.ID=oo.Op_oikeuden_tyyppiID
LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
JOIN dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
INNER JOIN Op_oikeuteen_liittyva_tila olt ON olt.OpiskeluoikeusID=oo.ID
INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID=olt.Op_oikeuden_tilaID
WHERE O.id IN (SELECT opiskelija_id FROM #tkopisk)
AND h.db = @db
AND o.db = @db
AND oo.db = @db
AND L.db = @db
AND olt.db = @db
AND ot.Koodi IN ('2','4')
AND kt.Koodi = '1' --aktiivinen
AND LT.Koodi = '1' /* Läsnä */
GROUP BY L.Alkamispaivamaara, oo.Alkamispaivamaara, oo.ID, o.id
INSERT INTO #Min_aloituspvm (oid, alk_pvm)
SELECT oid, MIN(minAlkamispaivamaara) As alk_pvm
FROM #Aloittanut
GROUP BY oid
UPDATE T
SET lo_aloituspvm = M.alk_pvm
FROM #tkopisk T
INNER JOIN Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
INNER JOIN #Min_aloituspvm M ON o.ID=M.oid
WHERE 1=1
AND o.db = @db
UPDATE T
set T.lo_aloituspvm = T.oo_aloituspvm
FROM #tkopisk T
WHERE T.oo_aloituspvm > T.lo_aloituspvm
SELECT DISTINCT
@db AS oppilaitos
, T.oppilaitostunnus
, COALESCE(T.hetu,'') "hetu"
, T.opiskelijaAvain
, T.opiskeluoikeusAvain
, @vuosi AS vuosi
, T.tila
, T.lo_aloituspvm
, T.oo_aloituspvm
, T.Tkkoodi
FROM #tkopisk T
DROP TABLE #tkopisk
DROP TABLE #param
DROP TABLE #Aloittanut
DROP TABLE #Min_aloituspvm
Alkuun
Perusjoukko Pankki (tulos ylläolevien tiedostoista johdettu)
Kumulatiivinen pankki opintopistekertymä jos on kertyny yli 55 op tai 27 op keväällä aloittaneet.
Saldot ja suoritusstatukset "suorittanut 55 ilman pankkia", "suorittanut pankin avulla", "suorittanut 27".
Taulu johon kerätään pankkilaskelmat tilastovuosittain per opiskelija:
CREATE TABLE [Pankki55](
[oppilaitos] [varchar](20) NOT NULL,
[oppilaitostunnus] [varchar](10) NOT NULL,
[hetu] [varchar](11) NOT NULL,
[opiskelijaAvain] [varchar](100) NOT NULL,
[opiskeluoikeusAvain] [varchar](100) NULL,
[vuosi] [int] NOT NULL,
[uutta_pankkiin] [int] NULL CONSTRAINT [DF_Pankki55_uutta_pankkiin] DEFAULT ((0)),
[pankki_kumu_ennen_55] [int] NULL CONSTRAINT [DF_Pankki55_pankki_kumu_ennen_55] DEFAULT ((0)),
[op_summa_kun_ote_pankista] [int] NULL CONSTRAINT [DF_Pankki55_op_summa_kun_ote_pankista] DEFAULT ((0)),
[pankki_saldo_55] [int] NULL CONSTRAINT [DF_Pankki55_pankki_saldo_55] DEFAULT ((0)),
[suorittanut_27] [smallint] NULL CONSTRAINT [DF_Pankki55_suorittanut_27] DEFAULT ((0)),
[suorittanut_55_ilman_pankkia] [smallint] NULL CONSTRAINT [DF_Pankki55_suorittanut_55_ilman_pankkia] DEFAULT ((0)),
[suorittanut_55_pankin_avulla] [smallint] NULL CONSTRAINT [DF_Pankki55_suorittanut_55_pankin_avulla] DEFAULT ((0)),
[reg_datum] [smalldatetime] NULL CONSTRAINT [DF_Pankki55_reg_datum] DEFAULT (getdate())
)
DECLARE @vuosi varchar(4) = @vuosi_para
DECLARE @db varchar(20) = @korkeakoulu
DELETE FROM [Pankki55] WHERE oppilaitos = @korkeakoulu AND vuosi = @vuosi
INSERT INTO [Pankki55] ([oppilaitos],[oppilaitostunnus],[hetu],[opiskelijaAvain],[opiskeluoikeusAvain],[vuosi], uutta_pankkiin)
SELECT DISTINCT [oppilaitos],[oppilaitostunnus],[hetu],[opiskelijaAvain],[opiskeluoikeusAvain],[vuosi] , CASE WHEN summa >= 55 THEN summa -55 ELSE 0 END AS uutta_pankkiin
FROM [Pisteet55] WHERE oppilaitos = @korkeakoulu AND vuosi = @vuosi
UPDATE P
SET P.uutta_pankkiin = (P2.summa - 27)
, P.suorittanut_27 = 1
, P.pankki_saldo_55 = (P2.summa - 27)
FROM Pankki55 P
JOIN Pisteet55 P2 ON P2.oppilaitostunnus = P.oppilaitostunnus AND P2.opiskelijaAvain = P.opiskelijaAvain AND P.opiskeluoikeusAvain = P2.opiskeluoikeusAvain
JOIN Lasnaolo55 L ON L.oppilaitostunnus = P.oppilaitostunnus AND L.opiskelijaAvain = P.opiskelijaAvain AND L.opiskeluoikeusAvain = P.opiskeluoikeusAvain
WHERE P.oppilaitos = @db
AND P.vuosi = @vuosi
AND P2.vuosi = @vuosi
AND L.vuosi = @vuosi
AND L.uusi_opisk_kevat = 1
AND P2.summa >= 27
UPDATE PUU
SET PUU.pankki_kumu_ennen_55 = P.pankki_saldo_55
FROM Pankki55 PUU
JOIN Pankki55 P ON P.oppilaitostunnus = PUU.oppilaitostunnus AND P.opiskelijaAvain = PUU.opiskelijaAvain AND P.opiskeluoikeusAvain = PUU.opiskeluoikeusAvain
JOIN Lasnaolo55 L ON L.oppilaitostunnus = P.oppilaitostunnus AND L.opiskelijaAvain = P.opiskelijaAvain AND L.opiskeluoikeusAvain = P.opiskeluoikeusAvain
WHERE PUU.oppilaitos = @db
AND P.vuosi = @vuosi
AND PUU.vuosi = cast(@vuosi AS int)+1
AND L.vuosi = @vuosi
AND L.uusi_opisk_kevat = 1
UPDATE P
SET P.pankki_kumu_ennen_55 = (
( SELECT TOP 1 COALESCE(VV.uutta_pankkiin,0) FROM Pankki55 VV WHERE VV.vuosi = P2.vuosi AND VV.oppilaitos = P2.oppilaitos AND VV.opiskelijaAvain = P2.opiskelijaAvain AND VV.opiskeluoikeusAvain = P2.opiskeluoikeusAvain )
+ ( SELECT TOP 1 COALESCE(VV2.pankki_kumu_ennen_55,0) FROM Pankki55 VV2 WHERE VV2.vuosi = P2.vuosi AND VV2.oppilaitos = P2.oppilaitos AND VV2.opiskelijaAvain = P2.opiskelijaAvain AND VV2.opiskeluoikeusAvain = P2.opiskeluoikeusAvain )
)
FROM Pankki55 P
JOIN Pisteet55 P2 ON P2.oppilaitostunnus = P.oppilaitostunnus AND P2.opiskelijaAvain = P.opiskelijaAvain AND P.opiskeluoikeusAvain = P2.opiskeluoikeusAvain
WHERE P.oppilaitos = @db
AND P.vuosi = @vuosi
AND P2.vuosi = cast(cast(@vuosi as int)-1 as varchar)
UPDATE P
SET P.pankki_saldo_55 = (P.pankki_kumu_ennen_55 - (55 - (P2.summa) ))
, P.op_summa_kun_ote_pankista = COALESCE(P2.summa,0) + (55 - (COALESCE(P2.summa,0)) )
, P.suorittanut_55_pankin_avulla = 1
, P.suorittanut_55_ilman_pankkia = 0
FROM Pankki55 P
JOIN Pisteet55 P2 ON P2.oppilaitostunnus = P.oppilaitostunnus AND P2.opiskelijaAvain = P.opiskelijaAvain AND P.opiskeluoikeusAvain = P2.opiskeluoikeusAvain
WHERE P.oppilaitos = @db
AND P.vuosi = @vuosi
AND P2.vuosi = @vuosi
AND P2.summa < 55
AND P2.summa > 0
AND P.pankki_kumu_ennen_55 >= (55 - (P2.summa))
UPDATE PUU
SET PUU.pankki_kumu_ennen_55 = P.pankki_saldo_55
FROM Pankki55 PUU
JOIN Pankki55 P ON P.oppilaitostunnus = PUU.oppilaitostunnus AND P.opiskelijaAvain = PUU.opiskelijaAvain AND P.opiskeluoikeusAvain = PUU.opiskeluoikeusAvain
WHERE PUU.oppilaitos = @db
AND P.vuosi = @vuosi
AND PUU.vuosi = cast(@vuosi AS int)+1
UPDATE P
SET P.suorittanut_55_ilman_pankkia = 1
, P.suorittanut_55_pankin_avulla = 0
, P.pankki_saldo_55 = P.uutta_pankkiin + P.pankki_kumu_ennen_55
FROM Pankki55 P
JOIN Pisteet55 P2 ON P2.oppilaitostunnus = P.oppilaitostunnus AND P2.opiskelijaAvain = P.opiskelijaAvain AND P.opiskeluoikeusAvain = P2.opiskeluoikeusAvain
WHERE P.oppilaitos = @db
AND P.vuosi = @vuosi
AND P2.vuosi = @vuosi
AND P2.summa >= 55
UPDATE PUU
SET PUU.pankki_kumu_ennen_55 = P.pankki_saldo_55
FROM Pankki55 PUU
JOIN Pankki55 P ON P.oppilaitostunnus = PUU.oppilaitostunnus AND P.opiskelijaAvain = PUU.opiskelijaAvain AND P.opiskeluoikeusAvain = PUU.opiskeluoikeusAvain
WHERE PUU.oppilaitos = @db
AND P.vuosi = @vuosi
AND PUU.vuosi = cast(@vuosi AS int)+1
DECLARE @vuosi varchar(4) = @vuosi_para
DECLARE @db varchar(20) = @korkeakoulu
DELETE FROM [Pankki55] WHERE oppilaitos = @korkeakoulu AND vuosi = @vuosi
INSERT INTO [Pankki55] ([oppilaitos],[oppilaitostunnus],[hetu],[opiskelijaAvain],[opiskeluoikeusAvain],[vuosi], uutta_pankkiin)
SELECT DISTINCT [oppilaitos],[oppilaitostunnus],[hetu],[opiskelijaAvain],[opiskeluoikeusAvain],[vuosi] , CASE WHEN summa >= 55 THEN summa -55 ELSE 0 END AS uutta_pankkiin
FROM [Pisteet55] WHERE oppilaitos = @korkeakoulu AND vuosi = @vuosi
UPDATE P
SET P.uutta_pankkiin = (P2.summa - 27)
, P.suorittanut_27 = 1
, P.pankki_saldo_55 = (P2.summa - 27)
FROM Pankki55 P
JOIN Pisteet55 P2 ON P2.oppilaitostunnus = P.oppilaitostunnus AND P2.opiskelijaAvain = P.opiskelijaAvain
JOIN Lasnaolo55 L ON L.oppilaitostunnus = P.oppilaitostunnus AND L.opiskelijaAvain = P.opiskelijaAvain
WHERE P.oppilaitos = @db
AND P.vuosi = @vuosi
AND P2.vuosi = @vuosi
AND L.vuosi = @vuosi
AND L.uusi_opisk_kevat = 1
AND P2.summa >= 27
UPDATE PUU
SET PUU.pankki_kumu_ennen_55 = P.pankki_saldo_55
FROM Pankki55 PUU
JOIN Pankki55 P ON P.oppilaitostunnus = PUU.oppilaitostunnus AND P.opiskelijaAvain = PUU.opiskelijaAvain
JOIN Lasnaolo55 L ON L.oppilaitostunnus = P.oppilaitostunnus AND L.opiskelijaAvain = P.opiskelijaAvain
WHERE PUU.oppilaitos = @db
AND P.vuosi = @vuosi
AND PUU.vuosi = cast(@vuosi AS int)+1
AND L.vuosi = @vuosi
AND L.uusi_opisk_kevat = 1
UPDATE P
SET P.pankki_kumu_ennen_55 = (
( SELECT TOP 1 COALESCE(VV.uutta_pankkiin,0) FROM Pankki55 VV WHERE VV.vuosi = P2.vuosi AND VV.oppilaitos = P2.oppilaitos AND VV.opiskelijaAvain = P2.opiskelijaAvain )
+ ( SELECT TOP 1 COALESCE(VV2.pankki_kumu_ennen_55,0) FROM Pankki55 VV2 WHERE VV2.vuosi = P2.vuosi AND VV2.oppilaitos = P2.oppilaitos AND VV2.opiskelijaAvain = P2.opiskelijaAvain )
)
FROM Pankki55 P
JOIN Pisteet55 P2 ON P2.oppilaitostunnus = P.oppilaitostunnus AND P2.opiskelijaAvain = P.opiskelijaAvain
WHERE P.oppilaitos = @db
AND P.vuosi = @vuosi
AND P2.vuosi = cast(cast(@vuosi as int)-1 as varchar)
UPDATE P
SET P.pankki_saldo_55 = (P.pankki_kumu_ennen_55 - (55 - (P2.summa) ))
, P.op_summa_kun_ote_pankista = COALESCE(P2.summa,0) + (55 - (COALESCE(P2.summa,0)) )
, P.suorittanut_55_pankin_avulla = 1
, P.suorittanut_55_ilman_pankkia = 0
FROM Pankki55 P
JOIN Pisteet55 P2 ON P2.oppilaitostunnus = P.oppilaitostunnus AND P2.opiskelijaAvain = P.opiskelijaAvain
WHERE P.oppilaitos = @db
AND P.vuosi = @vuosi
AND P2.vuosi = @vuosi
AND P2.summa < 55
AND P2.summa > 0
AND P.pankki_kumu_ennen_55 >= (55 - (P2.summa))
UPDATE PUU
SET PUU.pankki_kumu_ennen_55 = P.pankki_saldo_55
FROM Pankki55 PUU
JOIN Pankki55 P ON P.oppilaitostunnus = PUU.oppilaitostunnus AND P.opiskelijaAvain = PUU.opiskelijaAvain
WHERE PUU.oppilaitos = @db
AND P.vuosi = @vuosi
AND PUU.vuosi = cast(@vuosi AS int)+1
UPDATE P
SET P.suorittanut_55_ilman_pankkia = 1
, P.suorittanut_55_pankin_avulla = 0
, P.pankki_saldo_55 = P.uutta_pankkiin + P.pankki_kumu_ennen_55
FROM Pankki55 P
JOIN Pisteet55 P2 ON P2.oppilaitostunnus = P.oppilaitostunnus AND P2.opiskelijaAvain = P.opiskelijaAvain
WHERE P.oppilaitos = @db
AND P.vuosi = @vuosi
AND P2.vuosi = @vuosi
AND P2.summa >= 55
UPDATE PUU
SET PUU.pankki_kumu_ennen_55 = P.pankki_saldo_55
FROM Pankki55 PUU
JOIN Pankki55 P ON P.oppilaitostunnus = PUU.oppilaitostunnus AND P.opiskelijaAvain = PUU.opiskelijaAvain
WHERE PUU.oppilaitos = @db
AND P.vuosi = @vuosi
AND PUU.vuosi = cast(@vuosi AS int)+1
Alkuun
Perusjoukko Opiskelijat
CREATE TABLE [Opiskelija55](
[oppilaitos] [varchar](20) NOT NULL,
[oppilaitostunnus] [varchar](10) NOT NULL,
[hetu] [varchar](11) NOT NULL,
[opiskelijaAvain] [varchar](100) NOT NULL,
[Sukupuoli] [smallint] NULL,
[reg_datum] [smalldatetime] NULL CONSTRAINT [DF_Opiskelija55_reg_datum] DEFAULT (getdate()),
CONSTRAINT [PK_Opiskelija55] PRIMARY KEY CLUSTERED
(
[oppilaitostunnus] ASC,
[hetu] ASC,
[opiskelijaAvain] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
Kerätään yhteen ylläoleviin poimitut. Lisätiedoksi vielä opiskelijan sukupuoli.
DELETE FROM [Opiskelija55] WHERE oppilaitos = @kk
INSERT INTO [Opiskelija55] ([oppilaitos],[oppilaitostunnus],[hetu],[opiskelijaAvain],[Sukupuoli])
SELECT DISTINCT [oppilaitos],[oppilaitostunnus],[hetu],[opiskelijaAvain], S.Koodi AS sukupuoli
FROM Lasnaolo55 L
JOIN Opiskelija O ON O.Avain = L.opiskelijaAvain
JOIN Henkilo H ON O.henkiloid = H.id
JOIN Sukupuoli S ON H.SukupuoliID = S.id
WHERE L.oppilaitos = @kk
AND O.db = @kk
AND H.db = @kk
AND O.Avain NOT IN (SELECT OO.opiskelijaAvain FROM Opiskelija55 OO WHERE OO.oppilaitos = @kk )
DELETE FROM [Opiskelija55] WHERE oppilaitos = @kk
INSERT INTO [Opiskelija55] ([oppilaitos],[oppilaitostunnus],[hetu],[opiskelijaAvain],[Sukupuoli])
SELECT DISTINCT [oppilaitos],[oppilaitostunnus],[hetu],[opiskelijaAvain], S.Koodi AS sukupuoli
FROM Lasnaolo55 L
JOIN Opiskelija O ON O.Avain = L.opiskelijaAvain
JOIN Henkilo H ON O.henkiloid = H.id
JOIN Sukupuoli S ON H.SukupuoliID = S.id
WHERE L.oppilaitos = @kk
AND O.db = @kk
AND H.db = @kk
AND O.Avain NOT IN (SELECT OO.opiskelijaAvain FROM Opiskelija55 OO WHERE OO.oppilaitos = @kk )
Alkuun
Esimerkkidataa per perusjoukko (taulu) ja pankkilaskelma opiskelijan aikajanana
| Pisteet55 |
| opiskelijaAvain | opiskeluoikeusAvain
| vuosi
| edellinen_lv_syys_pisteet | edellinen_lv_kevat_pisteet | summa |
|---|
| 123456789 | 334444 | 2012 | 0 | 30 | 30 |
| 123456789 | 334444 | 2013 | 26 | 41 | 67 |
| 123456789 | 334444 | 2014 | 34 | 24 | 58 |
| 123456789 | 334466 | 2015 | 18 | 30 | 48 |
| 123456789 | 334466 | 2016 | 33 | 5 | 38 |
Keltainen väri: Opiskelija ensimmäisenä keväänä (2012) suorittanut 30 op, josta 3 op yli 27 op kertyy pankkiin seuraavalle vuodelle (2013). Seuraavalle vuodelle (2013) kumulativinen pankkisaldo 3.
Sininen väri: Seuraavana vuonna (2013) edellisen lukuvuoden (2012-13) opintopisteet 67 op, eli opiskelija on suorittanut >= 55 op, kertyy 12 op uusia pisteitä pankkiin. Ei tarvita pankkia. Suorittanut_55_ilman_pankkia = 1. Kumulativisena saldona seuraavalle vuodelle (2014) on nyt 15.
Vihreä väri: Seuraavana vuonna (2014) (2013-14) suorittanut 58 op. Ei tarvita vieläkään pankkia. Suorittanut_55_ilman_pankkia =1. Tulee 3 op uutta pistettä pankkin. Kumulatiivinen saldo seuraavalle (2015) on 18 op.
Punainen väri: Seuraavana (2015) edelliseltä (2014-15) kertynyt nyt vain 48 op, eli on alle 55 op ja nyt otetaan pankista jotta saadaan 55 op ja saldoksi jää 11. Suorittanut_55_pankin_avulla = 1
Alkuun
| Pankki55 |
| opiskelijaAvain | opiskeluoikeusAvain
| vuosi
| uutta_pankkiin
| pankki_kumu_ennen_55
| op_summa_kun_ote_pankista | pankki_saldo_55 | suorittanut_27 | suorittanut_55_ilman_pankkia | suorittanut_55_pankin_avulla |
|---|
| 123456789 | 334444 | 2012 | 3 | 0 | 0 | 3 | 1 | 0 | 0 |
| 123456789 | 334444 | 2013 | 12 | 3 | 0 | 15 | 0 | 1 | 0 |
| 123456789 | 334466 | 2014 | 3 | 15 | 0 | 18 | 0 | 1 | 0 |
| 123456789 | 334466 | 2015 | 0 | 18 | 55 | 11 | 0 | 0 | 1 |
| Opiskeluoikeus55 |
| opiskelijaAvain | opiskeluoikeusAvain | vuosi
| tila
| lo_aloituspvm
| oo_aloituspvm
| Tkkoodi |
|---|
| 123456789 |
| 2011 |
| 1.1.2012 |
|
|
| 123456789 | 334444 | 2012 | 1 | 1.1.2012 | 1.8.2011 | 655502 |
| 123456789 | 334444 | 2013 | 1 | 1.8.2012 | 1.8.2012 | 655502 |
| 123456789 | 334466 | 2014 | 1 | 1.8.2012 | 1.8.2012 | 751502 |
| 123456789 | 334466 | 2015 | 1 | 1.8.2012 | 1.8.2012 | 751502 |
| Lasnaolo55 |
| opiskelijaAvain | opiskeluoikeusAvain
| vuosi | syys_olo | kevat_olo | edellinen_syys_olo | uusi_opisk_kevat
| uusi_opisk |
|---|
| 123456789 |
| 2011 | 2 | 0 | 0 | 0 | 0 |
| 123456789 | 334444 | 2012 | 1 | 1 | 2 | 1 | 1 |
| 123456789 | 334444 | 2013 | 1 | 1 | 1 | 0 | 0 |
| 123456789 | 334466 | 2014 | 1 | 1 | 1 | 0 | 0 |
| 123456789 | 334466 | 2015 | 1 | 1 | 1 | 0 | 0 |
Alkuun
Lyhyt kuvaus laskentaprosessista.
- Lasketaan pisteet 2006 - 2016
- Haetaan läsnäolotiedot 2006 - 2016
- Haetaan opiskeluoikeustiedot 2006 - 2016
- Poimitaan keväällä aloittaneet
- Poimitaan kaikki tilastovuoden uudet opiskelijat
- Alustetaan pankkitiedot
- Lasketaan kevällä aloittaneiden >= 27 op keränneet
- Lasketaan kumulatiivinen pankkitili
- Pankkisaldot kumulatiivisen laskennan jälkeen
- Kerätään yhteen opiskelijat
- Loppuraportti ylläolevasta kerätystä per tilastovuosi
Taulu jonne tulokset kerätään
CREATE TABLE [ViisViisLaskentaTaulukko](
[oppilaitos] [varchar](20) NULL,
[oppilaitostunnus] [varchar](10) NULL,
[vuosi] [int] NULL,
[hetu] [varchar](11) NULL,
[olok] [smallint] NULL,
[olos] [smallint] NULL,
[syys] [int] NULL,
[kevat] [int] NULL,
[summa] [int] NULL,
[tkoodi] [varchar](6) NULL,
[lo_aloituspvm] [date] NULL,
[oo_aloituspvm] [date] NULL,
[opiskeluoikeus_id] [int] NULL,
[uutta_pankkiin] [int] NULL,
[pankki_kumu_ennen_55] [int] NULL,
[op_summa_kun_ote_pankista] [int] NULL,
[pankki_saldo_55] [int] NULL,
[suorittanut_27] [smallint] NULL,
[suorittanut_55_ilman_pankkia] [smallint] NULL,
[suorittanut_55_pankin_avulla] [smallint] NULL,
[uusi_opisk_kevat] [smallint] NULL,
[uusi_opisk] [smallint] NULL,
[opiskelijaAvain] [varchar](100) NULL,
[opiskeluoikeusAvain] [varchar](100) NULL,
[sukupuoli] [smallint] NULL,
[edellinen_syysolo] [smallint] NULL,
[reg_datum] [smalldatetime] NULL CONSTRAINT [DF_ViisViisLaskentaTaulukko_reg_datum] DEFAULT (getdate())
)
Poimitaan tiedot korkeakouluittain (@korkeakoulu):
AMK:
DELETE FROM ViisViisLaskentaTaulukko WHERE oppilaitos = @korkeakoulu
INSERT INTO [dbo].[ViisViisLaskentaTaulukko]
([oppilaitos]
,[oppilaitostunnus]
,[vuosi]
,[hetu]
,[olok]
,[olos]
,[syys]
,[kevat]
,[summa]
,[tkoodi]
,[lo_aloituspvm]
,[oo_aloituspvm]
,[uutta_pankkiin]
,[pankki_kumu_ennen_55]
,[op_summa_kun_ote_pankista]
,[pankki_saldo_55]
,[suorittanut_27]
,[suorittanut_55_ilman_pankkia]
,[suorittanut_55_pankin_avulla]
,[uusi_opisk_kevat]
,[uusi_opisk]
,[opiskelijaAvain]
,[opiskeluoikeusAvain]
,[sukupuoli]
,[edellinen_syysolo] )
SELECT DISTINCT O.[oppilaitos]
, O.[oppilaitostunnus]
, OK.[vuosi]
, O.hetu
, L.[kevat_olo]
, L.[syys_olo]
, P.[edellinen_lv_syys_pisteet]
, P.[edellinen_lv_kevat_pisteet]
, P.[summa]
, OK.[tkkoodi]
, OK.[lo_aloituspvm]
, OK.[oo_aloituspvm]
, PK.[uutta_pankkiin]
, PK.[pankki_kumu_ennen_55]
, PK.[op_summa_kun_ote_pankista]
, PK.[pankki_saldo_55]
, PK.[suorittanut_27]
, PK.[suorittanut_55_ilman_pankkia]
, PK.[suorittanut_55_pankin_avulla]
, L.[uusi_opisk_kevat]
, L.[uusi_opisk]
, O.[opiskelijaAvain]
, Ok.opiskeluoikeusAvain
, O.[sukupuoli]
, L.[edellinen_syys_olo]
FROM [Opiskelija55] O
INNER JOIN Opiskeluoikeus55 OK ON OK.opiskelijaAvain = O.opiskelijaAvain
AND OK.oppilaitostunnus = O.oppilaitostunnus
LEFT JOIN Lasnaolo55 L ON L.vuosi = OK.vuosi
AND L.oppilaitostunnus = OK.oppilaitostunnus
AND L.opiskelijaAvain = OK.opiskelijaAvain
AND L.opiskeluoikeusAvain = OK.opiskeluoikeusAvain
LEFT JOIN Pisteet55 P ON P.vuosi = L.vuosi
AND P.oppilaitostunnus = L.oppilaitostunnus
AND P.opiskelijaAvain = L.opiskelijaAvain
AND P.opiskeluoikeusAvain = L.opiskeluoikeusAvain
LEFT JOIN Pankki55 PK ON PK.vuosi = P.vuosi
AND PK.oppilaitostunnus = P.oppilaitostunnus
AND PK.opiskelijaAvain = P.opiskelijaAvain
AND PK.opiskeluoikeusAvain = P.opiskeluoikeusAvain
WHERE O.oppilaitos = @korkeakoulu
AND OK.tila = 1
Alkuun
YO:
DELETE FROM ViisViisLaskentaTaulukko WHERE oppilaitos = @korkeakoulu
INSERT INTO [dbo].[ViisViisLaskentaTaulukko]
([oppilaitos]
,[oppilaitostunnus]
,[vuosi]
,[hetu]
,[olok]
,[olos]
,[syys]
,[kevat]
,[summa]
,[tkoodi]
,[lo_aloituspvm]
,[oo_aloituspvm]
,[uutta_pankkiin]
,[pankki_kumu_ennen_55]
,[op_summa_kun_ote_pankista]
,[pankki_saldo_55]
,[suorittanut_27]
,[suorittanut_55_ilman_pankkia]
,[suorittanut_55_pankin_avulla]
,[uusi_opisk_kevat]
,[uusi_opisk]
,[opiskelijaAvain]
,[opiskeluoikeusAvain]
,[sukupuoli]
,[edellinen_syysolo] )
SELECT DISTINCT O.[oppilaitos]
, O.[oppilaitostunnus]
, OK.[vuosi]
, O.hetu
, L.[kevat_olo]
, L.[syys_olo]
, P.[edellinen_lv_syys_pisteet]
, P.[edellinen_lv_kevat_pisteet]
, P.[summa]
, OK.[tkkoodi]
, OK.[lo_aloituspvm]
, OK.[oo_aloituspvm]
, PK.[uutta_pankkiin]
, PK.[pankki_kumu_ennen_55]
, PK.[op_summa_kun_ote_pankista]
, PK.[pankki_saldo_55]
, PK.[suorittanut_27]
, PK.[suorittanut_55_ilman_pankkia]
, PK.[suorittanut_55_pankin_avulla]
, L.[uusi_opisk_kevat]
, L.[uusi_opisk]
, O.[opiskelijaAvain]
, Ok.opiskeluoikeusAvain
, O.[sukupuoli]
, L.[edellinen_syys_olo]
FROM [Opiskelija55] O
INNER JOIN Opiskeluoikeus55 OK ON OK.opiskelijaAvain = O.opiskelijaAvain
AND OK.oppilaitostunnus = O.oppilaitostunnus
LEFT JOIN Lasnaolo55 L ON L.vuosi = OK.vuosi
AND L.oppilaitostunnus = OK.oppilaitostunnus
AND L.opiskelijaAvain = OK.opiskelijaAvain
--AND L.opiskeluoikeusAvain = OK.opiskeluoikeusAvain
LEFT JOIN Pisteet55 P ON P.vuosi = L.vuosi
AND P.oppilaitostunnus = L.oppilaitostunnus
AND P.opiskelijaAvain = L.opiskelijaAvain
--AND P.opiskeluoikeusAvain = L.opiskeluoikeusAvain
LEFT JOIN Pankki55 PK ON PK.vuosi = P.vuosi
AND PK.oppilaitostunnus = P.oppilaitostunnus
AND PK.opiskelijaAvain = P.opiskelijaAvain
--AND PK.opiskeluoikeusAvain = P.opiskeluoikeusAvain
WHERE O.oppilaitos = @korkeakoulu
AND OK.tila = 1
Alkuun
Lopputulostiedoista muodostetaan korkeakoulukohtaiset tarkistustiedostot VirtaSFTP-palvelimelle.
Tarkistustiedostot
Tiedot/sarakkeet csv-tiedostossa:
oppilaitos;oppilaitostunnus;vuosi;hetu;sukupuoli;edellinen_syysolo;olok;olos;syys;kevat;summa;uutta_pankkiin;pankki_kumu_ennen_55;op_summa_kun_ote_pankista;pankki_saldo_55;suorittanut_27;suorittanut_55_ilman_pankkia;suorittanut_55_pankin_avulla;uusi_opisk_kevat;uusi_opisk;tkoodi;lo_aloituspvm;oo_aloituspvm;opiskelijaAvain;opiskeluoikeusAvain
oppilaitos : Lyhenne, korkeakoulun tunniste Virrassa
oppilaitostunnus : Organisaatiokoodi (5 numeroinen)
vuosi : Tilastovuosi
hetu : Opiskelijan henkilötunnus tai syntymäaika
sukupuoli : Opiskelijan sukupuoli (1=mies, 2=nainen)
edellinen_syysolo : Tilastovuodesta katsottuna edellisen syyslukukauden tieto 1=läsnä, 2=poissa, 0=ei kirjoilla/tieto puuttuu
olok : kevätlukukauden tieto 1=läsnä, 2=poissa, 0=ei kirjoilla/tieto puuttuu
olos : tilastovuoden syyslukukauden tieto 1=läsnä, 2=poissa, 0=ei kirjoilla/tieto puuttuu
syys : edellisen syyslukukauden opintopisteet (kokonaisluku) 1.8-31.12.tilastovuosi-1
kevat : edellisen kevätlukukauden opintopisteet (kokonaisluku) 1.1-31.7.tilastovuosi
summa : syys + kevat pisteet yhteensä 1.8.tilastovuosi-1 - 31.7.tilastovuosi
uutta_pankkiin : tilastovuonna pankkiin seuraavalle vuodelle
pankki_kumu_ennen_55 : pankin kumulatiivinen kertymä ennen 55 laskentaa tilastovuonna
op_summa_kun_ote_pankista : opintopistesumma kun on käytetty pankkia
pankki_saldo_55 : pankkisaldo
suorittanut_27 : Suorittanut >= 27 op
suorittanut_55_ilman_pankkia : Suorittanut >= 55 op ilman pankkia
suorittanut_55_pankin_avulla : Suorittanut 55 op pankin avulla
uusi_opisk_kevat : Keväällä aloittanut uusi opiskelija
uusi_opisk : Vuoden uusi opiskelija
tkoodi : Tilastokeskuksen 6-numeroinen koulutuskoodi
lo_aloituspvm : Ensimmäinen läsnäolopäivämäärä. Laskettu ensimmäisestä läsnäolotiedosta tarvitaan uusi_opisk -tietoa päätellessä
oo_aloituspvm : Opiskeluoikeuden alkamispäivämäärä. Tilastovuoden edellisen lukuvuoden aktiivinen opiskeluoikeus
opiskelijaAvain : Opiskelijan yksilöivä avain
opiskeluoikeusAvain : Opiskeluoikeuden yksilöivä avain
Seuraavat tiedot ko. opiskeluoikeuden ja vuoden osalta viimeisimmästä vuoden aikana edes päivän voimassa olleesta opiskeluoikeusjaksosta
rahoituslahde : Rahoituslähteen koodi
koulutuskunta : Koulutuskunnan koodi
kieli : Opetuskielen koodi
kansalaisuus : Kansalaisuuden koodi
Alkuun
csv-tiedostoja voi Excelissä esim. COUNTIF() tai SUMIF() funktioita käyttämällä tehdä tarkistuslaskelmia, opiskelijakohtaisesti tarkistaa tietoja opiskelijaAvain, opiskeluoikeusAvaimien tai hetun mukaan.
Esimerkkitapauksia
Esim1. Suorittanut 55 pankin avulla
Opiskelijalla on suorittanut edellisellä lukuvuodella 50 op ja pankissa on 5 op.
summa = 50 (edellisen lukuvuoden pisteet)
pankki_kumu_ennen_55 = 5 (pankkiin kertyneet op aiemilta vuosilta)
op_summa_kun_ote_pankista = 55
pankki_saldo_55 = 0 (saldo jäljellä kun käytetty pankkia)
=> suorittanut_55_pankin_avulla = 1
Esim2. Pankki tyhjenee jos pisteet eivät riitä saavuttamaan 55 op
summa = 35 (edellisen lukuvuoden pisteet)
pankki_kumu_ennen_55 = 5 (pankkiin kertyneet aiemilta vuosilta)
pankki_saldo_55 = 0 (saldo sen jälkeen kun käytetty pankkia)
=> suorittanut_55_ilman_pankkia=0
=> suorittanut_55_pankin_avulla=0
Esim3. Suorittanut 55 op ilman pankkia
Opiskelijalle kertynyt yli 55 op.
summa = 59 (edellisen lukuvuoden pisteet)
uutta_pankkiin = 4
pankki_kumu_ennen_55 = 5 (pankkiin kertyneet op aiemilta vuosilta)
pankki_saldo_55 = 9 (uutta_pankkiin + pankki_kumu_ennen_55)
=> suorittanut_55_ilman_pankkia = 1
Esim4. Keväällä aloittanut suorittanut 27 op
summa = 30 (edellisen lukuvuoden pisteet, eli ainoastaan keväältä)
uutta_pankkiin = 3
pankki_saldo_55 = 3
=> suorittanut_27 = 1
Alkuun
1. Vähintään 55 opintopistettä suorittaneiden määrä (summa mittareista 3., 4. ja 5.).
2. Vähintään 55 opintopistettä suorittaneiden määrä, rahoitusmalli (sama kuin 1., paitsi että keväällä aloittaneiden määrä jaettuna kahdella)
3. Keväällä aloittaneista vähintään 27 op:ta suorittaneet
4. Suorittanut 55 op ilman pankkia
5. Suorittanut 55 op pankin kanssa
6. Läsnäolleiden määrä (läsnäolotieto 1 joko syksyllä tai keväällä (esim. s2014 tai k2015)).
7. 55 op suorittaneiden osuus (mittari 1. jaettuna mittarilla 6.)
Raportin muuttujat:
a. lukuvuosi, esim 2014/2015
b. korkeakoulu
c. tutkintokoodi ja siitä johdettuna OKM:n ohjauksen alat ja ISCED-luokitus
d. sukupuoli
e. aika aloittamisesta (tilastolukuvuosi miinus aloittamislukuvuosi, asteikko 0,1...6,7 ja 8 tai enemmän)
f. uutena keväällä aloittanut
Alkuun
Kysymykset ja vastaukset
Perusjoukon määrittely
- Sisällytetäänkö laskentaan myös lukuvuoden aikana eronneet (esim. toiseen korkeakouluun siirtyneet)?
Vastaus: Kyllä. Kaikki opiskelijat jotka ovat kyseisessä korkeakoulussa keränneet opintopisteitä lasketaan mukaan perusjoukkoon ja mikäli ovat myös >= 55 op suorittaneet tulevat myös mukaan 55 suorittaneiden joukkoon.
- Kun opintopisteiden perusjoukon poiminnassa (ja muutamassa muussakin kohdassa) lukee: ”opiskelijoille joille tälle ajanjaksolle voimassa aktiivinen opiskeluoikeus tyyppiä 2, 4”, tarkoittaako se, että:
a. opintopisteet huomioidaan aikaväliltä 1.8.VUOSI-1 – 31.7.VUOSI, kunhan opiskelijalla on ollut tuolla aikavälillä ainakin yhden päivän voimassa em. ehdot täyttävä opiskeluoikeus, vai että:
b. opintopisteet poimitaan aikavälin 1.8.VUOSI-1 – 31.7.VUOSI niiltä osaväleiltä, joina opiskelijalla on ollut voimassa em. ehdot täyttävä opiskeluoikeus, vai sittenkin että:
c. opiskelijalla pitää olla ollut 20.9.VUOSI (lukuvuotta seuraavana syksynä 20.9.) voimassa em. ehdot täyttävä opiskeluoikeus? (opinto-oikeuden voimassa ololla välillä 1.8.VUOSI-1 – 31.7.VUOSI ei ole merkitystä.)
Vastaus:
a. Ei lasketa päiviä. Eli riittäsi yksikin päivä.
b. Kyllä. Tilastovuosi yhdistää osat. Pisteet edelliseltä lukuvuodelta tilastovuoden perspektiivistä, läsnäolotiedot ja voimassa ollut tutkinnon opiskeluoikeus edellisellä lukuvuonna.
c. Ei. Mutta edellisellä, 1.8.VUOSI-1 - 31.7.VUOSI pitää olla oikeus voimassa 20.9.VUOSI-1.
Hyväksiluetut suoritukset
- Lasketaanko hyväksiluvut mukaan kirjauspäivän mukaan vaikka se olisi suoritettu ennen opintojen alkua?
Vastaus: Jos kirjauspäivällä tarkoitetaan samaa kuin suoritukselle rekistöröity päivämäärä (hyväksilukupäivä tai muuten suorituspäivä) niin kyllä. Kaikki hyväksiluetut pisteet lasketetaan mukaan vaikka on suoritettu ennen opintojen alkua.
Esim. Siirtoopiskelija joka tuo toiseen korkeakouluun mukanaan 100 op jotka hyväksiluetaan, tästä kertyy 45 p pankkiin seuraavalle vuodelle uudessa korkeakoulussa.
Pankki
- Esimerkissä opiskelija oli ensimmäisen lukukautensa (syksy) poissa, sitä seuraavan kevään läsnä. Tästä seurasi, että opiskelija katsottiin keväällä aloittaneeksi, ja opintopisteiden tavoiteraja oli 27 op. Opinto-oikeuden keskelle (opiskelun aloittamisen ja opinto-oikeuden päättymisen väliin osuvia) poissaoloja ei ilmeisesti huomioida laskennassa mitenkään? Eli, jos opiskelija on vaikkapa ensimmäisen lukuvuotensa (syksy + kevät) läsnä, ja sitä seuraavan kokonaisen lukuvuoden (syksy + kevät) kokonaan poissa, niin tavoiteraja jälkimmäisen lukuvuoden osalta on edelleen 55 op (eikä 0 op.)?
Vastaus: Tällä hetkellä pisteet lasketaan joka tapauksessa. Eli jos opiskelijalla on ollut välivuosi kesken opintojen niin siltä ajalta on kertynyt 0 pistettä ja pankki myös tyhjenee silloin mikäli nyt ei ollut kertynyt reilusti pisteitä pankkin edellisiltä vuosilta.
- Case jossa opiskelijan ensimmäinen opinto-oikeus (kandi tai maisteri) on alkanut vaikkapa 1.8.1980 (ollut läsnä syksyllä 1980), ja hän on siihen liittyen valmistunut taiteen maisteriksi v. 1992. Opiskelija on sittemmin päässyt suorittamaan tutkintoa, uusi opinto-oikeus on alkanut 1.8.2005, ja hän on ollut ensimmäisen syyslukukauden (syksy 2005) poissa, sitä seuraavan kevään (kevät 2006) läsnä: Casen opiskelijaa ei ilmeisesti katsota keväällä 2006 aloittaneeksi. – Vai katsotaanko? Ts. alkaako pankki kertyä vuodesta 1981 (opintopisteet 1.8.1980 – 31.7.1981), vai vuodesta 2006 (opintopisteet 1.8.2005 – 31.7.2006)?
Vastaus: Noin pitkälle historiaan kuin 80-luvulta asti ei ole poimittu. Toistaiseksi tehdyissä testilaskennoissa on vuodesta 2006 alkaen aloitettu laskennat. Siitä ajasta jolta korkeakouluille vielä mahdollista kaikkien osalta saada poimittua dataa läsnäolo- ja pistelaskentoja ynm. varten ja saada tulos myös valmiiksi laskettua vuorokauden aikana. Tässä esimerkissä olisi siis tullut vuodesta 2006 aloittanut, siltä väliltä löytynyt opiskeluoikeusjakso. 1980 vuonna alkanut oikeus, läsnäolot tai pisteet eivät olisi mukana.
- Miten 55.0 opintopistelaskenta toimii siinä tapauksessa, että opiskelija on ulkomailla vaihdossa ja on ilmoittautunut siksi aikaa poissaolevaksi? Tyhjeneekö pankkitili? (ei varmaan saisi tyhjentyä). Pitäisikö näitä tapauksia käsitellä eri tavalla?
Vastaus: Itse 55 pistelaskenta ei ota kantaa onko opiskelija läsnä tai poissa. Pisteet lasketaan joka tapauksessa jos on voimassa oleva opiskeluoikeus. Jos opiskelija ei ole kerännyt 55 op edellisenä lukuvuonna ja tilillä ei riitä katetta niin se tyhjenee.
Yksi mittareista on toki jossa osuus 55 läsnaolevista, mutta se on vielä ehdotus. Muissa mittareissa lasketaan yhteen 55, 27 op olipa opiskelija läsna tai ei.
Raportti ja mittarit
55 op raportista https://extra.vipunen.fi/opintotiedot/Sivut/55-op.aspx
- Väh. 55 op suorittaneet: onko tämä sarake se mikä tulisi 55 op suorittaneiden osuudeksi uudella laskentatavalla?
Vastaus:
Kyllä ja ei.
1. Väh. 55 opintopistettä suorittaneet on summa mittareista 3, 4 ja 5 (3=27 op keväällä, 4= 55 op ilman pankkia, 5= 55 op pankin avulla)
2. Vähintään 55 opintopistettä suorittaneiden, rahoitusmalli (Sama kuin 1. paitsi että keväällä aloittaneiden 27 op (3.) määrä jaettuna kahdella).
- Onko tässä perusjoukkona syksyllä 14 kirjoilla olleet (eli mukana myös ne jotka valmistuivat esim. toukokuussa 2015)
Vastaus:
Perusjoukkona esim. lv 14-15 on syyslukukaudella 2014 ja/tai kevätlukukaudella 2015 kirjoilla olleet.
Eli jos on ollut syksyllä 2014 kirjoilla ja valmistunut esim. vuoden lopussa tai kevään 2015 puolella tulee mukaan.
- Väh. 55 op suorittaneet, rahoitusmalli: miten eroaa edellisestä? Meidän koulun osalta eroa edelliseen on -3 opiskelijaa. Mistä tuo ero käytännössä syntyy?
Vastaus:
Väh. 55 opintopistettä suorittaneet, rahoitusmalli" on sama kuin 1. paitsi että keväällä aloittaneiden 27 op (3.) määrä jaettuna kahdella.
- Keväällä aloittaneista väh. 27 op suorittaneet: tässä on siis esillä 2014-15, eli tarkoittaako tuo sellaisia keväällä 2015 opiskeluoikeuden saaneita jotka saivat tehdyksi vähintään 27 op ajalla 1.1 – 31.7.2015?
Vastaus:
Keväällä 2015 aloittaneet jotka ovat keränneet pisteitä 27 op tai enemmän 1.1 – 31.7.2015. Aloitustieto kevällä aloittanut ensimmäisen läsnäololukukauden mukaan.
Opiskeluoikeus on esim. alkanut viime syksynä 2014, mutta aloittanut opiskelut vasta keväällä 2015, eli kun ensimmästä kertaa läsnä opiskelemassa.
- Suorittaneet 55 op pankin kanssa: onko tässä sellaiset joilla 2014-15 kertymä ei ollut 55 op, mutta ylsivät siihen kun otettiin huomioon edellisten lukuvuosien kumulatiiviset saldot?
Vastaus:
Suorittanut 55 op ilman pankkia" ovat edellisenä lukuvuonna 2014-15 >= 55 op keränneet, joko ensimmäisen vuoden jälkeen, tai vanhempi opiskelija joka kerännyt 55 op ilman että tarvinnut käyttää pankkia.
Esim. 60 op kerännyt, tästä menee seuraavalle vuodelle 5 op pankkiin, jotka ensi vuonna tarvitaan mikäli on silloin vain 50 op, ja näiden 5 op avulla tuona vuonna pääsee ryhmään "Suorittanut 55 op pankin avulla
- Läsnäolleet: minkä ajankohdan läsnäolotilanne tämä on? Tilastointivuosi vai edellinen syksy?
Vastaus:
Tilastovuoteen nähden edellinen lukuvuosi. Eli tilastovuoden 2015 nähden edellinen syys 2014 ja/tai kevätlukukausi 2015 läsnäolleet
Vastaus:
Nyt raportissa on edellisenä lukuvuotena 2014-15 ”Väh. 55 op suorittaneet", syksyllä 2014 ja/tai kevällä 2015 läsnäolleiden osuudesta.
- Millä tavoin näissä luvuissa on käsitelty hyväksilukuja?
Onko mukaan laskettu kaikki sellaiset hyväksilukemiset joissa hyväksikukemispäivä ajoittuu aikavälille 1.8.2014 – 31.7.2015 ja suorituspäivä voi olla mikä tahansa päivä (vaikka 10 v vanha)?
Vastaus:
Hyväksilukupäivämäärän mukaan 1.8.2014 – 31.7.2015, suorituspäivä voi olla vaikka 10 v vanha.
Opintopisteiden päivämäärät on ennakkoon tallennettu omaan näkymään kaikista opintosuorituksista niin että laskennassa se päivämäärä jonka mukaan lasketaan tulee hyväksilukupäivämäärä jos sellainen on, muuten käytetään suorituspäivämäärää.
Tarkistustiedostot ja niiden hyödyntämien
Kysymys: Olisiko mahdollista tuottaa 55 op -tiedon (ja jatkossa päivittäiseen, ajankohtaiseen) tarkistukseen yhdistelmää tiedostoista "tarkistus_55_2016_OPPILAITOS.csv" ja "tktutk2016_OPPILAITOS.csv" ? Ensiksi mainitusta puuttuu oleellisia, tarkistusta nopeuttavia kenttiä kuten: Sukunimi, Etunimet, Opintopisteet yhteensä, Suoritettavien opintojen laajuus.
Vastaus:
Alkuun
10 Comments
Fredrik Finnberg
Lisää kysymyksiä...
Unknown User (virrahi@laurea.fi)
'Vähintään 55 opintopistettä suorittaneiden, rahoitusmalli', jaetaanko tässä myös vain syyslukukauden läsnäolleiden (valm. / eronnut joulukuussa) määrä kahdella?
Fredrik Finnberg
Ei. Vain kevään jaetaan kahdella.
Hanna Kosteila
Mikä merkitys on tilastovuoden syyskauden läsnäololla?
Fredrik Finnberg
Nyt syksyn 2016 osalta ei mitään muuta kuin tarkistusmielessä.
Vuosittaisissa "olos"-tiedoissa pitäisi olla vastaava määrä (läsnä ja poissa olevia) kuin sitä seuraavan vuoden "edellinen_syysolo" tiedoissa esiintyy.
Mutta esim. siirtoopiskelijat antavat oman mausteensa tähän.
Hanna Kosteila
ExtraVipusen raportilla sarakkeet loogisempaan järjestykseen, jolloin otsikotkin saa helpommin ymmärrettäviksi (esim. siten, että kertyvät vasemmalta oikealle ja viimeisenä lopullinen määrä).
Fredrik Finnberg
Kiitos palautteesta! Voidaan muuttaa järjestystä jos sillä saa helpommin ymmärrettävän raportin.
Kysessä on vasta ensimmäinen versio, ei mikään kiveen hakattu versio.
Helena Majamäki
Kaisu Piiroinen: Näissä tietovarannasta päivittyvissä tiedoissa periaate on se, että korjaus vaikuttaa rahoituslaskentaan sillä yhden vuoden tiedot ovat mukana rahoitusmallilaskennassa kolmen vuoden ajan (=laskettaessa kolmen vuoden keskiarvoa). Eli vuonna X tilanne lukitaan aineistovuoden Y osalta. Kun tiedot poimitaan uudelleen vuonna X+1, tarkistetaan onko vuoden Y tietoihin tullut päivityksiä ja otetaan ne (korjatut tiedot) mukaan vuonna X+1 ja X+2. Eli takautuvasti voi korjata tietoja, mutta ne eivät vaikuta valmiiseen laskentaan, mutta tulevat mukaan kahtena seuraavana vuonna.
Fredrik Finnberg
Läsnäolevien määrä 55 op raportilla ja csv-tarkistustiedostossa.
Kysymys HY: "Emme kuitenkaan saa määritelmällä täsmälleen samoja lukuja kuin mitä Vipusessa on. Läsnäolevien määrä on Vipusessa 2013-2016 joka vuodelle 2 opiskelijaa enemmän ja vuonna 2017 3 opiskelijaa enemmän."
Fredrik Finnberg
Vastaus:
Hei
Kysymyksesi mistä läsnäolleiden määrä tulee extravipusen 55 op raportille.
Koskee kuvauksen 55oplaskennanpoimintakuvaus-mittarit_extravipunen
kohtaa 6. Läsnäolleiden määrä (läsnäolotieto 1 joko syksyllä tai keväällä (esim. s2014 tai k2015)).
Aineisto josta mainitsette on varmaan kaksi kertaa viikossa Virrassa muodostuvat tarkistus_55.csv aineistot VirtaSFTP hakemistoonne?
Jos esim. teidän nyt ajankohtaisen tarkistus_55_2018_HY.csv tiedoston avaa Excelissä läsnäolo tiedot tulee hakea taulukon sarakkeista
"edellinen_syysolo" (sarake F) ja "olok" (sarake G)
edellinen_syysolo (=S2017) ja olok (=K2018) lasketaan läsnä jos 1.
Kombinaatiot että on 1 (=Läsnä) ovat:
edellinen_syys olok
1 1 Läsnä syys ja kevät
2 1 Poissa syys, läsnä kevät
1 2 Läsnä syys, poissa kevät
0 1 Ei kirjoilla syys, läsnä kevät
1 0 Läsnä syys, ei kirjoilla kevät
2018 on tilastovuosi, ja tarkastellään edellistä lukuvuotta 2017-18, "edellinen syys" on 2018 nähden syyslukukausi 2017 ja kevät on kevätlukukausi 2018.
Sama edellisten 2017, 2016 jne. edellinen lukuvuosi aina tilastovuoteen nähden.