Maahanmuuttajien valmentavaan koulutukseen osallistuneet opiskelijat

// Summataulukko, koulutusaloittain miehet ja naiset


CREATE TABLE #kpl_taulu (
        organisaatiokoodi varchar(5),
        vuosi int,
        koulutusala int,
        sukupuoli smallint,
        kpl int DEFAULT 0
        )

INSERT INTO #kpl_taulu(organisaatiokoodi,vuosi,koulutusala,sukupuoli,kpl)
SELECT     Y.OrganisaatioKoodi
        ,'2015' AS vuosi 
        , K.koodi        
        , (SELECT S.koodi FROM Sukupuoli S WHERE S.id = H.SukupuoliID) AS sp 
        , count(*) AS kpl    
            
FROM  Henkilo H 
        LEFT JOIN Opiskelija O ON H.id = O.HenkiloID
        LEFT JOIN Opiskeluoikeus OO ON O.id = OO.OpiskelijaID
        JOIN Koulutusala K ON K.id = OO.KoulutusalaID
        JOIN Opiskeluoikeuden_tyyppi OT ON OT.id = OO.Op_oikeuden_tyyppiID
        JOIN Yhteiset.Asetukset.Instanssit Y ON Y.DB = H.DB            
WHERE    OT.Koodi = '17'
AND        (YEAR(OO.Paattymispaivamaara) >= 2015 OR  OO.Paattymispaivamaara IS NULL)
AND        YEAR(OO.Alkamispaivamaara) >= 2015
AND        YEAR(OO.Alkamispaivamaara) < 2016


GROUP BY  Y.OrganisaatioKoodi, H.SukupuoliID, K.Nimi , K.koodi
ORDER BY  Y.OrganisaatioKoodi, K.koodi, H.SukupuoliID, K.Nimi 


--TRUNCATE TABLE amk8_suorat_tk

--INSERT INTO amk8_suorat_tk (organisaatiokoodi,vuosi,koulutusala,miehia,naisia)


SELECT     DISTINCT kt.organisaatiokoodi 
        , kt.vuosi
        , kt.koulutusala                
        , CASE WHEN ( select kpl from #kpl_taulu kp where kp.sukupuoli = 1 and kp.organisaatiokoodi = kt.organisaatiokoodi and kp.koulutusala = kt.koulutusala )    IS NULL THEN 0

        ELSE ( select kpl from #kpl_taulu kp where kp.sukupuoli = 1 and kp.organisaatiokoodi = kt.organisaatiokoodi and kp.koulutusala = kt.koulutusala )    
        END
        AS miehia 

        , CASE WHEN ( select kpl  from #kpl_taulu kp where kp.sukupuoli = 2 and kp.organisaatiokoodi = kt.organisaatiokoodi and kp.koulutusala = kt.koulutusala )    IS NULL THEN 0

        ELSE ( select kpl  from #kpl_taulu kp where kp.sukupuoli = 2 and kp.organisaatiokoodi = kt.organisaatiokoodi and kp.koulutusala = kt.koulutusala )    
        END        
        AS naisia
                
FROM    #kpl_taulu kt
            JOIN Yhteiset.Asetukset.Instanssit Y ON Y.OrganisaatioKoodi = kt.organisaatiokoodi
ORDER BY kt.organisaatiokoodi, kt.vuosi, kt.koulutusala

DROP TABLE #kpl_taulu

// Tarkistustiedosto jossa opiskelijatietoja

DECLARE @YEAR varchar(4) = '2015'                        

DECLARE @alk_pvm date    
SET @alk_pvm =    @YEAR+'-01-01'        

DECLARE @lop_pvm  date         
SET @lop_pvm = @YEAR+'-12-31'    
        
--TRUNCATE TABLE     VirtaVIE.dbo.amk8_Opiskelijat    

--INSERT INTO amk8_Opiskelijat (OrganisaatioKoodi,kk,vuosi,koulutusalakoodi,sukupuoli,opiskelu_oikeus_avain, opiskelu_oikeuden_alku, opiskelu_oikeuden_paattymispvm,opiskelija_avain,Opiskeluoikeuden_kohteena_olevan_koulutuksen_tunniste)

SELECT     DISTINCT Y.OrganisaatioKoodi        
        , @YEAR AS vuosi         
        , K.koodi        AS koulutusalakoodi
        , (SELECT S.koodi FROM Sukupuoli S WHERE S.id = H.SukupuoliID) AS sukupuoli        
        , OO.avain AS opiskelu_oikeus_avain
        , OO.Alkamispaivamaara AS "opiskelu_oikeuden_alku"
        , OO.Paattymispaivamaara AS "opiskelu_oikeuden_paattymispvm"
        , O.avain AS opiskelija_avain
        , OJ.Koulutusmoduulitunniste AS "Opiskeluoikeuden_kohteena_olevan_koulutuksen_tunniste"                        

FROM  Henkilo H 

        JOIN Opiskelija O ON H.id = O.HenkiloID
        JOIN Opiskeluoikeus OO ON O.id = OO.OpiskelijaID

        LEFT JOIN Opiskeluoikeusjakso OJ ON OJ.OpiskeluoikeusID = OO.id
        JOIN Koulutusala K ON K.id = OO.KoulutusalaID

        JOIN 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 dbo.Op_oikeuteen_liittyva_tila AS oolt ON oolt.OpiskeluoikeusID = oo.id
        JOIN dbo.Opiskeluoikeuden_tila AS ooti ON oolt.Op_oikeuden_tilaID = ooti.ID  
                
WHERE    OT.Koodi = '17'        /* Valmentava koulutus oikeustyyppi */
AND        YH.Oppilaitostyyppi_koodi = '41'  /* AMK */

-- 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