Tutkinnon osat avoimessa yliopisto-opetuksessa ja erillisinä opintoina

Avoimen yliopisto-opetuksen tiedonkeruissa tiedot kerätään avoimen yliopisto-opetuksen ja erillisten opintojen opiskelijoiden opiskelijamääristä. Tiedot kerätään ensisijaisesti VIRTA-opinotietopalvelusta.
Mikäli tietoja ei ole saatavissa VIRTAsta, voi yliopisto toimittaa ne csv-tiedostona VIRTA-tiimille

Tiedonkeruun käsikirjassa YO tutkinnon osat avoimessa yliopisto-opetuksessa ja erillisinä opintoina

Tarkistustiedostot muodostuu korkeakoulun SFTP-hakemistoon aineistot\OKM_KOTA_Tiedonkeruut alle.

YO6_2018_opiskelijat_tarkistus_KK.csv (opiskelijat poimittu Virrasta, päivittäin klo 22 jälkeen)

YO6_2018_summa_KK.csv (summa tiedot poimituista opiskelijoista, varsinainen raportoitava tilasto summatieto)

Saraketiedot: organisaatiokoodi;vuosi;Avo_tutkinto_miehet;Avo_tutkinto_naiset;Avo_miehet;Avo_naiset;Erilliset_Oikeudet;Erilliset_Opettajat;

organisaatiokoodi: Korkeakoulun oppilaitoskoodi
vuosi: Tilastovuosi
Avo_tutkinto_miehet: Avoimen yliopisto-opetuksen opiskelijat, asianomaisen yliopiston tutkinto-opiskelija, miehet
Avo_tutkinto_naiset: Avoimen yliopisto-opetuksen opiskelijat, asianomaisen yliopiston tutkinto-opiskelija, naiset
Avo_miehet: Avoimen yliopisto-opetuksen opiskelijat, ei-asianomaisen yliopiston tutkinto-opiskelija, miehet
Avo_naiset: Avoimen yliopisto-opetuksen opiskelijat, ei-asianomaisen yliopiston tutkinto-opiskelija, naiset
Erilliset_Oikeudet: Erillisinä opintoina ilman tutkinnonsuorittamisoikeutta opiskelleiden henkilöiden lukumäärä
Erilliset_Opettajat: Erillisinä opintoina ilman tutkinnonsuorittamisoikeutta opettajankoulutuksen opintoja opiskelleiden henkilöiden lukumäärä

Poimintakuvaus

+ Haetaan kaikki tilastovuoden Avoimen opiskeluoikeudet (opiskeluoikeustyyppi 13 = Avoimen opinnot ), opiskeluoikeuden tila tulee olla aktiivinen (1)
Opiskelija (henkilö) saa esiintyä tilastovuonna vain kerran asianomaisessa korkeakoulussa.

+ Kerätään tästä joukosta opiskelijat joilla on jokin tutkintoon johtava opiskeluoikeus (opiskeluoikeustyyppi 2, 4, 6, 7)

+ Erotellaan asianomaisen yliopiston tutkinto-opiskelijat ja ei-asianomaisen yliopiston tutkinto-opiskelijat.

+ Poimitaan Erilliset Opettajat (opiskeluoikeustyyppi 14 = Opettajan pedagogiset opinnot), opiskeluoikeuden tila tulee olla aktiivinen (1)
Vaatimus että on opintoja suoritettu. Opintosuorituksia luokituskoodilla 5 (= Suoritus on erillisellä opiskeluoikeudella opettajankoulutuksen opintoja suorittavan opiskelijan suorittama)
Ei saa olla tutkinto-opiskelija, kotimainen tai ulkomaa vaihtoopiskelija

+ Poimitaan Erilliset Opiskeluoikeudet (opiskeluoikeustyyppi 18 = Erillisoikeus), opiskeluoikeuden tila tulee olla aktiivinen (1)
Vaatimus että on opintoja suoritettu. Opintosuorituksia luokituskoodilla 4 (= Suoritus on erillisellä opiskeluoikeudella opiskelevan suorittama) EILOYDY
Ei erillinen opettaja oikeus, tutkinto-opiskelija, kotimainen tai ulkomaa vaihtoopiskelija.

+ Yhdistetään avoimet-erilliset-opiskelijat-tarkistus tiedot (YO6_2016_opiskelijat_tarkistus tiedot)

+ Lasketaan summataulukkoon sukupuolittain tutkinto-oikeudet, avoimet-oikeudet, erilliset oikeudet, erilliset opettajat (YO6_2016_summa tiedot)

SQL poiminnalle

YO6-tiedot

DECLARE @YEAR varchar(4) = '2016'                        
        
DECLARE @alk_pvm date    
SET @alk_pvm =    @YEAR+'-01-01'        
                    
DECLARE @lop_pvm  date         
SET @lop_pvm = @YEAR+'-12-31'    

CREATE TABLE #avoimen_oikeudet (
        henkiloid int,
        hetu varchar(12),
        sukupuoli char(1),
        opiskelijaAvain varchar(100),
        opiskeluOikeusAvain varchar(100),
        opiskelijaid int,
        opiskeluoikeusid int,
        alkamispaivamaara date,
        paattymispaivamaara date,
        tila_alkpvm date,
        tila_paattymispvm date,
        kk varchar(12),
        opiskeluoikeustyyppi char(2)                
)

CREATE NONCLUSTERED INDEX IDX_avoopiskelijaid  ON #avoimen_oikeudet(opiskelijaid)    


CREATE TABLE #tmp_avoimen_oikeudet (
        henkiloid int,
        hetu varchar(12),
        sukupuoli char(1),
        opiskelijaAvain varchar(100),
        opiskeluOikeusAvain varchar(100),
        opiskelijaid int,
        opiskeluoikeusid int,
        alkamispaivamaara date,
        paattymispaivamaara date,
        tila_alkpvm date,
        tila_paattymispvm date,
        kk varchar(12),
        opiskeluoikeustyyppi char(2)                
)

CREATE NONCLUSTERED INDEX IDX_tmp_avoimen_opiskelijaid  ON #tmp_avoimen_oikeudet(opiskelijaid)    


/* Välitaulu opiskeluoikeuksille */
CREATE TABLE #Tmp_Opiskelijat(
        id int NULL,
        oid int NULL,
        alkupvm date NULL,
        loppupvm date NULL,
        koodi nvarchar(20) NULL,
        OO_Alkamispaivamaara date NULL,
        OO_Paattymispaivamaara date NULL
)        


/* Välitaulu kaikille muille opiskeluoikeuksille */
CREATE TABLE #Tmp_Kaikki_Opiskelijat(
        id int NULL,
        oid int NULL,
        alkupvm date NULL,
        loppupvm date NULL,
        koodi nvarchar(20) NULL,
        OO_Alkamispaivamaara date NULL,
        OO_Paattymispaivamaara date NULL,
        opiskelijaAvain varchar(100)
)        

/* Kaikki opiskelijat,  opiskeluoikeustyypit ('1','2','3','4','5','6','7','8','9')     , 1-tila kalenterivuoden sisällä, päättynyt tai alkanut  */
INSERT INTO #Tmp_Kaikki_Opiskelijat ( id, oid, alkupvm, loppupvm, koodi, OO_Alkamispaivamaara, OO_Paattymispaivamaara, opiskelijaAvain )
SELECT DISTINCT oo.OpiskelijaID AS id,
                        oo.id AS oid,
                        oolt.Alkamispaivamaara AS alkupvm, 
                        ISNULL(oolt.Paattymispaivamaara, '2112-12-21') AS loppupvm,
                        OOT.koodi,
                        OO.Alkamispaivamaara,
                        OO.Paattymispaivamaara,
                        o.avain
FROM virta.dbo.Opiskeluoikeus oo
        JOIN virta.dbo.opiskelija o ON o.id = oo.OpiskelijaID
        JOIN virta.dbo.Opiskeluoikeuden_tyyppi AS OOT ON oo.Op_oikeuden_tyyppiID = oot.id
        JOIN virta.dbo.Op_oikeuteen_liittyva_tila AS oolt ON oolt.OpiskeluoikeusID = oo.id
        JOIN virta.dbo.Opiskeluoikeuden_tila AS ooti ON oolt.Op_oikeuden_tilaID = ooti.ID  

WHERE     ISNULL(oolt.Paattymispaivamaara, '2112-12-21') >= @alk_pvm     -- kalenterivuosi 1.1 - 31.12.201x
AND     oolt.Alkamispaivamaara <= @lop_pvm

AND     ooti.koodi = 1 -- opiskeluoikeuden tila aktiivinen

AND        OOT.koodi IN ('1','2','3','4','5','6','7','8','9')    


CREATE NONCLUSTERED INDEX IDX_Tmp_Kaikki_Opiskelijat_id   ON #Tmp_Kaikki_Opiskelijat(id)
CREATE NONCLUSTERED INDEX IDX_Tmp_Kaikki_Opiskelijat_oid  ON #Tmp_Kaikki_Opiskelijat(oid)




