Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagesql
titleSQL
linenumberstrue
collapsetrue
-- Temp-taulu tietojen valintaa varten
-- NB! param-taulussa saa olla vain yksi rivi!
IF OBJECT_ID(''tempdb..#param'') IS NOT NULL
BEGIN
	truncate table #param
	drop table #param
	if @debug<>0 print convert(varchar,getdate(),120)+'' DROP temp-taulu param''
END
;
create table #param (
	vuosi varchar(4),
	org varchar(30),--KK-lyhenne (DB-nimi)
	kk varchar(2),--Yliopisto (positiot 1-2)
	tilpvm date,--Tilastointipäivä
)
if @debug<>0 print convert(varchar,getdate(),120)+'' CREATE temp-taulu param''
;
insert into #param (vuosi,org,kk,tilpvm)
	select
	@vuosi as vuosi
	,UPPER(DB_NAME()) as org
	,(case
	  when UPPER(DB_NAME()) = ''AA'' then ''03''
	  when UPPER(DB_NAME()) = ''AYO'' then ''25''
	  when UPPER(DB_NAME()) = ''HY'' then ''01''
	  when UPPER(DB_NAME()) = ''ISY'' then ''26''
	  when UPPER(DB_NAME()) = ''JY'' then ''06''
	  when UPPER(DB_NAME()) = ''LAY'' then ''21''
	  when UPPER(DB_NAME()) = ''LTY'' then ''14''
	  when UPPER(DB_NAME()) = ''OY'' then ''04''
	  when UPPER(DB_NAME()) = ''SHH'' then ''10''
	  when UPPER(DB_NAME()) = ''TAIY'' then ''28''
	  when UPPER(DB_NAME()) = ''TaY'' then ''05''
	  when UPPER(DB_NAME()) = ''TTY'' then ''15''
	  when UPPER(DB_NAME()) = ''TY'' then ''27''
	  when UPPER(DB_NAME()) = ''VY'' then ''13''
	  else ''NA''
	  end
	) as kk
	,@vuosi+''-09-20'' as tilpvm
;
--select * from #param


-- Temp-taulu tiedonkeruun tietoja varten
IF OBJECT_ID(''tempdb..#tkopisk'') IS NOT NULL
BEGIN
	truncate table #tkopisk
	drop table #tkopisk
	if @debug<>0 print convert(varchar,getdate(),120)+'' DROP temp-taulu tkopisk''
END
;
create table #tkopisk (
	kk varchar(2),--Yliopisto (positiot 1-2)
	ht varchar(11),--Henkilötunnus (positiot 3-13)
	fuksi varchar(1),--Tietueen tunnus (positio 14)
	olotamm varchar(1),--Ilmoittautumislaji keväällä (positio 15)
	olosyys varchar(1),--Ilmoittautumislaji syksyllä (positio 16)
	kirtu varchar(5),--Kirjoihintulo yliopistoon (positiot 17-21)
	--tdk varchar(2),--Tiedekunta/osasto (positiot 22-23)
	--tutk varchar(3),--Tutkinto (positiot 24-26)
	--ko varchar(4),--Koulutusohjelma (positiot 27-30)
	--paine  varchar(4),--Pääaine (positiot 31-34)
	sv varchar(3),--Aineenopettajan pedagogiset opinnot (positiot 35-37)
	--opalay varchar(2),--Opintoala (positiot 38-39)
	om varchar(1),--Opiskelumuoto (positio 40)
	kkun varchar(3),--Opiskelukunta (positiot 41-43)
	sp varchar(1),--Sukupuoli (positio 44)
	kansaly varchar(3),--Kansalaisuus (positiot 45-47)
	aikieliy varchar(1),--Äidinkieli (positio 48)
	askuntay varchar(3),--Asuinkunta (positiot 49-51)
	snimi nvarchar(30),--Sukunimi (positiot 52-81)
	enimi nvarchar(15),--Etunimet (positiot 82-96)
	avo varchar(1),--Hyväksytty avoimen yliopiston opintojen perusteella (positio 97)
	opes varchar(3),--Edellisenä syyslukukautena (1.8.-31.12.) suoritettujen opintopisteiden määrä (positiot 98-100)
	opek varchar(3),--Edellisenä kevätlukukautena (1.1.-31.7.) suoritettujen opintopisteiden määrä (positiot 101-103)
	opker varchar(4),--Edellisen lukuvuoden loppuun (31.7.) mennessä suoritettujen kaikkien opintopisteiden määrä (positiot 104-106)
					 -- NB muutettu 28.4.2014 4 merkkiin, vaikka 2013 oli vielä 3 merkkiä!
	lkmmt varchar(1),--Lukukausimaksukokeilu ja maksullinen tilauskoulutus (positio 107)
	opoik varchar(5),--Opiskeluoikeuden alkaminen nykyisessä tutkinnossa (positiot 108-112)
	koulk varchar(6),--Koulutuskoodi (positiot 113-118)
	--
	opiskelijaavain varchar(100),
	opiskeluoikeusavain varchar(100),
	opiskelija_id int,
	opiskeluoikeus_id int
)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelija_id ON #tkopisk (opiskelija_id)
CREATE NONCLUSTERED INDEX IX_tkopisk_opiskeluoikeus_id ON #tkopisk (opiskeluoikeus_id)
if @debug<>0 print convert(varchar,getdate(),120)+'' CREATE temp-taulu tkopisk''
;

 


