Ammattikorkeakoulujen opinnaytetyot

CREATE TABLE Opinnaytetyot_summa_taulukko_opiskelija(
    [kk] [varchar](12) NULL,
    [organisaatio] [varchar](5) NULL,
    [vuosi] [int] NULL,
    [ohjausala] [smallint] NULL,
    [koulutustyyppi] [smallint] NULL,
    [hankeistettuja] [int] NULL CONSTRAINT [DF_Opinnaytetyot_summa_taulukko_opiskelija_hankeistettuja]  DEFAULT ((0)),
    [ei_hankeistettuja] [int] NULL CONSTRAINT [DF_Opinnaytetyot_summa_taulukko_opiskelija_ei_hankeistettuja]  DEFAULT ((0)),
    [reg_datum] [smalldatetime] NULL CONSTRAINT [DF_Opinnaytetyot_summa_taulukko_opiskelija_reg_datum]  DEFAULT (getdate())
)  


CREATE TABLE [dbo].[Opinnaytetyot_summa_taulukko_opintopiste](
    [kk] [varchar](12) NULL,
    [organisaatio] [varchar](5) NULL,
    [vuosi] [int] NULL,
    [ohjausala] [smallint] NULL,
    [koulutustyyppi] [smallint] NULL,
    [hankeistettuja] [int] NULL CONSTRAINT [DF_Opinnaytetyot_summa_taulukko_opintopiste_hankeistettuja]  DEFAULT ((0)),
    [ei_hankeistettuja] [int] NULL CONSTRAINT [DF_Opinnaytetyot_summa_taulukko_opintopiste_ei_hankeistettuja]  DEFAULT ((0)),
    [reg_datum] [smalldatetime] NULL CONSTRAINT [DF_Opinnaytetyot_summa_taulukko_opintopiste_reg_datum]  DEFAULT (getdate())
)  





CREATE VIEW vOpinnaytetyot
AS

SELECT      K.koodi AS ohjausalakoodi
        , K.nimi AS alanimi
        , CASE OT.koodi 

            WHEN '1' THEN CASE
                    WHEN OO.Aikuiskoulutus = '1' THEN '2'
                    ELSE '1'
                    END            
            WHEN '3' THEN '6'
            END
            AS koulutustyyppi
        , OS.db AS kk
        , YEAR(OS.suorituspaivamaara) AS suoritus_vuosi
        ,(select OrganisaatioKoodi from Yhteiset.Asetukset.Instanssit where DB = OS.db  ) AS oppilaitoskoodi    
        , OT.Koodi AS opiskeluoikeustyyppikoodi
        , OT.nimi AS opiskeluoikeustyyppi
        , OS.nimi
        , OS.laajuus
        , OS.suorituspaivamaara
         
        , CASE OS.Hankkeistettu
            WHEN 1 THEN 1
            WHEN 0 THEN 0
            WHEN NULL THEN 0
            END
            AS Hankkeistettu

        , OS.Tki_toiminnan_laajuus,OS.Tki_muut_laajuus,OS.Tki_harjoittelun_laajuus
        , OS.avain AS OSavain
        , OO.avain AS OOAvain
        , O.avain AS OpiskelijaAvain
        , H.Sukunimi
        , H.etunimet
        , OS.id AS opintosuoritusid
        , CASE OT.koodi 

            WHEN '1' THEN CASE
                    WHEN OO.Aikuiskoulutus = '1' THEN 'aikuiskoulutus(=monimuotototeutus)'
                    ELSE 'nuorten koulutus(=päivätoteutus)'
                    END            
            WHEN '3' THEN 'ylemmät amk-tutkinnot'
            END
            AS koulutustyyppi_str 
        
FROM     Opintosuoritus] OS
    JOIN Op_suor_koulutusala OK ON OK.OpintosuoritusID = OS.id
    JOIN Koulutusala    K ON K.id = OK.KoulutusalaID
    JOIN Opiskeluoikeus     OO ON OO.id = OS.OpiskeluoikeusID
    JOIN Opiskeluoikeuden_tyyppi] OT ON OT.id = OO.Op_oikeuden_tyyppiID
    JOIN Opiskelija    O ON O.id = OO.OpiskelijaID
    JOIN henkilo H ON H.id = O.HenkiloID
