Page tree
Skip to end of metadata
Go to start of metadata

 

 

Opettajapätevyyksien tarkistustiedostot ajetaan VirtaSFTP-palvelimelle korkeakoulukohtaiseen  \aineistot  hakemistoon päivittäin klo 19 - 21

Opiskelijakohtaiset pätevyystiedot per vuosi:  tarkistus_ope_KK_2015.csv, tarkistus_ope_KK_2016.csv, tarkistus_ope_KK_2017.csv, tarkistus_ope_KK_2018.csv

Summatasolla per vuosi: tarkistus_ope_KK_summa.csv

Keräystaulukko (kyseinen vuosi): tarkistus_ope_2017_keraystaulu_KK.csv


OKM:ään lähetettävä tieto on keräystaulukon mukainen (kaikki korkeakoulujen tiedot yhdessä sektoreittain, yo ja amk erikseen)

Pätevyydet opintosuorituksen kautta ensin, jollei ole niin poimitaan päättyneiden opiskeluoikeusjaksojen mukaan siihen liitetty pätevyyskoodin mukaan sinä vuonna.

 

 

 

Poimintalauseet
-- Keräystaulu Virta-kannassa:

CREATE TABLE [opettaja_patevyydet](
    [organisaatiokoodi] [varchar](20) NULL,
    [oppilaitostyyppi_koodi] [varchar](5) NULL,
    [db] [varchar](20) NULL,
    [patevyys_koodi] [varchar](5) NULL,
    [patevyys_nimi] [varchar](200) NULL,
    [vuosi] [int] NULL,
    [paivamaara] [date] NULL,
    [henkiloID] [int] NULL,
    [opiskelija_avain] [varchar](100) NULL,
    [opintosuoritus_avain] [varchar](100) NULL,
    [opintosuoritusLaji] [int] NULL,
    [reg_datum] [smalldatetime] NULL,
    [henkilotunnus] [varchar](20) NULL,
    [laajuus] [float] NULL
) 


-- Näkymä summatiedoille

CREATE VIEW [v_opettaja_patevyydet_summa]

AS 

SELECT   organisaatiokoodi
        , oppilaitostyyppi_koodi
        , db     
         ,    [vuosi] 
         ,    [patevyys_koodi]
         ,    [patevyys_nimi]              
         ,    count(*) AS kpl

FROM     [opettaja_patevyydet]

GROUP BY organisaatiokoodi, oppilaitostyyppi_koodi, db, vuosi , patevyys_koodi, patevyys_nimi



-- Uutta dataa. Vanhat pois alta.

TRUNCATE TABLE [dbo].[opettaja_patevyydet]

INSERT INTO [dbo].[opettaja_patevyydet]
           ([organisaatiokoodi]
           ,[oppilaitostyyppi_koodi]
           ,[db]
           ,[patevyys_koodi]
           ,[patevyys_nimi]
           ,[vuosi]
           ,[paivamaara]
           ,[henkiloID]
           ,[opiskelija_avain]
           ,[opintosuoritus_avain]
           ,[opintosuoritusLaji]
           ,[reg_datum]
           ,Henkilotunnus
           , laajuus
           )


SELECT      Y.organisaatiokoodi
        , K.Oppilaitostyyppi_koodi
        , HP.db
        , P.koodi AS patevyys_koodi
        , P.nimi AS patevyys_nimi
        , YEAR( HP.alkamispaivamaara ) AS vuosi
        , OS.Suorituspaivamaara AS paivamaara
        , HP.HenkiloID
        , O.Avain AS opiskelija_avain
        , OS.Avain AS opintosuoritus_avain    
        , OL.koodi AS OpintosuoritusLaji        
        , getdate() AS reg_datum
        ,    h.Henkilotunnus
        , OS.Laajuus 
        FROM      virta.dbo.Henkilon_patevyys HP
                JOIN virta.dbo.Opiskelija O ON O.HenkiloID = HP.HenkiloID
                JOIN virta.dbo.Henkilo H ON H.id = HP.HenkiloID
                JOIN virta.dbo.Opintosuoritus OS ON OS.id = HP.OpintosuoritusID
                JOIN virta.dbo.Patevyys P ON P.id = HP.patevyysid
                JOIN virta.dbo.Opintosuorituksen_laji OL ON OL.id = OS.Opintosuorituksen_lajiID
                JOIN Yhteiset.Asetukset.Instanssit Y ON Y.DB = HP.DB
                JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi K ON K.Organisaatio_koodi = Y.OrganisaatioKoodi
        WHERE    
         
             P.koodi LIKE '[a-z][a-z]'
AND YEAR( HP.alkamispaivamaara ) >= 2013



/* Päättyneiden opiskeluoikeusjaksojen mukaan jollei opintosuorituksen kautta tullut pätevyystieto */

INSERT INTO [dbo].[opettaja_patevyydet]
           ([organisaatiokoodi]
           ,[oppilaitostyyppi_koodi]
           ,[db]
           ,[patevyys_koodi]
           ,[patevyys_nimi]
           ,[vuosi]
           ,[paivamaara]
           ,[henkiloID]
           ,[opiskelija_avain]
           ,[opintosuoritus_avain]
           ,[opintosuoritusLaji]
           ,[reg_datum]
           ,Henkilotunnus
           , laajuus
           )

SELECT    YI.organisaatiokoodi    
        , K.Oppilaitostyyppi_koodi
        , OP.db
        , P.koodi AS patevyys_koodi
        , P.nimi AS patevyys_nimi
        
        , YEAR(OP.alkamispaivamaara) AS vuosi
        , OP.alkamispaivamaara AS paivamaara
        , O.henkiloid
        , O.Avain AS OpiskelijaAvain
        , OJ.avain AS opintosuoritus_avain
        , '1' AS OpintosuoritusLaji
        , getdate() AS reg_datum
        , h.Henkilotunnus
        , OO.Laajuus 
        

FROM     virta.dbo.[Opiskeluoikeusjakson_patevyys] OP
        JOIN virta.dbo.Patevyys P ON P.id = OP.patevyysid

        JOIN virta.dbo.Opiskeluoikeusjakso OJ ON OP.opiskeluoikeusjaksoid = OJ.id
        JOIN virta.dbo.Opiskeluoikeus OO ON OO.id = OJ.opiskeluoikeusid
        JOIN virta.dbo.Opiskelija O ON O.id     = OO.opiskelijaid        
        JOIN virta.dbo.Henkilo H ON H.id         = O.HenkiloID
        JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOL ON OOL.opiskeluoikeusid = OJ.opiskeluoikeusid

        JOIN virta.dbo.Opiskeluoikeuden_tila OT ON OT.id = OOL.op_oikeuden_tilaID
        JOIN Yhteiset.Asetukset.Instanssit YI ON YI.db = O.db
        JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi K ON K.organisaatio_koodi = YI.organisaatiokoodi

WHERE     YEAR(OP.alkamispaivamaara) >= 2015
AND      OT.koodi = 3
AND         YEAR(OOL.alkamispaivamaara) >= 2015
AND     K.Oppilaitostyyppi_koodi = '41'
AND     P.koodi LIKE '[a-z][a-z]'
AND        O.Avain NOT IN (SELECT VP.[opiskelija_avain] FROM dbo.[opettaja_patevyydet] VP WHERE VP.[patevyys_koodi] = P.koodi)











-- Korkeakouluttain , vuosittain

