-- Luotu oma keräystaulu Erilliset opettajat pätevyystiedoille Virta-kannassa:
CREATE TABLE [TK_eriope_patevyydet](
[Oppilaitostunnus] [varchar](5) NOT NULL,
[oppilaitosnimi] [varchar](50) NOT NULL,
[koulutuksen_sijaintikunta] [varchar](3) NOT NULL,
[kunta] [varchar](30) NOT NULL,
[Erilliset_opettajan_opinnot] [varchar](3) NOT NULL,
[patevyys_selite] [varchar](250) NOT NULL,
[suoritusvuosi_ja_kuukausi] [varchar](6) NOT NULL,
[suorituspvm] [date] NOT NULL,
[Henkilotunnus] [varchar](11) NOT NULL,
[Henkilon_nimi] [varchar](60) NOT NULL,
[Sukunimi] [varchar](50) NULL,
[Etunimet] [varchar](50) NULL,
[opintosuorituksen_avain] [varchar](50) NULL,
[opiskelija_avain] [varchar](50) NULL,
[paivityspvm] [smalldatetime] NULL,
[DB] [varchar](20) NULL
)
-- Tauluun tuodaan uusin vuoden (@vuosi) data-setti. Edellisen päivän tiedot pois.
CREATE TABLE #Lopputulos_patevyys(
erilliset_opettajan_opinnot varchar(2),
patevyys_selite varchar(250),
suoritusvuosi_ja_kuukausi varchar(6),
suorituspvm date,
henkilotunnus varchar(12),
henkilon_nimi varchar(200),
sukunimi varchar(100),
etunimet varchar(100),
opintosuorituksen_avain varchar(100),
opiskelija_avain varchar(100),
db varchar(10),
opintosuoritusid int,
opiskelijaid int,
patevyysid int,
oppilaitostunnus varchar(5),
oppilaitosnimi varchar(100),
koodi varchar(3),
kunta varchar(200)
)
CREATE TABLE #patevyys(
erilliset_opettajan_opinnot varchar(2),
patevyys_selite varchar(250),
suoritusvuosi_ja_kuukausi varchar(6),
suorituspvm date,
henkilotunnus varchar(12),
henkilon_nimi varchar(200),
sukunimi varchar(100),
etunimet varchar(100),
opintosuorituksen_avain varchar(100),
opiskelija_avain varchar(100),
db varchar(10),
opintosuoritusid int,
opiskelijaid int,
patevyysid int
)
INSERT INTO #patevyys ( erilliset_opettajan_opinnot, patevyys_selite,suoritusvuosi_ja_kuukausi,suorituspvm,henkilotunnus,henkilon_nimi,sukunimi,etunimet,opintosuorituksen_avain,opiskelija_avain,db,opintosuoritusid,opiskelijaid,patevyysid )
SELECT DISTINCT
p.Koodi AS Erilliset_opettajan_opinnot
, p.Nimi AS patevyys_selite /* Selite ei tiedonkeruussa */
, substring(convert(varchar(10),os.Suorituspaivamaara),1,4) + substring(convert(varchar(10),os.Suorituspaivamaara),6,2) AS suoritusvuosi_ja_kuukausi
, os.Suorituspaivamaara AS suorituspvm
, h.Henkilotunnus AS Henkilotunnus
, H.Sukunimi + ' ' + H.Etunimet AS Henkilon_nimi
, H.Sukunimi
, H.Etunimet
, OS.avain AS opintosuorituksen_avain
, O.avain AS opiskelija_avain
, HP.DB
, OS.id
, O.id
, P.ID
FROM Virta.dbo.Henkilon_patevyys HP
JOIN Virta.dbo.Henkilo H ON H.ID = HP.HenkiloID
JOIN Virta.dbo.Opintosuoritus OS ON HP.OpintosuoritusID = OS.ID
JOIN Virta.dbo.Patevyys P ON HP.Patevyysid = P.ID
JOIN Virta.dbo.Opiskelija O ON O.HenkiloID = H.ID
WHERE OS.Suorituspaivamaara BETWEEN @vuosi+'-01-01' AND @vuosi+'-12-31'
AND P.Koodi IN ('iu','ir','iy','im','ix','rv','va') /* Erilliset opettaja pätevyyskoodit rv, va lisätty 2023 */
INSERT INTO #Lopputulos_patevyys (erilliset_opettajan_opinnot,patevyys_selite,suoritusvuosi_ja_kuukausi,suorituspvm,henkilotunnus,henkilon_nimi,sukunimi,etunimet,opintosuorituksen_avain,opiskelija_avain,db,opintosuoritusid,opiskelijaid,patevyysid )
SELECT erilliset_opettajan_opinnot,patevyys_selite,suoritusvuosi_ja_kuukausi,suorituspvm,henkilotunnus,henkilon_nimi,sukunimi,etunimet,opintosuorituksen_avain,opiskelija_avain,db,opintosuoritusid,opiskelijaid,patevyysid
FROM #patevyys
CREATE TABLE #sijaintikunta (
opintosuoritusid int,
oppilaitostunnus varchar(5),
oppilaitosnimi varchar(100),
koodi varchar(3),
kunta varchar(200) ,
opiskelijaid int,
erilliset_opettajan_opinnot varchar(2)
)
INSERT INTO #sijaintikunta ( opintosuoritusid, oppilaitostunnus, oppilaitosnimi, koodi, kunta , opiskelijaid, erilliset_opettajan_opinnot)
SELECT DISTINCT p.opintosuoritusid, ORG.Koodi, ORG.Nimi ,k.koodi, k.nimi , p.opiskelijaid, p.erilliset_opettajan_opinnot
FROM #patevyys p
JOIN virta.dbo.Op_suorituksen_organisaatio op_org ON op_ORG.OpintosuoritusID = p.opintosuoritusid
JOIN virta.dbo.Organisaatio ORG ON ORG.ID = op_org.OrganisaatioID
JOIN virta.dbo.Opiskeluoikeus OO ON p.opiskelijaid = oo.OpiskelijaID
JOIN virta.dbo.Opiskeluoikeusjakso OJ ON OJ.OpiskeluoikeusID = OO.id
JOIN virta.dbo.Kunta K ON K.ID = OJ.KuntaID
WHERE k.koodi <> '999'
AND OJ.id IN (
SELECT OJP.OpiskeluoikeusjaksoID
FROM [virta].[dbo].[Opiskeluoikeusjakson_patevyys] OJP
JOIN [virta].[dbo].Patevyys VP ON VP.id = OJP.PatevyysID
WHERE VP.Koodi = p.erilliset_opettajan_opinnot
)
-- Jos tyhjä niin ensisijainen
INSERT INTO #sijaintikunta ( opintosuoritusid, oppilaitostunnus, oppilaitosnimi, koodi, kunta , opiskelijaid, erilliset_opettajan_opinnot)
SELECT DISTINCT p.opintosuoritusid, ORG.Koodi, ORG.Nimi ,k.koodi, k.nimi , p.opiskelijaid, p.erilliset_opettajan_opinnot
FROM #patevyys p
INNER JOIN virta.dbo.Op_suorituksen_organisaatio op_org ON op_ORG.OpintosuoritusID = p.opintosuoritusid
INNER JOIN virta.dbo.Organisaatio ORG ON ORG.ID = op_org.OrganisaatioID
INNER JOIN virta.dbo.Opiskeluoikeus OO ON p.opiskelijaid = oo.OpiskelijaID
INNER JOIN virta.dbo.Opiskeluoikeusjakso OJ ON OJ.OpiskeluoikeusID = OO.id
INNER JOIN virta.dbo.Kunta K ON K.ID = OJ.KuntaID
WHERE p.opintosuoritusid NOT IN (SELECT S.opintosuoritusid FROM #sijaintikunta S)
AND k.koodi <> '999'
-- 2020-04-04 Ensisijaisuus päteyyden suoritus ajankohtana
and OO.ID in (
select oe.OpiskeluoikeusID
from virta.dbo.Op_oikeuden_ensisijaisuus oe
where (p.suorituspvm) between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-01-01')
)
--SELECT * FROM #sijaintikunta
CREATE TABLE #dubbla (
opiskelijaid int,
koodi varchar(3),
kk varchar(10),
erilliset_opettajan_opinnot varchar(2)
)
INSERT INTO #dubbla (opiskelijaid, koodi,kk,erilliset_opettajan_opinnot)
SELECT p.opiskelijaid , s.koodi, p.db, p.erilliset_opettajan_opinnot
FROM #patevyys P
JOIN #sijaintikunta S ON S.opintosuoritusid = p.opintosuoritusid
ORDER BY db, oppilaitostunnus, henkilotunnus
CREATE TABLE #poistettavat (
kunta varchar(3),
opintosuoritusid int
)
INSERT INTO #poistettavat (kunta,opintosuoritusid )
SELECT CASE
WHEN S.oppilaitostunnus = '01901' AND s.koodi <> '091' THEN s.koodi
WHEN S.oppilaitostunnus = '01903' AND s.koodi <> '853' THEN s.koodi
WHEN S.oppilaitostunnus = '10122' AND s.koodi <> '837' THEN s.koodi
WHEN S.oppilaitostunnus = '01906' AND s.koodi <> '272' THEN s.koodi
WHEN S.oppilaitostunnus = '10089' AND s.koodi <> '853' THEN s.koodi
WHEN S.oppilaitostunnus = '10088' AND s.koodi <> '167' THEN s.koodi
WHEN S.oppilaitostunnus = '01918' AND s.koodi <> '698' THEN s.koodi -- Lappi Rovaniemi
WHEN S.oppilaitostunnus = '01904' AND s.koodi <> '564' THEN s.koodi -- Oulu
WHEN S.oppilaitostunnus = '10103' AND s.koodi <> '091' THEN s.koodi -- Taideyliopisto Helsinki
END
AS kunta
,s.opintosuoritusid
FROM #sijaintikunta s WHERE s.opiskelijaid IN (
SELECT opiskelijaid
FROM #dubbla
GROUP BY opiskelijaid
HAVING count(*)>1
)
ORDER BY s.opiskelijaid
UPDATE L
SET L.kunta = S.kunta,
L.koodi = S.koodi,
L.oppilaitostunnus = S.oppilaitostunnus,
L.oppilaitosnimi = S.oppilaitosnimi
FROM #Lopputulos_patevyys L
JOIN #sijaintikunta S ON S.opintosuoritusid = L.opintosuoritusid
WHERE L.opintosuoritusid NOT IN (SELECT p.opintosuoritusid FROM #poistettavat p WHERE p.kunta = S.kunta AND p.opintosuoritusid = s.opintosuoritusid)
AND S.kunta NOT IN (SELECT p.kunta FROM #poistettavat p WHERE p.kunta = S.kunta AND p.opintosuoritusid = s.opintosuoritusid)
CREATE TABLE #TK_eriope_patevyydet (
Oppilaitostunnus varchar(5) ,
oppilaitosnimi varchar(50) ,
koulutuksen_sijaintikunta varchar(3) ,
kunta varchar(30) ,
Erilliset_opettajan_opinnot varchar(3) ,
patevyys_selite varchar(250) ,
suoritusvuosi_ja_kuukausi varchar(6) ,
suorituspvm date ,
Henkilotunnus varchar(11) ,
Henkilon_nimi varchar(60) ,
Sukunimi varchar(50) ,
Etunimet varchar(50) ,
opintosuorituksen_avain varchar(100) ,
opiskelija_avain varchar(100) ,
paivityspvm smalldatetime ,
DB varchar(20)
)
INSERT INTO #TK_eriope_patevyydet(
Oppilaitostunnus
,oppilaitosnimi
,koulutuksen_sijaintikunta
,kunta
,Erilliset_opettajan_opinnot
,patevyys_selite
,suoritusvuosi_ja_kuukausi
,suorituspvm
,Henkilotunnus
,Henkilon_nimi
,Sukunimi
,Etunimet
,opintosuorituksen_avain
,opiskelija_avain
,paivityspvm
,DB )
SELECT
L.oppilaitostunnus,
L.oppilaitosnimi,
L.koodi,
L.kunta,
L.erilliset_opettajan_opinnot,
L.patevyys_selite,
L.suoritusvuosi_ja_kuukausi,
L.suorituspvm,
L.henkilotunnus,
L.henkilon_nimi,
L.sukunimi,
L.etunimet,
L.opintosuorituksen_avain,
L.opiskelija_avain,
getdate(),
L.db
FROM #Lopputulos_patevyys L
DELETE FROM [TK_eriope_patevyydet]
INSERT INTO [dbo].[TK_eriope_patevyydet]
([Oppilaitostunnus]
,[oppilaitosnimi]
,[koulutuksen_sijaintikunta]
,[kunta]
,[Erilliset_opettajan_opinnot]
,[patevyys_selite]
,[suoritusvuosi_ja_kuukausi]
,[suorituspvm]
,[Henkilotunnus]
,[Henkilon_nimi]
,[Sukunimi]
,[Etunimet]
,[opintosuorituksen_avain]
,[opiskelija_avain]
,[paivityspvm]
,[DB])
SELECT Oppilaitostunnus,oppilaitosnimi,koulutuksen_sijaintikunta,kunta,Erilliset_opettajan_opinnot,patevyys_selite,suoritusvuosi_ja_kuukausi,suorituspvm,Henkilotunnus,Henkilon_nimi,Sukunimi,Etunimet,opintosuorituksen_avain,opiskelija_avain,paivityspvm,DB
FROM #TK_eriope_patevyydet
DROP TABLE #patevyys
DROP TABLE #sijaintikunta
DROP TABLE #dubbla
DROP TABLE #poistettavat
DROP TABLE #Lopputulos_patevyys
DROP TABLE #TK_eriope_patevyydet
1 Comment
Jenni Kokko
Jun 20, 2019Otetaanko tässä huomioon kaikki suorituksiin liittyvät pätevyyskoodit vai jätetäänkö pois ne, joissa on suorituspäivänä tutkarioikeus olemassa? En saanut tätä selville sql:stä.