WHERE  OS.Opinnaytetyo = 1
AND YEAR(OS.suorituspaivamaara) >= 2014
AND YEAR(OS.suorituspaivamaara) < 2018
AND K.Versio = 'ohjausala'
AND OT.koodi IN ('1','3')

DECLARE    @vuosi int

-- Välitaulukko
CREATE TABLE #ont ( 
    vuosi int,
    oppilaitoskoodi varchar(5),
    ohjausalakoodi varchar(2),
    koulutustyyppi smallint,
    OpiskelijaAvain varchar(200), 
    laajuus float,
    hankkeistettu smallint,
    opintosuoritusid int,
    kk varchar(12)
); 

CREATE TABLE #summa_taulukko_uniikki ( 
    kk varchar(12), 
    organisaatio varchar(5),    
    vuosi int,    
    ohjausala smallint,    
    koulutustyyppi smallint,    
    hankeistettuja int,    
    ei_hankeistettuja int
);

CREATE TABLE #summa_taulukko_op ( 
    kk varchar(12), 
    organisaatio varchar(5),    
    vuosi int,    
    ohjausala smallint,    
    koulutustyyppi smallint,    
    hankeistettuja int,    
    ei_hankeistettuja int
);

CREATE TABLE #summa_uniikki_opiskelija_taulukko ( 
    kk varchar(12), 
    organisaatio varchar(5),    
    vuosi int,    
    ohjausala smallint,    
    koulutustyyppi smallint,    
    hankeistettuja int,    
    ei_hankeistettuja int
);

CREATE TABLE #summa_opintopisteet_taulukko ( 
    kk varchar(12), 
    organisaatio varchar(5),    
    vuosi int,    
    ohjausala smallint,    
    koulutustyyppi smallint,    
    hankeistettuja int,    
    ei_hankeistettuja int
);


 
-- Kaikki data 
INSERT INTO #ont ( vuosi,oppilaitoskoodi,ohjausalakoodi,koulutustyyppi,OpiskelijaAvain, laajuus, hankkeistettu,opintosuoritusid,kk )
SELECT suoritus_vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi, OpiskelijaAvain, laajuus, hankkeistettu,opintosuoritusid,kk 
FROM vOpinnaytetyot
WHERE suoritus_vuosi = @vuosi



 
 
