/*==============================================================================
FICHA: Indicador N°16 - DL 1153 2023
ELABORACION: OGTI/ OGEI - MINSA - JL
==============================================================================*/
use BD_HISINDICADORES
go

--***************************************************
--				BASES DE DATOS.
--***************************************************
--HIS MINSA 
select convert(date,periodo) fecha_atencion
	, convert(int,renaes) renaes
	, id_tipcond_serv
	, num_doc
	, id_tipitem tipo_dx
	, cod_item  
	, valor_lab
into #his_minsa
from BD_BACKUP_OGEI.dbo.MEF_TramaHisMinsa_202211_20221220 (nolock)
where id_tipo_doc=1 and sw=1
and cod_item in ('Z3491' ,'Z3492','Z3493','Z3591','Z3592','Z3593','Z359','Z349')		--Codigos de Atencion Prenatal

--REFCON
select num_doc, fecha_envio fecha, cod_unico renaes_origen, cod_unico_d renaes_destino, ups_destino, id_ciex
into #refcon
from BD_BACKUP_OGEI.dbo.MEF_TramaREFCON_202211_20221220 (nolock)
where tipo_traslado='REFERENCIA' and ups_destino like '%CONSULTA%EXTERNA%'
and tip_doc_pac='DNI' and sw=1
and (
	(id_ciex in ('O342'																				--Cesare anterior
				,'O990'																				--Anemia Severa
				,'O13X'																				--Hipertension Gestacional
				,'O23','O231','O232','O233','O234','O235'											--ITU
				,'O440'))																			--Placenta Previa
   or (id_ciex in ('Z3591','Z3592','Z3593') and P_edad_actual<=15 and P_tipoedad='A')				--Gestantes Adolescente
   )


--***************************************************
--					SINTAXIS
--***************************************************
declare @mes_eval int,
		@año int 

set @mes_eval=11 --<=========== Mes de evaluación
set @año=2022 --<============= Año de evaluación 

--%%%%%%%%%%%%%%%%
--  DENOMINADOR
--%%%%%%%%%%%%%%%%

--1.Total de gestantes atendidas nuevas en el servicio.
select distinct renaes, fecha_atencion, num_doc
into #hisminsa_den_aten
from(	
		select distinct renaes, fecha_atencion, num_doc
		, max(iif(id_tipcond_serv='N',1,0)) over (partition by num_doc, renaes) id 
		from #his_minsa
		where year(fecha_atencion)=@año
		and month(fecha_atencion)<=@mes_eval
) as t where id=1

-- Total de gestantes atendidas con 2 ó más APN en el hospital
select distinct renaes
				, fecha_atencion
				, num_doc
				, N_apn 
into #hisminsa_den
from(
		select  renaes, fecha_atencion, num_doc
		, row_number() over (partition by num_doc, renaes order by fecha_atencion asc) N_apn
		from #hisminsa_den_aten
) as t1 
where N_apn>=2


--%%%%%%%%%%%%%%%%
-- NUMERADOR
--%%%%%%%%%%%%%%%%

--1.Total de gestante atendida con 2 ó más APN en el hospital que han sido referidas por factores de riesgo específicos.

	--1.1 Cesare anterior
	select distinct num_doc, fecha, renaes_origen, renaes_destino, ups_destino
	into #refcon_cesarea from #refcon
	where id_ciex='O342' and year(fecha)=@año and month(fecha)<=@mes_eval

	select distinct renaes, fecha_atencion, num_doc, n_apn, num_a_cesarea=1, renaes_origen renaes_origen_a, ups_destino ups_destino_a
	into #referencia_cesarea
	from (
			select A.*, b.renaes_origen, b.ups_destino, row_number() over (partition by a.num_doc, a.renaes order by a.n_apn asc) id 
			from #hisminsa_den a
			inner join #refcon_cesarea b on a.renaes=b.renaes_destino and a.num_doc=b.num_doc collate Modern_Spanish_CI_AS 
			where a.fecha_atencion>=b.fecha
	) as t where id=1


	--1.2 Anemia Severa
	select distinct num_doc, fecha, renaes_origen, renaes_destino, ups_destino
	into #refcon_anemia from #refcon
	where id_ciex='O990' and year(fecha)=@año and month(fecha)<=@mes_eval

	select distinct renaes, fecha_atencion, num_doc, n_apn, num_b_anemia=1, renaes_origen renaes_origen_b, ups_destino  ups_destino_b
	into #referencia_anemia
	from (
			select A.*, b.renaes_origen, b.ups_destino, row_number() over (partition by a.num_doc, a.renaes order by a.n_apn asc) id 
			from #hisminsa_den a
			inner join #refcon_anemia b on a.renaes=b.renaes_destino and a.num_doc=b.num_doc collate Modern_Spanish_CI_AS 
			where a.fecha_atencion>=b.fecha
	) as t where id=1


	--1.3 Hipertension Gestacional
	select distinct num_doc, fecha, renaes_origen, renaes_destino, ups_destino
	into #refcon_hipertension from #refcon
	where id_ciex='O13X' and year(fecha)=@año and month(fecha)<=@mes_eval

	select distinct renaes, fecha_atencion, num_doc, n_apn, num_c_hipertension=1, renaes_origen renaes_origen_c, ups_destino ups_destino_c
	into #referencia_hipertension
	from (
			select A.*, b.renaes_origen, b.ups_destino, row_number() over (partition by a.num_doc, a.renaes order by a.n_apn asc) id 
			from #hisminsa_den a
			inner join #refcon_hipertension b on a.renaes=b.renaes_destino and a.num_doc=b.num_doc collate Modern_Spanish_CI_AS 
			where a.fecha_atencion>=b.fecha
	) as t where id=1


	--1.4 ITU
	select distinct num_doc, fecha, renaes_origen, renaes_destino, ups_destino
	into #refcon_ITU from #refcon
	where id_ciex in ('O23','O231','O232','O233','O234','O235') and year(fecha)=@año and month(fecha)<=@mes_eval

	select distinct renaes, fecha_atencion, num_doc, n_apn, num_d_itu=1, renaes_origen renaes_origen_d, ups_destino ups_destino_d
	into #referencia_ITU
	from (
			select A.*, b.renaes_origen, b.ups_destino, row_number() over (partition by a.num_doc, a.renaes order by a.n_apn asc) id 
			from #hisminsa_den a
			inner join #refcon_ITU b on a.renaes=b.renaes_destino and a.num_doc=b.num_doc collate Modern_Spanish_CI_AS 
			where a.fecha_atencion>=b.fecha
	) as t where id=1


	--1.5 Placenta Previa
	select distinct num_doc, fecha, renaes_origen, renaes_destino, ups_destino
	into #refcon_placenta from #refcon
	where id_ciex='O440' and year(fecha)=@año and month(fecha)<=@mes_eval

	select distinct renaes, fecha_atencion, num_doc, n_apn, num_e_placenta=1, renaes_origen renaes_origen_e, ups_destino ups_destino_e
	into #referencia_placenta
	from (
			select A.*, b.renaes_origen, b.ups_destino, row_number() over (partition by a.num_doc, a.renaes order by a.n_apn asc) id 
			from #hisminsa_den a
			inner join #refcon_placenta b on a.renaes=b.renaes_destino and a.num_doc=b.num_doc collate Modern_Spanish_CI_AS 
			where a.fecha_atencion>=b.fecha
	) as t where id=1

	
	--1.6 Gestantes adolescente
	select distinct num_doc, fecha, renaes_origen, renaes_destino, ups_destino
	into #refcon_gestadoles from #refcon
	where id_ciex in ('Z3591','Z3592','Z3593') and year(fecha)=@año and month(fecha)<=@mes_eval

	select distinct renaes, fecha_atencion, num_doc, n_apn, num_f_gestadoles=1, renaes_origen renaes_origen_f, ups_destino ups_destino_f
	into #referencia_gestadoles
	from (
			select A.*, b.renaes_origen, b.ups_destino, row_number() over (partition by a.num_doc, a.renaes order by a.n_apn asc) id 
			from #hisminsa_den a
			inner join #refcon_gestadoles b on a.renaes=b.renaes_destino and a.num_doc=b.num_doc collate Modern_Spanish_CI_AS 
			where a.fecha_atencion>=b.fecha
	) as t where id=1


--%%%%%%%%%%%%%%%%
--	INDICADOR
--%%%%%%%%%%%%%%%%
select a.*
, max(isnull(b.num_a_cesarea,0)) over (partition by a.num_doc, a.renaes) num_a_cesarea				, b.renaes_origen_a, b.ups_destino_a
, max(isnull(c.num_b_anemia,0)) over (partition by a.num_doc, a.renaes)  num_b_anemia				, c.renaes_origen_b, c.ups_destino_b
, max(isnull(d.num_c_hipertension,0)) over (partition by a.num_doc, a.renaes) num_c_hipertension	, d.renaes_origen_c, d.ups_destino_c
, max(isnull(e.num_d_itu,0)) over (partition by a.num_doc, a.renaes) num_d_itu						, e.renaes_origen_d, e.ups_destino_d
, max(isnull(f.num_e_placenta,0)) over (partition by a.num_doc, a.renaes) num_e_placenta			, f.renaes_origen_e, f.ups_destino_e
, max(isnull(g.num_f_gestadoles,0)) over (partition by a.num_doc, a.renaes) num_f_gestadoles		, g.renaes_origen_f, g.ups_destino_f
into #tabla_indicador
from #hisminsa_den a
left join #referencia_cesarea         b on a.renaes=b.renaes and a.fecha_atencion=b.fecha_atencion and a.N_apn=b.N_apn and a.num_doc=b.num_doc
left join #referencia_anemia		  c	on a.renaes=c.renaes and a.fecha_atencion=c.fecha_atencion and a.N_apn=c.N_apn and a.num_doc=c.num_doc
left join #referencia_hipertension    d on a.renaes=d.renaes and a.fecha_atencion=d.fecha_atencion and a.N_apn=d.N_apn and a.num_doc=d.num_doc
left join #referencia_ITU			  e on a.renaes=e.renaes and a.fecha_atencion=e.fecha_atencion and a.N_apn=e.N_apn and a.num_doc=e.num_doc
left join #referencia_placenta		  f on a.renaes=f.renaes and a.fecha_atencion=f.fecha_atencion and a.N_apn=f.N_apn and a.num_doc=f.num_doc
left join #referencia_gestadoles      g on a.renaes=g.renaes and a.fecha_atencion=g.fecha_atencion and a.N_apn=g.N_apn and a.num_doc=g.num_doc

--Generar numerador.
select *
, iif(num_a_cesarea + num_b_anemia + num_c_hipertension + num_d_itu + num_e_placenta + num_f_gestadoles>=1,1,0) id_num
into #tabla_indicador_previo
from #tabla_indicador

--Tabla reporte final.
select renaes, year(fecha_atencion) año, month(fecha_atencion) mes
, fecha_atencion, num_doc, den=1, id_num num
, num_a_cesarea, renaes_origen_a, ups_destino_a
, num_b_anemia, renaes_origen_b, ups_destino_b
, num_c_hipertension, renaes_origen_c, ups_destino_c
, num_d_itu, renaes_origen_d, ups_destino_d
, num_e_placenta, renaes_origen_e, ups_destino_e
, num_f_gestadoles, renaes_origen_f, ups_destino_f
into #tabla_reporte_final
from (
		select *, row_number() over (partition by num_doc, renaes order by id_num desc, fecha_atencion asc) id
		from #tabla_indicador_previo where id_num=1
) as t where id=1 and year(fecha_atencion)=@año

union all

select renaes, year(fecha_atencion) año, month(fecha_atencion) mes
, fecha_atencion, num_doc, den=1, id_num num
, num_a_cesarea, renaes_origen_a, ups_destino_a
, num_b_anemia, renaes_origen_b, ups_destino_b
, num_c_hipertension, renaes_origen_c, ups_destino_c
, num_d_itu, renaes_origen_d, ups_destino_d
, num_e_placenta, renaes_origen_e, ups_destino_e
, num_f_gestadoles, renaes_origen_f, ups_destino_f
from (
		select *, row_number() over (partition by num_doc, renaes order by fecha_atencion asc) id
		from #tabla_indicador_previo where id_num=0
) as t where id=1 and year(fecha_atencion)=@año

drop table #hisminsa_den
drop table #hisminsa_den_aten
drop table #refcon_anemia
drop table #refcon_cesarea
drop table #refcon_gestadoles
drop table #refcon_hipertension
drop table #refcon_ITU
drop table #refcon_placenta
drop table #referencia_anemia
drop table #referencia_cesarea
drop table #referencia_gestadoles
drop table #referencia_hipertension
drop table #referencia_ITU
drop table #referencia_placenta
drop table #tabla_indicador
drop table #tabla_indicador_previo

--===================
-- REPORTE
--===================
drop table BD_HISINDICADORES.dbo.DL1153_2023_CG16_APNreferencia

select 
B.diris Diris, CASE 
	WHEN B.DESC_DPTO='LIMA' AND B.DESC_PROV='LIMA' THEN 'LIMA METROPOLITANA'
	WHEN B.DESC_DPTO='LIMA' AND B.DESC_PROV<>'LIMA' THEN 'LIMA PROVINCIAS'
	ELSE B.DESC_DPTO END Departamento
, B.desc_prov Provincia
, B.desc_dist Distrito
, B.desc_red Red
, b.desc_mred MicroRed
, b.cat_estab
, b.desc_estab eess_nombre
, a.*
into BD_HISINDICADORES.dbo.DL1153_2023_CG16_APNreferencia
from #tabla_reporte_final a
inner join BD_BACKUP_OGEI.dbo.Renaes_20221220 b on convert(int,a.renaes)=convert(int,b.COD_ESTAB)
where b.CAT_ESTAB in ('III-2','II-2','II-1','II-E','III-E','III-1') and convert(int,b.ambito)=1


select Diris, Departamento, Provincia, Distrito, Red, MicroRed, Cat_estab, eess_nombre, mes, año 
, sum(den) den, sum(num) num
, sum(num_a_cesarea)  num_a_cesarea
, sum(num_b_anemia)   num_b_anemia
, sum(num_c_hipertension) num_c_hipertension
, sum(num_d_itu)  num_d_itu
, sum(num_e_placenta)  num_e_placenta
, sum(num_f_gestadoles)  num_f_gestadoles
from BD_HISINDICADORES.dbo.DL1153_2023_CG16_APNreferencia
group by Diris, Departamento, Provincia, Distrito, Red, MicroRed, Cat_estab, eess_nombre, mes, año 


drop table #his_minsa
drop table #refcon
drop table #tabla_reporte_final
-- =D