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

Virran tarkistustiedostot

Edistymisen seurannan tarkistustiedostot ajetaan joka yö VIRTASFTP\KORKEAKOULU\kela hakemistoon:
(12345=oppilaitosnro)

S12345_tyhja_pyyntotiedosto.txt

 - Tyhjä Kelan tietuemallin mukainen jota voi käyttää vertailuun entiseen tapaan omista järjestelmistä

S12345.txt   

 - Virrasta tiedoilla täydennetty pyyntötiedosto, eli pisteet, alarajapäivämäärä

loki_kela_edistseuranta_KORKEAKOULU.txt   

 - Sisältää korkeakoulukohtaisen tiedon minkä tiedonsiirtoajankohdan mukaan op laskettu

KELA_edistymisen_tarkistustiedosto_S12345.csv

- Sama sisältö kuin S12345.txt  csv-muodossa + Opiskeilijan Virta- "opiskelijaAvain" "opiskeluoikeusAvain" lisätiedot

KELA_edistymisen_kadonneet_tarkistustiedosto_12345.csv

- Tämä tiedosto puuttuu mikäli "kadonneita" ei ole,  eli tarkoittaa että opiskelijaa ei löydy Virrasta pyyntötiedoston mukaisesti kyseisestä korkeakoulun tiedoista
Tavallinen syy näille tapauksille on että opiskelijalla korkeakoulun Virta tiedoissa ei ole suomalaista hetua mutta Kelan tiedoissa (pyyntötiedostossa) tämä on ollut.

Tärkeitä tarkistettavia tietoja ovat:

- "000" "000" pisteet ja alhaiset pisteet/tukikuukaudet (pisteet koko opiskeluaikana ja seurantalukuvuosi)

- Alarajapäivä (opiskelujen aloituspäivämäärä)

 Jos useampi opiskeluoikeus, pitäisi olla viimeisin ennen 31.7.(kuluva vuosi) aloitettu. Päivittymättömät tiedot (alkuperäinen poimintapäivämäärä sekä pistetiedot tyhjät ja opintojen aloituspäivämäärää puuttuu)

Läsnäolotietojen merkitys opintojen edistymisen seurannassa

  • Opintojen aloituspäivä (Alarajapäivä) ilmoitetaan sen lukukauden mukaisesti jolloin opiskelija on ollut 1. kerran läsnäoleva.
  • Jos opiskelijalla on opiskeluoikeus kahteen tai useampaan tutkintoon, opintojen aloituspäivä ja suoritustiedot ilmoitetaan sen opiskeluoikeuden mukaan missä opinnoissa opiskelija on ollut läsnäoleva seurantalukuvuonna (viimeiseksi aloitetun opiskeluoikeuden mukaan, järjestyksessä perustutkinto, jatkotutkinto, muu opintotukeen oikeuttava opiskeluoikeus).
  • Jos opiskelija on ollut läsnä oleva kahdessa tai useammissa opinnoissa (AMK), opintojen aloituspäivä ja suoritustiedot ilmoitetaan sen opiskeluoikeuden mukaan missä opinnoissa opiskelijalla on enemmän suorituksia (opiskelijan etu).
  • Jos seurantalukuvuonna on läsnäolo opintoihin jotka on aloitettu kevätlukukaudella ilmoitetaan ko. opintojen aloituspäivä ja suoritukset (opiskelijalta ei vaadita vähimmäissuoritusvaatimusta vähintään (20 opintopisteen opintoja) 1. lukuvuodelta)

Tietojen poimintasäännöt

Tuodaan pyyntötiedoston data omaan tauluun:

kela_edistymisen_tiedot
CREATE TABLE [kela_edistymisen_tiedot](
    [rivinro] [int] NOT NULL,
    [siirtotunnus] [varchar](15) NOT NULL,
    [poimintapaiva] [varchar](8) NOT NULL,
    [siirtolaji] [varchar](5) NOT NULL,
    [lahettajatunn] [varchar](2) NOT NULL,
    [oppilaitos] [varchar](5) NOT NULL,
    [koulutusala] [varchar](3) NOT NULL,
    [henkilotunnus] [varchar](11) NOT NULL,
    [sukunimi] [varchar](30) NOT NULL,
    [etunimet] [varchar](30) NOT NULL,
    [ylarajapaiva] [varchar](8) NOT NULL,
    [kuukaudet] [varchar](3) NOT NULL
) 

Pyyntötiedoston mahdolliset korkeakoulun tiedoista ei löydetyt opiskelijat viedään vastaavaan omaan tauluun.

Alkuperäiset rivinro talteen. Jos ei henkilöä löydy viedä sama rivi muuttumattomana pyyntötiedostoon.

 

Haetaan täydennettävät tiedot (opintopisteet, aloituspäivämäärä) ja tallennetaan tauluun:

kela_opintopisteet
CREATE TABLE [kela_opintopisteet](
    [rivinro] [int] NOT NULL,
    [henkilotunnus] [varchar](11) NOT NULL,
    [alarajapaiva] [varchar](8) NOT NULL,
    [opintopisteet1] [varchar](3) NOT NULL,
    [opintopisteet2] [varchar](3) NOT NULL,
    [db] [varchar](8) NOT NULL,
    [regdatum] [smalldatetime] NULL CONSTRAINT [DF_kela_opintopisteet_regdatum]  DEFAULT (getdate()),
    [alarajapaiva_pvm] [date] NULL,
    [opintopisteet1_num] [int] NULL,
    [opintopisteet2_num] [int] NULL,
    [opiskelijaavain] [varchar](100) NULL,
    [opiskeluoikeusavain] [varchar](100) NULL
) 

Täydennettävät tiedot, opintopisteet ja aloituspäivämäärä:

Opintosuoritukset
 -- Täydennetään tiedot kela_opintopisteet tauluun

DECLARE @db varchar(20) = @korkeakoulu 


CREATE TABLE #orgkoodi ( orgkoodi varchar(8) )

INSERT INTO #orgkoodi (orgkoodi)
EXEC sp_get_organisaatio @db     
        
        
CREATE TABLE #muuttujat (
    db varchar(8),
    maximi int,
    minimi int,
    oppilaitostyyppi int
)

INSERT INTO #muuttujat(db,maximi,minimi,Oppilaitostyyppi )
SELECT YI.DB      
      ,  max(ET.rivinro)  AS maximi
      ,  min(ET.rivinro)  AS minimi
      , YO.Oppilaitostyyppi_koodi