Perusjoukon valinta

Poimitaan aputauluun perustiedot opiskelijoista (henkilöistä). Perusjoukon poiminta tehdään tarkoituksella vain henkilö- ja opiskelija-taulu pohjaisesti. Kun opiskelijat on saatu valittua, valitaan kullekin opiskelijalle opiskeluoikeus ensisijaisuuden perusteella.

...

Maisteriohjelma -tieto opiskeluoikeusluokituksen koodi arvo 5= Maisteriohjelma

 


Code Block
languagesql
titleSQL
linenumberstrue
collapsetrue
-- om muuttuja

-- maisteriohjelma muutos 17.10.2016
UPDATE     T
SET     om = '1'            
FROM     #tkopisk T
        INNER JOIN     Opiskeluoikeus o ON o.ID = T.opiskeluoikeus_id
        INNER JOIN     Opiskeluoikeusjakso oj ON oj.OpiskeluoikeusID = T.opiskeluoikeus_id
        LEFT JOIN     Tutkintonimike kt on kt.ID = oj.TutkintonimikeID
        INNER JOIN Op_oik_jaks_liittyva_luokittelu OOJL ON oj.id = OOJL.OpiskeluoikeusjaksoID
        INNER    JOIN Opiskeluoikeuden_luokittelu OL ON OL.ID = OOJL.Opiskeluoikeuden_luokitteluID            