/* Kaikki opiskelijat,  opiskeluoikeustyypit (oot.koodi) '13','14','18' , 1-tila kalenterivuoden sisällä, päättynyt tai alkanut  */
INSERT INTO #Tmp_Opiskelijat ( id, oid, alkupvm, loppupvm, koodi, OO_Alkamispaivamaara, OO_Paattymispaivamaara )
SELECT oo.OpiskelijaID AS id,
                        oo.id AS oid,
                        oolt.Alkamispaivamaara AS alkupvm, 
                        ISNULL(oolt.Paattymispaivamaara, '2112-12-21') AS loppupvm,
                        OOT.koodi,
                        OO.Alkamispaivamaara,
                        OO.Paattymispaivamaara
                        
FROM virta.dbo.Opiskeluoikeus oo
        JOIN virta.dbo.Opiskeluoikeuden_tyyppi AS OOT ON oo.Op_oikeuden_tyyppiID = oot.id
        JOIN virta.dbo.Op_oikeuteen_liittyva_tila AS oolt ON oolt.OpiskeluoikeusID = oo.id
        JOIN virta.dbo.Opiskeluoikeuden_tila AS ooti ON oolt.Op_oikeuden_tilaID = ooti.ID  

WHERE     ISNULL(oolt.Paattymispaivamaara, '2112-12-21') >= @alk_pvm     -- kalenterivuosi 1.1 - 31.12.201x
AND     oolt.Alkamispaivamaara <= @lop_pvm

AND     ooti.koodi = 1 -- opiskeluoikeuden tila aktiivinen

AND        OOT.koodi IN ('13','14','18')


CREATE NONCLUSTERED INDEX IDX_Tmp_Opiskelijat_id  ON #Tmp_Opiskelijat(id)
CREATE NONCLUSTERED INDEX IDX_Tmp_Opiskelijat_oid  ON #Tmp_Opiskelijat(oid)
        
    
TRUNCATE TABLE  #tmp_avoimen_oikeudet

INSERT INTO  #tmp_avoimen_oikeudet (henkiloid, hetu,sukupuoli,opiskelijaAvain,opiskeluOikeusAvain,opiskelijaid,opiskeluoikeusid,alkamispaivamaara,paattymispaivamaara,tila_alkpvm, tila_paattymispvm, kk, opiskeluoikeustyyppi )
SELECT  DISTINCT    H.id
                ,H.henkilotunnus 
        ,(SELECT s.koodi  FROM virta.dbo.sukupuoli S WHERE S.id = H.sukupuoliid) sukupuoli
        , o.avain
        , oo.avain
        , o.id
        , oo.id
        , oo.alkamispaivamaara
        , oo.paattymispaivamaara
        ,oolt.alkamispaivamaara AS tila_alkpvm
        ,oolt.paattymispaivamaara AS tila_paattymispvm
        , oo.db AS kk
        , '13'  AS opiskeluoikeustyyppi
FROM   virta.dbo.Henkilo H 
            JOIN  virta.dbo.Opiskelija O ON H.id = O.HenkiloID
            JOIN  virta.dbo.Opiskeluoikeus OO ON O.id = OO.OpiskelijaID    
            JOIN  virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID

            JOIN Yhteiset.Asetukset.Instanssit Y ON Y.DB = H.DB        
            JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi YH ON YH.Organisaatio_koodi = Y.OrganisaatioKoodi

            JOIN  virta.dbo.Op_oikeuteen_liittyva_tila AS oolt ON oolt.OpiskeluoikeusID = oo.id
            JOIN  virta.dbo.Opiskeluoikeuden_tila AS ooti ON oolt.Op_oikeuden_tilaID = ooti.ID  
                    
WHERE    OT.Koodi = '13'        /* Avoin kk oikeustyyppi */

AND        ISNULL(OO.Paattymispaivamaara, '2112-12-21') >= @alk_pvm  
AND        OO.Alkamispaivamaara <= @lop_pvm

-- Kalenterivuosi 1.1 - 31.12.201x
AND        ISNULL(oolt.Paattymispaivamaara, '2112-12-21') >= @alk_pvm 
AND        oolt.Alkamispaivamaara <= @lop_pvm    
AND        ooti.koodi = 1 -- opiskeluoikeuden tila aktiivinen

AND        YH.Oppilaitostyyppi_koodi = '42'  /* YO */
AND  EXISTS (    SELECT * 
                FROM #Tmp_Opiskelijat T                     
                WHERE T.oid = oo.ID   
                AND    T.koodi  = '13'        ) 
    
    
TRUNCATE TABLE #avoimen_oikeudet 

INSERT INTO  #avoimen_oikeudet (henkiloid, hetu,sukupuoli,opiskelijaAvain
            ,opiskeluOikeusAvain
            ,opiskelijaid
            ,opiskeluoikeusid
            ,alkamispaivamaara,paattymispaivamaara,tila_alkpvm, tila_paattymispvm
            , kk, opiskeluoikeustyyppi )    