SELECT organisaatiokoodi      
      ,db AS kk
      ,vuosi
      ,opintosuoritusLaji
      ,patevyys_koodi
      ,patevyys_nimi      
      ,convert(varchar(12),paivamaara)  AS  paivamaara   
      ,opiskelija_avain
      ,opintosuoritus_avain     
FROM  opettaja_patevyydet

WHERE     db = @mydb
AND        vuosi = @vuosi

ORDER BY patevyys_nimi, patevyys_koodi


-- Summatiedot korkeakouluittain 

SELECT [organisaatiokoodi]      
      ,[db]
      ,[vuosi]
      ,[patevyys_koodi]
      ,[patevyys_nimi]
      ,[kpl]
  FROM  [v_opettaja_patevyydet_summa]
  WHERE db = @mydb
  AND    vuosi >= 2014
  ORDER BY vuosi, [kpl], [patevyys_nimi]
  
  
  




 

 

Keräystaulukko poiminta

- Kerätään pätevyydet (opettaja pätevyydet poimittu ylläolevalla poiminnalla opettaja_patevyydet tauluun)

- kerätään tutkinnot

- viisi pätevyyttä maksimissa

- viisi aiempaa tutkintoa maksimissa (poiminnassa ei esinny kuin max 2)

- Pätevyysmerkinnän opintosuorituksen organisaatiokoodi, pätevyyskoodi, laajuus ja vuosi.

- Tutkinnon vuosi, nimi, koulutusala, tutkintokoodi, organisaatiokoodi

- Opiskelijan sukupuoli ja ikä

-Yhteen tulokset, kaikki pätevyysmittarit, tutkinnot

Loppuraporttiin ei viedä henkilötunnusta.

 

Taulussa siis 5 kohtaa pätevyyksille ja tutkinnoille.

Mittarit tulee alla olevista pätevyyksistä.

Sarakkeeseen tieto onko henkilöllä jokin max 5 pätevyydestä.

Ts. 18 saraketta ja esim. ensimmäiseen 1 jos riviltä löytyy ”ke” pätevyys ja 0 jos ei.

pätevyyskoodipätevyys

ke  

sivuaineena varhaiskasvatuksen tehtäviin ammatillisia valmiuksia antavat opinnot (YO) ja varhaiskasvatuksen ja sosiaalipedagogiikan opinnot (AMK)
ikpääaineeseen kuuluvat aineen opettajan pedagogiset opinnot
ilsivuaineena aineen opettajan pedagogiset opinnot
imerilliset opettajan pedagogiset opinnot
inpääaineeseen kuuluvat perusopetuksessa opetettavien aineiden ja aihekokonaisuuksien monialaiset opinnot
iosivuaineena perusopetuksessa opetettavien aineiden ja aihekokonaisuuksien monialaiset opinnot
iperilliset perusopetuksessa opetettavien aineiden ja aihekokonaisuuksien monialaiset opinnot
iqpääaineena varhaiskasvatuksen tehtäviin ammatillisia valmiuksia antavat opinnot (YO) ja varhaiskasvatuksen ja sosiaalipedagogiikan opinnot (AMK)
irerilliset erityislastentarhanopettajan opinnot
ispääaineeseen kuuluu erityisopettajan pätevyys
itsivuaineena erityisopettajan pätevyys
iuerilliset erityisopettajan opinnot
ivpääaineeseen kuuluu peruskoulun ja lukion opinto-ohjaajan pätevyys
iwsivuaineena peruskoulun ja lukion opinto-ohjaajan pätevyys
iyerilliset opinto-ohjaajan opinnot
jaopettajan pätevyys ammatillisessa opettajakorkeakoulutuksessa (amk)
jberityisopettajan pätevyys ammatillisessa opettajakorkeakoulutuksessa (amk)
jcopinto-ohjaajan pätevyys ammatillisessa opettajakorkeakoulutuksessa (amk)
Keräystaulukko
CREATE TABLE #patevyydet (
    patevyyskoodi char(5),
    patevyysselite varchar(200),
    vuosi int,
    laajuus float,
    henkilotunnus varchar(12),
    opiskelija_avain varchar(100), 
    Organisaatio_koodi varchar(6),
    kk varchar(100)
)
 
INSERT INTO #patevyydet (patevyyskoodi, patevyysselite, vuosi, laajuus, henkilotunnus,opiskelija_avain,Organisaatio_koodi, kk ) 
SELECT     patevyys_koodi
        ,[patevyys_nimi]
        ,[vuosi]
        , laajuus        
        ,[henkilotunnus]
        , opiskelija_avain        
        ,[organisaatiokoodi]    
        ,[db]          
FROM [opettaja_patevyydet]

-- 
CREATE TABLE #Aiemmat_YO_tutkinnot  (
        hetu varchar(11),
        opiskelija_avain varchar(100)  ,
        tkkoodi varchar(6),
        tutkinnon_nimi varchar(200),
        kala int,
        tv int,
        organisaatio varchar(6)        
)

INSERT INTO #Aiemmat_YO_tutkinnot (hetu,opiskelija_avain,tkkoodi,tutkinnon_nimi,kala,tv,organisaatio )
SELECT   H.Henkilotunnus AS hetu
        , o.avain AS opiskelija_avain        
        ,TN.Koodi AS TKkoodi 
        ,TN.Nimi AS tutkinnon_nimi    
        ,YK.opmala AS kala
        ,YEAR(OS.Suorituspaivamaara) AS tv
        ,ORG.Organisaatio_koodi        
FROM    Virta.dbo.Henkilo H
        LEFT JOIN Virta.dbo.Opiskelija O ON O.HenkiloID = H.id
        LEFT JOIN Virta.dbo.Opintosuoritus OS ON O.id = OS.OpiskelijaID
        LEFT JOIN Virta.dbo.Tutkintonimike TN ON TN.id = OS.TutkintonimikeID
        LEFT JOIN  [Yhteiset].[Koodistot].[Koulutusluokitus] YK ON TN.Koodi = YK.Koodi
        LEFT JOIN Yhteiset.Asetukset.Instanssit YI ON YI.DB = OS.DB
        LEFT JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi ORG ON ORG.Organisaatio_koodi = YI.OrganisaatioKoodi
WHERE    o.avain IN ( SELECT opiskelija_avain FROM #patevyydet PP WHERE PP.Organisaatio_koodi = ORG.Organisaatio_koodi) 
AND        OS.Opintosuorituksen_lajiID = 1
AND        ORG.Oppilaitostyyppi_koodi IN (41, 42)
AND        YEAR(OS.Suorituspaivamaara) <= 2016
ORDER BY H.Henkilotunnus


CREATE TABLE #patevyys_five (
            henkilotunnus varchar(11),
            opiskelija_avain varchar(100),

            patevyyskoodi1 char(2),
            Organisaatio_koodi1 varchar(6),
            vuosi1 int,
            laajuus1 float, 
            
            patevyyskoodi2 char(2),
            Organisaatio_koodi2 varchar(6),
            vuosi2 int,
            laajuus2 float,
                        
            patevyyskoodi3 char(2),
            Organisaatio_koodi3 varchar(6),
            vuosi3 int,
            laajuus3 float,
            
            patevyyskoodi4 char(2),
            Organisaatio_koodi4 varchar(6),
            vuosi4 int,
            laajuus4 float,
                        
            patevyyskoodi5 char(2),
            Organisaatio_koodi5 varchar(6),
            vuosi5 int,
            laajuus5 float        
)

