Avoimen ammattikorkeakoulun opiskelijat

                                
DECLARE @YEAR varchar(4) 

SET @YEAR = 2015        

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

DECLARE @lop_pvm  date         
SET @lop_pvm = @YEAR+'-12-31'    


/* Välitaulu kaikille 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
        )
        

/* Kaikki opiskelijat,  opiskeluoikeustyypit (oot.koodi), 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 Opiskeluoikeus oo
                JOIN Opiskeluoikeuden_tyyppi AS oot ON oo.Op_oikeuden_tyyppiID = oot.id
                JOIN Op_oikeuteen_liittyva_tila AS oolt ON oolt.OpiskeluoikeusID = oo.id
                JOIN 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

        
--TRUNCATE TABLE     amk7_Opiskelijat

--INSERT INTO amk7_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
        , Y.db AS kk
        , @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

        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 Op_oikeuteen_liittyva_tila AS oolt ON oolt.OpiskeluoikeusID = oo.id
        JOIN Opiskeluoikeuden_tila AS ooti ON oolt.Op_oikeuden_tilaID = ooti.ID         

        JOIN Yhteiset.Asetukset.Instanssit Y ON Y.DB = H.DB        
        JOIN Yhteiset.Koodistot.Organisaatio_Oppilaitostyyppi YH ON YH.Organisaatio_koodi = Y.OrganisaatioKoodi 
                
WHERE    OT.Koodi = '13'        /* Avoin kk oikeustyyppi */
AND        YH.Oppilaitostyyppi_koodi = '41'  /* AMK */

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

AND        ISNULL(OJ.Paattymispaivamaara, '2112-12-21') >= @alk_pvm  
AND        OJ.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

-- Opiskelijalla ei saa olla olemassa perustutkintoon johtavaa opiskeluoikeutta tältä ajalta
AND NOT EXISTS (    SELECT * 
                    FROM #Tmp_Opiskelijat 
                    WHERE oid = oo.ID   
                    AND koodi in ('1','2','3', '4', '5', '6', '7', '9')     ) 


AND O.ID NOT IN (        SELECT T3.id
                        FROM #Tmp_Opiskelijat T3
                        WHERE T3.koodi in ('13')
                        
                        AND T3.id IN (    SELECT T4.id
                                        FROM #Tmp_Opiskelijat  T4
                                        WHERE T4.id = o.ID  
                                        AND T4.koodi in ('1','2','3', '4', '5', '6', '7', '9')  
                                        AND T4.OO_Alkamispaivamaara < @alk_pvm 
                                        AND T4.OO_Paattymispaivamaara > @lop_pvm                                               
                                    )                                    
                )        

                    
DROP TABLE #Tmp_Opiskelijat