FROM [Yhteiset].[Asetukset].[Instanssit] YI
        JOIN [Yhteiset].[Koodistot].[Organisaatio_Oppilaitostyyppi] YO ON YI.OrganisaatioKoodi = YO.Organisaatio_koodi
        JOIN kela_edistymisen_tiedot ET ON ET.siirtotunnus = 'S'+YI.[OrganisaatioKoodi]

WHERE            
        YO.Oppilaitostyyppi_koodi IN (41,42) --amk 41  yo 42
GROUP BY YI.DB,YO.Oppilaitostyyppi_koodi

DECLARE @max_rivinro int
DECLARE @min_rivinro int
DECLARE @kk_tyyppi int

SELECT @max_rivinro = maximi FROM #muuttujat WHERE db=@db
SELECT @min_rivinro = minimi FROM #muuttujat WHERE db=@db
SELECT @kk_tyyppi = Oppilaitostyyppi FROM #muuttujat WHERE db=@db


CREATE TABLE #pisteet (
    opiskelija_id int not null,
    opiskeluoikeus_id int,
    opintosuoritus_id int not null,
    laajuus float not null,
    suoritus_pvm date not null,
    db varchar(20)
)

-- Pyyntötiedostosta löydettyjen hetujen mukaan, alkuperäisen rivien mukaan

CREATE TABLE #tkopisk (
    rivinro int,
    kk varchar(12),-- korkeakoulu
    ht varchar(11),--Henkilötunnus
    kirtu date,--Kirjoihintulopvm 
    sp varchar(1),--Sukupuoli
    snimi nvarchar(255),--Sukunimi
    enimi nvarchar(255),--Etunimet        
    opevuosi float, --yhteensä viime lukuvuonna (1.8.2015 - 31.7.2016)    
    opeyhteensa float,    
    opoik date,--Opiskeluoikeuden alkaminen nykyisessä tutkinnossa    
    koulk varchar(6),--Koulutuskoodi        
    opiskelijaavain varchar(100),
    opiskeluoikeusavain varchar(100),    
    opiskelija_id int,
    opiskeluoikeus_id int,    
    aloituspvm  varchar(10), -- KELA alarajapäivä string
    aloituspvm_datum  date
)


-- Milloin aloittanut, nk. alarajapvm KELAlle
CREATE TABLE #Aloittanut  (
    oid int,
    minAlkamispaivamaara date,
    LAlkamispaivamaara date,
    OAlkamispaivamaara date,
    opiskelija_id int not null
)


-- Milloin aloittanut, nk. alarajapvm KELAlle
CREATE TABLE #Aloittanut_bak  (
    oid int,
    minAlkamispaivamaara date,
    LAlkamispaivamaara date,
    OAlkamispaivamaara date,
    opiskelija_id int not null
)


CREATE TABLE #Min_aloituspvm_bak (
    oid int,
    alk_pvm date
)    
-- Uutta _bak temppitaulut



CREATE TABLE #Min_aloituspvm (
    oid int,
    alk_pvm date
)    

CREATE TABLE #Min_aloituspvm_siirto (
    oid int,
    alk_pvm date
)

