USE [BD_HISINDICADORES]
GO
/* Ficha N°03. Porcentaje de recién nacidos con controles CRED completo para la edad.
Numerador: Total de niñas/niños recién nacidos del denominador, con cuatro (04) controles de CRED hasta los 28 días registrados en HIS). 
Sintaxis: Suma de DNI o CNV que forman parte del denominador que registraron en HIS 04 controles CRED (CPMS: 99381.01) hasta los 29 días.
Denominador: Total de niñas/niños recién nacidos de 29 días de vida en el periodo de evaluación, con SIS, sin dato de seguro y sin seguro, registrados en el padrón nominal con DNI o CNV, según el ámbito de la jurisdicción determinado por la residencia registrada en el padrón nominal. 
Excluyendo las niñas/niños con bajo peso al nacer (menor de 2500 gramos) y/o prematuros (menor de 37 SG), registrados en CNV 
Sintaxis: Suma de recién nacidos que cumplen 29 días, registrados en padrón nominal con DNI o CNV. Excluyendo las niñas/niños con bajo peso al nacer (menor de 2500 gramos) y/o prematuros (menor de 37 SG), registrados en CNV

*/
-----------------BASE PADRON NOMINAL--------------------------
if object_id(N'tempdb..#DL1153_IND3_CRED_RN0', N'u') is not null drop table #DL1153_IND3_CRED_RN0;
	select  pn.co_padron_nominal,pn.ti_doc_identidad ,pn.nu_dni_menor , pn.nu_cui
		,pn.nu_cnv
		,case when isnumeric(pn.nu_dni_menor) = 0 then pn.nu_cnv 
			else pn.nu_dni_menor end as dni_cnv_menor
		, pn.de_genero_menor
		,convert(date,pn.fe_nac_menor,103) as fe_nac_menor
		,pn.co_dni_madre 
		,try_convert(int,rtrim(ltrim(pn.co_ubigeo_inei))) as co_ubigeo_inei
		,pn.co_centro_poblado		
		,mae.DESC_DPTO ,mae.DESC_PROV ,mae.DESC_DIST , mae.DIRIS
		--,fed as distritos_fed
		,pn.ti_seguro_menor as tipo_seguro
	 into #DL1153_IND3_CRED_RN0
	from  BD_BACKUP_OGEI.dbo.MEF_TramaPadronNominal_202308_20230920 pn
		 join Maestro_UBIGEO_20200407  mae on try_convert(int,rtrim(ltrim(pn.co_ubigeo_inei))) = try_convert(int,mae.ubigeo)
	where 	(isnumeric(pn.nu_dni_menor) = 1 
			or isnumeric(pn.nu_cnv) = 1 ) AND pn.ti_doc_identidad IN (1,3)  --asegura no ingresar registros nulos 
	
delete from  #DL1153_IND3_CRED_RN0 where dni_cnv_menor is null

	delete from #DL1153_IND3_CRED_RN0
		where convert(varchar(20),dni_cnv_menor) in (
								select convert(varchar(20),dni_cnv_menor)
								from #DL1153_IND3_CRED_RN0
								group by  convert(varchar(20),dni_cnv_menor)
								having count(*)>1
								) 
				and TI_DOC_IDENTIDAD not in (1)

if object_id(N'DL1153_IND3_CRED_RN', N'u') is not null drop table DL1153_IND3_CRED_RN;
	select convert(varchar(6),dateadd(day,29,convert(date,pn.fe_nac_menor,103)),112)  as periodo
		,dateadd(day,29,convert(date,pn.fe_nac_menor,103)) as cumple29d
		,pn.* 
		,cast(null as varchar(50)) as tipSeguro_cnv
		,0 as denominador ,0 as numerador 
		,0 as bp_prem
		,0 as BCG ,0 as HvB
		,cast(null as varchar(20)) as FecRN1,cast(null as varchar(20)) as FecRN2 ,0 as credRN1,0 as credRN2
		,cast(null as varchar(20))  as FecRN3,cast(null as varchar(20))  as FecRN4 ,0 as credRN3,0 as credRN4
		,0 as CRED_RN
		,0 as Tam_Neonatal
	into DL1153_IND3_CRED_RN
	from #DL1153_IND3_CRED_RN0 pn
		where convert(varchar(4),dateadd(day,29,convert(date,pn.fe_nac_menor,103)),112)='2023'


-----------------BASE CNV--------------------------
if object_id(N'tempdb..#histab_cnv_bajopeso', N'U') is not null drop table #histab_cnv_bajopeso;
		select nu_cnv 
				,peso_nacido as peso	
				,dur_emb_parto as semanas_embarazo
			into #histab_cnv_bajopeso
		from BD_BACKUP_OGEI.dbo.MEF_TramaCNV_202308_20230920 ---tabla de minsa
		where TRY_CONVERT(int,NU_CNV) IS NOT NULL and TRY_CONVERT(int,NU_CNV)<>0
			and ( 
					try_convert(int,peso_nacido) <2500
					or try_convert(int,dur_emb_parto) <37
				)

update a
set bp_prem = 1
from DL1153_IND3_CRED_RN a
			join #histab_cnv_bajopeso b on  try_convert(int,a.dni_cnv_menor) = try_convert(int,b.nu_cnv)

update  DL1153_IND3_CRED_RN
set denominador=1
where --tipo_seguro=1 and 
bp_prem=0


update a
set tipSeguro_cnv = case when FINANC_PARTO='02' then 'SIS' when FINANC_PARTO in ('00','01','10','11') then 'Otros' else null end 
from DL1153_IND3_CRED_RN a
			join BD_BACKUP_OGEI.dbo.MEF_TramaCNV_202308_20230920  b on  try_convert(int,a.dni_cnv_menor) = try_convert(int,b.nu_cnv)





-----------------BASE HIS--------------------------

if object_id(N'tempdb..#dni_cnv_menores', N'u') is not null drop table #dni_cnv_menores;
	select distinct dni_cnv_menor,  fe_nac_menor,
		case when ti_doc_identidad not in ('1') then 6 else ti_doc_identidad end  as ti_doc_identidad
	into #dni_cnv_menores
	from DL1153_IND3_CRED_RN


if object_id(N'tempdb..#FED_MC03_2023_CRED_ATE', N'u') is not null drop table #FED_MC03_2023_CRED_ATE;
	 select id_cita 
		,right('00000000'+renaes,10) as cod_estab
		,periodo as fecate
		,ti_doc_identidad
		,num_doc as dni_his
		,dni_cnv_menor
		,fe_nac_menor
		,rownum as ncodigo
		,cod_item as codigo
		,id_tipitem as tipo_diagnost	
		,valor_lab 
	into #FED_MC03_2023_CRED_ATE 
		from BD_BACKUP_OGEI.dbo.MEF_TramaHisMinsa_202308_20230920 his
		join #dni_cnv_menores pn 	on  try_convert(int,his.num_doc) = try_convert(int,pn.dni_cnv_menor)
	where	cod_item in ('99381.01','Z001')
			and id_tipo_doc in (1,6)

	insert into #FED_MC03_2023_CRED_ATE
		select id_cita 
			,right('00000000'+renaes,10) as cod_estab
			,periodo as fecate
			,ti_doc_identidad
			,num_doc as dni_his
			,dni_cnv_menor
			,fe_nac_menor
			,rownum as ncodigo
			,cod_item as codigo
			,id_tipitem as tipo_diagnost	
			,valor_lab 
		from TramaHisMinsa_2022_20230302 his  
			join #dni_cnv_menores pn on  try_convert(int,his.num_doc) = try_convert(int,pn.dni_cnv_menor)
		where	cod_item in ('99381.01','Z001')
			and id_tipo_doc in (1,6)


-----------------------------------------------------------------------------------------
/* Numerador: : Suma de recién nacidos del  denominador  que cumplen con los siguientes criterios, registrado en HIS con DNI o CNV en línea:

	2. Cuentan con 4 controles CRED según edad registrado con código: HIS CIE Z001 o CPMS 99381.01., como se detalla a continuación:
	a) El primer control CRED + Lab1, se realiza a partir del 3er día de vida hasta el 6to día de vida, y 
	b) A partir del segundo control CRED hasta el cuarto control CRED, se realiza un control cada semana (07 días)
	y lab 2,3 y 4. (SACAR REPORTE CON LAB Y SIN LAB).
	Nota: En los RN menores a 3 días de vida se les da por cumplido el indicador.*/

	-- Control RN1
if object_id(N'tempdb..#RN1', N'U') IS NOT NULL DROP TABLE #RN1;
	select distinct  a.periodo,a.dni_cnv_menor, a.fe_nac_menor, a.cumple29d, b.fecate Feccred_rn1 into #RN1 
	from DL1153_IND3_CRED_RN a 
	inner join #FED_MC03_2023_CRED_ATE b on a.dni_cnv_menor=b.dni_cnv_menor and convert(date,b.fecate,103) <= convert(date,cumple29d,103)
	and (datediff(dd,a.fe_nac_menor,b.fecate) between 3 and 6) and fecate<=cumple29d 
	WHERE codigo IN ('99381.01','Z001')
	and denominador = 1


-- Control RN2
if object_id(N'tempdb..#RN2', N'U') IS NOT NULL DROP TABLE #RN2;
	select distinct  a.periodo,a.dni_cnv_menor, a.fe_nac_menor, a.cumple29d, b.fecate Feccred_rn2 into #RN2 
	from #RN1 a inner join #FED_MC03_2023_CRED_ATE b on a.dni_cnv_menor=b.dni_cnv_menor and convert(date,b.fecate,103) <= convert(date,cumple29d,103)
	and  (datediff(dd,a.fe_nac_menor,b.fecate) between 7 and 13) and convert(date,b.fecate,103)>=dateadd(dd,3,Feccred_rn1)
	WHERE codigo IN ('99381.01','Z001')
	

-- Control RN3
if object_id(N'tempdb..#RN3', N'U') IS NOT NULL DROP TABLE #RN3;
	select distinct  a.periodo,a.dni_cnv_menor, a.fe_nac_menor, a.cumple29d, b.fecate Feccred_rn3 into #RN3 
	from #RN2 a inner join #FED_MC03_2023_CRED_ATE b on a.dni_cnv_menor=b.dni_cnv_menor and convert(date,b.fecate,103) <= convert(date,cumple29d,103)
	and (datediff(dd,a.fe_nac_menor,b.fecate) between 14 and 20) and convert(date,b.fecate,103)>=dateadd(dd,7,Feccred_rn2)
	WHERE codigo IN ('99381.01','Z001')

-- Control RN4
if object_id(N'tempdb..#RN4', N'U') IS NOT NULL DROP TABLE #RN4;
	select distinct  a.periodo,a.dni_cnv_menor, a.fe_nac_menor, a.cumple29d, b.fecate Feccred_rn4 into #RN4
	from #RN3 a inner join #FED_MC03_2023_CRED_ATE b on a.dni_cnv_menor=b.dni_cnv_menor and convert(date,b.fecate,103) <= convert(date,cumple29d,103)
	and (datediff(dd,a.fe_nac_menor,b.fecate) between 21 and 28) and convert(date,b.fecate,103)>=dateadd(dd,7,Feccred_rn3)
	WHERE codigo IN ('99381.01','Z001')

if object_id(N'tempdb..#Cred_RN_1_4', N'U') IS NOT NULL DROP TABLE #Cred_RN_1_4;
	select distinct a.*,feccred_rn2,feccred_rn3,feccred_rn4
		,iif(feccred_rn1 is not null,1,0) credRN1
		,iif(feccred_rn2 is not null,1,0) credRN2
		,iif(feccred_rn3 is not null,1,0) credRN3
		,iif(feccred_rn4 is not null,1,0) credRN4
	into #Cred_RN_1_4
	from #RN1 a
		left join #RN2 b on  a.dni_cnv_menor = b.dni_cnv_menor and a.periodo=b.periodo
		LEFT join #RN3 c on  a.dni_cnv_menor = c.dni_cnv_menor and a.periodo=c.periodo
		LEFT join #RN4 d on   a.dni_cnv_menor = d.dni_cnv_menor and a.periodo=d.periodo


UPDATE a
set FecRN1=Feccred_rn1
,FecRN2=feccred_rn2
,credRN1=b.credRN1
,credRN2=b.credRN2
,FecRN3=feccred_rn3
,FecRN4=feccred_rn4
,credRN3=b.credRN3
,credRN4=b.credRN4

from DL1153_IND3_CRED_RN a
	join #Cred_RN_1_4 b on a.dni_cnv_menor = b.dni_cnv_menor and a.periodo=b.periodo


/*******************************************************************************************************************************************************/

update  DL1153_IND3_CRED_RN
set numerador=1
where denominador=1
		and credRN1=1 and credRN2=1 and credRN3=1 and credRN4=1  --2
	
/**********************************************************************************************
		REPORTE DE INDICADOR
***********************************************************************************************/


if object_id(N'..R_DL1153_IND3_CRED_RN', N'u') is not null drop table R_DL1153_IND3_CRED_RN;
	select	Periodo
			,case when ti_doc_identidad not in ('1') then 'CNV' else 'DNI' end  as ti_doc_identidad
			,case
					when left(co_ubigeo_inei,4) = '1501' and desc_dpto = '15 LIMA' then '15 LIMA METROPOLITANA'
					when desc_dpto = '15 LIMA'  and left(co_ubigeo_inei,4) <>'1501' then '15 LIMA PROVINCIAS'
				else desc_dpto 
			end as desc_dpto 
			,DIRIS
			,DESC_PROV ,DESC_DIST
			
			,tipo_seguro 
			,tipSeguro_cnv
			,bp_prem
			,sum(isnull(denominador,0)) as denominador
			,sum(isnull(numerador,0)) as numerador	
			,sum(isnull(credRN1,0)) as credRN1 
			,sum(isnull(credRN2,0)) as credRN2 
			,sum(isnull(credRN3,0)) as credRN3 
			,sum(isnull(credRN4,0)) as credRN4	
			,sum(isnull(cred_RN,0)) as credRN	
			into R_DL1153_IND3_CRED_RN
	from  DL1153_IND3_CRED_RN pn
	where denominador=1 and bp_prem=0
	group by Periodo
			,case when ti_doc_identidad not in ('1') then 'CNV' else 'DNI' end
			,case
					when left(co_ubigeo_inei,4) = '1501' and DESC_DPTO = '15 LIMA' then '15 LIMA METROPOLITANA'
					when DESC_DPTO = '15 LIMA'  and left(co_ubigeo_inei,4) <>'1501' then '15 LIMA PROVINCIAS'
				else desc_dpto 
			end  
			,DIRIS
			,DESC_PROV ,DESC_DIST
			
			,tipo_seguro 
			,tipSeguro_cnv
			,bp_prem
	order by 1


delete from R_DL1153_IND3_CRED_RN where TRY_CONVERT(int,PERIODO) <202201 or TRY_CONVERT(int,periodo)>202308
select periodo,sum(isnull(numerador,0)) as num	,sum(isnull(denominador,0)) as den	
			,sum(isnull(credRN1,0)) as credRN1 ,sum(isnull(credRN2,0)) as credRN2 
			,sum(isnull(credRN3,0)) as credRN3 ,sum(isnull(credRN4,0)) as credRN4				
		
from R_DL1153_IND3_CRED_RN
where tipo_seguro=1--tipSeguro_cnv IN ('SIS','otro')--
group by periodo
order by 1

select * from R_DL1153_IND3_CRED_RN --order by dni_cnv_menor, periodo