INSERT INTO #patevyys_five(henkilotunnus,opiskelija_avain,patevyyskoodi1,Organisaatio_koodi1, vuosi1, laajuus1, patevyyskoodi2,Organisaatio_koodi2, vuosi2,laajuus2, patevyyskoodi3,Organisaatio_koodi3,vuosi3,laajuus3, patevyyskoodi4, Organisaatio_koodi4, vuosi4, laajuus4, patevyyskoodi5, Organisaatio_koodi5, vuosi5, laajuus5 )
SELECT DISTINCT O.henkilotunnus,opiskelija_avain,
             
            (SELECT patevyyskoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, patevyyskoodi
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi
            ) patevyyskoodi
            WHERE ROWNUMBER = 1) patevyyskoodi1, 

            (SELECT organisaatiokoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, patevyyskoodi
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) organisaatiokoodi
            WHERE ROWNUMBER = 1) Organisaatio_koodi1,

            (SELECT vuosi  
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, vuosi 
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) vuosi 
            WHERE ROWNUMBER = 1) vuosi1,            
            
            (SELECT laajuus 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, laajuus
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) laajuus
            WHERE ROWNUMBER = 1) laajuus1,                
            
            (SELECT patevyyskoodi
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, patevyyskoodi
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi
            ) patevyyskoodi
            WHERE ROWNUMBER = 2) patevyyskoodi2,

            (SELECT organisaatiokoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, patevyyskoodi
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) organisaatiokoodi
            WHERE ROWNUMBER = 2) Organisaatio_koodi2, 

            
            (SELECT vuosi  
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, vuosi 
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) vuosi 
            WHERE ROWNUMBER = 2) vuosi2,            
            
            (SELECT laajuus 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, laajuus
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) laajuus
            WHERE ROWNUMBER = 2) laajuus2,                    
            
            (SELECT patevyyskoodi
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, patevyyskoodi
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi
            ) patevyyskoodi
            WHERE ROWNUMBER = 3) patevyyskoodi3,

            (SELECT organisaatiokoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, patevyyskoodi
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) organisaatiokoodi
            WHERE ROWNUMBER = 3) Organisaatio_koodi3,

            (SELECT vuosi  
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, vuosi 
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) vuosi 
            WHERE ROWNUMBER = 3) vuosi3,            
            
            (SELECT laajuus 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, laajuus
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) laajuus
            WHERE ROWNUMBER = 3) laajuus3,                    
            
            (SELECT patevyyskoodi
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, patevyyskoodi
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi
            ) patevyyskoodi
            WHERE ROWNUMBER = 4) patevyyskoodi4,

            (SELECT organisaatiokoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, patevyyskoodi
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) organisaatiokoodi
            WHERE ROWNUMBER = 4) Organisaatio_koodi4,

            (SELECT vuosi  
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, vuosi 
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) vuosi 
            WHERE ROWNUMBER = 4) vuosi4,            
            
            (SELECT laajuus 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, laajuus
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) laajuus
            WHERE ROWNUMBER = 4) laajuus4,            
        
            (SELECT patevyyskoodi
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, patevyyskoodi
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi
            ) patevyyskoodi
            WHERE ROWNUMBER = 5) patevyyskoodi5,

            (SELECT organisaatiokoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, patevyyskoodi
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) organisaatiokoodi
            WHERE ROWNUMBER = 5) Organisaatio_koodi5,
        
            (SELECT vuosi  
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, vuosi 
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) vuosi 
            WHERE ROWNUMBER = 5) vuosi5,            
            
            (SELECT laajuus 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY patevyyskoodi) AS ROWNUMBER, laajuus
                FROM    #patevyydet
                WHERE    O.henkilotunnus = #patevyydet.henkilotunnus
                AND        O.opiskelija_avain = #patevyydet.opiskelija_avain
                AND        O.organisaatiokoodi = #patevyydet.Organisaatio_koodi    
            ) laajuus
            WHERE ROWNUMBER = 5) laajuus5            
                    
FROM [opettaja_patevyydet] O
ORDER BY O.henkilotunnus


CREATE table #tutkinto_five (
            henkilotunnus varchar(11),
            opiskelija_avain varchar(100), 
            tkkoodi1 varchar(6),
            oppilaitostunnus1 varchar(6),
            tkkoodi2 varchar(6),
            oppilaitostunnus2 varchar(6), 
            tkkoodi3 varchar(6),
            oppilaitostunnus3 varchar(6), 
            tkkoodi4 varchar(6),
            oppilaitostunnus4 varchar(6), 
            tkkoodi5 varchar(6),
            oppilaitostunnus5 varchar(6)
        )


INSERT INTO  #tutkinto_five (henkilotunnus,opiskelija_avain, tkkoodi1,oppilaitostunnus1,tkkoodi2,oppilaitostunnus2,tkkoodi3,oppilaitostunnus3,tkkoodi4,oppilaitostunnus4,tkkoodi5,oppilaitostunnus5) 

SELECT DISTINCT O.henkilotunnus, O.opiskelija_avain,
        
            (SELECT tkkoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi) AS ROWNUMBER, tkkoodi
                FROM    #Aiemmat_YO_tutkinnot
                WHERE    O.henkilotunnus = #Aiemmat_YO_tutkinnot.hetu
                AND        O.opiskelija_avain =  #Aiemmat_YO_tutkinnot.opiskelija_avain
                AND        O.organisaatiokoodi = #Aiemmat_YO_tutkinnot.organisaatio
            ) tkkoodi
            WHERE ROWNUMBER = 1) tkkoodi1, 

            (SELECT organisaatiokoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi) AS ROWNUMBER, tkkoodi
                FROM    #Aiemmat_YO_tutkinnot
                WHERE    O.henkilotunnus = #Aiemmat_YO_tutkinnot.hetu
                AND        O.opiskelija_avain =  #Aiemmat_YO_tutkinnot.opiskelija_avain
                AND        O.organisaatiokoodi = #Aiemmat_YO_tutkinnot.organisaatio    
            ) organisaatiokoodi
            WHERE ROWNUMBER = 1) oppilaitostunnus1, 
            
            (SELECT tkkoodi
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi) AS ROWNUMBER, tkkoodi
                FROM    #Aiemmat_YO_tutkinnot
                WHERE    O.henkilotunnus = #Aiemmat_YO_tutkinnot.hetu
                AND        O.opiskelija_avain =  #Aiemmat_YO_tutkinnot.opiskelija_avain
                AND        O.organisaatiokoodi = #Aiemmat_YO_tutkinnot.organisaatio
            ) tkkoodi
            WHERE ROWNUMBER = 2) tkkoodi2,
            
            (SELECT organisaatiokoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi) AS ROWNUMBER, tkkoodi
                FROM    #Aiemmat_YO_tutkinnot
                WHERE    O.henkilotunnus = #Aiemmat_YO_tutkinnot.hetu
                AND        O.opiskelija_avain =  #Aiemmat_YO_tutkinnot.opiskelija_avain
                AND        O.organisaatiokoodi = #Aiemmat_YO_tutkinnot.organisaatio    
            ) organisaatiokoodi
            WHERE ROWNUMBER = 2) oppilaitostunnus2,     
                    
            (SELECT tkkoodi
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi) AS ROWNUMBER, tkkoodi
                FROM    #Aiemmat_YO_tutkinnot
                WHERE    O.henkilotunnus = #Aiemmat_YO_tutkinnot.hetu
                AND        O.opiskelija_avain =  #Aiemmat_YO_tutkinnot.opiskelija_avain
                AND        O.organisaatiokoodi = #Aiemmat_YO_tutkinnot.organisaatio
            ) tkkoodi
            WHERE ROWNUMBER = 3) tkkoodi3,

            (SELECT organisaatiokoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi) AS ROWNUMBER, tkkoodi
                FROM    #Aiemmat_YO_tutkinnot
                WHERE    O.henkilotunnus = #Aiemmat_YO_tutkinnot.hetu
                AND        O.opiskelija_avain =  #Aiemmat_YO_tutkinnot.opiskelija_avain
                AND        O.organisaatiokoodi = #Aiemmat_YO_tutkinnot.organisaatio    
            ) organisaatiokoodi
            WHERE ROWNUMBER = 3) oppilaitostunnus3,     
                        
            (SELECT tkkoodi
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi) AS ROWNUMBER, tkkoodi
                FROM    #Aiemmat_YO_tutkinnot
                WHERE    O.henkilotunnus = #Aiemmat_YO_tutkinnot.hetu
                AND        O.opiskelija_avain =  #Aiemmat_YO_tutkinnot.opiskelija_avain
                AND        O.organisaatiokoodi = #Aiemmat_YO_tutkinnot.organisaatio
            ) tkkoodi
            WHERE ROWNUMBER = 4) tkkoodi4,

            (SELECT organisaatiokoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi) AS ROWNUMBER, tkkoodi
                FROM    #Aiemmat_YO_tutkinnot
                WHERE    O.henkilotunnus = #Aiemmat_YO_tutkinnot.hetu
                AND        O.opiskelija_avain =  #Aiemmat_YO_tutkinnot.opiskelija_avain
                AND        O.organisaatiokoodi = #Aiemmat_YO_tutkinnot.organisaatio    
            ) organisaatiokoodi
            WHERE ROWNUMBER = 4) oppilaitostunnus4,             
            
            (SELECT tkkoodi
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi) AS ROWNUMBER, tkkoodi
                FROM    #Aiemmat_YO_tutkinnot
                WHERE    O.henkilotunnus = #Aiemmat_YO_tutkinnot.hetu
                AND        O.opiskelija_avain =  #Aiemmat_YO_tutkinnot.opiskelija_avain
                AND        O.organisaatiokoodi = #Aiemmat_YO_tutkinnot.organisaatio
            ) tkkoodi
            WHERE ROWNUMBER = 5) tkkoodi5,            
            
            (SELECT organisaatiokoodi 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi) AS ROWNUMBER, tkkoodi
                FROM    #Aiemmat_YO_tutkinnot
                WHERE    O.henkilotunnus = #Aiemmat_YO_tutkinnot.hetu
                AND        O.opiskelija_avain =  #Aiemmat_YO_tutkinnot.opiskelija_avain
                AND        O.organisaatiokoodi = #Aiemmat_YO_tutkinnot.organisaatio    
            ) organisaatiokoodi
            WHERE ROWNUMBER = 5) oppilaitostunnus5     
            
FROM [opettaja_patevyydet] O
ORDER BY O.henkilotunnus, O.opiskelija_avain



CREATE table #tutkinto_five_finale (
            henkilotunnus varchar(11),
            opiskelija_avain varchar(100),  
            tkkoodi1 varchar(6),
            oppilaitostunnus1 varchar(6),
            tkkoodi2 varchar(6),
            oppilaitostunnus2 varchar(6), 
            tkkoodi3 varchar(6),
            oppilaitostunnus3 varchar(6), 
            tkkoodi4 varchar(6),
            oppilaitostunnus4 varchar(6), 
            tkkoodi5 varchar(6),
            oppilaitostunnus5 varchar(6)
        )


CREATE TABLE #yksi_tutkintorivi ( hetu varchar(12),opiskelija_avain varchar(100) )

INSERT INTO  #yksi_tutkintorivi (hetu, opiskelija_avain)
SELECT henkilotunnus  AS hetu, opiskelija_avain
FROM #tutkinto_five 
GROUP BY henkilotunnus, opiskelija_avain
HAVING count(*)=1

INSERT INTO #tutkinto_five_finale 
SELECT * FROM #tutkinto_five 
WHERE henkilotunnus IN (SELECT hetu AS henkilotunnus FROM #yksi_tutkintorivi)
AND opiskelija_avain  IN (SELECT opiskelija_avain FROM #yksi_tutkintorivi)


DELETE FROM #tutkinto_five 
WHERE henkilotunnus IN (SELECT hetu AS henkilotunnus FROM #yksi_tutkintorivi)
AND opiskelija_avain  IN (SELECT opiskelija_avain FROM #yksi_tutkintorivi)

CREATE TABLE #kaksi_tutkintorivia ( hetu varchar(12),opiskelija_avain varchar(100) )

DELETE FROM #tutkinto_five WHERE tkkoodi1 IS NULL

INSERT INTO  #kaksi_tutkintorivia (hetu, opiskelija_avain)
SELECT henkilotunnus  AS hetu, opiskelija_avain
FROM #tutkinto_five 
GROUP BY henkilotunnus, opiskelija_avain
HAVING count(*)=2


INSERT INTO  #tutkinto_five_finale (henkilotunnus, opiskelija_avain, tkkoodi1,oppilaitostunnus1,tkkoodi2,oppilaitostunnus2)     

SELECT DISTINCT    KT.hetu AS henkilotunnus, KT.opiskelija_avain, 
    
        (SELECT tkkoodi1 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi1) AS ROWNUMBER, tkkoodi1
                FROM    #tutkinto_five T
                WHERE    T.henkilotunnus = KT.hetu
                AND T.opiskelija_avain = KT.opiskelija_avain
                
            ) tkkoodi1
            WHERE ROWNUMBER = 1) tkkoodi1, 
            
            
            (SELECT oppilaitostunnus1 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi1) AS ROWNUMBER, oppilaitostunnus1
                FROM    #tutkinto_five T
                WHERE    T.henkilotunnus = KT.hetu
                AND T.opiskelija_avain = KT.opiskelija_avain
                
            ) oppilaitostunnus1
            WHERE ROWNUMBER = 1) oppilaitostunnus1, 
            
            (SELECT tkkoodi1 
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi1) AS ROWNUMBER, tkkoodi1
                FROM    #tutkinto_five T
                WHERE    T.henkilotunnus = KT.hetu
                AND T.opiskelija_avain = KT.opiskelija_avain
                
            ) tkkoodi1
            WHERE ROWNUMBER = 2) tkkoodi2, 
            
            
            (SELECT oppilaitostunnus1
            FROM 
            (
                SELECT ROW_NUMBER() OVER (ORDER BY tkkoodi1) AS ROWNUMBER, oppilaitostunnus1
                FROM    #tutkinto_five T
                WHERE    T.henkilotunnus = KT.hetu
                AND T.opiskelija_avain = KT.opiskelija_avain
                
            ) oppilaitostunnus1
            WHERE ROWNUMBER = 2) oppilaitostunnus2
            
FROM  #kaksi_tutkintorivia KT
            


CREATE TABLE #patevyys_merkinnat (
        henkilotunnus varchar(11), opiskelija_avain varchar(100)   
        ,patevyyskoodi1 char(2),patevyysselite1 varchar(512),oppilaitostunnus1 varchar(6),Patevyysmerk_vuosi1 int,laajuus1 float
        ,patevyyskoodi2 char(2),patevyysselite2 varchar(512),oppilaitostunnus2 varchar(6),Patevyysmerk_vuosi2 int,laajuus2 float
        ,patevyyskoodi3 char(2),patevyysselite3 varchar(512),oppilaitostunnus3 varchar(6),Patevyysmerk_vuosi3 int,laajuus3 float
        ,patevyyskoodi4 char(2),patevyysselite4 varchar(512),oppilaitostunnus4 varchar(6),Patevyysmerk_vuosi4 int,laajuus4 float
        ,patevyyskoodi5 char(2),patevyysselite5 varchar(512),oppilaitostunnus5 varchar(6),Patevyysmerk_vuosi5 int,laajuus5 float
)