-- Uniikki opiskelija hankkeistettu
INSERT INTO #summa_uniikki_opiskelija_taulukko ( vuosi,organisaatio, kk, ohjausala, koulutustyyppi, hankeistettuja )
SELECT  vuosi, oppilaitoskoodi, kk, cast(ohjausalakoodi AS int)  ohjausalakoodi ,  koulutustyyppi, count(*) AS hankeistettu_opiskelija
FROM (SELECT DISTINCT vuosi, oppilaitoskoodi,ohjausalakoodi,koulutustyyppi,OpiskelijaAvain, kk  FROM  #ont ) X 
WHERE vuosi = @vuosi
AND OpiskelijaAvain IN (SELECT O.OpiskelijaAvain FROM #ont O WHERE  O.hankkeistettu = 1 AND O.oppilaitoskoodi = X.oppilaitoskoodi)
GROUP BY  vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
ORDER BY vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk

-- Uniikki opiskelija ei hankkeistettu
INSERT INTO #summa_uniikki_opiskelija_taulukko ( vuosi,organisaatio, kk, ohjausala, koulutustyyppi, ei_hankeistettuja )
SELECT  vuosi, oppilaitoskoodi, kk, cast(ohjausalakoodi AS int)  ohjausalakoodi ,  koulutustyyppi, count(*) AS ei_hankeistettu_opiskelija
FROM (SELECT DISTINCT vuosi, oppilaitoskoodi,ohjausalakoodi,koulutustyyppi,OpiskelijaAvain, kk  FROM  #ont ) X 
WHERE vuosi = @vuosi
AND OpiskelijaAvain NOT IN (SELECT O.OpiskelijaAvain FROM #ont O WHERE  O.hankkeistettu = 1 AND O.oppilaitoskoodi = X.oppilaitoskoodi)
GROUP BY  vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
ORDER BY vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk


-- AMK hankkeistettu 15 op ylittävät
INSERT INTO #summa_opintopisteet_taulukko ( vuosi,organisaatio, kk, ohjausala, koulutustyyppi, hankeistettuja )
SELECT  vuosi, oppilaitoskoodi, kk, cast(ohjausalakoodi AS int)  ohjausalakoodi ,  koulutustyyppi, count(*) AS hankeistettu_15
FROM (    SELECT  DISTINCT vuosi, oppilaitoskoodi,ohjausalakoodi,koulutustyyppi,OpiskelijaAvain, kk
        FROM  #ont          
        GROUP BY  OpiskelijaAvain, vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
        HAVING sum(laajuus) >= 15
        ) X 
WHERE vuosi = @vuosi
AND OpiskelijaAvain IN (SELECT O.OpiskelijaAvain FROM #ont O WHERE  O.hankkeistettu = 1 AND O.oppilaitoskoodi = X.oppilaitoskoodi)
AND koulutustyyppi <> 6
GROUP BY  vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
ORDER BY vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk

-- AMK ei hankkeistettu 15 op ylittävät
INSERT INTO #summa_opintopisteet_taulukko ( vuosi,organisaatio, kk, ohjausala, koulutustyyppi, ei_hankeistettuja )
SELECT  vuosi, oppilaitoskoodi, kk, cast(ohjausalakoodi AS int)  ohjausalakoodi,  koulutustyyppi, count(*) AS ei_hankeistettu_15
FROM (    SELECT  DISTINCT vuosi, oppilaitoskoodi,ohjausalakoodi,koulutustyyppi,OpiskelijaAvain, kk
        FROM  #ont          
        GROUP BY  OpiskelijaAvain, vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
        HAVING sum(laajuus) >= 15
        ) X 
WHERE vuosi = @vuosi
AND OpiskelijaAvain NOT IN (SELECT O.OpiskelijaAvain FROM #ont O WHERE  O.hankkeistettu = 1 AND O.oppilaitoskoodi = X.oppilaitoskoodi)
AND koulutustyyppi <> 6
GROUP BY  vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
ORDER BY vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk


-- AMK hankkeistettu 10 op ylittävät
INSERT INTO #summa_opintopisteet_taulukko ( vuosi,organisaatio, kk, ohjausala, koulutustyyppi, hankeistettuja )
SELECT  vuosi, oppilaitoskoodi, kk, cast(ohjausalakoodi AS int)  ohjausalakoodi ,  koulutustyyppi, count(*) AS hankeistettu_15
FROM (    SELECT  DISTINCT vuosi, oppilaitoskoodi,ohjausalakoodi,koulutustyyppi,OpiskelijaAvain, kk
        FROM  #ont          
        GROUP BY  OpiskelijaAvain, vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
        HAVING sum(laajuus) >= 10  AND sum(laajuus) < 15
        ) X 
WHERE vuosi = @vuosi
AND OpiskelijaAvain IN (SELECT O.OpiskelijaAvain FROM #ont O WHERE  O.hankkeistettu = 1 AND O.oppilaitoskoodi = X.oppilaitoskoodi)
AND koulutustyyppi <> 6
AND OpiskelijaAvain IN (
                        SELECT O.avain
                          FROM [Tutkintonimike] T
                                JOIN virta.dbo.Opintosuoritus OS ON OS.TutkintonimikeID = T.id
                                JOIN virta.dbo.opiskelija O ON O.id = OS.OpiskelijaID
                          WHERE T.koodi = '652101'  -- Rakennusmestari 10 op
                          AND  YEAR(OS.suorituspaivamaara) = @vuosi
)


GROUP BY  vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
ORDER BY vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk

-- AMK ei hankkeistettu 10 op ylittävät
INSERT INTO #summa_opintopisteet_taulukko ( vuosi,organisaatio, kk, ohjausala, koulutustyyppi, ei_hankeistettuja )
SELECT  vuosi, oppilaitoskoodi, kk, cast(ohjausalakoodi AS int)  ohjausalakoodi,  koulutustyyppi, count(*) AS ei_hankeistettu_15
FROM (    SELECT  DISTINCT vuosi, oppilaitoskoodi,ohjausalakoodi,koulutustyyppi,OpiskelijaAvain, kk
        FROM  #ont          
        GROUP BY  OpiskelijaAvain, vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
        HAVING sum(laajuus) >= 10  AND sum(laajuus) < 15
        ) X 
WHERE vuosi = @vuosi
AND OpiskelijaAvain NOT IN (SELECT O.OpiskelijaAvain FROM #ont O WHERE  O.hankkeistettu = 1 AND O.oppilaitoskoodi = X.oppilaitoskoodi)
AND koulutustyyppi <> 6
AND OpiskelijaAvain IN (
                        SELECT O.avain
                          FROM [Tutkintonimike] T
                                JOIN virta.dbo.Opintosuoritus OS ON OS.TutkintonimikeID = T.id
                                JOIN virta.dbo.opiskelija O ON O.id = OS.OpiskelijaID
                          WHERE T.koodi = '652101'  -- Rakennusmestari 10 op
                          AND  YEAR(OS.suorituspaivamaara) = @vuosi
)



GROUP BY  vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
ORDER BY vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk


 
-- YAMK hankkeistettu 30 op ylittävät
INSERT INTO #summa_opintopisteet_taulukko ( vuosi,organisaatio, kk, ohjausala, koulutustyyppi, hankeistettuja )
SELECT  vuosi, oppilaitoskoodi, kk, cast(ohjausalakoodi AS int)  ohjausalakoodi ,  koulutustyyppi, count(*) AS hankeistettu_15
FROM (    SELECT  DISTINCT vuosi, oppilaitoskoodi,ohjausalakoodi,koulutustyyppi,OpiskelijaAvain, kk
        FROM  #ont          
        GROUP BY  OpiskelijaAvain, vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
        HAVING sum(laajuus) >= 30
        ) X 
WHERE vuosi = @vuosi
AND OpiskelijaAvain IN (SELECT O.OpiskelijaAvain FROM #ont O WHERE  O.hankkeistettu = 1 AND O.oppilaitoskoodi = X.oppilaitoskoodi)
AND koulutustyyppi = 6
GROUP BY  vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
ORDER BY vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk

-- YAMK ei hankkeistettu 30 op ylittävät
INSERT INTO #summa_opintopisteet_taulukko ( vuosi,organisaatio, kk, ohjausala, koulutustyyppi, ei_hankeistettuja )
SELECT  vuosi, oppilaitoskoodi, kk, cast(ohjausalakoodi AS int)  ohjausalakoodi,  koulutustyyppi, count(*) AS ei_hankeistettu_15
FROM (    SELECT  DISTINCT vuosi, oppilaitoskoodi,ohjausalakoodi,koulutustyyppi,OpiskelijaAvain, kk
        FROM  #ont          
        GROUP BY  OpiskelijaAvain, vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
        HAVING sum(laajuus) >= 30
        ) X 
WHERE vuosi = @vuosi
AND OpiskelijaAvain NOT IN (SELECT O.OpiskelijaAvain FROM #ont O WHERE  O.hankkeistettu = 1 AND O.oppilaitoskoodi = X.oppilaitoskoodi)
AND koulutustyyppi = 6
GROUP BY  vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk
ORDER BY vuosi, oppilaitoskoodi, ohjausalakoodi,  koulutustyyppi , kk




INSERT INTO #summa_taulukko_op ( kk, organisaatio,vuosi,ohjausala,koulutustyyppi, hankeistettuja,ei_hankeistettuja )
SELECT    DISTINCT T.kk, T.organisaatio, T.vuosi, T.ohjausala, T.koulutustyyppi
        , ( SELECT TOP 1 H.hankeistettuja 
            FROM #summa_opintopisteet_taulukko H 
            WHERE H.organisaatio = T.organisaatio 
            AND H.vuosi = T.vuosi
            AND H.ohjausala = T.ohjausala 
            AND H.koulutustyyppi = T.koulutustyyppi 
            AND H.hankeistettuja > 0
            ORDER BY H.hankeistettuja
            ) hankeistettuja

        , ( SELECT TOP 1 H.ei_hankeistettuja 
            FROM #summa_opintopisteet_taulukko H 
            WHERE H.organisaatio = T.organisaatio 
            AND H.vuosi = T.vuosi
            AND H.ohjausala = T.ohjausala 
            AND H.koulutustyyppi = T.koulutustyyppi 
            AND H.ei_hankeistettuja > 0
            ORDER BY H.ei_hankeistettuja
            ) ei_hankeistettuja

FROM    #summa_opintopisteet_taulukko T
ORDER BY T.organisaatio, T.ohjausala, T.koulutustyyppi


INSERT INTO #summa_taulukko_uniikki ( kk, organisaatio,vuosi,ohjausala,koulutustyyppi, hankeistettuja,ei_hankeistettuja )
SELECT    DISTINCT T.kk, T.organisaatio, T.vuosi, T.ohjausala, T.koulutustyyppi
        , ( SELECT TOP 1 H.hankeistettuja 
            FROM #summa_uniikki_opiskelija_taulukko H 
            WHERE H.organisaatio = T.organisaatio 
            AND H.vuosi = T.vuosi
            AND H.ohjausala = T.ohjausala 
            AND H.koulutustyyppi = T.koulutustyyppi 
            AND H.hankeistettuja > 0
            ORDER BY H.hankeistettuja
            ) hankeistettuja

        , ( SELECT TOP 1 H.ei_hankeistettuja 
            FROM #summa_uniikki_opiskelija_taulukko H 
            WHERE H.organisaatio = T.organisaatio 
            AND H.vuosi = T.vuosi
            AND H.ohjausala = T.ohjausala 
            AND H.koulutustyyppi = T.koulutustyyppi 
            AND H.ei_hankeistettuja > 0
            ORDER BY H.ei_hankeistettuja
            ) ei_hankeistettuja

FROM    #summa_uniikki_opiskelija_taulukko T
ORDER BY T.organisaatio, T.ohjausala, T.koulutustyyppi




DELETE FROM Opinnaytetyot_summa_taulukko_opintopiste WHERE vuosi = @vuosi

INSERT INTO Opinnaytetyot_summa_taulukko_opintopiste ( kk, organisaatio,vuosi,ohjausala,koulutustyyppi, hankeistettuja,ei_hankeistettuja )
SELECT kk, organisaatio,vuosi,ohjausala,koulutustyyppi, hankeistettuja,ei_hankeistettuja  
FROM #summa_taulukko_op
ORDER BY  organisaatio, ohjausala,  koulutustyyppi , kk, vuosi 



DELETE FROM Opinnaytetyot_summa_taulukko_opiskelija WHERE vuosi = @vuosi

INSERT INTO Opinnaytetyot_summa_taulukko_opiskelija ( kk, organisaatio,vuosi,ohjausala,koulutustyyppi, hankeistettuja,ei_hankeistettuja )
SELECT kk, organisaatio,vuosi,ohjausala,koulutustyyppi, hankeistettuja,ei_hankeistettuja  
FROM #summa_taulukko_uniikki
ORDER BY organisaatio, ohjausala,  koulutustyyppi , kk, vuosi 


DROP TABLE #ont
 
DROP TABLE #summa_taulukko_uniikki
DROP TABLE #summa_taulukko_op 
DROP TABLE #summa_uniikki_opiskelija_taulukko
DROP TABLE #summa_opintopisteet_taulukko