Päällekkäiset opinto-oikeudet

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:
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
Space shortcuts

Page tree

Määrittely

Poiminta