WHERE         OL.koodi = '5'        -- Entinen (Muu) maisteriohjelma
AND         (SELECT tilpvm FROM #param) BETWEEN oj.Alkamispaivamaara AND COALESCE(oj.Paattymispaivamaara,'9999-01-01')
AND        YEAR(o.alkamispaivamaara) = cast(@vuosi as int);

...

Code Block
titleErikoistumiskoulutus
collapsetrue
-- Erikoistumiskoulutus,  ei-tutkintokoulutus 999999
UPDATE T
SET T.ohjauksenAla = K.koodi,
    T.koulk = '999999',
    T.opiskeluoikeusavain = o.avain,
    T.opiskeluoikeus_id = o.id

FROM #tkopisk T
    INNER JOIN  Opiskeluoikeus o ON o.id = T.opiskeluoikeus_id
    INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = o.Op_oikeuden_tyyppiID
    INNER JOIN koulutusala K ON K.id = O.koulutusalaid
WHERE     1=1
--  erikoistumiskoulutus (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('19')
AND K.versio = 'ohjausala'


-- Erikoistumiskoulutus, 999999 eikä ole tuotu ohjauksen alaa?
UPDATE T
SET  T.koulk = '999999',
    T.opiskeluoikeusavain = o.avain,
    T.opiskeluoikeus_id = o.id
FROM #tkopisk T
    INNER JOIN  Opiskeluoikeus o ON o.id = T.opiskeluoikeus_id
    INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = o.Op_oikeuden_tyyppiID
    INNER JOIN koulutusala K ON K.id = O.koulutusalaid
WHERE     1=1
--  erikoistumiskoulutus (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('19')
AND K.versio <> 'ohjausala'


 
-- Erikoistumiskoulutuksen koulutuskoodi jos on
UPDATE T
SET T.ekkoodi = E.koodi 
FROM #tkopisk T
    INNER JOIN  Opiskeluoikeus o ON o.id = T.opiskeluoikeus_id
    INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = o.Op_oikeuden_tyyppiID
    INNER JOIN koulutusala K ON K.id = O.koulutusalaid
    INNER JOIN op_oik_liittyva_erikoistumiskoulutus OE ON OE.opiskeluoikeusid = o.id
    INNER JOIN Erikoistumiskoulutus E ON E.id = OE.erikoistumiskoulutusid
WHERE     1=1
-- erikoistumisopinnot  (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('19')
AND K.versio = 'ohjausala'

-- Ei läsnäoloa, tulkitaan aktiivinen tila läsnäoloksi Erikoistumiskoulutuksen tyyppi 19
UPDATE T
SET  T.olosyys = '1'
FROM #tkopisk T
    INNER JOIN  Opiskeluoikeus o ON o.id = T.opiskeluoikeus_id
    INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = o.Op_oikeuden_tyyppiID
    INNER JOIN koulutusala K ON K.id = O.koulutusalaid
WHERE     1=1
--  erikoistumiskoulutus (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('19')
AND K.versio = 'ohjausala'
AND T.olosyys IS NULL

UPDATE T
SET  T.olosyys = '1'
FROM #tkopisk T
    INNER JOIN  Opiskeluoikeus o ON o.id = T.opiskeluoikeus_id
    INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = o.Op_oikeuden_tyyppiID
    INNER JOIN koulutusala K ON K.id = O.koulutusalaid
WHERE     1=1
--  erikoistumiskoulutus (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('19')
AND K.versio <> 'ohjausala'
AND T.olosyys IS NULL


UPDATE T
SET  T.kirtu = T.opoik
FROM #tkopisk T
    INNER JOIN  Opiskeluoikeus o ON o.id = T.opiskeluoikeus_id
    INNER JOIN Opiskeluoikeuden_tyyppi ot ON ot.ID = o.Op_oikeuden_tyyppiID    
WHERE     1=1
--  erikoistumiskoulutus (siis EI tutkinto-tyyppinen)
AND ot.Koodi in ('19')
AND T.kirtu IS NULL
AND T.opoik IS NOT NULL

 


Äidinkieli (aikieliy)

Lisättiin tietovarannossa oleva tieto jo perusjoukon valinnassa. Puuttuvat muutetaan arvoon 3=muu.

...

Code Block
languagesql
titleSQL
linenumberstrue
collapsetrue
---
-- Opinnot, suoritukset, opintopisteet
---

-- Opintopisteitä varten tehdä oma erillinen aputaulu
-- Temp-taulu tietojen valintaa varten
-- NB! param-taulussa saa olla vain yksi rivi!
IF OBJECT_ID(''tempdb..#pisteet'') IS NOT NULL
BEGIN
	    truncate table #pisteet
	    drop table #pisteet
	if @debug<>0   print convert(varchar,getdate(),120)+'' DROP temp-taulu pisteet''
END
;
create table #pisteet (
	    opiskelija_id int not null,
	    opiskeluoikeus_id int,
	    opintosuoritus_id int not null,
	    laajuus decimal(18,6) not null,
	    suoritus_pvm date not null,
	    hyvaksiluettu_pvm date,
	    es bit,
	    ek bit,
	    ker bit
)
if @debug<>0 print convert(varchar,getdate(),120)+'' CREATE temp-taulu pisteet''
;
insert into #pisteet (opiskelija_id,opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm,hyvaksiluettu_pvm,es,ek,ker)
	    select
	     opiskelija_id = s.OpiskelijaID
	    ,opiskeluoikeus_id = s.OpiskeluoikeusID
	    ,opintosuoritus_id = s.ID
	    ,laajuus = s.Laajuus
	    ,suoritus_pvm = s.Suorituspaivamaara
	    ,hyvaksiluettu_pvm = shl.Hyvaksilukupaivamaara
	    ,es = case when s.Suorituspaivamaara
		        between cast(cast(#param.vuosi as int)-1 as varchar)+''-08-01'' and cast(cast(#param.vuosi as int)-1 as varchar)+''-12-31''
	      then 1 else 0 end
	    ,ek = case when s.Suorituspaivamaara between #param.vuosi+''-01-01'' and #param.vuosi+''-07-31''
	      then 1 else 0 end
	    ,ker = case when s.Suorituspaivamaara <= #param.vuosi+''-07-31''
	      then 1 else 0 end
	    from virta.dbo.Opintosuoritus s
	    inner join virta.dbo.Opintosuorituksen_laji sl on sl.ID=s.Opintosuorituksen_lajiID and sl.Koodi=''2'' --muu opintosuoritus
	    -- HYVÄKSILUETTU
	    left join virta.dbo.Opsuorituksen_hyvaksiluku shl on shl.OpintosuoritusID=s.ID
	    cross join #param -- saa olla vain yksi rivi!
	    where 1=1
	    -- lehtitaso, tai ei ainakaan vanhempi
	    --and s.ID not in (select ss.OpintosuoritusID from virta.dbo.Op_suor_sisaltyvyys ss)
    -- Muutos 2017-09-16
ei lajin 3,4 suoritukset     AND s.ID NOT IN (
        
        SELECT     ss.OpintosuoritusID 
        FROM     virta.dbo.Op_suor_sisaltyvyys ss
       
        --  Sisältyvyydet Lajeja 3 ja 4 ei huomioida       
        WHERE      
                    ss.OpintosuoritusID2 NOT IN ( 
        
            SELECT sss.ID            
            FROM virta.dbo.Opintosuoritus sss
            
                INNER JOIN virta.dbo.Opintosuorituksen_laji sssl ON sssl.ID = sss.Opintosuorituksen_lajiID
                
            WHERE     sssl.Koodi in ('3','4')
                         
        )        
        
    )
	    
    -- eirajoita mukaanperusjoukkoon
liian myöhään hyväksiluettuja 	 and coalesce(shl.Hyvaksilukupaivamaara,''1900-01-01'')<=#param.tilpvms.OpiskelijaID in (select T.opiskelija_id from #tkopisk T)
;
CREATE NONCLUSTERED INDEX IX_pisteet_opiskelija_id ON #pisteet (opiskelija_id)
 INCLUDE (opiskeluoikeus_id,opintosuoritus_id,laajuus,suoritus_pvm)
;
CREATE NONCLUSTERED INDEX IX_pisteet_suoritus_pvm ON #pisteet (suoritus_pvm)
INCLUDE ([opiskelija_id],[opiskeluoikeus_id],[laajuus])
;
CREATE NONCLUSTERED INDEX IX_pisteet_hyvaksiluettu_pvm ON #pisteet (hyvaksiluettu_pvm)
INCLUDE ([opiskelija_id],[opiskeluoikeus_id],[laajuus],[suoritus_pvm])
;
--select * from #pisteet
--
-- "opintopistekysymykset eivät koske jatkotutkintoja opiskelevia, joille opintopistetietoja ei merkitä"
-- jatko-oikeus: merkitään tyhjää/''0'':
update T
set opes=''0''
  , opek=''0''
  , opker=''0''
from #tkopisk T
where T.opiskeluoikeus_id IN (
	select o.id from Opiskeluoikeus o
	inner join Opiskeluoikeuden_tyyppi ot on ot.ID=o.Op_oikeuden_tyyppiID
	and ot.Koodi in (
		''5'', --Lääkärien erikoistumiskoulutus
		''6'', --Lisensiaatintutkinto
		''7''  --Tohtorintutkinto
	)
	where o.OpiskelijaID=T.opiskelija_id --sido,nopeuta
)
;
update T
set opes=(
  select cast(cast(round(sum(
	case when s.es = 1 then s.Laajuus else 0 end),0) as int) as varchar)
  from #pisteet s
  where s.opiskelija_id=T.opiskelija_id
  --and s.opiskeluoikeus_id=T.opiskeluoikeus_id
  group by s.opiskelija_id
)
from #tkopisk T
where opes is null
;
update T
set opek=(
  select cast(cast(round(sum(
	case when s.ek = 1 then s.Laajuus else 0 end),0) as int) as varchar)
  from #pisteet s
  where s.opiskelija_id=T.opiskelija_id
  --and s.opiskeluoikeus_id=T.opiskeluoikeus_id
  group by s.opiskelija_id
)
from #tkopisk T
where opek is null
;
update T
set opker=(
  select cast(cast(round(sum(
	case when s.ker = 1 then s.Laajuus else 0 end),0) as int) as varchar)
  from #pisteet s
  where s.opiskelija_id=T.opiskelija_id
  --and s.opiskeluoikeus_id=T.opiskeluoikeus_id
  group by s.opiskelija_id
)
from #tkopisk T
where opker is null
;

update T
set opes=''0''
from #tkopisk T
where opes is null
;
update T
set opek=''0''
from #tkopisk T
where opek is null
;
update T
set opker=''0''
from #tkopisk T
where opker is null
;



print convert(varchar,getdate(),120)+' opintosuoritustiedot'
-- 2017-10-13 Jos Kirtu tyhjä opiskeluoikeuden alkamispaivamaara
-- kirtu
UPDATE T
SET kirtu=(cast(year(o.Alkamispaivamaara) as varchar)
         +(case
           -- 8-12 (elo-joulu): syksy
           when month(o.Alkamispaivamaara)>=8 then '2'
           -- 1-7 (tammi-heinä): kevät
           else '1'
           end)
    )
FROM #tkopisk T
inner join virta.dbo.Opiskeluoikeus o on o.ID=T.opiskeluoikeus_id
WHERE kirtu IS NULL
AND T.koulk IN ('775101','775201','775999')
;      

 



-- 2018-10-16
-- Tämä korjaa TAY case kun erikoislääkärin oikeus alkaa syksyllä, aiempi läsnäolo 1,2 kevällä eri opiskeluoikeus
update T
set olotamm='0'
from #tkopisk T
where kirtu=(select vuosi from #param)+'2'
;


Tulos

Lopullisessa tuloksessa muokataan vielä etunollat kohdilleen, jolloin itse asiassa moni tyhjä merkkijono muuttuu siis myös nolla-arvoksi.

...

3.10.2014 :: Opiskelijatiedonkeruu 2014 :: wikiversio 17