INSERT INTO  #patevyys_merkinnat (henkilotunnus, opiskelija_avain, patevyyskoodi1,patevyysselite1,Patevyysmerk_vuosi1,laajuus1,oppilaitostunnus1,patevyyskoodi2,patevyysselite2,Patevyysmerk_vuosi2,laajuus2,oppilaitostunnus2,patevyyskoodi3,patevyysselite3,Patevyysmerk_vuosi3,laajuus3,oppilaitostunnus3,patevyyskoodi4,patevyysselite4,Patevyysmerk_vuosi4,laajuus4,oppilaitostunnus4,patevyyskoodi5,patevyysselite5,Patevyysmerk_vuosi5,laajuus5,oppilaitostunnus5)

SELECT    PF.henkilotunnus, PF.opiskelija_avain,
        PF.patevyyskoodi1
        ,(SELECT TOP 1 patevyysselite FROM #patevyydet WHERE patevyyskoodi = PF.patevyyskoodi1) patevyysselite1    
        ,PF.vuosi1
        ,PF.laajuus1
        ,PF.Organisaatio_koodi1

        ,PF.patevyyskoodi2
        ,(SELECT TOP 1 patevyysselite FROM #patevyydet WHERE patevyyskoodi = PF.patevyyskoodi2) patevyysselite2
        ,PF.vuosi2
        ,PF.laajuus2
        ,PF.Organisaatio_koodi2
        
        ,PF.patevyyskoodi3
        ,(SELECT TOP 1 patevyysselite FROM #patevyydet WHERE patevyyskoodi = PF.patevyyskoodi3) patevyysselite3
        ,PF.vuosi3
        ,PF.laajuus3
        ,PF.Organisaatio_koodi3

        ,PF.patevyyskoodi4
        ,(SELECT TOP 1 patevyysselite FROM #patevyydet WHERE patevyyskoodi = PF.patevyyskoodi4) patevyysselite4
        ,PF.vuosi4
        ,PF.laajuus4
        ,PF.Organisaatio_koodi4
        
        ,PF.patevyyskoodi5 
        ,(SELECT TOP 1 patevyysselite FROM #patevyydet WHERE patevyyskoodi = PF.patevyyskoodi5) patevyysselite5
        ,PF.vuosi5
        ,PF.laajuus5
        ,PF.Organisaatio_koodi5

FROM    #patevyys_five PF


DELETE FROM #patevyys_merkinnat WHERE patevyyskoodi1 IS NULL


CREATE TABLE #tutkinto_merkinnat (
        henkilotunnus varchar(11),opiskelija_avain varchar(100)
        ,tkkoodi1 varchar(6),tutkinnon_nimi1 varchar(256),oppilaitostunnus1 varchar(6), kala1 int,tv1 int
        ,tkkoodi2 varchar(6),tutkinnon_nimi2 varchar(256),oppilaitostunnus2 varchar(6), kala2 int,tv2 int
        ,tkkoodi3 varchar(6),tutkinnon_nimi3 varchar(256),oppilaitostunnus3 varchar(6), kala3 int,tv3 int
        ,tkkoodi4 varchar(6),tutkinnon_nimi4 varchar(256),oppilaitostunnus4 varchar(6), kala4 int,tv4 int
        ,tkkoodi5 varchar(6),tutkinnon_nimi5 varchar(256),oppilaitostunnus5 varchar(6), kala5 int,tv5 int
    )


INSERT INTO #tutkinto_merkinnat (henkilotunnus,opiskelija_avain,tkkoodi1,tutkinnon_nimi1,oppilaitostunnus1,kala1,tv1,tkkoodi2,tutkinnon_nimi2,oppilaitostunnus2,kala2,tv2,tkkoodi3,tutkinnon_nimi3,oppilaitostunnus3,kala3,tv3,tkkoodi4,tutkinnon_nimi4,oppilaitostunnus4,kala4,tv4,tkkoodi5,tutkinnon_nimi5,oppilaitostunnus5,kala5,tv5)

SELECT   TF.henkilotunnus, TF.opiskelija_avain
        ,TF.tkkoodi1 
        ,(SELECT TOP 1 tutkinnon_nimi FROM #Aiemmat_YO_tutkinnot AY WHERE AY.tkkoodi = TF.tkkoodi1) tutkinnon_nimi1
        
        ,(SELECT TOP 1 Organisaatio   FROM #Aiemmat_YO_tutkinnot AY WHERE hetu = TF.henkilotunnus AND opiskelija_avain = TF.opiskelija_avain AND tkkoodi = TF.tkkoodi1 AND Organisaatio = TF.oppilaitostunnus1 ) oppilaitostunnus1
        
        ,(SELECT TOP 1 kala FROM #Aiemmat_YO_tutkinnot AY WHERE AY.tkkoodi = TF.tkkoodi1) kala1
        ,(SELECT TOP 1 tv FROM #Aiemmat_YO_tutkinnot AY WHERE tkkoodi = TF.tkkoodi1 AND hetu = TF.henkilotunnus AND opiskelija_avain = TF.opiskelija_avain  AND Organisaatio = TF.oppilaitostunnus1 ) tv1

        ,TF.tkkoodi2
        ,(SELECT TOP 1 tutkinnon_nimi FROM #Aiemmat_YO_tutkinnot AY WHERE AY.tkkoodi = TF.tkkoodi2) tutkinnon_nimi2
        
        ,(SELECT TOP 1 Organisaatio   FROM #Aiemmat_YO_tutkinnot AY WHERE hetu = TF.henkilotunnus  AND opiskelija_avain = TF.opiskelija_avain AND tkkoodi = TF.tkkoodi2 AND Organisaatio = TF.oppilaitostunnus2 ) oppilaitostunnus2
        
        ,(SELECT TOP 1 kala FROM #Aiemmat_YO_tutkinnot AY WHERE AY.tkkoodi = TF.tkkoodi2) kala2
        ,(SELECT TOP 1 tv FROM #Aiemmat_YO_tutkinnot AY WHERE tkkoodi = TF.tkkoodi2 AND hetu = TF.henkilotunnus  AND opiskelija_avain = TF.opiskelija_avain AND Organisaatio = TF.oppilaitostunnus2 ) tv2
        
        ,TF.tkkoodi3
        ,(SELECT TOP 1 tutkinnon_nimi FROM #Aiemmat_YO_tutkinnot AY WHERE AY.tkkoodi = TF.tkkoodi3) tutkinnon_nimi3
        
        ,(SELECT TOP 1 Organisaatio   FROM #Aiemmat_YO_tutkinnot AY WHERE hetu = TF.henkilotunnus  AND opiskelija_avain = TF.opiskelija_avain AND tkkoodi = TF.tkkoodi3 AND Organisaatio = TF.oppilaitostunnus3 ) oppilaitostunnus3
        
        ,(SELECT TOP 1 kala FROM #Aiemmat_YO_tutkinnot AY WHERE AY.tkkoodi = TF.tkkoodi3) kala3
        ,(SELECT TOP 1 tv FROM #Aiemmat_YO_tutkinnot AY WHERE tkkoodi = TF.tkkoodi3 AND hetu = TF.henkilotunnus  AND opiskelija_avain = TF.opiskelija_avain AND Organisaatio = TF.oppilaitostunnus3 ) tv3
        
        ,TF.tkkoodi4
        ,(SELECT TOP 1 tutkinnon_nimi FROM #Aiemmat_YO_tutkinnot AY WHERE AY.tkkoodi = TF.tkkoodi4) tutkinnon_nimi4

        ,(SELECT TOP 1 Organisaatio   FROM #Aiemmat_YO_tutkinnot AY WHERE hetu = TF.henkilotunnus  AND opiskelija_avain = TF.opiskelija_avain AND tkkoodi = TF.tkkoodi4 AND Organisaatio = TF.oppilaitostunnus4 ) oppilaitostunnus4

        ,(SELECT TOP 1 kala FROM #Aiemmat_YO_tutkinnot AY WHERE AY.tkkoodi = TF.tkkoodi4) kala4
        ,(SELECT TOP 1 tv FROM #Aiemmat_YO_tutkinnot AY WHERE tkkoodi = TF.tkkoodi4 AND hetu = TF.henkilotunnus AND opiskelija_avain = TF.opiskelija_avain ) tv4
        
        ,TF.tkkoodi5
        ,(SELECT TOP 1 tutkinnon_nimi FROM #Aiemmat_YO_tutkinnot AY WHERE AY.tkkoodi = TF.tkkoodi5) tutkinnon_nimi5

        ,(SELECT TOP 1 Organisaatio   FROM #Aiemmat_YO_tutkinnot AY WHERE hetu = TF.henkilotunnus  AND opiskelija_avain = TF.opiskelija_avain AND tkkoodi = TF.tkkoodi5 AND Organisaatio = TF.oppilaitostunnus5 ) oppilaitostunnus5        

        ,(SELECT TOP 1 kala FROM #Aiemmat_YO_tutkinnot AY WHERE AY.tkkoodi = TF.tkkoodi5) kala5
        ,(SELECT TOP 1 tv FROM #Aiemmat_YO_tutkinnot AY WHERE tkkoodi = TF.tkkoodi5 AND hetu = TF.henkilotunnus  AND opiskelija_avain = TF.opiskelija_avain AND Organisaatio = TF.oppilaitostunnus5 ) tv5
        
FROM #tutkinto_five_finale  TF



CREATE TABLE #manniskor (        
        sukup varchar(10),
        ika int,
        Henkilotunnus varchar(12),
        
        opiskelija_avain varchar(100) 
                
        )


INSERT INTO #manniskor (sukup,ika,Henkilotunnus,opiskelija_avain)
SELECT     DISTINCT         
        sp.Nimi AS sukup    
        ,YEAR(getdate())-(1900+substring(h.henkilotunnus,5,2))-1 AS ika
        ,H.Henkilotunnus
        
        ,O.opiskelija_avain        
FROM [opettaja_patevyydet] O
        JOIN Virta.dbo.Henkilo H  ON O.henkilotunnus = H.henkilotunnus
        JOIN Virta.dbo.Sukupuoli SP ON     SP.ID = H.SukupuoliID


CREATE TABLE #tulokset (    

             
        Henkilotunnus varchar(12),
        opiskelija_avain varchar(100), 
        sukup varchar(10),
        ika int 
        ,patevyyskoodi1 char(2),patevyysselite1 varchar(512),oppilaitostunnus1 varchar(6),Patevyysmerk_vuosi1 int,laajuus1 float
        ,patevyyskoodi2 char(2),patevyysselite2 varchar(512),oppilaitostunnus2 varchar(6),Patevyysmerk_vuosi2 int,laajuus2 float
        ,patevyyskoodi3 char(2),patevyysselite3 varchar(512),oppilaitostunnus3 varchar(6),Patevyysmerk_vuosi3 int,laajuus3 float
        ,patevyyskoodi4 char(2),patevyysselite4 varchar(512),oppilaitostunnus4 varchar(6),Patevyysmerk_vuosi4 int,laajuus4 float
        ,patevyyskoodi5 char(2),patevyysselite5 varchar(512),oppilaitostunnus5 varchar(6),Patevyysmerk_vuosi5 int,laajuus5 float
        ,tkkoodi1 varchar(6),tutkinnon_nimi1 varchar(256),toppilaitostunnus1 varchar(6), kala1 int,tv1 int
        ,tkkoodi2 varchar(6),tutkinnon_nimi2 varchar(256),toppilaitostunnus2 varchar(6), kala2 int,tv2 int
        ,tkkoodi3 varchar(6),tutkinnon_nimi3 varchar(256),toppilaitostunnus3 varchar(6), kala3 int,tv3 int
        ,tkkoodi4 varchar(6),tutkinnon_nimi4 varchar(256),toppilaitostunnus4 varchar(6), kala4 int,tv4 int
        ,tkkoodi5 varchar(6),tutkinnon_nimi5 varchar(256),toppilaitostunnus5 varchar(6), kala5 int,tv5 int
)



INSERT INTO #tulokset ( Henkilotunnus,opiskelija_avain, sukup,ika,
patevyyskoodi1,patevyysselite1,oppilaitostunnus1,Patevyysmerk_vuosi1,laajuus1,
patevyyskoodi2,patevyysselite2,oppilaitostunnus2,Patevyysmerk_vuosi2,laajuus2,
patevyyskoodi3,patevyysselite3,oppilaitostunnus3,Patevyysmerk_vuosi3,laajuus3,
patevyyskoodi4,patevyysselite4,oppilaitostunnus4,Patevyysmerk_vuosi4,laajuus4,
patevyyskoodi5,patevyysselite5,oppilaitostunnus5,Patevyysmerk_vuosi5,laajuus5,
tkkoodi1,tutkinnon_nimi1,toppilaitostunnus1,kala1,tv1,
tkkoodi2,tutkinnon_nimi2,toppilaitostunnus2,kala2,tv2,
tkkoodi3,tutkinnon_nimi3,toppilaitostunnus3,kala3,tv3,
tkkoodi4,tutkinnon_nimi4,toppilaitostunnus4,kala4,tv4,
tkkoodi5,tutkinnon_nimi5,toppilaitostunnus5,kala5,tv5 )        
        
SELECT    DISTINCT

        
        M.Henkilotunnus
        ,M.opiskelija_avain        
        ,M.sukup 
        ,M.ika        

    



        ,PM.patevyyskoodi1,PM.patevyysselite1,PM.oppilaitostunnus1,

        
         PM.Patevyysmerk_vuosi1,PM.laajuus1
        ,PM.patevyyskoodi2,PM.patevyysselite2,PM.oppilaitostunnus2

        ,PM.Patevyysmerk_vuosi2,PM.laajuus2
        ,PM.patevyyskoodi3,PM.patevyysselite3,PM.oppilaitostunnus3
        
        ,PM.Patevyysmerk_vuosi3,PM.laajuus3
        ,PM.patevyyskoodi4,PM.patevyysselite4,PM.oppilaitostunnus4
        
        ,PM.Patevyysmerk_vuosi4,PM.laajuus4
        ,PM.patevyyskoodi5,PM.patevyysselite5,PM.oppilaitostunnus5
        
        ,PM.Patevyysmerk_vuosi5,PM.laajuus5
        
        ,TM.tkkoodi1,TM.tutkinnon_nimi1,TM.oppilaitostunnus1 AS toppilaitostunnus1
    
        ,TM.kala1,TM.tv1
        ,TM.tkkoodi2,TM.tutkinnon_nimi2,TM.oppilaitostunnus2 AS toppilaitostunnus2
    
        ,TM.kala2,TM.tv2
        ,TM.tkkoodi3,TM.tutkinnon_nimi3,TM.oppilaitostunnus3 AS toppilaitostunnus3
    
        ,TM.kala3,TM.tv3
        ,TM.tkkoodi4,TM.tutkinnon_nimi4,TM.oppilaitostunnus4 AS toppilaitostunnus4
        
        ,TM.kala4,TM.tv4
        ,TM.tkkoodi5,TM.tutkinnon_nimi5,TM.oppilaitostunnus5 AS toppilaitostunnus5
        
        ,TM.kala5,TM.tv5

FROM    #manniskor M 
        LEFT JOIN #patevyys_merkinnat PM ON PM.henkilotunnus = M.Henkilotunnus AND PM.opiskelija_avain = M.opiskelija_avain
        LEFT JOIN #tutkinto_merkinnat TM ON TM.henkilotunnus = PM.henkilotunnus AND TM.opiskelija_avain = PM.opiskelija_avain
WHERE     PM.patevyyskoodi1 IS NOT NULL        

AND (PM.Patevyysmerk_vuosi1 = 2015 OR PM.Patevyysmerk_vuosi2 = 2015  OR PM.Patevyysmerk_vuosi3 = 2015 OR PM.Patevyysmerk_vuosi4 = 2015  OR PM.Patevyysmerk_vuosi5 = 2015)


ORDER BY PM.oppilaitostunnus1, M.henkilotunnus, M.opiskelija_avain    
        



CREATE TABLE #kaikki_patevyysmittarit (
         
        opiskelija_avain varchar(100)  
        , henkilotunnus  varchar(12)
        , organisaatiokoodi varchar(5)
        , oppilaitostyyppi_koodi int 
        , db varchar(20)
        , opintosuoritusLaji int
        , vuosi int    
        , patevyys_koodi varchar(3)
        , patevyys_nimi varchar(200)
        , ke varchar(3)
        , ik varchar(3)
        , il varchar(3)
        , im varchar(3)
        , "in" varchar(3)
        , "io" varchar(3)
        , ip varchar(3)
        , iq varchar(3)
        , ir varchar(3)
        , "is" varchar(3)
        , it varchar(3)
        , iu varchar(3)
        , iv varchar(3)
        , iw varchar(3)
        , iy varchar(3)
        , jb varchar(3)
        , ja varchar(3)
        , jc varchar(3)
)

INSERT INTO #kaikki_patevyysmittarit (opiskelija_avain,henkilotunnus,organisaatiokoodi,oppilaitostyyppi_koodi,db,opintosuoritusLaji,vuosi,patevyys_koodi,patevyys_nimi,ke,ik,il,im,"in","io",ip,iq,ir,"is",it,iu,iv,iw,iy,jb,ja,jc)
SELECT  
        
        opiskelija_avain
        , henkilotunnus 
        ,[organisaatiokoodi]
        ,oppilaitostyyppi_koodi
        ,[db]
      ,[opintosuoritusLaji]
      ,[vuosi]
      ,[patevyys_koodi]
      ,[patevyys_nimi]  
        , CASE WHEN patevyys_koodi = 'ke'  THEN 1 ELSE 0 END  AS ke
        , CASE WHEN patevyys_koodi = 'ik'  THEN 1 ELSE 0 END  AS ik
        , CASE WHEN patevyys_koodi = 'il'  THEN 1 ELSE 0 END  AS il
        , CASE WHEN patevyys_koodi = 'im'  THEN 1 ELSE 0 END  AS im
        , CASE WHEN patevyys_koodi = 'in'  THEN 1 ELSE 0 END  AS "in"
        , CASE WHEN patevyys_koodi = 'io'  THEN 1 ELSE 0 END  AS "io"
        , CASE WHEN patevyys_koodi = 'ip'  THEN 1 ELSE 0 END  AS ip
        , CASE WHEN patevyys_koodi = 'iq'  THEN 1 ELSE 0 END  AS iq
        , CASE WHEN patevyys_koodi = 'ir'  THEN 1 ELSE 0 END  AS ir
        , CASE WHEN patevyys_koodi = 'is'  THEN 1 ELSE 0 END  AS "is"
        , CASE WHEN patevyys_koodi = 'it'  THEN 1 ELSE 0 END  AS it
        , CASE WHEN patevyys_koodi = 'iu'  THEN 1 ELSE 0 END  AS iu
        , CASE WHEN patevyys_koodi = 'iv'  THEN 1 ELSE 0 END  AS iv
        , CASE WHEN patevyys_koodi = 'iw'  THEN 1 ELSE 0 END  AS iw
        , CASE WHEN patevyys_koodi = 'iy'  THEN 1 ELSE 0 END  AS iy
        , CASE WHEN patevyys_koodi = 'jb'  THEN 1 ELSE 0 END  AS jb
        , CASE WHEN patevyys_koodi = 'ja'  THEN 1 ELSE 0 END  AS ja
        , CASE WHEN patevyys_koodi = 'jc'  THEN 1 ELSE 0 END  AS jc

FROM [opettaja_patevyydet]


CREATE TABLE #seven_eleven2014 (

            
             opiskelija_avain varchar(100)  
            ,henkilotunnus varchar(11)
            , organisaatiokoodi   varchar(5)
            ,oppilaitostyyppi_koodi int 
            , db  varchar(20)
            , vuosi int
            ,
            patevkoodi_ke smallint,
            patevkoodi_ik smallint,
            patevkoodi_il smallint,
            patevkoodi_im smallint,
            patevkoodi_in smallint,
            patevkoodi_io smallint,
            patevkoodi_ip smallint,
            patevkoodi_iq smallint,
            patevkoodi_ir smallint,
            patevkoodi_is smallint,
            patevkoodi_it smallint,
            patevkoodi_iu smallint,
            patevkoodi_iv smallint,
            patevkoodi_iw smallint,
            patevkoodi_iy smallint,
            patevkoodi_jb smallint,
            patevkoodi_ja smallint,
            patevkoodi_jc smallint
            
)

CREATE TABLE #seven_eleven2015 (

            
            opiskelija_avain varchar(100)  
            ,henkilotunnus varchar(11)
            , organisaatiokoodi   varchar(5)
            ,oppilaitostyyppi_koodi int 
            , db  varchar(20)
            , vuosi int
            ,
            patevkoodi_ke smallint,
            patevkoodi_ik smallint,
            patevkoodi_il smallint,
            patevkoodi_im smallint,
            patevkoodi_in smallint,
            patevkoodi_io smallint,
            patevkoodi_ip smallint,
            patevkoodi_iq smallint,
            patevkoodi_ir smallint,
            patevkoodi_is smallint,
            patevkoodi_it smallint,
            patevkoodi_iu smallint,
            patevkoodi_iv smallint,
            patevkoodi_iw smallint,
            patevkoodi_iy smallint,
            patevkoodi_jb smallint,
            patevkoodi_ja smallint,
            patevkoodi_jc smallint
            
)


CREATE TABLE #seven_eleven2016 (

            opiskelija_avain varchar(100)  
            ,henkilotunnus varchar(11)
            , organisaatiokoodi   varchar(5)
            ,oppilaitostyyppi_koodi int 
            , db  varchar(20)
            , vuosi int
            ,
            patevkoodi_ke smallint,
            patevkoodi_ik smallint,
            patevkoodi_il smallint,
            patevkoodi_im smallint,
            patevkoodi_in smallint,
            patevkoodi_io smallint,
            patevkoodi_ip smallint,
            patevkoodi_iq smallint,
            patevkoodi_ir smallint,
            patevkoodi_is smallint,
            patevkoodi_it smallint,
            patevkoodi_iu smallint,
            patevkoodi_iv smallint,
            patevkoodi_iw smallint,
            patevkoodi_iy smallint,
            patevkoodi_jb smallint,
            patevkoodi_ja smallint,
            patevkoodi_jc smallint
            
)


        

INSERT INTO  #seven_eleven2015  (opiskelija_avain,henkilotunnus,organisaatiokoodi,oppilaitostyyppi_koodi,db,vuosi,patevkoodi_ke,patevkoodi_ik,patevkoodi_il,patevkoodi_im,patevkoodi_in,patevkoodi_io,patevkoodi_ip,patevkoodi_iq,patevkoodi_ir,patevkoodi_is,patevkoodi_it,patevkoodi_iu,patevkoodi_iv,patevkoodi_iw,patevkoodi_iy,patevkoodi_jb,patevkoodi_ja,patevkoodi_jc  )

SELECT  DISTINCT 
            opiskelija_avain
            ,henkilotunnus 
            , organisaatiokoodi  
            ,oppilaitostyyppi_koodi  
            , db  
            , vuosi ,

coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.ke <> 0),0) patevkoodi_ke    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.ik <> 0),0) patevkoodi_ik    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.il <> 0),0) patevkoodi_il    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.im <> 0),0) patevkoodi_im    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.[in] <> 0),0) patevkoodi_in    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.[io] <> 0),0) patevkoodi_io    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.ip <> 0),0) patevkoodi_ip    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.iq <> 0),0) patevkoodi_iq    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.ir <> 0),0) patevkoodi_ir    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.[is] <> 0),0) patevkoodi_is    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.it <> 0),0) patevkoodi_it    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.iu <> 0),0) patevkoodi_iu    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.iv <> 0),0) patevkoodi_iv    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.iw <> 0),0) patevkoodi_iw    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.iy <> 0),0) patevkoodi_iy    , 
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.jb <> 0),0) patevkoodi_jb    ,

coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.ja <> 0),0) patevkoodi_ja    ,
coalesce((SELECT distinct 1 FROM #kaikki_patevyysmittarit WHERE O.henkilotunnus = #kaikki_patevyysmittarit.henkilotunnus AND  O.organisaatiokoodi = #kaikki_patevyysmittarit.organisaatiokoodi AND O.vuosi = #kaikki_patevyysmittarit.vuosi AND #kaikki_patevyysmittarit.jc <> 0),0) patevkoodi_jc    


FROM [opettaja_patevyydet] O

WHERE O.vuosi = 2015

ORDER BY O.henkilotunnus        


SELECT DISTINCT  sukup,ika,organisaatiokoodi,oppilaitostyyppi_koodi,db,vuosi,patevkoodi_ke,patevkoodi_ik,patevkoodi_il,patevkoodi_im,patevkoodi_in,patevkoodi_io,patevkoodi_ip,patevkoodi_iq,patevkoodi_ir,patevkoodi_is,patevkoodi_it,patevkoodi_iu,patevkoodi_iv,patevkoodi_iw,patevkoodi_iy,patevkoodi_jb,patevyyskoodi1,patevyysselite1,oppilaitostunnus1,Patevyysmerk_vuosi1,laajuus1,patevyyskoodi2,patevyysselite2,oppilaitostunnus2,Patevyysmerk_vuosi2,laajuus2,patevyyskoodi3,patevyysselite3,oppilaitostunnus3,Patevyysmerk_vuosi3,laajuus3,patevyyskoodi4,patevyysselite4,oppilaitostunnus4,Patevyysmerk_vuosi4,laajuus4,patevyyskoodi5,patevyysselite5,oppilaitostunnus5,Patevyysmerk_vuosi5,laajuus5,tkkoodi1,tutkinnon_nimi1,toppilaitostunnus1,kala1,tv1,tkkoodi2,tutkinnon_nimi2,toppilaitostunnus2,kala2,tv2,tkkoodi3,tutkinnon_nimi3,toppilaitostunnus3,kala3,tv3,tkkoodi4,tutkinnon_nimi4,toppilaitostunnus4,kala4,tv4,tkkoodi5,tutkinnon_nimi5,toppilaitostunnus5,kala5,tv5,S.opiskelija_avain,patevkoodi_ja,patevkoodi_jc
FROM     #seven_eleven2015 S
          LEFT JOIN #tulokset T ON S.henkilotunnus = T.henkilotunnus AND S.opiskelija_avain  = T.opiskelija_avain

WHERE db = @korkeakoulu

ORDER BY organisaatiokoodi,oppilaitostyyppi_koodi,S.opiskelija_avain,db,vuosi,patevkoodi_ke,patevkoodi_ik,patevkoodi_il,patevkoodi_im,patevkoodi_in,patevkoodi_io,patevkoodi_ip,patevkoodi_iq,patevkoodi_ir,patevkoodi_is,patevkoodi_it,patevkoodi_iu,patevkoodi_iv,patevkoodi_iw,patevkoodi_iy,patevkoodi_jb,patevyyskoodi1,patevyysselite1,oppilaitostunnus1,Patevyysmerk_vuosi1,laajuus1,patevyyskoodi2,patevyysselite2,oppilaitostunnus2,Patevyysmerk_vuosi2,laajuus2,patevyyskoodi3,patevyysselite3,oppilaitostunnus3,Patevyysmerk_vuosi3,laajuus3,patevyyskoodi4,patevyysselite4,oppilaitostunnus4,Patevyysmerk_vuosi4,laajuus4,patevyyskoodi5,patevyysselite5,oppilaitostunnus5,Patevyysmerk_vuosi5,laajuus5,tkkoodi1,tutkinnon_nimi1,toppilaitostunnus1,kala1,tv1,tkkoodi2,tutkinnon_nimi2,toppilaitostunnus2,kala2,tv2,tkkoodi3,tutkinnon_nimi3,toppilaitostunnus3,kala3,tv3,tkkoodi4,tutkinnon_nimi4,toppilaitostunnus4,kala4,tv4,tkkoodi5,tutkinnon_nimi5,toppilaitostunnus5,kala5,tv5,sukup,ika,patevkoodi_ja,patevkoodi_jc






DROP TABLE #kaikki_patevyysmittarit
DROP TABLE #seven_eleven2014
DROP TABLE #seven_eleven2015
DROP TABLE #seven_eleven2016        
DROP TABLE #yksi_tutkintorivi
DROP TABLE #kaksi_tutkintorivia
DROP TABLE #manniskor    
DROP TABLE #tutkinto_five_finale 
DROP TABLE #tutkinto_merkinnat
DROP TABLE #patevyys_merkinnat
DROP TABLE #tulokset
DROP TABLE #tutkinto_five
DROP TABLE #patevyys_five
DROP TABLE #Aiemmat_YO_tutkinnot 
DROP TABLE #patevyydet     



 

 

 

 

  • No labels

2 Comments

  1. Miksi ei kerätä tietoa AMK:n opettajapätevyydelle ja, vain erityisopettajapätevyydelle jb?

    jaopettajan pätevyys ammatillisessa opettajakorkeakoulutuksessa (amk)opettaja ("ammatillinen opettaja")
    1. Asia nyt tarkistettu, raportoidaan mittareina myös ammatilliset opettajapätevyydet "ja" ja "jc" ammatillinen opinto-ohjaajakoulutus. Nämä tulevat ensi viikon alkupuolella mukaan koontiraporttiin, toki ovat nyt jo poiminnassa mukana.