 GO
 IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[sa].[v_virta_otp_opettajapatevyydet4]'))
 EXEC dbo.sp_executesql @statement = N'
-
-
-
 
 
 











' 
GO
/****** Object:  View [sa].[v_virta_otp_opettajapatevyydet1]    Script Date: 8.5.2020 11:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[sa].[v_virta_otp_opettajapatevyydet1]'))
EXEC dbo.sp_executesql @statement = N'


CREATE VIEW [sa].[v_virta_otp_opettajapatevyydet1] AS

--Pätevyydet yhteen sarakkeeseen

SELECT distinct
	 opiskelija_avain = organisaatiokoodi+opiskelija_avain
	,opiskelija = opiskelija_avain
	,vuosi
	,sukupuolikoodi
	,ika
	,organisaatiokoodi
	,patevyys_nro = RIGHT(patevyydet_selite,1)
	,patevyys_koodi
	,patevyys_selite
	,patevyys_vuosi
	,tutkinto_koodi
	,tutkinto_vuosi
	,tutkinto_organisaatio
	,tutkinnon_yhteydessa = case when patevyys_selite not like ''%erilliset%'' then 1 else 0 end
	,erillinen = case when patevyys_selite like ''%erilliset%'' then 1 else 0 end
FROM (

	SELECT 
		 sa1.vuosi
		,sa1.organisaatiokoodi
		,sa1.opiskelija_avain
		,sa1.sukupuolikoodi
		,sa1.ika
		,sa1.patevyyskoodi1
		,sa1.patevyyskoodi2
		,sa1.patevyyskoodi3
		,sa1.patevyyskoodi4
		,sa1.patevyyskoodi5
		,sa1.patevyysselite1
		,sa1.patevyysselite2
		,sa1.patevyysselite3
		,sa1.patevyysselite4
		,sa1.patevyysselite5
		,sa1.Patevyysmerk_vuosi1
		,sa1.Patevyysmerk_vuosi2
		,sa1.Patevyysmerk_vuosi3
		,sa1.Patevyysmerk_vuosi4
		,sa1.Patevyysmerk_vuosi5
		,tkkoodi1 = coalesce(sa1.tkkoodi1,sa2.tkkoodi1)
		,tkkoodi2 = coalesce(sa1.tkkoodi2,sa2.tkkoodi2)
		,tkkoodi3 = coalesce(sa1.tkkoodi3,sa2.tkkoodi3)
		,tkkoodi4 = coalesce(sa1.tkkoodi4,sa2.tkkoodi4)
		,tkkoodi5 = coalesce(sa1.tkkoodi5,sa2.tkkoodi5)
		,tv1 = coalesce(sa1.tv1,sa2.tv1)
		,tv2 = coalesce(sa1.tv2,sa2.tv2)
		,tv3 = coalesce(sa1.tv3,sa2.tv3)
		,tv4 = coalesce(sa1.tv4,sa2.tv4)
		,tv5 = coalesce(sa1.tv5,sa2.tv5)
		,toppilaitostunnus1 = coalesce(sa1.toppilaitostunnus1,sa2.toppilaitostunnus1)
		,toppilaitostunnus2 = coalesce(sa1.toppilaitostunnus2,sa2.toppilaitostunnus2)
		,toppilaitostunnus3 = coalesce(sa1.toppilaitostunnus3,sa2.toppilaitostunnus3)
		,toppilaitostunnus4 = coalesce(sa1.toppilaitostunnus4,sa2.toppilaitostunnus4)
		,toppilaitostunnus5 = coalesce(sa1.toppilaitostunnus5,sa2.toppilaitostunnus5)

	FROM [ANTERO].[sa].[sa_virta_otp_opettajapatevyydet] sa1
	--datassa henkilöitä joiden tiedot kahdella rivillä joista toiselta puuttuu pohjatutkintotiedot
	LEFT JOIN ANTERO.sa.sa_virta_otp_opettajapatevyydet sa2 on sa2.opiskelija_avain=sa1.opiskelija_avain 
														   and sa2.db=sa1.db 
														   and sa2.vuosi=sa1.vuosi 
														   and sa1.tkkoodi1 is null 
														   and sa2.tkkoodi1 is not null
	--WHERE sa1.opiskelija_avain in (''13893528'',''15822233'',''17552211'',''17590625'')

) Q
	
unpivot (
patevyys_koodi for patevyydet_koodi in ([patevyyskoodi1],[patevyyskoodi2],[patevyyskoodi3],[patevyyskoodi4],[patevyyskoodi5])
) unpvt1

unpivot (
patevyys_selite for patevyydet_selite in ([patevyysselite1],[patevyysselite2],[patevyysselite3],[patevyysselite4],[patevyysselite5])
) unpvt2

unpivot (
patevyys_vuosi for patevyydet_vuodet in ([Patevyysmerk_vuosi1],[Patevyysmerk_vuosi2],[Patevyysmerk_vuosi3],[Patevyysmerk_vuosi4],[Patevyysmerk_vuosi5])
) unpvt3

cross apply (
values  
	([tkkoodi1],''tkkoodi1''),
	([tkkoodi2],''tkkoodi2''),
	([tkkoodi3],''tkkoodi3''),
	([tkkoodi4],''tkkoodi4''),
	([tkkoodi5],''tkkoodi5'')
) ca1 (tutkinto_koodi, tutkinnot_koodi)
				 
cross apply (
values  
	([tv1],''tv1''),
	([tv2],''tv2''),
	([tv3],''tv3''),
	([tv4],''tv4''),
	([tv5],''tv5'')
) ca2 (tutkinto_vuosi, tutkinnot_vuosi)

cross apply (
values  
	([toppilaitostunnus1],''tunn1''),
	([toppilaitostunnus2],''tunn2''),
	([toppilaitostunnus3],''tunn3''),
	([toppilaitostunnus4],''tunn4''),
	([toppilaitostunnus5],''tunn5'')
) ca3 (tutkinto_organisaatio, tutkinnot_organisaatio)

WHERE RIGHT(patevyydet_selite,1) = RIGHT(patevyydet_koodi,1) 
AND RIGHT(patevyydet_koodi,1) = RIGHT(patevyydet_vuodet,1) 
AND RIGHT(tutkinnot_koodi,1) = RIGHT(tutkinnot_vuosi,1)
AND RIGHT(tutkinnot_vuosi,1) = RIGHT(tutkinnot_organisaatio,1)






' 
GO
/****** Object:  View [sa].[v_virta_otp_opettajapatevyydet2]    Script Date: 8.5.2020 11:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[sa].[v_virta_otp_opettajapatevyydet2]'))
EXEC dbo.sp_executesql @statement = N'





CREATE VIEW [sa].[v_virta_otp_opettajapatevyydet2] AS

--Pätevyyksien ryhmittely

SELECT
	 opiskelija_avain
	,opiskelija
	,vuosi
	,sukupuoli_koodi = sukupuolikoodi
	,ika
	,organisaatio_koodi = organisaatiokoodi
	,patevyys_vuosi
	,[Varhaiskasvatuksen tehtäviin ammatillisia valmiuksia antavat opinnot] = case when [vo] is not null or [iq] is not null or [ke] is not null or [vs] is not null then 1 else 0 end
	,[Aineenopettajan pedagogiset opinnot] = case when [ik] is not null or [il] is not null or [im] is not null then 1 else 0 end
	,[Ammatillinen opettajakoulutus] = case when [ja] is not null or [jb] is not null or [ix] is not null then 1 else 0 end
	,[Perusopetuksessa opetettavien aineiden ja aihekokonaisuuksien monialaiset opinnot] = case when [in] is not null or [io] is not null or [ip] is not null then 1 else 0 end
	,[Erityisopettajan opinnot] = case when [is] is not null or [it] is not null or [iu] is not null then 1 else 0 end
	,[Opinto-ohjaajan opinnot] = case when [iv] is not null or [iw] is not null or [iy] is not null or [jc] is not null then 1 else 0 end
	,[Erityislastentarhanopettajan opinnot] = case when [ir] is not null then 1 else 0 end
	,tutkinto_koodi --= case when rnk_tutkinto_vuosi = 1 then tutkinto_koodi else null end
	,tutkinto_vuosi --= case when rnk_tutkinto_vuosi = 1 then tutkinto_vuosi else null end
	,tutkinto_organisaatio --= case when rnk_tutkinto_vuosi = 1 then tutkinto_organisaatio else null end
	,tutkinnon_yhteydessa
	,erillinen_eka = case when erillinen = 1 and rnk_patevyys_vuosi = 1 then 1 else 0 end
	,erillinen_tayd = case when erillinen = 1 and rnk_patevyys_vuosi != 1 then 1 else 0 end
	,rnk_tutkinto_vuosi
	,rnk_patevyys_vuosi
FROM (

	SELECT 
		 opiskelija_avain
		,opiskelija
		,vuosi
		,sukupuolikoodi
		,ika
		,organisaatiokoodi
		,patevyys_nro
		,q1.patevyys_koodi
		,d.patevyysryhma1_koodi
		,patevyys_selite
		,patevyys_vuosi
		,tutkinnon_yhteydessa
		,erillinen
		,tutkinto_koodi
		,tutkinto_vuosi
		,tutkinto_organisaatio
		,case 
			when d.patevyysryhma1_fi = ''opettajaaineet'' then null 
			else rank() over (partition by opiskelija_avain order by d.patevyysryhma1_fi, patevyys_vuosi, patevyys_nro, erillinen) 
		 end rnk_patevyys_vuosi
		,rank() over (partition by opiskelija_avain order by COALESCE(NULLIF(tutkinto_koodi,''999999''),''111111'') desc) rnk_tutkinto_vuosi
	FROM sa.v_virta_otp_opettajapatevyydet1 q1
	LEFT JOIN ANTERO.dw.d_virta_patevyydet d on d.patevyys_koodi = q1.patevyys_koodi
			
	
) q2


pivot (
		min(patevyys_koodi) for patevyys_koodi in (
			[vo],[iq],[ke],[vs],		--varhais
			[ik],[il],[im],				--aineen
			[ip],[in],[io],				--perusopetus
			[iu],[is],[it],				--erityis
			[iy],[iv],[iw],				--opo
			[ir],						--erityislastentarha
			[ja],[jb],[jc],[ix]			--ammatillinen
		)
) pvt

--vain uusin tutkinto
WHERE rnk_tutkinto_vuosi = 1










' 

GO
SET QUOTED_IDENTIFIER ON
GO



IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[sa].[v_virta_otp_opettajapatevyydet1]'))
EXEC dbo.sp_executesql @statement = N'


CREATE VIEW [sa].[v_virta_otp_opettajapatevyydet1] AS

--Pätevyydet yhteen sarakkeeseen

SELECT distinct
	 opiskelija_avain = organisaatiokoodi+opiskelija_avain
	,opiskelija = opiskelija_avain
	,vuosi
	,sukupuolikoodi
	,ika
	,organisaatiokoodi
	,patevyys_nro = RIGHT(patevyydet_selite,1)
	,patevyys_koodi
	,patevyys_selite
	,patevyys_vuosi
	,tutkinto_koodi
	,tutkinto_vuosi
	,tutkinto_organisaatio
	,tutkinnon_yhteydessa = case when patevyys_selite not like ''%erilliset%'' then 1 else 0 end
	,erillinen = case when patevyys_selite like ''%erilliset%'' then 1 else 0 end
FROM (

	SELECT 
		 sa1.vuosi
		,sa1.organisaatiokoodi
		,sa1.opiskelija_avain
		,sa1.sukupuolikoodi
		,sa1.ika
		,sa1.patevyyskoodi1
		,sa1.patevyyskoodi2
		,sa1.patevyyskoodi3
		,sa1.patevyyskoodi4
		,sa1.patevyyskoodi5
		,sa1.patevyysselite1
		,sa1.patevyysselite2
		,sa1.patevyysselite3
		,sa1.patevyysselite4
		,sa1.patevyysselite5
		,sa1.Patevyysmerk_vuosi1
		,sa1.Patevyysmerk_vuosi2
		,sa1.Patevyysmerk_vuosi3
		,sa1.Patevyysmerk_vuosi4
		,sa1.Patevyysmerk_vuosi5
		,tkkoodi1 = coalesce(sa1.tkkoodi1,sa2.tkkoodi1)
		,tkkoodi2 = coalesce(sa1.tkkoodi2,sa2.tkkoodi2)
		,tkkoodi3 = coalesce(sa1.tkkoodi3,sa2.tkkoodi3)
		,tkkoodi4 = coalesce(sa1.tkkoodi4,sa2.tkkoodi4)
		,tkkoodi5 = coalesce(sa1.tkkoodi5,sa2.tkkoodi5)
		,tv1 = coalesce(sa1.tv1,sa2.tv1)
		,tv2 = coalesce(sa1.tv2,sa2.tv2)
		,tv3 = coalesce(sa1.tv3,sa2.tv3)
		,tv4 = coalesce(sa1.tv4,sa2.tv4)
		,tv5 = coalesce(sa1.tv5,sa2.tv5)
		,toppilaitostunnus1 = coalesce(sa1.toppilaitostunnus1,sa2.toppilaitostunnus1)
		,toppilaitostunnus2 = coalesce(sa1.toppilaitostunnus2,sa2.toppilaitostunnus2)
		,toppilaitostunnus3 = coalesce(sa1.toppilaitostunnus3,sa2.toppilaitostunnus3)
		,toppilaitostunnus4 = coalesce(sa1.toppilaitostunnus4,sa2.toppilaitostunnus4)
		,toppilaitostunnus5 = coalesce(sa1.toppilaitostunnus5,sa2.toppilaitostunnus5)

	FROM [ANTERO].[sa].[sa_virta_otp_opettajapatevyydet] sa1
	--datassa henkilöitä joiden tiedot kahdella rivillä joista toiselta puuttuu pohjatutkintotiedot
	LEFT JOIN ANTERO.sa.sa_virta_otp_opettajapatevyydet sa2 on sa2.opiskelija_avain=sa1.opiskelija_avain 
														   and sa2.db=sa1.db 
														   and sa2.vuosi=sa1.vuosi 
														   and sa1.tkkoodi1 is null 
														   and sa2.tkkoodi1 is not null
	--WHERE sa1.opiskelija_avain in (''13893528'',''15822233'',''17552211'',''17590625'')

) Q
	
unpivot (
patevyys_koodi for patevyydet_koodi in ([patevyyskoodi1],[patevyyskoodi2],[patevyyskoodi3],[patevyyskoodi4],[patevyyskoodi5])
) unpvt1

unpivot (
patevyys_selite for patevyydet_selite in ([patevyysselite1],[patevyysselite2],[patevyysselite3],[patevyysselite4],[patevyysselite5])
) unpvt2

unpivot (
patevyys_vuosi for patevyydet_vuodet in ([Patevyysmerk_vuosi1],[Patevyysmerk_vuosi2],[Patevyysmerk_vuosi3],[Patevyysmerk_vuosi4],[Patevyysmerk_vuosi5])
) unpvt3

cross apply (
values  
	([tkkoodi1],''tkkoodi1''),
	([tkkoodi2],''tkkoodi2''),
	([tkkoodi3],''tkkoodi3''),
	([tkkoodi4],''tkkoodi4''),
	([tkkoodi5],''tkkoodi5'')
) ca1 (tutkinto_koodi, tutkinnot_koodi)
				 
cross apply (
values  
	([tv1],''tv1''),
	([tv2],''tv2''),
	([tv3],''tv3''),
	([tv4],''tv4''),
	([tv5],''tv5'')
) ca2 (tutkinto_vuosi, tutkinnot_vuosi)

cross apply (
values  
	([toppilaitostunnus1],''tunn1''),
	([toppilaitostunnus2],''tunn2''),
	([toppilaitostunnus3],''tunn3''),
	([toppilaitostunnus4],''tunn4''),
	([toppilaitostunnus5],''tunn5'')
) ca3 (tutkinto_organisaatio, tutkinnot_organisaatio)

WHERE RIGHT(patevyydet_selite,1) = RIGHT(patevyydet_koodi,1) 
AND RIGHT(patevyydet_koodi,1) = RIGHT(patevyydet_vuodet,1) 
AND RIGHT(tutkinnot_koodi,1) = RIGHT(tutkinnot_vuosi,1)
AND RIGHT(tutkinnot_vuosi,1) = RIGHT(tutkinnot_organisaatio,1)

' 


GO
/****** Object:  View [sa].[v_virta_otp_opettajapatevyydet2]    Script Date: 8.5.2020 11:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[sa].[v_virta_otp_opettajapatevyydet2]'))
EXEC dbo.sp_executesql @statement = N'





CREATE VIEW [sa].[v_virta_otp_opettajapatevyydet2] AS

--Pätevyyksien ryhmittely

SELECT
	 opiskelija_avain
	,opiskelija
	,vuosi
	,sukupuoli_koodi = sukupuolikoodi
	,ika
	,organisaatio_koodi = organisaatiokoodi
	,patevyys_vuosi
	,[Varhaiskasvatuksen tehtäviin ammatillisia valmiuksia antavat opinnot] = case when [vo] is not null or [iq] is not null or [ke] is not null or [vs] is not null then 1 else 0 end
	,[Aineenopettajan pedagogiset opinnot] = case when [ik] is not null or [il] is not null or [im] is not null then 1 else 0 end
	,[Ammatillinen opettajakoulutus] = case when [ja] is not null or [jb] is not null or [ix] is not null then 1 else 0 end
	,[Perusopetuksessa opetettavien aineiden ja aihekokonaisuuksien monialaiset opinnot] = case when [in] is not null or [io] is not null or [ip] is not null then 1 else 0 end
	,[Erityisopettajan opinnot] = case when [is] is not null or [it] is not null or [iu] is not null then 1 else 0 end
	,[Opinto-ohjaajan opinnot] = case when [iv] is not null or [iw] is not null or [iy] is not null or [jc] is not null then 1 else 0 end
	,[Erityislastentarhanopettajan opinnot] = case when [ir] is not null then 1 else 0 end
	,tutkinto_koodi --= case when rnk_tutkinto_vuosi = 1 then tutkinto_koodi else null end
	,tutkinto_vuosi --= case when rnk_tutkinto_vuosi = 1 then tutkinto_vuosi else null end
	,tutkinto_organisaatio --= case when rnk_tutkinto_vuosi = 1 then tutkinto_organisaatio else null end
	,tutkinnon_yhteydessa
	,erillinen_eka = case when erillinen = 1 and rnk_patevyys_vuosi = 1 then 1 else 0 end
	,erillinen_tayd = case when erillinen = 1 and rnk_patevyys_vuosi != 1 then 1 else 0 end
	,rnk_tutkinto_vuosi
	,rnk_patevyys_vuosi
FROM (

	SELECT 
		 opiskelija_avain
		,opiskelija
		,vuosi
		,sukupuolikoodi
		,ika
		,organisaatiokoodi
		,patevyys_nro
		,q1.patevyys_koodi
		,d.patevyysryhma1_koodi
		,patevyys_selite
		,patevyys_vuosi
		,tutkinnon_yhteydessa
		,erillinen
		,tutkinto_koodi
		,tutkinto_vuosi
		,tutkinto_organisaatio
		,case 
			when d.patevyysryhma1_fi = ''opettajaaineet'' then null 
			else rank() over (partition by opiskelija_avain order by d.patevyysryhma1_fi, patevyys_vuosi, patevyys_nro, erillinen) 
		 end rnk_patevyys_vuosi
		,rank() over (partition by opiskelija_avain order by COALESCE(NULLIF(tutkinto_koodi,''999999''),''111111'') desc) rnk_tutkinto_vuosi
	FROM sa.v_virta_otp_opettajapatevyydet1 q1
	LEFT JOIN ANTERO.dw.d_virta_patevyydet d on d.patevyys_koodi = q1.patevyys_koodi
			
	
) q2


pivot (
		min(patevyys_koodi) for patevyys_koodi in (
			[vo],[iq],[ke],[vs],		--varhais
			[ik],[il],[im],				--aineen
			[ip],[in],[io],				--perusopetus
			[iu],[is],[it],				--erityis
			[iy],[iv],[iw],				--opo
			[ir],						--erityislastentarha
			[ja],[jb],[jc],[ix]			--ammatillinen
		)
) pvt

--vain uusin tutkinto
WHERE rnk_tutkinto_vuosi = 1

'




IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[sa].[v_virta_otp_opettajapatevyydet3]'))
EXEC dbo.sp_executesql @statement = N'


CREATE VIEW [sa].[v_virta_otp_opettajapatevyydet3] AS