SELECT DISTINCT 
            TA.henkiloid, TA.hetu,TA.sukupuoli,TA.opiskelijaAvain
                
            , (SELECT TOP 1 AA.opiskeluOikeusAvain FROM #tmp_avoimen_oikeudet AA WHERE AA.opiskelijaid = TA.opiskelijaid  ORDER BY AA.tila_alkpvm ) AS opiskeluOikeusAvain 
            
            , TA.opiskelijaid
    
            , (SELECT TOP 1 BB.opiskeluoikeusid FROM #tmp_avoimen_oikeudet BB WHERE BB.opiskelijaid = TA.opiskelijaid    ORDER BY BB.tila_alkpvm ) AS opiskeluoikeusid 
            
            , (SELECT TOP 1 CC.alkamispaivamaara FROM #tmp_avoimen_oikeudet CC WHERE CC.opiskelijaid = TA.opiskelijaid   ORDER BY CC.tila_alkpvm ) AS alkamispaivamaara 
            
            , (SELECT TOP 1 DD.paattymispaivamaara FROM #tmp_avoimen_oikeudet DD WHERE DD.opiskelijaid = TA.opiskelijaid    ORDER BY DD.tila_alkpvm ) AS paattymispaivamaara 
            
            , (SELECT TOP 1 EE.tila_alkpvm FROM #tmp_avoimen_oikeudet EE WHERE EE.opiskelijaid = TA.opiskelijaid    ORDER BY EE.tila_alkpvm ) AS tila_alkpvm 
            
            , (SELECT TOP 1 FF.tila_paattymispvm FROM #tmp_avoimen_oikeudet FF WHERE FF.opiskelijaid = TA.opiskelijaid     ORDER BY FF.tila_alkpvm ) AS tila_paattymispvm                                 
            
            , TA.kk
            , TA.opiskeluoikeustyyppi 
FROM  #tmp_avoimen_oikeudet TA
WHERE TA.opiskelijaid NOT IN (SELECT A.opiskelijaid FROM #avoimen_oikeudet A WHERE A.opiskelijaid = TA.opiskelijaid  )
ORDER BY TA.opiskelijaid
    

    
CREATE TABLE #tmp_tutkinto_oikeudet (
        henkiloid int,
        hetu varchar(12),
        sukupuoli char(1),
        opiskelijaAvain varchar(100),
        opiskeluOikeusAvain varchar(100),
        opiskelijaid int,
        opiskeluoikeusid int,
        alkamispaivamaara date,
        paattymispaivamaara date,
        tila_alkpvm date,
        tila_paattymispvm date,
        kk varchar(12),
        opiskeluoikeustyyppi char(2)            
)


CREATE TABLE #tutkinto_oikeudet (
        henkiloid int,
        hetu varchar(12),
        sukupuoli char(1),
        opiskelijaAvain varchar(100),
        opiskeluOikeusAvain varchar(100),
        opiskelijaid int,
        opiskeluoikeusid int,
        alkamispaivamaara date,
        paattymispaivamaara date,
        tila_alkpvm date,
        tila_paattymispvm date,
        kk varchar(12),
        opiskeluoikeustyyppi char(2)            
)    


CREATE NONCLUSTERED INDEX IDX_tutkinto_opiskelijaid  ON  #tutkinto_oikeudet(opiskelijaid)
CREATE NONCLUSTERED INDEX IDX_tmp_tutkinto_oikeudet_oid  ON  #tmp_tutkinto_oikeudet(opiskelijaid)    


TRUNCATE TABLE  #tmp_tutkinto_oikeudet

INSERT INTO  #tmp_tutkinto_oikeudet (henkiloid, hetu,sukupuoli,opiskelijaAvain,opiskeluOikeusAvain,opiskelijaid,opiskeluoikeusid,alkamispaivamaara,paattymispaivamaara,tila_alkpvm,tila_paattymispvm,kk, opiskeluoikeustyyppi  )
SELECT  DISTINCT    H.id
                    ,H.henkilotunnus 
            ,(SELECT s.koodi  FROM virta.dbo.sukupuoli S WHERE S.id = H.sukupuoliid) sukupuoli
            , o.avain
            , oo.avain
            , o.id
            , oo.id
            , oo.alkamispaivamaara
            , oo.paattymispaivamaara
            ,OOT.alkamispaivamaara AS tila_alkpvm
            ,OOT.paattymispaivamaara AS tila_paattymispvm
            , oo.db AS kk
            ,     OT.koodi
FROM  virta.dbo.Henkilo H 
            JOIN virta.dbo.Opiskelija O ON H.id = O.HenkiloID
            JOIN virta.dbo.Opiskeluoikeus OO ON O.id = OO.OpiskelijaID    
            JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID

            JOIN Yhteiset.Asetukset.Instanssit Y ON Y.DB = H.DB        
            JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi YH ON YH.Organisaatio_koodi = Y.OrganisaatioKoodi

            JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOT ON OOT.OpiskeluoikeusID = OO.id
            JOIN virta.dbo.Opiskeluoikeuden_tila OOTT ON OOTT.id = OOT.Op_oikeuden_tilaID                     
             
WHERE      OT.koodi IN  ('2','4','6','7')        
AND        o.id IN (  SELECT A.opiskelijaid FROM #avoimen_oikeudet A  )  
AND        o.id NOT IN (  SELECT TT.opiskelijaid FROM #tmp_tutkinto_oikeudet TT  ) -- Esiintyä vain kerran    

AND        ISNULL(OO.Paattymispaivamaara, '2112-12-21') >= @alk_pvm  
AND        OO.Alkamispaivamaara <= @lop_pvm

-- Kalenterivuosi 1.1 - 31.12.201x
AND        ISNULL(OOT.Paattymispaivamaara, '2112-12-21') >= @alk_pvm 
AND        OOT.Alkamispaivamaara <= @lop_pvm    

AND        OOTT.koodi = 1 -- opiskeluoikeuden tila aktiivinen

AND        YH.Oppilaitostyyppi_koodi = '42'  /* YO */
            
AND  EXISTS (    SELECT * 
                FROM #Tmp_Opiskelijat TTO                     
                WHERE TTO.id = o.ID   
                AND    TTO.koodi  = '13'        ) 
--
AND NOT EXISTS (    SELECT * 
                    FROM #Tmp_Opiskelijat TTO2                     
                    WHERE TTO2.id = o.ID   
                    AND    TTO2.koodi  = '13'                                
                    AND OOT.Alkamispaivamaara > TTO2.OO_Alkamispaivamaara  --Tutkinto-oikeus alkanut myöhemmin kuin avoimen, eli Ei-tutkinto-Avo opisk, vaan Avoin opisk
                    
                    ) 
                    
AND NOT EXISTS (    SELECT * 
                    FROM #Tmp_Kaikki_Opiskelijat  TK
                            JOIN #Tmp_Opiskelijat TTO3  ON TTO3.id = TK.id
                    WHERE TK.id = o.ID   
                    AND    TTO3.koodi  = '13'
                    AND TK.OO_Paattymispaivamaara <    TTO3.OO_Alkamispaivamaara                
                    --Tutkinto-oikeus alkanut myöhemmin kuin avoimen, eli Ei-tutkinto-Avo opisk, vaan Avoin opisk
                    
                    )             
                    
-- 
--Tutkinto-oikeus loppunut keväällä kesällä ja myöhemmin syksyllä avoin, eli Ei-tutkinto opiskelijoiden joukossa vaan Avointen joukossa
AND NOT EXISTS (    SELECT * 
                    FROM #Tmp_Opiskelijat TTO3                     
                    WHERE TTO3.id = o.ID   
                    AND    TTO3.koodi  = '13'                            
                    AND     TTO3.id 
                            IN (    SELECT  DISTINCT O2.ID        
                                    FROM   virta.dbo.Henkilo H2
                                             JOIN virta.dbo.Opiskelija O2 ON O2.HenkiloID = H2.ID
                                             JOIN virta.dbo.Opiskeluoikeus OO2 ON OO2.OpiskelijaID = O2.ID
                                             JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOT2 ON OO2.ID = OOT2.OpiskeluoikeusID
                                             JOIN virta.dbo.Opiskeluoikeuden_tila OT2    ON OT2.ID = OOT2.Op_oikeuden_tilaID
                                    WHERE                                 
                                                        OOT2.Alkamispaivamaara <  TTO3.OO_Alkamispaivamaara
                                            AND         OOT2.Alkamispaivamaara > @alk_pvm 
                                            AND            OT2.Koodi IN ( '3', '4', '5' ) /* Opiskeluoikeus päättynyt */                                                   
                                )                                                
                )






--     
INSERT INTO  #tmp_tutkinto_oikeudet (henkiloid, hetu,sukupuoli,opiskelijaAvain,opiskeluOikeusAvain,opiskelijaid,opiskeluoikeusid,alkamispaivamaara,paattymispaivamaara,tila_alkpvm,tila_paattymispvm,kk, opiskeluoikeustyyppi  )
SELECT  DISTINCT    H.id
                    ,H.henkilotunnus 
            ,(SELECT s.koodi  FROM virta.dbo.sukupuoli S WHERE S.id = H.sukupuoliid) sukupuoli
            , o.avain
            , oo.avain
            , o.id
            , oo.id
            , oo.alkamispaivamaara
            , oo.paattymispaivamaara
            ,OOT.alkamispaivamaara AS tila_alkpvm
            ,OOT.paattymispaivamaara AS tila_paattymispvm
            , oo.db AS kk
            ,     OT.koodi
FROM  virta.dbo.Henkilo H 
            JOIN virta.dbo.Opiskelija O ON H.id = O.HenkiloID
            JOIN virta.dbo.Opiskeluoikeus OO ON O.id = OO.OpiskelijaID    
            JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID

            JOIN Yhteiset.Asetukset.Instanssit Y ON Y.DB = H.DB        
            JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi YH ON YH.Organisaatio_koodi = Y.OrganisaatioKoodi

            JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOT ON OOT.OpiskeluoikeusID = OO.id
            JOIN virta.dbo.Opiskeluoikeuden_tila OOTT ON OOTT.id = OOT.Op_oikeuden_tilaID                     
             
WHERE      o.id NOT IN (  SELECT TT.opiskelijaid FROM #tmp_tutkinto_oikeudet TT  ) -- Esiintyä vain kerran    

AND        o.id IN (  SELECT TK.id FROM #Tmp_Kaikki_Opiskelijat TK  )  

AND     OT.koodi IN  ('2','4','6','7')    

AND        ISNULL(OO.Paattymispaivamaara, '2112-12-21') >= @alk_pvm  
AND        OO.Alkamispaivamaara <= @lop_pvm

-- Kalenterivuosi 1.1 - 31.12.201x
AND        ISNULL(OOT.Paattymispaivamaara, '2112-12-21') >= @alk_pvm 
AND        OOT.Alkamispaivamaara <= @lop_pvm        

AND        OOTT.koodi = 1 -- opiskeluoikeuden tila aktiivinen

AND        YH.Oppilaitostyyppi_koodi = '42'  /* YO */
            
AND  EXISTS (    SELECT * 
                FROM #Tmp_Opiskelijat TTO                     
                WHERE TTO.id = o.ID   
                AND    TTO.koodi  = '13'        ) 
                
AND        o.id  IN (    SELECT TK2.id 
                    FROM #Tmp_Kaikki_Opiskelijat TK2                                 
                    WHERE TK2.id = o.id                           
                    AND      TK2.alkupvm < @alk_pvm
                    AND      ( TK2.loppupvm IS NULL OR TK2.loppupvm >  @lop_pvm )                        
                    )


        
--     
-- Iltalisä

INSERT INTO  #tmp_tutkinto_oikeudet (henkiloid, hetu,sukupuoli,opiskelijaAvain,opiskeluOikeusAvain,opiskelijaid,opiskeluoikeusid,alkamispaivamaara,paattymispaivamaara,tila_alkpvm,tila_paattymispvm,kk, opiskeluoikeustyyppi  )
SELECT  DISTINCT    H.id
                    ,H.henkilotunnus 
            ,(SELECT s.koodi  FROM virta.dbo.sukupuoli S WHERE S.id = H.sukupuoliid) sukupuoli
            , o.avain
            , oo.avain
            , o.id
            , oo.id
            , oo.alkamispaivamaara
            , oo.paattymispaivamaara
            ,OOT.alkamispaivamaara AS tila_alkpvm
            ,OOT.paattymispaivamaara AS tila_paattymispvm
            , oo.db AS kk
            ,     OT.koodi
FROM  virta.dbo.Henkilo H 
            JOIN virta.dbo.Opiskelija O ON H.id = O.HenkiloID
            JOIN virta.dbo.Opiskeluoikeus OO ON O.id = OO.OpiskelijaID    
            JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID

            JOIN Yhteiset.Asetukset.Instanssit Y ON Y.DB = H.DB        
            JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi YH ON YH.Organisaatio_koodi = Y.OrganisaatioKoodi

            JOIN virta.dbo.Op_oikeuteen_liittyva_tila OOT ON OOT.OpiskeluoikeusID = OO.id
            JOIN virta.dbo.Opiskeluoikeuden_tila OOTT ON OOTT.id = OOT.Op_oikeuden_tilaID                     
             
WHERE      o.id NOT IN (  SELECT TT.opiskelijaid FROM #tmp_tutkinto_oikeudet TT  ) -- Esiintyä vain kerran    

AND        o.id IN (  SELECT TK.id FROM #Tmp_Kaikki_Opiskelijat TK  )  

AND     OT.koodi IN  ('2','4','6','7')    

AND        ISNULL(OO.Paattymispaivamaara, '2112-12-21') >= @alk_pvm  
AND        OO.Alkamispaivamaara <= @lop_pvm

-- Kalenterivuosi 1.1 - 31.12.201x
AND        ISNULL(OOT.Paattymispaivamaara, '2112-12-21') >= @alk_pvm 
AND        OOT.Alkamispaivamaara <= @lop_pvm        

AND        OOTT.koodi = 1 -- opiskeluoikeuden tila aktiivinen

AND        YH.Oppilaitostyyppi_koodi = '42'  
        
AND  EXISTS (    SELECT * 
                FROM #Tmp_Opiskelijat TTO                     
                WHERE TTO.id = o.ID   
                AND    TTO.koodi  = '13'        ) 
                
AND        O.id   IN (    SELECT TK2.id 
                        FROM #Tmp_Kaikki_Opiskelijat TK2                     
                        WHERE TK2.id = o.ID   
                        AND    TK2.koodi  = '2'
                        AND YEAR(TK2.OO_Paattymispaivamaara) = 2016
                    ) 
                    
AND        O.id   IN (    SELECT TK3.id 
                        FROM #Tmp_Kaikki_Opiskelijat TK3                     
                        WHERE TK3.id = o.ID   
                        AND    TK3.koodi  = '4'
                        AND TK3.OO_Paattymispaivamaara IS NULL
                        
                    ) 
    
-- Iltalisä loppuu

-- Kaikki valmista kerätään tutkinto-oikeudet joilla on avoimen oikeuksia    

TRUNCATE TABLE  #tutkinto_oikeudet

INSERT INTO  #tutkinto_oikeudet (henkiloid, hetu,sukupuoli,opiskelijaAvain

            ,opiskeluOikeusAvain
            ,opiskelijaid
            ,opiskeluoikeusid
            ,alkamispaivamaara
            ,paattymispaivamaara
            ,tila_alkpvm
            ,tila_paattymispvm
            ,kk
            , opiskeluoikeustyyppi  )
            
SELECT  DISTINCT    TA.henkiloid
            
            , TA.hetu 
            , TA.sukupuoli
            , TA.opiskelijaAvain
                         
            , (SELECT TOP 1 AA.opiskeluOikeusAvain FROM #avoimen_oikeudet AA WHERE AA.opiskelijaid = TA.opiskelijaid AND AA.opiskeluoikeustyyppi = '13'  ORDER BY AA.tila_alkpvm, AA.opiskeluoikeustyyppi DESC ) AS opiskeluOikeusAvain 
                
            , TA.opiskelijaid
            
            , (SELECT TOP 1 BB.opiskeluoikeusid FROM #avoimen_oikeudet BB WHERE BB.opiskelijaid = TA.opiskelijaid AND BB.opiskeluoikeustyyppi = '13'  ORDER BY BB.tila_alkpvm , BB.opiskeluoikeustyyppi DESC ) AS opiskeluoikeusid 
            
            , (SELECT TOP 1 CC.alkamispaivamaara FROM #avoimen_oikeudet CC WHERE CC.opiskelijaid = TA.opiskelijaid AND CC.opiskeluoikeustyyppi = '13'  ORDER BY CC.tila_alkpvm, CC.opiskeluoikeustyyppi DESC ) AS alkamispaivamaara 
            
            , (SELECT TOP 1 DD.paattymispaivamaara FROM #avoimen_oikeudet DD WHERE DD.opiskelijaid = TA.opiskelijaid AND DD.opiskeluoikeustyyppi = '13'  ORDER BY DD.tila_alkpvm, DD.opiskeluoikeustyyppi DESC ) AS paattymispaivamaara 
            
            , (SELECT TOP 1 EE.tila_alkpvm FROM #avoimen_oikeudet EE WHERE EE.opiskelijaid = TA.opiskelijaid AND EE.opiskeluoikeustyyppi = '13'  ORDER BY EE.tila_alkpvm, EE.opiskeluoikeustyyppi DESC ) AS tila_alkpvm 
            
            , (SELECT TOP 1 FF.tila_paattymispvm FROM #avoimen_oikeudet FF WHERE FF.opiskelijaid = TA.opiskelijaid AND FF.opiskeluoikeustyyppi = '13'  ORDER BY FF.tila_alkpvm, FF.opiskeluoikeustyyppi DESC ) AS tila_paattymispvm          
                    
            , TA.kk
            
            , (SELECT TOP 1 GG.opiskeluoikeustyyppi FROM #tmp_tutkinto_oikeudet GG WHERE GG.opiskelijaid = TA.opiskelijaid AND GG.opiskeluoikeustyyppi <> '13'  ORDER BY GG.tila_alkpvm, GG.opiskeluoikeustyyppi DESC ) AS opiskeluoikeustyyppi          
            
            
FROM #tmp_tutkinto_oikeudet TA
WHERE TA.opiskelijaid NOT IN (SELECT A.opiskelijaid FROM #tutkinto_oikeudet A WHERE A.opiskelijaid = TA.opiskelijaid  )

ORDER BY TA.opiskelijaid    


    
DELETE FROM YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot

INSERT INTO YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot (
        kk,
        organisaatiokoodi,
        vuosi,         
        hetu,
        sukupuoli,
        opiskelijaAvain,
        opiskeluOikeusAvain,
        OOalkamispaivamaara,
        OOpaattymispaivamaara,
        OOAktivinentila_alkpvm,
        OOAktivinentila_paattynytpvm,
        opiskeluoikeustyyppi,
        opiskelijaid    
)
SELECT     DISTINCT    kk
        ,(SELECT Y.organisaatiokoodi FROM  Yhteiset.Asetukset.Instanssit Y WHERE Y.db = kk) AS organisaatiokoodi
        , @YEAR AS vuosi
        , hetu
        ,sukupuoli
        ,opiskelijaAvain
        ,opiskeluOikeusAvain
        ,alkamispaivamaara
        ,paattymispaivamaara
        ,tila_alkpvm
        ,tila_paattymispvm
        ,'13' + ':' + opiskeluoikeustyyppi  AS opiskeluoikeustyyppi
        , opiskelijaid            
FROM  #tutkinto_oikeudet
WHERE  opiskelijaid NOT IN (SELECT TV.opiskelijaid FROM YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot TV )

         
INSERT INTO  YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot (
        kk,
        organisaatiokoodi,
        vuosi,         
        hetu,
        sukupuoli,
        opiskelijaAvain,
        opiskeluOikeusAvain,
        OOalkamispaivamaara,
        OOpaattymispaivamaara,
        OOAktivinentila_alkpvm,
        OOAktivinentila_paattynytpvm,
        opiskeluoikeustyyppi,
        opiskelijaid
) 
SELECT     DISTINCT AA.kk
        ,(SELECT Y.organisaatiokoodi FROM  Yhteiset.Asetukset.Instanssit Y WHERE Y.db = kk) AS organisaatiokoodi
        , @YEAR  AS vuosi
        ,     AA.hetu
        ,AA.sukupuoli
        ,AA.opiskelijaAvain
        ,AA.opiskeluOikeusAvain
        ,AA.alkamispaivamaara
        ,AA.paattymispaivamaara
        ,AA.tila_alkpvm
        ,AA.tila_paattymispvm
        ,AA.opiskeluoikeustyyppi    
        ,AA.opiskelijaid    
FROM  #avoimen_oikeudet AA
WHERE AA.opiskelijaid NOT IN (SELECT T.opiskelijaid FROM #tutkinto_oikeudet T )
AND      AA.opiskelijaid NOT IN (SELECT TV.opiskelijaid FROM YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot TV )


--

CREATE TABLE #erilliset_opettajat (
        henkiloid int,
        hetu varchar(12),
        sukupuoli char(1),
        opiskelijaAvain varchar(100),
        opiskeluOikeusAvain varchar(100),
        opiskelijaid int,
        opiskeluoikeusid int,
        alkamispaivamaara date,
        paattymispaivamaara date,
        tila_alkpvm date,
        tila_paattymispvm date,
        kk varchar(12),
        opiskeluoikeustyyppi char(2)                
)

CREATE TABLE #erilliset_oikeudet (
        henkiloid int,
        hetu varchar(12),
        sukupuoli char(1),
        opiskelijaAvain varchar(100),
        opiskeluOikeusAvain varchar(100),
        opiskelijaid int,
        opiskeluoikeusid int,
        alkamispaivamaara date,
        paattymispaivamaara date,
        tila_alkpvm date,
        tila_paattymispvm date,
        kk varchar(12),
        opiskeluoikeustyyppi char(2)                
)

CREATE TABLE #tmp_erilliset_opettajat (
        henkiloid int,
        hetu varchar(12),
        sukupuoli char(1),
        opiskelijaAvain varchar(100),
        opiskeluOikeusAvain varchar(100),
        opiskelijaid int,
        opiskeluoikeusid int,
        alkamispaivamaara date,
        paattymispaivamaara date,
        tila_alkpvm date,
        tila_paattymispvm date,
        kk varchar(12),
        opiskeluoikeustyyppi char(2)                
)

CREATE TABLE #tmp_erilliset_oikeudet (
        henkiloid int,
        hetu varchar(12),
        sukupuoli char(1),
        opiskelijaAvain varchar(100),
        opiskeluOikeusAvain varchar(100),
        opiskelijaid int,
        opiskeluoikeusid int,
        alkamispaivamaara date,
        paattymispaivamaara date,
        tila_alkpvm date,
        tila_paattymispvm date,
        kk varchar(12),
        opiskeluoikeustyyppi char(2)                
)    


CREATE NONCLUSTERED INDEX IDX_eope_opiskelijaid  ON #erilliset_opettajat(opiskelijaid)    
CREATE NONCLUSTERED INDEX IDX_eril_opiskelijaid  ON #erilliset_oikeudet(opiskelijaid)

CREATE NONCLUSTERED INDEX IDX_tmp_eope_opiskelijaid  ON #tmp_erilliset_opettajat(opiskelijaid)    
CREATE NONCLUSTERED INDEX IDX_tmp_eril_opiskelijaid  ON #tmp_erilliset_oikeudet(opiskelijaid)

TRUNCATE TABLE  #tmp_erilliset_opettajat

INSERT INTO  #tmp_erilliset_opettajat (henkiloid, hetu,sukupuoli,opiskelijaAvain,opiskeluOikeusAvain,opiskelijaid,opiskeluoikeusid,alkamispaivamaara,paattymispaivamaara,tila_alkpvm,tila_paattymispvm,kk,opiskeluoikeustyyppi  )
SELECT  DISTINCT    H.id
                ,H.henkilotunnus 
        ,(SELECT s.koodi  FROM virta.dbo.sukupuoli S WHERE S.id = H.sukupuoliid) sukupuoli
        , o.avain
        , oo.avain
        , o.id
        , oo.id
        , oo.alkamispaivamaara
        , oo.paattymispaivamaara
        ,oolt.alkamispaivamaara AS tila_alkpvm
        ,oolt.paattymispaivamaara AS tila_paattymispvm
        , oo.db AS kk
        , '14'  AS opiskeluoikeustyyppi
FROM  virta.dbo.Henkilo H 
            JOIN virta.dbo.Opiskelija O ON H.id = O.HenkiloID
            JOIN virta.dbo.Opiskeluoikeus OO ON O.id = OO.OpiskelijaID    
        JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID
        
        JOIN Yhteiset.Asetukset.Instanssit Y ON Y.DB = H.DB        
        JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi YH ON YH.Organisaatio_koodi = Y.OrganisaatioKoodi

        JOIN  virta.dbo.Op_oikeuteen_liittyva_tila AS oolt ON oolt.OpiskeluoikeusID = oo.id
        JOIN  virta.dbo.Opiskeluoikeuden_tila AS ooti ON oolt.Op_oikeuden_tilaID = ooti.ID  

WHERE    OT.koodi = '14' -- opettajan pedagogisia opintoja erillisopintoina

AND        o.id NOT IN (  SELECT EO.opiskelijaid FROM #tmp_erilliset_opettajat EO  ) -- Esiintya vain kerran

AND        ISNULL(OO.Paattymispaivamaara, '2112-12-21') >= @alk_pvm  
AND        OO.Alkamispaivamaara <= @lop_pvm

-- Kalenterivuosi 1.1 - 31.12.201x
AND        ISNULL(oolt.Paattymispaivamaara, '2112-12-21') >= @alk_pvm 
AND        oolt.Alkamispaivamaara <= @lop_pvm    
AND        ooti.koodi = 1 -- opiskeluoikeuden tila aktiivinen

AND        YH.Oppilaitostyyppi_koodi = '42'  /* YO */
AND  EXISTS (    SELECT * 
                FROM #Tmp_Opiskelijat T                     
                WHERE T.oid = oo.ID   
                AND    T.koodi  = '14'        ) 
                
AND     O.id IN (     SELECT     OS.OpiskelijaID 
                    FROM     virta.dbo.Opintosuoritus OS 
                        INNER JOIN  virta.dbo.Op_suor_liittyva_luokittelu AS  OSL ON OSL.opintosuoritusid = OS.id
                    
                    WHERE     OS.db = O.db 
                    AND        OSL.db = O.db 
                    AND        YEAR(OS.suorituspaivamaara) = CONVERT(int, @YEAR) 
                    AND     OSL.Opintosuorituksen_luokitteluID IN ( SELECT L.id FROM virta.dbo.[Op_suor_luokittelu] L WHERE L.koodi = '5' )                    
                )

AND     o.id  NOT IN (    SELECT TK2.id 
                    FROM #Tmp_Kaikki_Opiskelijat TK2                                 
                    WHERE TK2.id = o.id   
                    AND      TK2.koodi  IN ('2','4','6','7','8','9')        
                    AND      TK2.alkupvm < '2015-08-01'
                    AND      ( TK2.loppupvm IS NULL OR TK2.loppupvm >  '2016-07-31' )                        
                    )    


AND     o.id  NOT IN (    SELECT TK2.id 
                        FROM #Tmp_Kaikki_Opiskelijat TK2                                 
                        WHERE TK2.id = o.id   
                        AND      TK2.koodi  IN ('2','4','6','7','8','9')        
                        AND      TK2.alkupvm BETWEEN OO.Alkamispaivamaara AND ISNULL(OO.Paattymispaivamaara, '2112-12-21')                                                
                        )            


TRUNCATE TABLE  #erilliset_opettajat

INSERT INTO  #erilliset_opettajat (henkiloid, hetu,sukupuoli,opiskelijaAvain,opiskeluOikeusAvain,opiskelijaid,opiskeluoikeusid,alkamispaivamaara,paattymispaivamaara,tila_alkpvm,tila_paattymispvm,kk,opiskeluoikeustyyppi  )
SELECT DISTINCT 
            TA.henkiloid, TA.hetu,TA.sukupuoli,TA.opiskelijaAvain
                
            , (SELECT TOP 1 AA.opiskeluOikeusAvain FROM #tmp_erilliset_opettajat AA WHERE AA.opiskelijaid = TA.opiskelijaid  ORDER BY AA.tila_alkpvm ) AS opiskeluOikeusAvain 
            
            , TA.opiskelijaid
    
            , (SELECT TOP 1 BB.opiskeluoikeusid FROM #tmp_erilliset_opettajat BB WHERE BB.opiskelijaid = TA.opiskelijaid  ORDER BY BB.tila_alkpvm ) AS opiskeluoikeusid 
            
            , (SELECT TOP 1 CC.alkamispaivamaara FROM #tmp_erilliset_opettajat CC WHERE CC.opiskelijaid = TA.opiskelijaid  ORDER BY CC.tila_alkpvm ) AS alkamispaivamaara 
            
            , (SELECT TOP 1 DD.paattymispaivamaara FROM #tmp_erilliset_opettajat DD WHERE DD.opiskelijaid = TA.opiskelijaid  ORDER BY DD.tila_alkpvm ) AS paattymispaivamaara 
            
            , (SELECT TOP 1 EE.tila_alkpvm FROM #tmp_erilliset_opettajat EE WHERE EE.opiskelijaid = TA.opiskelijaid  ORDER BY EE.tila_alkpvm ) AS tila_alkpvm 
            
            , (SELECT TOP 1 FF.tila_paattymispvm FROM #tmp_erilliset_opettajat FF WHERE FF.opiskelijaid = TA.opiskelijaid  ORDER BY FF.tila_alkpvm ) AS tila_paattymispvm                                 
            
            , TA.kk
            , TA.opiskeluoikeustyyppi 
FROM  #tmp_erilliset_opettajat TA
WHERE TA.opiskelijaid NOT IN (SELECT A.opiskelijaid FROM #erilliset_opettajat A WHERE A.opiskelijaid = TA.opiskelijaid  )
ORDER BY TA.opiskelijaid
    


TRUNCATE TABLE  #tmp_erilliset_oikeudet

INSERT INTO  #tmp_erilliset_oikeudet (henkiloid, hetu,sukupuoli,opiskelijaAvain,opiskeluOikeusAvain,opiskelijaid,opiskeluoikeusid,alkamispaivamaara,paattymispaivamaara,tila_alkpvm,tila_paattymispvm,kk,opiskeluoikeustyyppi   )
SELECT  DISTINCT    H.id
                ,H.henkilotunnus 
        ,(SELECT s.koodi  FROM virta.dbo.sukupuoli S WHERE S.id = H.sukupuoliid) sukupuoli
        , o.avain
        , oo.avain
        , o.id
        , oo.id
        , oo.alkamispaivamaara
        , oo.paattymispaivamaara
        ,oolt.alkamispaivamaara AS tila_alkpvm
        ,oolt.paattymispaivamaara AS tila_paattymispvm
        , oo.db AS kk
        , '18'  AS opiskeluoikeustyyppi

FROM  virta.dbo.Henkilo H 
            JOIN virta.dbo.Opiskelija O ON H.id = O.HenkiloID
            JOIN virta.dbo.Opiskeluoikeus OO ON O.id = OO.OpiskelijaID    
        JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID
        
        JOIN Yhteiset.Asetukset.Instanssit Y ON Y.DB = H.DB        
        JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi YH ON YH.Organisaatio_koodi = Y.OrganisaatioKoodi

        JOIN  virta.dbo.Op_oikeuteen_liittyva_tila AS oolt ON oolt.OpiskeluoikeusID = oo.id
        JOIN  virta.dbo.Opiskeluoikeuden_tila AS ooti ON oolt.Op_oikeuden_tilaID = ooti.ID  

WHERE    OT.koodi = '18' -- erillinen opiskeluoikeus 

AND  EXISTS (    SELECT * 
                FROM #Tmp_Opiskelijat T                     
                WHERE T.id = o.ID   
                AND    T.koodi  = '18'        )     

AND        o.id NOT IN (  SELECT EO.opiskelijaid FROM #erilliset_oikeudet EO  ) -- Esiintya vain kerran
AND        o.id NOT IN (  SELECT EOPE.opiskelijaid FROM #erilliset_opettajat EOPE  )  

AND        ISNULL(OO.Paattymispaivamaara, '2112-12-21') >= @alk_pvm  
AND        OO.Alkamispaivamaara <= @lop_pvm

-- Kalenterivuosi 1.1 - 31.12.201x
AND        ISNULL(oolt.Paattymispaivamaara, '2112-12-21') >= @alk_pvm 
AND        oolt.Alkamispaivamaara <= @lop_pvm    
AND        ooti.koodi = 1 -- opiskeluoikeuden tila aktiivinen

AND        YH.Oppilaitostyyppi_koodi = '42'  /* YO */

                
AND     O.id IN (     SELECT     OS.OpiskelijaID 
                    FROM     virta.dbo.Opintosuoritus OS 
                        INNER JOIN  virta.dbo.Op_suor_liittyva_luokittelu AS  OSL ON OSL.opintosuoritusid = OS.id
                    
                    WHERE     OS.db = O.db 
                    AND        OSL.db = O.db 
                    AND        YEAR(OS.suorituspaivamaara) = CONVERT(int, @YEAR)
                    AND     OSL.Opintosuorituksen_luokitteluID IN ( SELECT L.id FROM virta.dbo.[Op_suor_luokittelu] L WHERE L.koodi = '4' )                    
                )

AND     o.id  NOT IN (    SELECT TK2.id 
                        FROM #Tmp_Kaikki_Opiskelijat TK2                                 
                        WHERE TK2.id = o.id   
                        AND      TK2.koodi  IN ('2','4','6','7','9')        
                        AND      TK2.alkupvm < '2015-08-01'
                        AND      ( TK2.loppupvm IS NULL OR TK2.loppupvm >  '2016-07-31' )                        
                        )

AND     o.id  NOT IN (    SELECT TK2.id 
                        FROM #Tmp_Kaikki_Opiskelijat TK2                                 
                        WHERE TK2.id = o.id   
                        AND      TK2.koodi  IN ('2','4','6','7','9')        
                        AND      TK2.alkupvm BETWEEN OO.Alkamispaivamaara AND ISNULL(OO.Paattymispaivamaara, '2112-12-21')
                                                
                        )
                        
--

            



INSERT INTO  #tmp_erilliset_oikeudet (henkiloid, hetu,sukupuoli,opiskelijaAvain,opiskeluOikeusAvain,opiskelijaid,opiskeluoikeusid,alkamispaivamaara,paattymispaivamaara,tila_alkpvm,tila_paattymispvm,kk,opiskeluoikeustyyppi   )
SELECT  DISTINCT    H.id
                ,H.henkilotunnus 
        ,(SELECT s.koodi  FROM virta.dbo.sukupuoli S WHERE S.id = H.sukupuoliid) sukupuoli
        , o.avain
        , oo.avain
        , o.id
        , oo.id
        , oo.alkamispaivamaara
        , oo.paattymispaivamaara
        ,oolt.alkamispaivamaara AS tila_alkpvm
        ,oolt.paattymispaivamaara AS tila_paattymispvm
        , oo.db AS kk
        , '18'  AS opiskeluoikeustyyppi

FROM  virta.dbo.Henkilo H 
            JOIN virta.dbo.Opiskelija O ON H.id = O.HenkiloID
            JOIN virta.dbo.Opiskeluoikeus OO ON O.id = OO.OpiskelijaID    
        JOIN virta.dbo.Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID
        
        JOIN Yhteiset.Asetukset.Instanssit Y ON Y.DB = H.DB        
        JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi YH ON YH.Organisaatio_koodi = Y.OrganisaatioKoodi

        JOIN  virta.dbo.Op_oikeuteen_liittyva_tila AS oolt ON oolt.OpiskeluoikeusID = oo.id
        JOIN  virta.dbo.Opiskeluoikeuden_tila AS ooti ON oolt.Op_oikeuden_tilaID = ooti.ID  

WHERE    OT.koodi = '18' -- erillinen opiskeluoikeus 

AND  EXISTS (    SELECT * 
                FROM #Tmp_Opiskelijat T                     
                WHERE T.id = o.ID   
                AND    T.koodi  = '18'        )     

AND        o.id NOT IN (  SELECT EO.opiskelijaid FROM #erilliset_oikeudet EO  ) -- Esiintya vain kerran
AND        o.id NOT IN (  SELECT EOPE.opiskelijaid FROM #erilliset_opettajat EOPE  )  

AND        ISNULL(OO.Paattymispaivamaara, '2112-12-21') >= @alk_pvm  
AND        OO.Alkamispaivamaara <= @lop_pvm

-- Kalenterivuosi 1.1 - 31.12.201x
AND        ISNULL(oolt.Paattymispaivamaara, '2112-12-21') >= @alk_pvm 
AND        oolt.Alkamispaivamaara <= @lop_pvm    
AND        ooti.koodi = 1 -- opiskeluoikeuden tila aktiivinen

AND        YH.Oppilaitostyyppi_koodi = '42'  /* YO */

                
AND     O.id IN (     SELECT     OS.OpiskelijaID 
                    FROM     virta.dbo.Opintosuoritus OS 
                        INNER JOIN  virta.dbo.Op_suor_liittyva_luokittelu AS  OSL ON OSL.opintosuoritusid = OS.id
                    
                    WHERE     OS.db = O.db 
                    AND        OSL.db = O.db 
                    AND        YEAR(OS.suorituspaivamaara) = CONVERT(int, @YEAR)
                    AND     OSL.Opintosuorituksen_luokitteluID IN ( SELECT L.id FROM virta.dbo.[Op_suor_luokittelu] L WHERE L.koodi = '4' )                    
                )

AND     o.id IN (    SELECT TK2.id 
                        FROM #Tmp_Kaikki_Opiskelijat TK2                                 
                        WHERE TK2.id = o.id   
                        AND      TK2.koodi  IN ('2','4','6','7','9')        
                        AND      TK2.alkupvm >= '2016-08-01'                            
                        AND      oo.alkamispaivamaara    < TK2.alkupvm
                        )


--

TRUNCATE TABLE  #erilliset_oikeudet
    
INSERT INTO  #erilliset_oikeudet (henkiloid, hetu,sukupuoli,opiskelijaAvain,opiskeluOikeusAvain,opiskelijaid,opiskeluoikeusid,alkamispaivamaara,paattymispaivamaara,tila_alkpvm,tila_paattymispvm,kk,opiskeluoikeustyyppi  )
SELECT DISTINCT 
            TA.henkiloid, TA.hetu,TA.sukupuoli,TA.opiskelijaAvain
                
            , (SELECT TOP 1 AA.opiskeluOikeusAvain FROM #tmp_erilliset_oikeudet AA WHERE AA.opiskelijaid = TA.opiskelijaid  ORDER BY AA.tila_alkpvm ) AS opiskeluOikeusAvain 
            
            , TA.opiskelijaid
    
            , (SELECT TOP 1 BB.opiskeluoikeusid FROM #tmp_erilliset_oikeudet BB WHERE BB.opiskelijaid = TA.opiskelijaid  ORDER BY BB.tila_alkpvm ) AS opiskeluoikeusid 
            
            , (SELECT TOP 1 CC.alkamispaivamaara FROM #tmp_erilliset_oikeudet CC WHERE CC.opiskelijaid = TA.opiskelijaid  ORDER BY CC.tila_alkpvm ) AS alkamispaivamaara 
            
            , (SELECT TOP 1 DD.paattymispaivamaara FROM #tmp_erilliset_oikeudet DD WHERE DD.opiskelijaid = TA.opiskelijaid  ORDER BY DD.tila_alkpvm ) AS paattymispaivamaara 
            
            , (SELECT TOP 1 EE.tila_alkpvm FROM #tmp_erilliset_oikeudet EE WHERE EE.opiskelijaid = TA.opiskelijaid  ORDER BY EE.tila_alkpvm ) AS tila_alkpvm 
            
            , (SELECT TOP 1 FF.tila_paattymispvm FROM #tmp_erilliset_oikeudet FF WHERE FF.opiskelijaid = TA.opiskelijaid  ORDER BY FF.tila_alkpvm ) AS tila_paattymispvm                                 
            
            , TA.kk
            , TA.opiskeluoikeustyyppi 
FROM  #tmp_erilliset_oikeudet TA
WHERE TA.opiskelijaid NOT IN (SELECT A.opiskelijaid FROM #erilliset_oikeudet A WHERE A.opiskelijaid = TA.opiskelijaid AND A.opiskeluoikeustyyppi = '18' )
ORDER BY TA.opiskelijaid
    


INSERT INTO YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot (
        kk,
        organisaatiokoodi,
        vuosi,         
        hetu,
        sukupuoli,
        opiskelijaAvain,
        opiskeluOikeusAvain,
        OOalkamispaivamaara,
        OOpaattymispaivamaara,
        OOAktivinentila_alkpvm,
        OOAktivinentila_paattynytpvm,
        opiskeluoikeustyyppi,
        opiskelijaid    
)
SELECT     DISTINCT EO.kk
        ,(SELECT Y.organisaatiokoodi FROM  Yhteiset.Asetukset.Instanssit Y WHERE Y.db = EO.kk) AS organisaatiokoodi            
        , @YEAR  AS vuosi
        , EO.hetu
        ,EO.sukupuoli
        ,EO.opiskelijaAvain
        ,EO.opiskeluOikeusAvain
        ,EO.alkamispaivamaara
        ,EO.paattymispaivamaara
        ,EO.tila_alkpvm
        ,EO.tila_paattymispvm
        ,EO.opiskeluoikeustyyppi
        ,EO.opiskelijaid
FROM  #erilliset_oikeudet EO
WHERE  EO.opiskelijaid NOT IN (SELECT TV.opiskelijaid FROM YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot TV WHERE TV.opiskelijaid = EO.opiskelijaid AND TV.opiskeluoikeustyyppi = '18')


INSERT INTO YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot  (
        kk,
        organisaatiokoodi,
        vuosi,         
        hetu,
        sukupuoli,
        opiskelijaAvain,
        opiskeluOikeusAvain,
        OOalkamispaivamaara,
        OOpaattymispaivamaara,
        OOAktivinentila_alkpvm,
        OOAktivinentila_paattynytpvm,
        opiskeluoikeustyyppi,
        opiskelijaid        
) 

SELECT     DISTINCT EOpe.kk
        ,(SELECT Y.organisaatiokoodi FROM  Yhteiset.Asetukset.Instanssit  Y WHERE Y.db = EOpe.kk) AS organisaatiokoodi
        , @YEAR  AS vuosi
        , EOpe.hetu            
        ,EOpe.sukupuoli
        ,EOpe.opiskelijaAvain
        ,EOpe.opiskeluOikeusAvain
        ,EOpe.alkamispaivamaara
        ,EOpe.paattymispaivamaara
        ,EOpe.tila_alkpvm
        ,EOpe.tila_paattymispvm
        ,EOpe.opiskeluoikeustyyppi
        ,EOpe.opiskelijaid    
FROM  #erilliset_opettajat EOpe
WHERE  EOpe.opiskelijaid NOT IN (SELECT TV.opiskelijaid FROM YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot TV WHERE TV.opiskelijaid = EOpe.opiskelijaid AND TV.opiskeluoikeustyyppi = '14')


CREATE TABLE #eri_oik (
    kk varchar(12),    
    lkm int
)

CREATE TABLE #eri_ope (
    kk varchar(12),    
    lkm int
)

INSERT INTO #eri_oik (kk, lkm)
SELECT kk, count(*)  AS lkm
FROM #erilliset_oikeudet
WHERE  opiskelijaid IN (SELECT Tark.opiskelijaid FROM  YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot Tark  WHERE Tark.opiskeluoikeustyyppi = '18')
GROUP BY kk

INSERT INTO #eri_ope (kk, lkm)
SELECT kk, count(*)  AS lkm
FROM #erilliset_opettajat
WHERE  opiskelijaid IN (SELECT Tark.opiskelijaid FROM  YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot Tark  WHERE Tark.opiskeluoikeustyyppi = '14')
GROUP BY kk

CREATE TABLE #YO6_Avoin_Erilliset (
    kk varchar(12),
    organisaatiokoodi varchar(5),
    vuosi int DEFAULT 2016, 

    Avo_tutkinto_miehet int DEFAULT 0,
    Avo_tutkinto_naiset int DEFAULT 0,
    
    Avo_miehet int DEFAULT 0,
    Avo_naiset int DEFAULT 0,
    Erilliset_Oikeudet int DEFAULT 0,
    Erilliset_Opettajat int DEFAULT 0
)

INSERT INTO #YO6_Avoin_Erilliset (kk, organisaatiokoodi)
SELECT DISTINCT db AS kk, organisaatiokoodi
FROM  Yhteiset.Asetukset.Instanssit 
WHERE db IN ('AA','AYO','HY','ISY','JY','LAY','LTY','OY','SHH','TAIY','TaY','TTY','TY','VY')

CREATE TABLE #AvoTutkinto_miehet (
    kk varchar(12),    
    Avo_tutkinto_miehet int
)

CREATE TABLE #AvoTutkinto_naiset (
    kk varchar(12),    
    Avo_tutkinto_naiset int
)

CREATE TABLE #Avo_miehet (
    kk varchar(12),    
    Avo_miehet int
)

CREATE TABLE #Avo_naiset (
    kk varchar(12),    
    Avo_naiset int
)

INSERT INTO #AvoTutkinto_miehet (kk,Avo_tutkinto_miehet)
SELECT T.kk, count(*) As miehia
FROM  #tutkinto_oikeudet T
WHERE    T.sukupuoli = 1
AND  T.opiskelijaid IN (SELECT Tark.opiskelijaid FROM  YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot Tark  WHERE Tark.opiskeluoikeustyyppi LIKE '13:%')
GROUP BY T.kk

INSERT INTO #AvoTutkinto_naiset (kk,Avo_tutkinto_naiset) 
SELECT TT.kk, count(*) AS naisia
FROM  #tutkinto_oikeudet TT
WHERE    TT.sukupuoli = 2
AND  TT.opiskelijaid IN (SELECT Tark.opiskelijaid FROM  YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot Tark  WHERE Tark.opiskeluoikeustyyppi LIKE '13:%')
GROUP BY TT.kk

INSERT INTO #Avo_miehet (kk,Avo_miehet)
SELECT AA.kk, count(*) As miehia 
FROM #avoimen_oikeudet  AA
WHERE AA.henkiloid NOT IN (SELECT T.henkiloid FROM #tutkinto_oikeudet T )
AND    AA.sukupuoli = 1
AND  AA.opiskelijaid IN (SELECT Tark.opiskelijaid FROM  YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot Tark  WHERE Tark.opiskeluoikeustyyppi = '13')
GROUP BY AA.kk

INSERT INTO #Avo_naiset (kk,Avo_naiset)
SELECT A.kk, count(*) As naisia 
FROM #avoimen_oikeudet  A
WHERE A.henkiloid NOT IN (SELECT T.henkiloid FROM #tutkinto_oikeudet T )
AND    A.sukupuoli = 2
AND A.opiskelijaid IN (SELECT Tark.opiskelijaid FROM  YO6_Avoimet_Erillset_Opiskelijat_TarkistusTiedot Tark  WHERE Tark.opiskeluoikeustyyppi = '13')
GROUP BY A.kk

UPDATE U
SET  U.Avo_tutkinto_miehet = M.Avo_tutkinto_miehet
FROM #YO6_Avoin_Erilliset U
    JOIN #AvoTutkinto_miehet M ON M.kk = U.kk

UPDATE U
SET  U.Avo_tutkinto_naiset  = M.Avo_tutkinto_naiset
FROM #YO6_Avoin_Erilliset U
    JOIN #AvoTutkinto_naiset M ON M.kk = U.kk    

UPDATE U
SET  U.Avo_miehet  = M.Avo_miehet
FROM #YO6_Avoin_Erilliset U
    JOIN #Avo_miehet M ON M.kk = U.kk

UPDATE U
SET  U.Avo_naiset  = M.Avo_naiset
FROM #YO6_Avoin_Erilliset U
    JOIN #Avo_naiset M ON M.kk = U.kk        
    
UPDATE U
SET  U.Erilliset_Oikeudet   = M.lkm 
FROM #YO6_Avoin_Erilliset U
    JOIN #eri_oik M ON M.kk = U.kk        
    
UPDATE U
SET  U.Erilliset_Opettajat = M.lkm 
FROM #YO6_Avoin_Erilliset U
    JOIN #eri_ope M ON M.kk = U.kk    

    
DELETE FROM  YO6_Avoimet_Erilliset_Summataulukko    

INSERT INTO YO6_Avoimet_Erilliset_Summataulukko (
    kk,
    organisaatiokoodi,
    vuosi,
    Avo_tutkinto_miehet,
    Avo_tutkinto_naiset,    
    Avo_miehet,
    Avo_naiset,
    Erilliset_Oikeudet,
    Erilliset_Opettajat        
)                            
SELECT    kk,    
        organisaatiokoodi,
        vuosi, 
        Avo_tutkinto_miehet,
        Avo_tutkinto_naiset,
        Avo_miehet,
        Avo_naiset,
        Erilliset_Oikeudet,
        Erilliset_Opettajat                 
FROM    #YO6_Avoin_Erilliset 
ORDER BY kk
    



DROP TABLE #Tmp_Opiskelijat
DROP TABLE #Tmp_Kaikki_Opiskelijat
DROP TABLE #tmp_avoimen_oikeudet
DROP TABLE #avoimen_oikeudet 

DROP TABLE #tmp_tutkinto_oikeudet
DROP TABLE #tutkinto_oikeudet

DROP TABLE #YO6_Avoin_Erilliset

DROP TABLE #AvoTutkinto_miehet
DROP TABLE #AvoTutkinto_naiset

DROP TABLE #Avo_miehet
DROP TABLE #Avo_naiset

DROP TABLE #tmp_erilliset_opettajat
DROP TABLE #erilliset_opettajat                        

DROP TABLE #tmp_erilliset_oikeudet
DROP TABLE #erilliset_oikeudet

DROP TABLE #eri_oik
DROP TABLE #eri_ope

SQL-poiminnan kuvaus:

Haetaan kaikki avoimen oikeudet tauluun #avoimen_oikeudet laskentataulun #tmp_avoimen_oikeudet kautta jotta voidaan käsitellä opiskelijat joilla on useampi avoimen oikeuden jakso, yhtä aikaa tai peräkkäiset ynm.

Haetaan tauluun #Tmp_Opiskelijat kaikki opiskelijat joilla on oikeuksia tyyppiä Avoimen, Erilliset opettajat tai Erilliset oikeudet ('13','14','18')

Kerätään tauluun #Tmp_Kaikki_Opiskelijat kaikki tutkintoon johtavat ja vaihto-opiskelijoiden samanaikaiset aktiiviset opiskelu-oikeudet.

Haetaan ensin kaikki tilastovuoden aikana aktiivinen tilassa (1) olleet opiskeiljat joilla on opiskeluoikeudet tyyppiä ('13','14','18') tauluun #Tmp_Opiskelijat

Haetaan #tmp_avoimen_oikeudet tauluun kaikki kalenteri vuoden aikana aktiiviset oikeudet tyyppiä '13', lasketaaan kertaalleen vain yksi esiintyvä ko. korkeakoulussa tauluun #avoimen_oikeudet.

Haetaan kaikki tutkintoon johtavat opiskeluoikeuden, aktiiviset opiskelijat tauluihin #tmp_tutkinto_oikeudet ja #tutkinto_oikeudet, opiskeluoikeudet tyyppiä ('2','4','6','7') joilta löytyy myös tyyppiä '13' #avoimen_oikeudet, valitaan vain yksi tutkinto-oikeus jolla on aktiivinen opiskeluoikeus kalenterivuoden aikana.

Tutkinto-opiskelijaksi ei poimita opiskelijaa jos tutkinto-oikeus on alkanut avoimen opiskeluoikeuden (13) alkamisen jälkeen tai tutkinto-oikeus alkanut myöhemmin kuin avoimen oikeus, silloin opiskelija on ei-tutkinto-avo opisk, vain avoimen opiskelija (opiskeluoikeus), tai jos tutkinto-oikeus on päättynyt ennen avoimen oikeuden alkamista. Jos tutkinto-oikeus alkanut myöhemmin kuin avoimen oikeus, opiskelija on ei-tutkinto-avo opisk, vain avoimen opiskelija (opiskeluoikeus) ja opiskeluoikeus alkanut 1.1.2016 jälkeen, ennen avoimen oikeuden alkamispäivämäärää.

Haetaan kaikki tutkinto-opiskelijat joilla on avoimen opiskeluoikeus(13) ja joiden tutkinnonjohtava oikeus alkanut ennen 1.1.2016 ja joilla ei ole päättymispvm tai päättymispvm on 31.12.2016 jälkeen.

Kerätään #tutkinto_oikeudet tauluun #tmp_tutkinto_oikeudet opiskelijoiden avoimen oikeudet (alkamis ja päättymispäivämäärä), vain yksi, ensiksi alkanut oikeusjakso. Opiskeluoikeustyypiksi ensimmäiseksi voimassa aktiivinen tutkinto-oikeustyyppi X ('2','4','6','7') , viedään tarkistustietoihin opiskeluoikeustyyppillä 13:X

Loput avoimen opiskelijat (1#) joille ei ole löytynyt aktiivista tutkinto-oikeutta ('2','4','6','7') ovat vain avoimen tyyppiä 13.

Erilliset_opettajat ja erilliset oikeudet opiskelijat (Tiedot kerätään erikseen erillisiä opintoja suorittavista ja opettajakoulutuksen opintoja suorittavista):

- Erillisinä opintoina ilman tutkinnonsuorittamisoikeutta opiskelleiden henkilöiden lukumäärä (ei sisällä opettajankoulutuksen opinto-oikeuden omaavia)

- Erillisinä opintoina ilman tutkinnonsuorittamisoikeutta opettajankoulutuksen opintoja opiskelleiden henkilöiden lukumäärä.

Poimitaan samassa erässä missä myös avoimen tyypiä 13 kerättiin, sama tiedonkeruutaulukko YO6-tiedoille, kaikki opiskelijat joilla on avoimen, erillisen tai erillisen opettaja opiskeluoikeus tyyppiä (13, 14, 18).
Haetaan opiskeluoikeudet jonka aktiivinen tila (1) on alkanut ennen 31.12.2016 tai päättynyt 1.1.2016 jälkeen, eli kaikki voimassa olleet kalenterivuoden 1.1-31.12.2016 aikana tauluun #Tmp_Opiskelijat (taulussa kaikki vuoden 2016 aikana opiskelijat joilla on ja ollut opiskeluoikeudet tyyppiä 13, 14, 18).

Haetaan #erilliset_opettajat ja #erilliset_oikeudet omiin välitauluihin. Ensin #erilliset_opettajat tauluun: Haetaan kaikki joilla on opettajan pedagogisia opintoja erillisopintoina '14' opiskeluoikeus #Tmp_Opiskelijat taulusta ja jonka aktiivinen opiskeluoikeuden tila (1) alkanut ennen 31.12.2016 tai päättynyt 1.1.2016 jälkeen ja jolta löytyy opintosuorituksia suoritettu vuonna 2016 luokittelulla koodi = '5' (=Suoritus on erillisellä opiskeluoikeudella opettajankoulutuksen opintoja suorittavan opiskelijan suorittama)

Opiskelija jolla ei ole tutkinto-oikeutta lukukauden ajalta, oikeus joka alkanut ennen 1.8.2015 ja loppupvm on NULL tai 31.7.2016 jälkeen (lukuvuoden päättymisen) ja tutkinto-oikeus ('2','4','6','7','8','9') ei ole alkanut erillisen oikeuden (18) alkamispäivämäärän ja päättymisen välillä.

Kerätään kaikki erillisen opettajan oikeudet, ensimmäiseksi alkanut oikeusjakso.

#erilliset_oikeudet: Haetaan kaikki joilla on "erillinen opiskeluoikeus" tyyppin '18' opiskeluoikeus #Tmp_Opiskelijat taulusta joka ei ole #erilliset_opettajat taulussa (joukossa) ja opiskeluoikeus (18) aktiivinen tilassa (1) ja alkanut ennen 31.12.2016 tai päättynyt 1.1.2016 jälkeen ja jolta löytyy opintosuorituksia vuonna 2016 luokittelulla koodi = '4' (=Suoritus on erillisellä opiskeluoikeudella opiskelevan suorittama) ja jolla ei ole tutkintoikeutta lukuvuoden ajalta, alkanut ennen 1.8.2015 ja loppupvm on tyhjä tai 31.7.2016 jälkeen (lukuvuoden päättymisen) ja tutkinto-oikeus ('2','4','6','7','8','9') ei ole alkanut erillisen oikeuden alkamispäivämäärän ja päättymisen välillä.

Kerätään yhteen. Erilliset oikeuden (14) ja opettaja (18) opiskelijalla saa olla myös avoimen 13 opiskeluoikeus.

Summataan #eri_oik opiskeluoikeustyyppi = '18'
Summataan #eri_ope opiskeluoikeustyyppi = '14'

Lasketaan summat sukupuolittain miehet, naiset.

#Avo_miehet opiskeluoikeustyyppi = '13'
#Avo_naiset opiskeluoikeustyyppi = '13'

#AvoTutkinto_miehet opiskeluoikeustyyppi LIKE '13:%'
#AvoTutkinto_naiset opiskeluoikeustyyppi LIKE '13:%'

Viedään summa-taulukkoon Summataulukko
kk, organisaatiokoodi, vuosi, Avo_tutkinto_miehet, Avo_tutkinto_naiset, Avo_miehet, Avo_naiset, Erilliset_Oikeudet, Erilliset_Opettajat

Space shortcuts

Page tree

Poimintakuvaus

SQL poiminnalle

SQL-poiminnan kuvaus: