- Created by Unknown User (psane@csc.fi), last updated by Aino-Kaisa Ellfolk on Apr 29, 2022 6 minute read
Määrittely
Opinto-oikeuksista tarvitaan seuraavat tiedot:
- Tieto jokaisesta yksittäisestä opinto-oikeudesta ja kytkentä niistä opinto-oikeuksista jotka ovat samalla henkilöllä. Tieto sillä tavalla että henkilöt voi ryhmitellä ryhmiin:
1 opinto-oikeus;
2 opinto-oikeutta;
3 opinto-oikeutta;
4 opinto-oikeutta
tai enemmän. - Jokaisesta opinto-oikeudesta opinto-oikeuden 6 nro-koodi ja korkeakoulu-tiedot.
Näiden tietojen perusteella voi opinto-oikeudet jakaa myös yo- ja amk-sektoreiden välillä.
- Jokaisesta opinto-oikeudesta tieto on ”ensisijainen opinto-oikeus”. Näitä voi henkilöllä olla yksi per korkeakulu, eri korkeakouluissa useita
- Jokaisesta ylemmästä korkeakoulututkinnon opinto-oikeudesta tieto kuuluuko siihen oikeus alempaan korkeakoulututkintoon. (koskee vain yliopistoja).
Poiminta
Tulossa selostus tai aukikirjoitus SQL-kyselyistä, mutta itse SQL ohessa:
Expand source
use virta go declare @debug int = 1 declare @vuosi varchar(4) = '2014' -- 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,tilpvm) select @vuosi as vuosi ,@vuosi+'-09-20' as tilpvm ; --select * from #param -- Temp-taulu tiedonkeruun tietoja varten IF OBJECT_ID('tempdb..#oikeus') IS NOT NULL BEGIN truncate table #oikeus drop table #oikeus if @debug<>0 print convert(varchar,getdate(),120)+' DROP temp-taulu oikeus' END ; create table #oikeus ( KK varchar(8), Henkilotunnus varchar(11), Koulutuskoodi varchar(6), Tutkintonimike varchar(200), Ensisijainen bit, Ylempi_liittyy_alempi bit, -- myös null-arvo merkityksellinen! -- opiskelijaavain varchar(100), opiskeluoikeusavain varchar(100), opiskelija_id int, opiskeluoikeus_id int ) CREATE NONCLUSTERED INDEX IX_tkopisk_opiskelija_id ON #oikeus (opiskelija_id) CREATE NONCLUSTERED INDEX IX_tkopisk_opiskeluoikeus_id ON #oikeus (opiskeluoikeus_id) if @debug<>0 print convert(varchar,getdate(),120)+' CREATE temp-taulu tkopisk' ; insert into #oikeus (kk,Henkilotunnus,Koulutuskoodi,Tutkintonimike,Ensisijainen,Ylempi_liittyy_alempi ,opiskelijaavain,opiskeluoikeusavain ,opiskelija_id,opiskeluoikeus_id ) select --'>'+CAST(@oik_lkm as varchar) as "Opiskeluoikeuksia", o.DB as "KK" , h.Henkilotunnus , tk.Koodi as "Koulutuskoodi" , tk.Nimi as "Tutkintonimike" --/*-- ensisijainen tilpvm ,coalesce( ( select distinct 1 from Op_oikeuden_ensisijaisuus oe where oe.OpiskeluoikeusID=o.ID and oe.DB=o.DB and (select tilpvm from #param) between oe.Alkamispaivamaara and coalesce(oe.Paattymispaivamaara,'9999-01-01') ),0) "Ensisijainen" --/*-- maisterioikeuteen liittyy kandi? ,(case when ot.Koodi='4' then -- opiskeluoikeuden tyyppi: ylempi kk-tutkinto (maisteri) coalesce( ( select distinct 1 as "liittyy kandi" from Opiskeluoikeus kandi inner join Opiskeluoikeuden_liittyvyys lii on lii.OpiskeluoikeusID=kandi.ID --and lii.OpiskeluoikeusID2=o.id and lii.DB=kandi.DB where lii.OpiskeluoikeusID2=o.ID and kandi.DB=o.DB ),0) else null -- ei ylempi end ) as "Ylempi_liittyy_alempi" --*/ ,opiskelijaavain=p.Avain,opiskeluoikeusavain=o.Avain ,opiskelija_id=p.ID,opiskeluoikeus_id=o.ID from Henkilo h inner join Opiskelija p on p.HenkiloID=h.ID and p.DB=h.DB inner join Opiskeluoikeus o on o.OpiskelijaID=p.ID and o.DB=p.DB inner join Opiskeluoikeuden_tyyppi ot on ot.ID=o.Op_oikeuden_tyyppiID --and ot.DB=o.DB inner join Op_oikeuteen_liittyva_tila lt on lt.OpiskeluoikeusID=o.ID and lt.DB=o.DB inner join Opiskeluoikeuden_tila kt on kt.ID=lt.Op_oikeuden_tilaID --and kt.DB=lt.DB inner join Opiskeluoikeusjakso oj on oj.OpiskeluoikeusID=o.id and oj.DB=o.DB inner join Tutkintonimike tk on tk.ID=oj.TutkintonimikeID --and tk.DB=oj.DB -- where 1=1 -- tutkinto-tyyppinen and ot.Koodi in ('1','2','3','4','5','6','7') -- TODO: muita tyyppejä? -- voimassa tilpvm and (select tilpvm from #param) between o.Alkamispaivamaara and coalesce(o.Paattymispaivamaara,'9999-01-01') -- tila and kt.Koodi = '1' --aktiivinen and (select tilpvm from #param) between lt.Alkamispaivamaara and coalesce(lt.Paattymispaivamaara,'9999-01-01') and len(h.Henkilotunnus)=11 -- virallinen suomalainen hetu (ei syntymäajan perusteella) -- ilmo and p.ID in ( select l.OpiskelijaID from Lukukausi_ilmoittautuminen l inner join Lukukausi_ilm_tila lt on lt.ID=l.Lukukausi_ilmoittautumisen_tilaID where lt.Koodi in ('1','2','3') --läsnä, poissa, poissa ei kuluta and l.Ilmoittautumispaivamaara <= (select tilpvm from #param) and (select tilpvm from #param) between l.Alkamispaivamaara and coalesce(l.Paattymispaivamaara,'9999-01-01') and l.DB=p.DB ) -- jakso and (select tilpvm from #param) between oj.Alkamispaivamaara and coalesce(oj.Paattymispaivamaara,'9999-01-01') --*/ ; --select count(*), count(distinct Henkilotunnus) from #oikeus select '1' as "Oikeuksia", KK ,(select oo.Organisaatio_nimi from [Yhteiset].[Koodistot].[Organisaatio_Oppilaitostyyppi] oo inner join Yhteiset.Asetukset.Instanssit i on i.OrganisaatioKoodi=oo.Organisaatio_koodi where i.DB=KK ) as "Korkeakoulu" ,Koulutuskoodi,Tutkintonimike --koulutusluokitus: --,[Nimi],[Alkamispaivamaara],[Paattymispaivamaara] ,(select top 1 opmala from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Koulutusala2002_koodi" ,(select top 1 opmalaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Koulutusala2002" ,(select top 1 opm95opa from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Opintoala1995_koodi" ,(select top 1 opm95opaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Opintoala1995" -- valitaan koulutusala sektorin mukaan: ,coalesce( (case when exists (select * from Yhteiset..Hae_Yliopistot(KK,DEFAULT)) then (select top 1 opm95opa from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) else (select top 1 opmala from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) end ),'-1') "Koulutusala_koodi" ,coalesce( (case when exists (select * from Yhteiset..Hae_Yliopistot(KK,DEFAULT)) then (select top 1 opmalaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) else (select top 1 opm95opaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) end ),'Tuntematon') "Koulutusala" ,(case Ensisijainen when 1 then 'Ensisijainen' else '' end) as "Ensijaisuus" ,(case Ylempi_liittyy_alempi when 1 then 'Liittyy alempi oikeus' when 0 then 'Ei liity alempaa' else '' end) as "Ylempään oikeuteen liittyy alempi" ,COUNT(*) Lkm from #oikeus i where i.Henkilotunnus in ( select j.Henkilotunnus from #oikeus j group by j.Henkilotunnus having COUNT(*)=1 ) group by KK, Koulutuskoodi,Tutkintonimike,Ensisijainen,Ylempi_liittyy_alempi union select '2' as "Oikeuksia", KK ,(select oo.Organisaatio_nimi from [Yhteiset].[Koodistot].[Organisaatio_Oppilaitostyyppi] oo inner join Yhteiset.Asetukset.Instanssit i on i.OrganisaatioKoodi=oo.Organisaatio_koodi where i.DB=KK ) as "Korkeakoulu" ,Koulutuskoodi,Tutkintonimike --koulutusluokitus: --,[Nimi],[Alkamispaivamaara],[Paattymispaivamaara] ,(select top 1 opmala from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Koulutusala2002_koodi" ,(select top 1 opmalaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Koulutusala2002" ,(select top 1 opm95opa from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Opintoala1995_koodi" ,(select top 1 opm95opaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Opintoala1995" -- valitaan koulutusala sektorin mukaan: ,coalesce( (case when exists (select * from Yhteiset..Hae_Yliopistot(KK,DEFAULT)) then (select top 1 opm95opa from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) else (select top 1 opmala from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) end ),'-1') "Koulutusala_koodi" ,coalesce( (case when exists (select * from Yhteiset..Hae_Yliopistot(KK,DEFAULT)) then (select top 1 opmalaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) else (select top 1 opm95opaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) end ),'Tuntematon') "Koulutusala" ,(case Ensisijainen when 1 then 'Ensisijainen' else '' end) as "Ensijaisuus" ,(case Ylempi_liittyy_alempi when 1 then 'Liittyy alempi oikeus' when 0 then 'Ei liity alempaa' else '' end) as "Ylempään oikeuteen liittyy alempi" ,COUNT(*) Lkm from #oikeus i where i.Henkilotunnus in ( select j.Henkilotunnus from #oikeus j group by j.Henkilotunnus having COUNT(*)=2 ) group by KK, Koulutuskoodi,Tutkintonimike,Ensisijainen,Ylempi_liittyy_alempi union select '3' as "Oikeuksia", KK ,(select oo.Organisaatio_nimi from [Yhteiset].[Koodistot].[Organisaatio_Oppilaitostyyppi] oo inner join Yhteiset.Asetukset.Instanssit i on i.OrganisaatioKoodi=oo.Organisaatio_koodi where i.DB=KK ) as "Korkeakoulu" ,Koulutuskoodi,Tutkintonimike --koulutusluokitus: --,[Nimi],[Alkamispaivamaara],[Paattymispaivamaara] ,(select top 1 opmala from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Koulutusala2002_koodi" ,(select top 1 opmalaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Koulutusala2002" ,(select top 1 opm95opa from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Opintoala1995_koodi" ,(select top 1 opm95opaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Opintoala1995" -- valitaan koulutusala sektorin mukaan: ,coalesce( (case when exists (select * from Yhteiset..Hae_Yliopistot(KK,DEFAULT)) then (select top 1 opm95opa from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) else (select top 1 opmala from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) end ),'-1') "Koulutusala_koodi" ,coalesce( (case when exists (select * from Yhteiset..Hae_Yliopistot(KK,DEFAULT)) then (select top 1 opmalaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) else (select top 1 opm95opaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) end ),'Tuntematon') "Koulutusala" ,(case Ensisijainen when 1 then 'Ensisijainen' else '' end) as "Ensijaisuus" ,(case Ylempi_liittyy_alempi when 1 then 'Liittyy alempi oikeus' when 0 then 'Ei liity alempaa' else '' end) as "Ylempään oikeuteen liittyy alempi" ,COUNT(*) Lkm from #oikeus i where i.Henkilotunnus in ( select j.Henkilotunnus from #oikeus j group by j.Henkilotunnus having COUNT(*)=3 ) group by KK, Koulutuskoodi,Tutkintonimike,Ensisijainen,Ylempi_liittyy_alempi union select '4' as "Oikeuksia", KK ,(select oo.Organisaatio_nimi from [Yhteiset].[Koodistot].[Organisaatio_Oppilaitostyyppi] oo inner join Yhteiset.Asetukset.Instanssit i on i.OrganisaatioKoodi=oo.Organisaatio_koodi where i.DB=KK ) as "Korkeakoulu" ,Koulutuskoodi,Tutkintonimike --koulutusluokitus: --,[Nimi],[Alkamispaivamaara],[Paattymispaivamaara] ,(select top 1 opmala from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Koulutusala2002_koodi" ,(select top 1 opmalaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Koulutusala2002" ,(select top 1 opm95opa from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Opintoala1995_koodi" ,(select top 1 opm95opaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Opintoala1995" -- valitaan koulutusala sektorin mukaan: ,coalesce( (case when exists (select * from Yhteiset..Hae_Yliopistot(KK,DEFAULT)) then (select top 1 opm95opa from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) else (select top 1 opmala from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) end ),'-1') "Koulutusala_koodi" ,coalesce( (case when exists (select * from Yhteiset..Hae_Yliopistot(KK,DEFAULT)) then (select top 1 opmalaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) else (select top 1 opm95opaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) end ),'Tuntematon') "Koulutusala" ,(case Ensisijainen when 1 then 'Ensisijainen' else '' end) as "Ensijaisuus" ,(case Ylempi_liittyy_alempi when 1 then 'Liittyy alempi oikeus' when 0 then 'Ei liity alempaa' else '' end) as "Ylempään oikeuteen liittyy alempi" ,COUNT(*) Lkm from #oikeus i where i.Henkilotunnus in ( select j.Henkilotunnus from #oikeus j group by j.Henkilotunnus having COUNT(*)=4 ) group by KK, Koulutuskoodi,Tutkintonimike,Ensisijainen,Ylempi_liittyy_alempi union select '>4' as "Oikeuksia", KK ,(select oo.Organisaatio_nimi from [Yhteiset].[Koodistot].[Organisaatio_Oppilaitostyyppi] oo inner join Yhteiset.Asetukset.Instanssit i on i.OrganisaatioKoodi=oo.Organisaatio_koodi where i.DB=KK ) as "Korkeakoulu" ,Koulutuskoodi,Tutkintonimike --koulutusluokitus: --,[Nimi],[Alkamispaivamaara],[Paattymispaivamaara] ,(select top 1 opmala from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Koulutusala2002_koodi" ,(select top 1 opmalaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Koulutusala2002" ,(select top 1 opm95opa from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Opintoala1995_koodi" ,(select top 1 opm95opaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) "Opintoala1995" -- valitaan koulutusala sektorin mukaan: ,coalesce( (case when exists (select * from Yhteiset..Hae_Yliopistot(KK,DEFAULT)) then (select top 1 opm95opa from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) else (select top 1 opmala from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) end ),'-1') "Koulutusala_koodi" ,coalesce( (case when exists (select * from Yhteiset..Hae_Yliopistot(KK,DEFAULT)) then (select top 1 opmalaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) else (select top 1 opm95opaselite from [Yhteiset].[Koodistot].[Koulutusluokitus] where [Koodi]=i.Koulutuskoodi) end ),'Tuntematon') "Koulutusala" ,(case Ensisijainen when 1 then 'Ensisijainen' else '' end) as "Ensijaisuus" ,(case Ylempi_liittyy_alempi when 1 then 'Liittyy alempi oikeus' when 0 then 'Ei liity alempaa' else '' end) as "Ylempään oikeuteen liittyy alempi" ,COUNT(*) Lkm from #oikeus i where i.Henkilotunnus in ( select j.Henkilotunnus from #oikeus j group by j.Henkilotunnus having COUNT(*)>4 ) group by KK, Koulutuskoodi,Tutkintonimike,Ensisijainen,Ylempi_liittyy_alempi -- end
- No labels