 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

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'










' 