-- YO  Yliopistot
IF ( @kk_tyyppi = 42 ) 
BEGIN

            INSERT into #tkopisk (rivinro, kk, ht, sp, snimi, enimi, kirtu, opiskelijaavain, opiskelija_id)
            SELECT        ke.rivinro AS rivinro,
                         kk = @db
                        ,ht = h.Henkilotunnus
                        ,sp = (select top 1 k.Koodi FROM Sukupuoli k WHERE k.ID=h.SukupuoliID)
                        ,snimi = h.Sukunimi
                        ,enimi = h.Etunimet
                        ,kirtu = p.Kirjoihintulopaivamaara
                        ,opiskelijaavain = p.Avain
                        ,opiskelija_id = p.ID
            FROM        Henkilo h
                    JOIN  dbo.Opiskelija p on p.HenkiloID = h.ID
                    JOIN  kela_edistymisen_tiedot Ke ON h.henkilotunnus = Ke.henkilotunnus
            WHERE    1=1
            AND        h.db = @db
            AND        p.db = @db
            AND        ke.rivinro >= @min_rivinro  -- Pienin rivinro
            AND        Ke.rivinro <= @max_rivinro  -- Pitää hakea organisaatio mukaisen suurin rivinro            

            -- tutkinto-oikeus
            AND        p.ID IN (
                            select o.OpiskelijaID
                            FROM Opiskeluoikeus o
                                inner join Opiskeluoikeuden_tyyppi ot on ot.ID=o.Op_oikeuden_tyyppiID
                                 
                            WHERE 1=1
                            AND o.db = @db                
                            -- tutkinto-tyyppinen YO
                            AND ot.Koodi in ('2','4','5','6','7','11','14','18')                    
            )
            
            
            TRUNCATE TABLE #Aloittanut
            

            INSERT INTO #Aloittanut (oid, minAlkamispaivamaara, LAlkamispaivamaara, OAlkamispaivamaara,opiskelija_id ) 
            SELECT    oo.ID,
                    CASE    WHEN MIN(L.Alkamispaivamaara) >=  OO.Alkamispaivamaara  THEN MIN(L.Alkamispaivamaara)
                            ELSE    OO.Alkamispaivamaara
                    END            AS minAlkamispaivamaara

                    ,l.Alkamispaivamaara AS LAlkamispaivamaara
                    ,oo.Alkamispaivamaara    AS OAlkamispaivamaara
                    ,o.id
                    
                    
            
            FROM     dbo.Henkilo H 
                    JOIN dbo.Opiskelija O ON O.HenkiloID = H.ID
                    JOIN dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
                    JOIN Opiskeluoikeuden_tyyppi ot on ot.ID=oo.Op_oikeuden_tyyppiID
                    LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
                    JOIN dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID 
                    INNER JOIN Op_oikeuteen_liittyva_tila olt ON olt.OpiskeluoikeusID=oo.ID
                    INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID=olt.Op_oikeuden_tilaID
                                
            WHERE     LT.Koodi = 1 /* Läsnä */
            AND     ot.Koodi IN ('2','4','5','6','7','11','14','18')
            AND        H.Henkilotunnus IN (SELECT ht FROM #tkopisk)    
            AND        oo.db     = @db
            AND        o.db    = @db
            AND        h.db    = @db
            AND        kt.Koodi = '1' --aktiivinen
            AND     ('2015-08-01')  between olt.Alkamispaivamaara and coalesce(olt.Paattymispaivamaara,'9999-01-01')

            -- 
            AND oo.ID in (                
                            SELECT oe.OpiskeluoikeusID 
                            FROM Op_oikeuden_ensisijaisuus oe                
                            WHERE ('2015-08-01') between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-01-01')
                            AND oe.db = @db
            )                    
            -- Ei vanhoja mukaan 
            -- 
            AND  oo.ID  NOT IN (    SELECT    DISTINCT OO.ID
                                    FROM     Henkilo H 
                                    LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
                                    LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID                                    
                                    WHERE     OO.Paattymispaivamaara < '2015-08-01'    
                                    AND H.db = @db                                
                                    AND O.db = @db                                
                                    AND OO.db = @db
            )
            
            AND        OO.ID NOT IN (   SELECT  DISTINCT OO1.ID       
                                                                    FROM     Henkilo H1
                                                                     JOIN Opiskelija O1 ON O1.HenkiloID = H1.ID
                                                                     JOIN Opiskeluoikeus OO1 ON OO1.OpiskelijaID = O1.ID
                                                                     JOIN Op_oikeuteen_liittyva_tila OOT1 ON OO1.ID = OOT1.OpiskeluoikeusID
                                                                     JOIN Opiskeluoikeuden_tila OT1    ON OT1.ID = OOT1.Op_oikeuden_tilaID
                                                                    WHERE     
                                                                            h1.db     =  @db    
                                                                    AND        o1.db     =  @db        
                                                                    AND        oo1.db     =  @db                                                    
                                                                    AND     OT1.Koodi IN ( 3,4, 5) /* Päättynyt jotenkin */               
                                                                    AND        OOT1.Alkamispaivamaara <= '2015-08-01'                                        
                                        
                                    )

            GROUP BY L.Alkamispaivamaara, oo.Alkamispaivamaara, oo.ID, o.id

            
            TRUNCATE TABLE #Aloittanut_bak                    

            INSERT INTO #Aloittanut_bak (oid, minAlkamispaivamaara, LAlkamispaivamaara, OAlkamispaivamaara,opiskelija_id ) 
            SELECT    oo.ID 
                        ,CASE    WHEN MIN(L.Alkamispaivamaara) >=  OO.Alkamispaivamaara  THEN MIN(L.Alkamispaivamaara)
                                ELSE    OO.Alkamispaivamaara
                        END            AS minAlkamispaivamaara

                        ,l.Alkamispaivamaara AS LAlkamispaivamaara
                        ,oo.Alkamispaivamaara    AS OAlkamispaivamaara
                        ,o.id

            FROM     dbo.Henkilo H 
                        JOIN dbo.Opiskelija O ON O.HenkiloID = H.ID
                        JOIN dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
                        JOIN Opiskeluoikeuden_tyyppi ot on ot.ID=oo.Op_oikeuden_tyyppiID
                        LEFT JOIN Lukukausi_ilmoittautuminen L ON L.OpiskelijaID = O.ID
                        JOIN dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID 
                        INNER JOIN Op_oikeuteen_liittyva_tila olt ON olt.OpiskeluoikeusID=oo.ID
                        INNER JOIN Opiskeluoikeuden_tila kt ON kt.ID=olt.Op_oikeuden_tilaID
        
            WHERE     LT.Koodi = 1 /* Läsnä */
                AND     ot.Koodi IN ('2','4','5','6','7','11','14','18')
                AND        H.Henkilotunnus IN (SELECT ht FROM #tkopisk)    
                AND        oo.db     =  @db    
                AND        o.db    =  @db    
                AND        h.db    =  @db    
                AND        kt.Koodi = '1' --aktiivinen
                AND     ('2015-08-01')  between olt.Alkamispaivamaara and coalesce(olt.Paattymispaivamaara,'9999-01-01')

            AND  oo.ID  NOT IN (    SELECT    DISTINCT OO.ID
                                        FROM     Henkilo H 
                                        LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
                                        LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID                                    
                                        WHERE     OO.Paattymispaivamaara < '2015-08-01'    
                                        AND H.db =  @db                                    
                                        AND O.db =  @db                                
                                        AND OO.db =  @db    
                )

            AND        OO.ID NOT IN (   SELECT  DISTINCT OO1.ID       
                                                                FROM     Henkilo H1
                                                                 JOIN Opiskelija O1 ON O1.HenkiloID = H1.ID
                                                                 JOIN Opiskeluoikeus OO1 ON OO1.OpiskelijaID = O1.ID
                                                                 JOIN Op_oikeuteen_liittyva_tila OOT1 ON OO1.ID = OOT1.OpiskeluoikeusID
                                                                 JOIN Opiskeluoikeuden_tila OT1    ON OT1.ID = OOT1.Op_oikeuden_tilaID
                                                                WHERE     
                                                                        h1.db     =  @db    
                                                                AND        o1.db     =  @db        
                                                                AND        oo1.db     =  @db                                                    
                                                                AND     OT1.Koodi IN ( 3,4, 5) /* Päättynyt jotenkin */               
                                                                AND        OOT1.Alkamispaivamaara <= '2015-08-01'                                        
                                    
                                    )

            GROUP BY L.Alkamispaivamaara, oo.Alkamispaivamaara, oo.ID, o.id                        



            INSERT INTO  #Min_aloituspvm (oid, alk_pvm)
            SELECT oid, MIN(minAlkamispaivamaara)  As alk_pvm
            FROM    #Aloittanut 
            GROUP BY oid
            
                            
            INSERT INTO  #Min_aloituspvm_bak (oid, alk_pvm)
            SELECT oid, MIN(minAlkamispaivamaara)  As alk_pvm
            FROM    #Aloittanut_bak 
            GROUP BY oid
        
            
            UPDATE T
            SET aloituspvm = convert(varchar(12),M.alk_pvm,104),
                aloituspvm_datum =      M.alk_pvm            
            
            FROM #tkopisk T
                    INNER JOIN Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
                    INNER JOIN Opiskeluoikeuden_tyyppi ot on ot.ID=o.Op_oikeuden_tyyppiID
                    INNER JOIN Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=o.ID
                    INNER JOIN Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
                    INNER JOIN #Min_aloituspvm M ON o.ID=M.oid
            WHERE 1=1
            
            AND o.db = @db
            AND lt.db = @db
            
            -- tutkinto-tyyppinen YO
            AND ot.Koodi in ('2','4','5','6','7','11','14','18')
            -- voimassa pvm
            AND ('2015-08-01')  between o.Alkamispaivamaara and coalesce(o.Paattymispaivamaara,'9999-01-01')
            -- tila
            -- NB Katsotaan nyt vain aktiivinen (ei päättynyt). Riski on juuri 1.8. tutkinnon suorittaneet -- ehkä?
            AND kt.Koodi = '1' --aktiivinen
            AND ('2015-08-01')  between lt.Alkamispaivamaara and coalesce(lt.Paattymispaivamaara,'9999-01-01')
        
            -- ensisijainen pvm        
            -- 
            and o.ID in (
                        select oe.OpiskeluoikeusID 
                        FROM Op_oikeuden_ensisijaisuus oe                            
                        WHERE ('2015-08-01') between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-01-01')
                        AND oe.db = @db
            )

            -- Ei vanhoja mukaan 
            -- 
            AND  o.ID  NOT IN (    SELECT    DISTINCT OO.ID
                                    FROM     Henkilo H 
                                    LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
                                    LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID                                    
                                    WHERE     OO.Paattymispaivamaara < '2015-08-01'    
                                    AND H.db = @db                                
                                    AND O.db = @db                                
                                    AND OO.db = @db                                
            )                
                    

            -- valitaan yksi per opiskelija
            UPDATE T
            SET opiskeluoikeus_id=o.ID, opiskeluoikeusavain=o.avain
              , opoik=  o.Alkamispaivamaara 
            FROM #tkopisk T
                inner join Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
                inner join Opiskeluoikeuden_tyyppi ot on ot.ID=o.Op_oikeuden_tyyppiID
                inner join Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=o.ID
                inner join Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
            WHERE 1=1
            AND o.db = @db
            AND lt.db = @db
            -- tutkinto-tyyppinen
            and ot.Koodi in ('2','4','5','6','7','11','14','18')
            -- voimassa pvm
            and  ('2015-08-01') between o.Alkamispaivamaara and coalesce(o.Paattymispaivamaara,'9999-01-01')
            -- tila            
            and kt.Koodi = '1' --aktiivinen
            and  ('2015-08-01') between lt.Alkamispaivamaara and coalesce(lt.Paattymispaivamaara,'9999-01-01')
            -- ensisijainen pvm
            -- 
            and o.ID in (
                        select oe.OpiskeluoikeusID 
                        FROM Op_oikeuden_ensisijaisuus oe                        
                        WHERE  ('2015-08-01') between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-01-01')
                        AND oe.db = @db
            )
            
           

    

            

            IF EXISTS (SELECT * FROM #tkopisk T WHERE T.aloituspvm IS NULL)
            BEGIN
                        UPDATE T
                        SET aloituspvm = convert(varchar(12),M.alk_pvm,104),
                            aloituspvm_datum =      M.alk_pvm        
            
                        FROM #tkopisk T
                                INNER JOIN Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
                                INNER JOIN Opiskeluoikeuden_tyyppi ot on ot.ID=o.Op_oikeuden_tyyppiID
                                INNER JOIN Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=o.ID
                                INNER JOIN Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID
                                INNER JOIN #Min_aloituspvm_bak M ON o.ID=M.oid
                        WHERE    1=1    
                        AND        T.aloituspvm IS NULL                 
                        AND        o.db = @db
                        AND        lt.db = @db
            
                        -- tutkinto-tyyppinen YO
                        AND ot.Koodi in ('2','4','5','6','7','11','14','18')
                        -- voimassa pvm
                        AND ('2015-08-01')  between o.Alkamispaivamaara and coalesce(o.Paattymispaivamaara,'9999-01-01')
                        -- tila
                        -- NB Katsotaan nyt vain aktiivinen (ei päättynyt). Riski on juuri 1.8. tutkinnon suorittaneet -- ehkä?
                        AND kt.Koodi = '1' --aktiivinen
                        AND ('2015-08-01')  between lt.Alkamispaivamaara and coalesce(lt.Paattymispaivamaara,'9999-01-01')

                        -- Ei vanhoja mukaan 
                        -- 
                        AND  o.ID  NOT IN (        SELECT    DISTINCT OO.ID
                                                FROM     Henkilo H 
                                                LEFT JOIN Opiskelija O ON O.HenkiloID = H.ID
                                                LEFT JOIN Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID                                    
                                                WHERE     OO.Paattymispaivamaara < '2015-08-01'    
                                                AND H.db = @db                                
                                                AND O.db = @db                                
                                                AND OO.db = @db                                
                        )    



            END


                                
            UPDATE T
            SET T.opoik=kirtu            
            FROM #tkopisk T
            WHERE T.opiskeluoikeus_id is  null        
            
            
            UPDATE T
            set T.aloituspvm = convert(varchar(12),kirtu,104) ,
                T.aloituspvm_datum =     kirtu
                
            FROM #tkopisk T
            WHERE T.aloituspvm IS  NULL
            
            
            UPDATE T
            SET      T.aloituspvm =     convert(varchar(12), T.opoik ,104)
                    ,T.aloituspvm_datum = T.opoik         
            FROM #tkopisk T
            WHERE     T.opoik > T.aloituspvm_datum

            
            INSERT INTO #pisteet (opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm                                                
                        ,db)
                SELECT
                 opiskelija_id = s.OpiskelijaID
                ,opiskeluoikeus_id = s.OpiskeluoikeusID
                ,opintosuoritus_id = s.ID
                ,laajuus = s.Laajuus
                ,suoritus_pvm = s.Suorituspaivamaara                                
                 , @db AS db
     
                FROM Opintosuoritus s
                inner join Opintosuorituksen_laji sl on sl.ID=s.Opintosuorituksen_lajiID and sl.Koodi IN ('2','3')  --muu opintosuoritus                
                
                WHERE 1=1
                AND     s.db = @db                
                
                -- lehtitaso
                and s.ID not in (select ss.OpintosuoritusID FROM Op_suor_sisaltyvyys ss WHERE ss.db = @db)        
                -- AND s.ID NOT IN (SELECT OH.OpintosuoritusID FROM Opsuorituksen_hyvaksiluku OH WHERE OH.db = @db )


            --
            UPDATE T
            SET opevuosi = (    SELECT sum(laajuus) 
                                FROM  #pisteet s 
                                WHERE         s.opiskelija_id=T.opiskelija_id
                                AND s.suoritus_pvm >= '2015-08-01' AND s.suoritus_pvm <= '2016-07-31'
                                AND s.suoritus_pvm >=  T.aloituspvm_datum
                                
                                GROUP BY s.opiskelija_id )
            FROM #tkopisk T

            --            
            UPDATE T
            SET opeyhteensa= (    SELECT sum(laajuus) 
                                FROM  #pisteet s 
                                WHERE         s.opiskelija_id=T.opiskelija_id
                                AND         s.suoritus_pvm >= T.aloituspvm_datum AND s.suoritus_pvm <= '2016-07-31'                     
                                
                                GROUP BY s.opiskelija_id )                                
            FROM #tkopisk T


            UPDATE T
             set opevuosi = 0 
             FROM #tkopisk T
             WHERE opevuosi is null

            UPDATE T
             set opeyhteensa = 0 
             FROM #tkopisk T
             WHERE opeyhteensa is null

            -- LOPPUTULOS

            DELETE FROM kela_opintopisteet WHERE db = @db

            INSERT INTO kela_opintopisteet(rivinro,henkilotunnus,alarajapaiva,opintopisteet1,opintopisteet2,db,alarajapaiva_pvm,opintopisteet1_num,opintopisteet2_num, opiskelijaavain, opiskeluoikeusavain)

            SELECT    rivinro         
                    , ISNULL(ht,'')       "henkilotunnus" -- (ht)                    
                    , ISNULL(replace(aloituspvm,'.',''),'        ')     "alarajapaiva"         -- 8 tyhjää jos ei arvoa                 
                    , right('000' + ISNULL(cast(ceiling(opeyhteensa) as varchar(3)),''), 3) AS opintopisteet1
                    , right('000' + ISNULL(cast(ceiling(opevuosi) as varchar(3)),''), 3) AS opintopisteet2
                    , ISNULL(kk,'')  as db  -- (kk)
                    , aloituspvm_datum AS alarajapaiva_pvm
                    , ceiling(opeyhteensa) AS opintopisteet1_num
                    , ceiling(opevuosi) AS opintopisteet2_num
                    , opiskelijaavain, opiskeluoikeusavain

            FROM    #tkopisk T            
            ORDER BY rivinro                                    
                    
END


-- ================================================================================================================
-- AMK  Ammattikorkeakoulu
IF ( @kk_tyyppi = 41 ) 
BEGIN        
            TRUNCATE TABLE #tkopisk

            INSERT into #tkopisk (rivinro, kk, ht, sp, snimi, enimi, kirtu, opiskelijaavain, opiskelija_id)
            SELECT        DISTINCT ke.rivinro AS rivinro,
                         kk = @db
                        ,ht = h.Henkilotunnus
                        ,sp = (select top 1 k.Koodi FROM Sukupuoli k WHERE k.ID=h.SukupuoliID)
                        ,snimi = h.Sukunimi
                        ,enimi = h.Etunimet
                        ,kirtu = p.Kirjoihintulopaivamaara
                        ,opiskelijaavain = p.Avain
                        ,opiskelija_id = p.ID
            FROM        Henkilo h
                    JOIN dbo.Opiskelija p on p.HenkiloID=h.ID
                    JOIN  kela_edistymisen_tiedot Ke ON h.henkilotunnus = Ke.henkilotunnus
            WHERE    1=1
            AND        h.db = @db
            AND        p.db = @db
            AND        ke.rivinro >= @min_rivinro  -- Pienin rivinro
            AND        Ke.rivinro <= @max_rivinro  -- Pitää hakea organisaatio mukaisen suurin rivinro        
            
            -- tutkinto-oikeus, erikoistumisopinnot ja ammatillinen opettajakoulutus
            AND        p.ID in (
                                select o.OpiskelijaID
                                FROM Opiskeluoikeus o
                                    inner join Opiskeluoikeuden_tyyppi ot on ot.ID=o.Op_oikeuden_tyyppiID
                                    
                                WHERE 1=1
                                AND o.db = @db                                                
                                    -- tutkinto-tyyppinen, erikoistumisopinnot ja ammatillinen opettajakoulutus
                                
                                AND        ot.Koodi IN ('1','3','10','12','15','17','19')    
                                        
            )
                            
                    
            TRUNCATE TABLE #Aloittanut
    

            INSERT INTO #Aloittanut (oid, minAlkamispaivamaara, LAlkamispaivamaara, OAlkamispaivamaara,opiskelija_id ) 
            SELECT    oo.ID,
                    CASE    WHEN MIN(L.Alkamispaivamaara) >=  OO.Alkamispaivamaara  THEN MIN(L.Alkamispaivamaara)
                            ELSE    OO.Alkamispaivamaara
                    END            AS minAlkamispaivamaara

                    ,l.Alkamispaivamaara AS LAlkamispaivamaara
                    ,oo.Alkamispaivamaara     AS OAlkamispaivamaara
                    , o.id        
            
            FROM     dbo.Henkilo H 
                JOIN     dbo.Opiskelija O ON O.HenkiloID = H.ID
                JOIN     dbo.Opiskeluoikeus OO ON OO.OpiskelijaID = O.ID
                JOIN    dbo.Opiskeluoikeuden_tyyppi ot on ot.ID=oo.Op_oikeuden_tyyppiID
                JOIN     dbo.Lukukausi_ilmoittautuminen L ON L.OpiskeluoikeusID = OO.ID
                JOIN     dbo.Lukukausi_ilm_tila LT ON LT.ID = L.Lukukausi_ilmoittautumisen_tilaID
            
            WHERE     LT.Koodi = 1 /* Läsnä */
                                        
            AND        ot.Koodi IN ('1','3','10','12','15','17','19')    
                
            AND        h.db     = @db
            AND        o.db     = @db    
            AND        oo.db     = @db    
            AND        L.db     = @db    
            AND        H.Henkilotunnus IN (SELECT ht FROM #tkopisk)        
            AND        oo.Alkamispaivamaara <= '2016-07-31'
            AND        L.Alkamispaivamaara <= '2016-07-31'

            -- Ei vanhoja mukaan 
            -- 
            AND        oo.ID  NOT IN (    SELECT    DISTINCT OO2.ID
                                    FROM     Henkilo H2 
                                    LEFT JOIN Opiskelija O2 ON O2.HenkiloID = H2.ID
                                    LEFT JOIN Opiskeluoikeus OO2 ON OO2.OpiskelijaID = O2.ID                                    
                                    WHERE     OO2.Paattymispaivamaara < '2015-08-01'
                                    AND        h2.db     = @db
                                    AND        o2.db     = @db    
                                    AND        oo2.db     = @db                            
            )
            
            AND        OO.ID NOT IN (   SELECT  DISTINCT OO1.ID       
                                        FROM     Henkilo H1
                                         JOIN Opiskelija O1 ON O1.HenkiloID = H1.ID
                                         JOIN Opiskeluoikeus OO1 ON OO1.OpiskelijaID = O1.ID
                                         JOIN Op_oikeuteen_liittyva_tila OOT1 ON OO1.ID = OOT1.OpiskeluoikeusID
                                         JOIN Opiskeluoikeuden_tila OT1    ON OT1.ID = OOT1.Op_oikeuden_tilaID
                                        WHERE     
                                                h1.db     = @db
                                        AND        o1.db     = @db    
                                        AND        oo1.db     = @db                                                    
                                        AND     OT1.Koodi IN ( 3, 5) /* Päättynyt jotenkin */               
                                        AND        OOT1.Alkamispaivamaara <= '2015-08-01'                                        
                                        
                                    )
                                                    
            /* Pitää olla läsnäoleva että saadaan oikea opiskeluoikeus ja sen aloituspvm */
            AND oo.ID IN (  SELECT    OpiskeluoikeusID AS id
                            FROM    Lukukausi_ilmoittautuminen l
                                    INNER JOIN Lukukausi_ilm_tila lt ON lt.ID=l.Lukukausi_ilmoittautumisen_tilaID
                            WHERE    lt.Koodi='1' --läsnäolo tältä ajalta
                            AND        l.db = @db    
                            --AND        l.alkamispaivamaara        >= '2015-08-01'
                            AND        l.Paattymispaivamaara    <= '2016-07-31'                                        
            )

            GROUP BY L.Alkamispaivamaara, oo.Alkamispaivamaara, oo.ID, o.id        
            

            TRUNCATE TABLE #Min_aloituspvm
            

            INSERT INTO  #Min_aloituspvm (oid, alk_pvm)
            SELECT oid, MIN(minAlkamispaivamaara)  As alk_pvm
            FROM    #Aloittanut 
            GROUP BY oid            
            

            INSERT INTO #Min_aloituspvm_siirto (oid, alk_pvm)
            SELECT MA.oid,
                CASE    WHEN MA.alk_pvm >=  OO.Siirtopaivamaara  THEN OO.Siirtopaivamaara                                
                    ELSE    MA.alk_pvm
                    END            
                    AS alk_pvm    

            FROM #Min_aloituspvm MA
                    JOIN VIRTA.dbo.Opiskeluoikeus OO ON MA.oid = OO.id
            WHERE OO.Alkamispaivamaara <= '2016-07-31'
             
            
            UPDATE #Min_aloituspvm  
            SET alk_pvm = MT.alk_pvm
            FROM #Min_aloituspvm_siirto MT
            WHERE #Min_aloituspvm.oid = MT.oid
            


            UPDATE T
            SET      opiskeluoikeus_id = o.ID
                , opiskeluoikeusavain = o.avain
                , opoik= o.Alkamispaivamaara 
                , aloituspvm = convert(varchar(12), M.alk_pvm,104)
                , aloituspvm_datum =      M.alk_pvm 
            
            FROM #tkopisk T
                    join Opiskeluoikeus o on o.OpiskelijaID=T.opiskelija_id
                    join  #Min_aloituspvm M  ON M.oid = o.id 

            WHERE o.db     = @db        
                
            
            UPDATE T
            set T.opoik=kirtu
            FROM #tkopisk T
            WHERE T.opiskeluoikeus_id is  null
            

            UPDATE T
            set T.aloituspvm = convert(varchar(12),kirtu,104),
                T.aloituspvm_datum =     kirtu
            FROM #tkopisk T
            WHERE T.aloituspvm IS  NULL
            
            
                             

                            
            
            UPDATE T
            SET      T.aloituspvm =     convert(varchar(12), T.opoik ,104)
                    ,T.aloituspvm_datum = T.opoik         
            FROM #tkopisk T
            WHERE     T.opoik > T.aloituspvm_datum            
        

            INSERT INTO #pisteet (opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm                                                
                        ,db)
            SELECT
                opiskelija_id = s.OpiskelijaID
            ,opiskeluoikeus_id = s.OpiskeluoikeusID
            ,opintosuoritus_id = s.ID
            ,laajuus = s.Laajuus
            ,suoritus_pvm = s.Suorituspaivamaara    
                , @db AS db
     
            FROM Opintosuoritus s
            inner join Opintosuorituksen_laji sl on sl.ID=s.Opintosuorituksen_lajiID and sl.Koodi IN ('2','3')  --muu opintosuoritus            
                            
            WHERE 1=1
            AND s.db = @db                
            AND s.ID not in (select ss.OpintosuoritusID FROM Op_suor_sisaltyvyys ss WHERE ss.db = @db)
            
            
 
            UPDATE T
            SET opevuosi = (    SELECT sum(laajuus) 
                                FROM  #pisteet s 
                                WHERE         s.opiskeluoikeus_id=T.opiskeluoikeus_id
                                AND s.suoritus_pvm >= '2015-08-01' AND s.suoritus_pvm <= '2016-07-31'
                                AND s.suoritus_pvm >=  T.aloituspvm_datum
                                
                                GROUP BY s.opiskeluoikeus_id )
            FROM #tkopisk T


            UPDATE T
            SET opeyhteensa= (    SELECT sum(laajuus) 
                                FROM  #pisteet s 
                                WHERE         s.opiskeluoikeus_id=T.opiskeluoikeus_id
                                AND         s.suoritus_pvm >= T.aloituspvm_datum AND s.suoritus_pvm <= '2016-07-31'         
                                
                                GROUP BY s.opiskeluoikeus_id )                                
            FROM #tkopisk T            
 

            --

            UPDATE T
            SET opevuosi = (    SELECT sum(laajuus) 
                                FROM  #pisteet s 
                                WHERE         s.opiskelija_id=T.opiskelija_id
                                AND s.suoritus_pvm >= '2015-08-01' AND s.suoritus_pvm <= '2016-07-31'
                                AND s.suoritus_pvm >=  T.aloituspvm_datum
                                
                                GROUP BY s.opiskelija_id )
            FROM #tkopisk T
            --WHERE T.opiskeluoikeus_id IS NULL
            WHERE (T.opevuosi = 0 OR T.opevuosi is null)
 

            UPDATE T
            SET opeyhteensa= (    SELECT sum(laajuus) 
                                FROM  #pisteet s 
                                WHERE         s.opiskelija_id=T.opiskelija_id
                                AND         s.suoritus_pvm >= T.aloituspvm_datum AND s.suoritus_pvm <= '2016-07-31'         
                                
                                GROUP BY s.opiskelija_id )                                
            FROM #tkopisk T            
            --WHERE T.opiskeluoikeus_id IS NULL
            WHERE (T.opeyhteensa = 0 OR T.opeyhteensa is null)

            --
    
             UPDATE T
             set opevuosi = 0 
             FROM #tkopisk T
             WHERE opevuosi is null

             UPDATE T
             set opeyhteensa = 0 
             FROM #tkopisk T
             WHERE opeyhteensa is null


            -- 


            -- Mitä tulikaan tehtyä? Lokitetaanpas vähän    
            INSERT INTO kela_loki( hetu,opiskelijaid,opiskeluoikeusid,viesti,rivi,db,reg_datum)                    
            SELECT ht, opiskelija_id, opiskeluoikeus_id, 'opiskeluavain NULL poistettu' AS viesti, rivinro, kk, getdate() As reg_datum
            FROM #tkopisk  WHERE opiskeluoikeusavain IS NULL

        
            -- Poistetaanko kaikki ilman opiskelusoikeusavainta? Miksi ei ole avainta?
            DELETE FROM #tkopisk WHERE opiskeluoikeusavain IS NULL                

            -- On useampi opiskeluoikeus
            CREATE TABLE #kela_tuplat (
                                        ht varchar(15),
                                        opiskeluoikeus_id int,
                                        opevuosi float,
                                        opeyhteensa float,
                                        aloituspvm_datum date                            
                                        )
            
            -- Haetaan tuplat
            INSERT INTO #kela_tuplat (ht, opiskeluoikeus_id, opevuosi, opeyhteensa, aloituspvm_datum)
            SELECT ht, opiskeluoikeus_id, opevuosi, opeyhteensa, aloituspvm_datum FROM #tkopisk
            WHERE ht IN (SELECT ht FROM #tkopisk GROUP BY ht HAVING count(*) > 1 )

            -- Viimeisin alkanut opiskeluoikeus
            CREATE TABLE #kela_viimeisin_alkpvm (                        
                                                    henkilotunnus varchar(11) NOT NULL,                        
                                                    db varchar(8) NOT NULL,
                                                    viimeisin_alkpvm date 
                                                )
            -- Vain tuplat having count(*) > 1
            INSERT INTO #kela_viimeisin_alkpvm (henkilotunnus, db, viimeisin_alkpvm)
            SELECT ht AS henkilotunnus, kk AS db, max(aloituspvm_datum) AS viimeisin_alkpvm
            FROM #tkopisk
            WHERE aloituspvm_datum IS NOT NULL
            GROUP BY ht, kk
            HAVING count(*) > 1

            -- Poistettavat opiskeluoikeudet
            CREATE TABLE #kela_poistettavat ( opiskeluoikeus_id int )

            INSERT INTO #kela_poistettavat (opiskeluoikeus_id)
            SELECT opiskeluoikeus_id FROM #kela_tuplat KT
                        JOIN #kela_viimeisin_alkpvm KA ON KA.henkilotunnus = KT.ht
            WHERE    KT.aloituspvm_datum <  KA.viimeisin_alkpvm
            ORDER BY KT.ht

            -- Mitkä poistetaan lokitetaan
            INSERT INTO kela_loki( hetu,opiskelijaid,opiskeluoikeusid,viesti,rivi,db,reg_datum)                    
            SELECT ht, opiskelija_id, opiskeluoikeus_id, 'tuplat vanhempi oikeusid poistettu ' + cast( aloituspvm_datum as varchar(20) ) AS viesti, rivinro, kk, getdate() As reg_datum
            FROM #tkopisk  WHERE opiskeluoikeus_id IN (SELECT opiskeluoikeus_id FROM #kela_poistettavat)

            -- Poistetaan
            DELETE FROM #tkopisk WHERE opiskeluoikeus_id IN (SELECT opiskeluoikeus_id FROM #kela_poistettavat)    

            -- Vielä onko tuplia joilla sama alkupvm, poistetaan vanhat ja haetaan uudestaan
            TRUNCATE TABLE #kela_tuplat
            TRUNCATE TABLE #kela_poistettavat 
            
            -- Vain tuplat having count(*) > 1                                    
            INSERT INTO #kela_tuplat (ht, opiskeluoikeus_id, opevuosi, opeyhteensa, aloituspvm_datum)
            SELECT ht, opiskeluoikeus_id, opevuosi, opeyhteensa, aloituspvm_datum FROM #tkopisk
            WHERE ht IN (SELECT ht FROM #tkopisk GROUP BY ht HAVING count(*) > 1 )

            CREATE TABLE #kela_enemman_opevuosi (                        
                                                    henkilotunnus varchar(11) NOT NULL,                        
                                                    db varchar(8) NOT NULL,
                                                    more_opevuosi float
                                                )

            -- Enemmän pisteitä    Vain tuplat having count(*) > 1
            INSERT INTO #kela_enemman_opevuosi (henkilotunnus, db, more_opevuosi)
            SELECT ht AS henkilotunnus, kk AS db, max(opevuosi) AS more_opevuosi
            FROM #tkopisk
            WHERE aloituspvm_datum IS NOT NULL
            GROUP BY ht, kk
            HAVING count(*) > 1


            INSERT INTO #kela_poistettavat (opiskeluoikeus_id)
            SELECT opiskeluoikeus_id FROM #kela_tuplat KT
                        JOIN #kela_enemman_opevuosi KA ON KA.henkilotunnus = KT.ht
            WHERE    KT.opevuosi <  KA.more_opevuosi
            ORDER BY KT.ht


            -- Lokitetaan mitkä poistettiin
            INSERT INTO kela_loki( hetu,opiskelijaid,opiskeluoikeusid,viesti,rivi,db,reg_datum)                    
            SELECT ht, opiskelija_id, opiskeluoikeus_id, 'tuplat vähemmän pisteitä oikeusid poistettu ' + cast( opevuosi as varchar(10) ) AS viesti, rivinro, kk, getdate() As reg_datum
            FROM #tkopisk  WHERE opiskeluoikeus_id IN (SELECT opiskeluoikeus_id FROM #kela_poistettavat)

            -- Poistetaan tuplat joilla vähemmän pisteitä
            DELETE FROM #tkopisk WHERE opiskeluoikeus_id IN (SELECT opiskeluoikeus_id FROM #kela_poistettavat)    

            -- LOPPUTULOS

            DELETE FROM kela_opintopisteet WHERE db = @db

            INSERT INTO kela_opintopisteet(rivinro,henkilotunnus,alarajapaiva,opintopisteet1,opintopisteet2,db,alarajapaiva_pvm,opintopisteet1_num,opintopisteet2_num, opiskelijaavain, opiskeluoikeusavain)

            SELECT    rivinro         
                    , ISNULL(ht,'')       "henkilotunnus" -- (ht)                    
                    , ISNULL(replace(aloituspvm,'.',''),'        ')     "alarajapaiva"         -- 8 tyhjää jos ei arvoa                 
                    , right('000' + ISNULL(cast(ceiling(opeyhteensa) as varchar(3)),''), 3) AS opintopisteet1
                    , right('000' + ISNULL(cast(ceiling(opevuosi) as varchar(3)),''), 3) AS opintopisteet2
                    , ISNULL(kk,'')  as db  -- (kk)
                    , aloituspvm_datum AS alarajapaiva_pvm
                    , ceiling(opeyhteensa) AS opintopisteet1_num
                    , ceiling(opevuosi) AS opintopisteet2_num
                    , opiskelijaavain, opiskeluoikeusavain

            FROM    #tkopisk T            
            ORDER BY rivinro                        
                    

    DROP TABLE #kela_tuplat
    DROP TABLE #kela_viimeisin_alkpvm
    DROP TABLE #kela_poistettavat
    DROP TABLE #kela_enemman_opevuosi




END

-- Otetaan kadonneet  talteen
INSERT INTO [kela_lost_souls] (rivinro,siirtotunnus,poimintapaiva,siirtolaji,lahettajatunn,oppilaitos,koulutusala,henkilotunnus,sukunimi,etunimet,ylarajapaiva,kuukaudet, kk, reg_datum)
SELECT rivinro,siirtotunnus,poimintapaiva,siirtolaji,lahettajatunn,oppilaitos,koulutusala,henkilotunnus,sukunimi,etunimet,ylarajapaiva,kuukaudet, @db, getdate()  
FROM kela_edistymisen_tiedot WHERE oppilaitos = (SELECT orgkoodi FROM  #orgkoodi) AND henkilotunnus NOT IN (SELECT ht FROM #tkopisk)    




DROP TABLE #muuttujat    
DROP TABLE #tkopisk    
DROP TABLE #Aloittanut
DROP TABLE #Aloittanut_bak
DROP TABLE #Min_aloituspvm
DROP TABLE #Min_aloituspvm_bak
DROP TABLE #pisteet    
DROP TABLE #Min_aloituspvm_siirto    
DROP TABLE #orgkoodi




Lopputulos Kela tietuemallin mukainen ja csv-tarkistustiedosto jossa OpiskelijaAvain ja OpiskeluOikeusAvain tiedot:

Lopputulos
SELECT    ke.siirtotunnus
        ,'T' AS tietuetyyppi
        , replace(convert(varchar(12),getdate(),104),'.','')   AS poimintapaiva
        ,ke.siirtolaji
        ,ke.lahettajatunn
        ,ke.oppilaitos
        ,ke.koulutusala
        ,ke.henkilotunnus
        ,ke.sukunimi
        ,ke.etunimet
        ,ko.alarajapaiva
        ,ke.ylarajapaiva
        ,ko.opintopisteet1
        ,ko.opintopisteet2
        ,ke.kuukaudet
        ,ko.rivinro
FROM [kela_opintopisteet] ko
        JOIN [kela_edistymisen_tiedot] ke ON ke.rivinro = ko.rivinro
WHERE ko.db = @korkeakoulu
UNION
SELECT
        ks.siirtotunnus
        ,'T' AS tietuetyyppi
        ,ks.poimintapaiva
        ,ks.siirtolaji
        ,ks.lahettajatunn
        ,ks.oppilaitos
        ,ks.koulutusala
        ,ks.henkilotunnus
        ,ks.sukunimi
        ,ks.etunimet
        ,'        ' AS alarajapaiva
        ,ks.ylarajapaiva
        ,'   ' AS opintopisteet1
        ,'   ' AS opintopisteet2
        ,ks.kuukaudet
        ,ks.rivinro
FROM  [kela_lost_souls] ks
WHERE ks.kk = @korkeakoulu
ORDER BY rivinro    



--CSV-tarkistustiedostoon opiskelijaavain ja opiskeluoikeusavain
SELECT    ke.siirtotunnus
        ,'T' AS tietuetyyppi
        , replace(convert(varchar(12),getdate(),104),'.','')   AS poimintapaiva
        ,ke.siirtolaji
        ,ke.lahettajatunn
        ,ke.oppilaitos
        ,ke.koulutusala
        ,ke.henkilotunnus
        ,ke.sukunimi
        ,ke.etunimet
        ,ko.alarajapaiva
        ,ke.ylarajapaiva
        ,ko.opintopisteet1
        ,ko.opintopisteet2
        ,ke.kuukaudet
        ,ko.rivinro
        ,ko.opiskelijaavain
        ,ko.opiskeluoikeusavain    
FROM [kela_opintopisteet] ko
        JOIN [kela_edistymisen_tiedot] ke ON ke.rivinro = ko.rivinro
WHERE ko.db = @mydb
UNION
SELECT
        ks.siirtotunnus
        ,'T' AS tietuetyyppi
        ,ks.poimintapaiva
        ,ks.siirtolaji
        ,ks.lahettajatunn
        ,ks.oppilaitos
        ,ks.koulutusala
        ,ks.henkilotunnus
        ,ks.sukunimi
        ,ks.etunimet
        ,'        ' AS alarajapaiva
        ,ks.ylarajapaiva
        ,'   ' AS opintopisteet1
        ,'   ' AS opintopisteet2
        ,ks.kuukaudet
        ,ks.rivinro
        ,'' AS opiskelijaavain
        ,'' AS opiskeluoikeusavain
FROM  [kela_lost_souls] ks
WHERE ks.kk = @mydb
ORDER BY rivinro    





  • No labels