/*======================================================================================
FICHA: Indicador N° Porcentaje de personas con diagnóstico de cánceres prevalentes
que inician tratamiento oncológico en un tiempo menor y/o igual a 30 días - DL 1153 2024
ELABORACION: OGTI/ OGEI - MINSA
========================================================================================*/
use BD_HISINDICADORES
go

--***************************************************
--				BASES DE DATOS.
--***************************************************
IF OBJECT_ID('Tempdb..#renaes') IS NOT NULL DROP TABLE #renaes
select
	r.*
into #renaes
from BD_BACKUP_OGEI.dbo.Renaes_20231023 r
where	CAT_ESTAB in ('III-2','II-2','II-1','II-E','III-E','III-1') and
		convert(int,ambito) = 1 AND
		convert(int,cod_estab) in (1232,7397,7686,6218,2289,2305,3358,
								   3359,27857,753,7277,11470,6206,16918,
								   6210,6207,6211,5946,7634,7633,5987,
								   3,2084)

--HIS MINSA 
IF OBJECT_ID('Tempdb..#his_minsa') IS NOT NULL DROP TABLE #his_minsa
select
	id_cita			,
	fecha_atencion	= convert(date,periodo)	,
	renaes			= convert(int,renaes)	,
	id_tipo_doc		,
	num_doc			,
	id_tipedad_reg	,
	edad_reg		,
	id_genero		,
	tipo_dx			= id_tipitem,
	cod_item		,
	valor_lab
into #his_minsa
from BD_BACKUP_OGEI.dbo.MEF_TramaHisMinsa_202309_20231023 h with (nolock)
INNER JOIN #renaes r														ON h.renaes = CONVERT(INT,r.COD_ESTAB )
where sw = 1		and						--Validacion de tipo y numero de documento 
( 
	 (  (cod_item in ('19100','19101','19102','57500','77057','38500','38505')) or				-- Cancer de mama.
		(substring(cod_item,1,4) between 'C500' and 'C509')									)	  
or	 (	(cod_item='47000')														or				-- Cáncer de hígado.
		(substring(cod_item,1,4) between 'C220' and 'C229')									)	 
or   (	(cod_item in ('43234','43600'))											or				-- Cáncer de estómago 
		(substring(cod_item,1,4) between 'C160' and 'C169')									)																					
or   (	(cod_item in ('57500','58100','57291','57510','57520'))					or				-- Cáncer de cuello uterino
		(substring(cod_item,1,4) in ('C530','C531','C539'))									)	 																							
or	 (	(cod_item in ('44388','44389','45359','45358'))							or				-- Cáncer de colon y recto
		(substring(cod_item,1,4) in ('c19x','C20x'))							or
		(substring(cod_item,1,4) between 'C180' and 'C189')									)	 
or	 (	(cod_item='11100')														or				-- Cáncer de piel 
		(substring(cod_item,1,4) between 'C430' and 'C449')									)	
or   (	(cod_item='55700')														or				-- Cáncer de próstata
		(substring(cod_item,1,4)='C61X')													)	 
or	 (	(cod_item IN ('38500','88206'))											or				-- Linfoma
		(substring(cod_item,1,4)='C963')										or
		(substring(cod_item,1,4) between 'C810' and 'C859')									)	 
or	 (	(cod_item in ('85095','85102','38220','88185.02','88185.03','88205.04','85060')) or		-- Leucemia
		(substring(cod_item,1,4) = 'C901')										or
		(substring(cod_item,1,4) between 'C910' and 'C959')									)	
or	 (	(cod_item in ('32405','32400'))											or				-- Cáncer de pulmón
		(substring(cod_item,1,4) between 'C340' and 'C349')									)	
or   (	(cod_item in ('96402','96407','96549','90780','90782','90784','77305',					-- Tratamiento CPMS
					  '77500','77501','77781','77295','77301','77315','77401',
					  '77784','77290','76499','77263','77418','77285'))			or				
		(cod_item between '96409' and '96417')									or
		(cod_item between '96420' and '96423')												)
)


--***************************************************
--					SINTAXIS
--***************************************************
IF OBJECT_ID('Tempdb..#tabla_reporte') IS NOT NULL DROP TABLE #tabla_reporte
create table #tabla_reporte
(
año int,
mes int,
renaes int,
den int,
num int
)

declare @mes_inicio int,
		@mes_eval	int,
		@año		int 

set @mes_inicio	= 1		--<=========== Mes de evaluación inicial
set @mes_eval	= 9		--<=========== Mes de evaluación final
set @año		= 2023	--<=========== Año de evaluación 

while @mes_inicio <= @mes_eval
begin 

--%%%%%%%%%%%%%%%%
-- DENOMINADOR
--%%%%%%%%%%%%%%%%
--1. Sumatoria del Total de personas identificadas con Documento de Identidad con diagnóstico de prevalente definitivo

	--1.1 Cáncer de mama
	IF OBJECT_ID('Tempdb..#his_minsa_den_mama') IS NOT NULL DROP TABLE #his_minsa_den_mama
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc		,
		Den			= 1
	into #his_minsa_den_mama 
	from #his_minsa
	where	(substring(cod_item,1,4) between 'C500' and 'C509') and
			tipo_dx = 'D'										and
			(valor_lab is null or valor_lab = '')				and
			(id_cita in (	select
								distinct
								id_cita
							from #his_minsa
							where	cod_item	in ('19100','19101','19102','38505',
													'57500','77057','38500')		and
									tipo_dx		=	'D'								and
									valor_lab	=	'A')
			 )													and
			 edad_reg				>= 18						and
			 id_tipedad_reg			 = 'A'						and
			 id_genero				 = 'F'						and
			 year(fecha_atencion)	 = @año						and
			 month(fecha_atencion)  <= @mes_inicio

	--1.2 Cáncer de hígado
	IF OBJECT_ID('Tempdb..#his_minsa_den_higado') IS NOT NULL DROP TABLE #his_minsa_den_higado
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc		,
		Den			= 1
	into #his_minsa_den_higado
	from #his_minsa
	where	(substring(cod_item,1,4) between 'C220' and 'C229') and
			tipo_dx = 'D'										and
			(valor_lab is null or valor_lab = '')				and
			( id_cita in (	select
								distinct
								id_cita
							from #his_minsa
							where	cod_item  = '47000'	and
									tipo_dx   = 'D'		and
									valor_lab = 'A')
			)													AND
			edad_reg			  >= 18							and
			id_tipedad_reg		   = 'A'						AND
			year(fecha_atencion)   = @año						and
			month(fecha_atencion) <= @mes_inicio

	--1.3 Cáncer de estómago
	IF OBJECT_ID('Tempdb..#his_minsa_den_estomago') IS NOT NULL DROP TABLE #his_minsa_den_estomago
	select distinct renaes, id_tipo_doc, num_doc,
		Den			= 1
	into #his_minsa_den_estomago
	from #his_minsa
	where (substring(cod_item,1,4) between 'C160' and 'C169')	
	and tipo_dx = 'D'
	and (valor_lab is null or valor_lab = '')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('43234','43600') and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.4 Cáncer de cuello uterino
	IF OBJECT_ID('Tempdb..#his_minsa_den_uterino') IS NOT NULL DROP TABLE #his_minsa_den_uterino
	select distinct renaes, id_tipo_doc, num_doc,
		Den			= 1
	into #his_minsa_den_uterino
	from #his_minsa
	where (substring(cod_item,1,4) in ('C530','C531','C539'))	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('57500','58100','57510',
										 '57291','57520')			and
							tipo_dx   = 'D'							and
							valor_lab = 'A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and id_genero='F'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	-- 1.5 Cáncer de colon y recto
	IF OBJECT_ID('Tempdb..#his_minsa_den_colon') IS NOT NULL DROP TABLE #his_minsa_den_colon
	select distinct renaes, id_tipo_doc, num_doc,
		Den			= 1
	into #his_minsa_den_colon
	from #his_minsa
	where ( (substring(cod_item,1,4) between 'C180' and 'C189') or (substring(cod_item,1,4) in ('C19X','C20X'))  )	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('44388','44389','45359','45358') And tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	-- 1.6 Cáncer de piel
	IF OBJECT_ID('Tempdb..#his_minsa_den_piel') IS NOT NULL DROP TABLE #his_minsa_den_piel
	select distinct renaes, id_tipo_doc, num_doc,
		Den			= 1
	into #his_minsa_den_piel
	from #his_minsa
	where (substring(cod_item,1,4) between 'c430' and 'c449')  	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item='11100' and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	-- 1.7 Cáncer de prostata
	IF OBJECT_ID('Tempdb..#his_minsa_den_prostata') IS NOT NULL DROP TABLE #his_minsa_den_prostata
	select distinct renaes, id_tipo_doc, num_doc,
		Den			= 1
	into #his_minsa_den_prostata
	from #his_minsa
	where (substring(cod_item,1,4)='c61x')  	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item='55700' and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and id_genero='M'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.8 Linfoma
	IF OBJECT_ID('Tempdb..#his_minsa_den_linfoma') IS NOT NULL DROP TABLE #his_minsa_den_linfoma
	select distinct renaes, id_tipo_doc, num_doc,
		Den			= 1
	into #his_minsa_den_linfoma
	from #his_minsa
	where ( (substring(cod_item,1,4)='C963') or (substring(cod_item,1,4) between 'c810' and 'c859') )
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item IN ('38500','88206') and tipo_dx = 'D' and valor_lab = 'A')
		)
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.9 Leucemia
	IF OBJECT_ID('Tempdb..#his_minsa_den_leucemia') IS NOT NULL DROP TABLE #his_minsa_den_leucemia
	select distinct renaes, id_tipo_doc, num_doc,
		Den			= 1
	into #his_minsa_den_leucemia
	from #his_minsa
	where ( (substring(cod_item,1,4)='C901') or (substring(cod_item,1,4) between 'c910' and 'c959') )
	and tipo_dx='D'
	and (valor_lab is null or valor_lab = '')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('85095','85102','38220','88185.02',
										 '88185.03','88205.04','85060') and tipo_dx='D' and valor_lab='A')
		)
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.10 Cancer Pulmon
	IF OBJECT_ID('Tempdb..#his_minsa_den_pulmon') IS NOT NULL DROP TABLE #his_minsa_den_pulmon
	select distinct renaes, id_tipo_doc, num_doc,
		Den			= 1
	into #his_minsa_den_pulmon
	from #his_minsa
	where (substring(cod_item,1,4) between 'C340' and 'C349') 
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('32405','32400') and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg >= 18 and id_tipedad_reg='A'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--Denominador Final.
	IF OBJECT_ID('Tempdb..#dpcan_den_Parcial') IS NOT NULL DROP TABLE #dpcan_den_Parcial
	select
		distinct
		renaes	,
		id_tipo_doc	,
		num_doc		,
		Den			= 1
	into #dpcan_den_Parcial
	from (
			select a.* from			  #his_minsa_den_colon a
			union all select b.* from #his_minsa_den_estomago b
			union all select c.* from #his_minsa_den_higado c
			union all select d.* from #his_minsa_den_leucemia d
			union all select e.* from #his_minsa_den_linfoma e
			union all select f.* from #his_minsa_den_mama f
			union all select g.* from #his_minsa_den_piel g
			union all select h.* from #his_minsa_den_pulmon h
			union all select i.* from #his_minsa_den_prostata i
			union all select j.* from #his_minsa_den_uterino j
	) as t

	IF OBJECT_ID('Tempdb..#dpcan_den') IS NOT NULL DROP TABLE #dpcan_den
	SELECT
		t.*,
		dColon		= ISNULL(a.Den,0),
		dEstomago	= ISNULL(b.Den,0),
		dhigado		= ISNULL(c.Den,0),		
		dleucemia	= ISNULL(d.Den,0),
		dlinfoma	= ISNULL(e.Den,0),
		dmama		= ISNULL(f.Den,0),	
		dpiel		= ISNULL(g.Den,0),	
		dpulmon		= ISNULL(h.Den,0),	
		dprostata	= ISNULL(i.Den,0),
		duterino	= ISNULL(j.Den,0)
	INTO #dpcan_den
	FROM #dpcan_den_Parcial				t
	LEFT JOIN #his_minsa_den_colon		a ON t.num_doc = a.num_doc AND t.id_tipo_doc = a.id_tipo_doc AND t.renaes = a.renaes
	LEFT JOIN #his_minsa_den_estomago	b ON t.num_doc = b.num_doc AND t.id_tipo_doc = b.id_tipo_doc AND t.renaes = b.renaes
	LEFT JOIN #his_minsa_den_higado		c ON t.num_doc = c.num_doc AND t.id_tipo_doc = c.id_tipo_doc AND t.renaes = c.renaes
	LEFT JOIN #his_minsa_den_leucemia	d ON t.num_doc = d.num_doc AND t.id_tipo_doc = d.id_tipo_doc AND t.renaes = d.renaes
	LEFT JOIN #his_minsa_den_linfoma	e ON t.num_doc = e.num_doc AND t.id_tipo_doc = e.id_tipo_doc AND t.renaes = e.renaes
	LEFT JOIN #his_minsa_den_mama		f ON t.num_doc = f.num_doc AND t.id_tipo_doc = f.id_tipo_doc AND t.renaes = f.renaes
	LEFT JOIN #his_minsa_den_piel		g ON t.num_doc = g.num_doc AND t.id_tipo_doc = g.id_tipo_doc AND t.renaes = g.renaes
	LEFT JOIN #his_minsa_den_pulmon		h ON t.num_doc = h.num_doc AND t.id_tipo_doc = h.id_tipo_doc AND t.renaes = h.renaes
	LEFT JOIN #his_minsa_den_prostata	i ON t.num_doc = i.num_doc AND t.id_tipo_doc = i.id_tipo_doc AND t.renaes = i.renaes
	LEFT JOIN #his_minsa_den_uterino	j ON t.num_doc = j.num_doc AND t.id_tipo_doc = j.id_tipo_doc AND t.renaes = j.renaes

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

--1. Número de personas del denominador que inician tratamiento de quimioterapia y registradas en el HIS
	--1.1 Tratamiento contra Cancer de cuello uterino:
	IF OBJECT_ID('Tempdb..#his_minsa_Num_uterino') IS NOT NULL DROP TABLE #his_minsa_Num_uterino
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc		,
		Num			= 1
	into #his_minsa_Num_uterino
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or 
			(cod_item between '96420' and '96423') or
			(cod_item in ('96402','96407','96549','90780','90782',
						  '90784','77305','77500','77501','77781',
						  '77295','77301','77315','77401','77784',
						  '77290','76499','77263','77418','77285'))
		   )														AND
		tipo_dx						= 'D'							AND
		try_convert(int,valor_lab)	= 1								AND
		( id_cita in (select
						 distinct
						 id_cita
						 from #his_minsa
						 where	cod_item	IN ('C530','C531','C539')	AND
								tipo_dx		= 'R'						AND
								(valor_lab	= '' OR valor_lab IS NULL)
					  )
		)										AND
		 edad_reg				>= 18			AND
		 id_tipedad_reg			 = 'A'			AND
		 id_genero				 = 'F'			AND
		 year(fecha_atencion)	 = @año			AND
		 month(fecha_atencion)	<= @mes_inicio	

	--1.2 Tratamiento contra Cancer de mama. 
	IF OBJECT_ID('Tempdb..#his_minsa_Num_mama') IS NOT NULL DROP TABLE #his_minsa_Num_mama
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc,
		Num			= 1
	into #his_minsa_Num_mama
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or 
			(cod_item between '96420' and '96423') or
			(cod_item in ('96402','96407','96549','90780','90782',
						  '90784','77305','77500','77501','77781',
						  '77295','77301','77315','77401','77784',
						  '77290','76499','77263','77418','77285'))
		   )														AND
		tipo_dx						= 'D'							AND
		try_convert(int,valor_lab)	= 1								AND
		( id_cita in (select
						 distinct
						 id_cita
						 from #his_minsa
						 where	(substring(cod_item,1,4) between 'C500' and 'C509')	AND
								tipo_dx		= 'R'									AND
								(valor_lab	= '' OR valor_lab IS NULL)
					  )
		)										AND
		 edad_reg				>= 18			AND
		 id_tipedad_reg			 = 'A'			AND
		 id_genero				 = 'F'			AND
		 year(fecha_atencion)	 = @año			AND
		 month(fecha_atencion)	<= @mes_inicio

	--1.3 Tratamiento contra Cáncer de próstata. 
	IF OBJECT_ID('Tempdb..#his_minsa_Num_prostata') IS NOT NULL DROP TABLE #his_minsa_Num_prostata
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc,
		Num			= 1
	into #his_minsa_Num_prostata
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or 
			(cod_item between '96420' and '96423') or
			(cod_item in ('96402','96407','96549','90780','90782',
						  '90784','77305','77500','77501','77781',
						  '77295','77301','77315','77401','77784',
						  '77290','76499','77263','77418','77285'))
		   )														AND
		tipo_dx						= 'D'							AND
		try_convert(int,valor_lab)	= 1								AND
		( id_cita in (select
						 distinct
						 id_cita
						 from #his_minsa
						 where	substring(cod_item,1,4) = 'C61X'	AND
								tipo_dx					= 'R'		AND
								(valor_lab	= '' OR valor_lab IS NULL)
					  )
		)										AND
		 edad_reg				>= 18			AND
		 id_tipedad_reg			 = 'A'			AND
		 id_genero				 = 'M'			AND
		 year(fecha_atencion)	 = @año			AND
		 month(fecha_atencion)	<= @mes_inicio

	--1.4 Tratamiento contra Cáncer de colon y recto. 
	IF OBJECT_ID('Tempdb..#his_minsa_Num_colon') IS NOT NULL DROP TABLE #his_minsa_Num_colon
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc,
		Num			= 1
	into #his_minsa_Num_colon
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or 
			(cod_item between '96420' and '96423') or
			(cod_item in ('96402','96407','96549','90780','90782',
						  '90784','77305','77500','77501','77781',
						  '77295','77301','77315','77401','77784',
						  '77290','76499','77263','77418','77285'))
		   )														AND
		tipo_dx						= 'D'							AND
		try_convert(int,valor_lab)	= 1								AND
		( id_cita in (select
						 distinct
						 id_cita
						 from #his_minsa
						 where	(substring(cod_item,1,4) between 'C180' AND 'C189') OR
								 substring(cod_item,1,4) IN ('C19X','C20X')			AND
								tipo_dx		= 'R'									AND
								(valor_lab	= '' OR valor_lab IS NULL)
					  )
		)										AND
		 edad_reg				>= 18			AND
		 id_tipedad_reg			 = 'A'			AND
		 year(fecha_atencion)	 = @año			AND
		 month(fecha_atencion)	<= @mes_inicio

	--1.5 Tratamiento contra Cáncer de piel. 
	IF OBJECT_ID('Tempdb..#his_minsa_Num_piel') IS NOT NULL DROP TABLE #his_minsa_Num_piel
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc,
		Num			= 1
	into #his_minsa_Num_piel
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or 
			(cod_item between '96420' and '96423') or
			(cod_item in ('96402','96407','96549','90780','90782',
						  '90784','77305','77500','77501','77781',
						  '77295','77301','77315','77401','77784',
						  '77290','76499','77263','77418','77285'))
		   )														AND
		tipo_dx						= 'D'							AND
		try_convert(int,valor_lab)	= 1								AND
		( id_cita in (select
						 distinct
						 id_cita
						 from #his_minsa
						 where	(substring(cod_item,1,4) BETWEEN 'C430' AND 'C449') AND
								 tipo_dx	= 'R'									AND
								(valor_lab	= '' OR valor_lab IS NULL)
					  )
		)										AND
		 edad_reg				>= 18			AND
		 id_tipedad_reg			 = 'A'			AND
		 year(fecha_atencion)	 = @año			AND
		 month(fecha_atencion)	<= @mes_inicio


	--1.6 Tratamiento contra Cáncer de hígado 
	IF OBJECT_ID('Tempdb..#his_minsa_Num_higado') IS NOT NULL DROP TABLE #his_minsa_Num_higado
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc,
		Num			= 1
	into #his_minsa_Num_higado
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or 
			(cod_item between '96420' and '96423') or
			(cod_item in ('96402','96407','96549','90780','90782',
						  '90784','77305','77500','77501','77781',
						  '77295','77301','77315','77401','77784',
						  '77290','76499','77263','77418','77285'))
		   )														AND
		tipo_dx						= 'D'							AND
		try_convert(int,valor_lab)	= 1								AND
		( id_cita in (select
						 distinct
						 id_cita
						 from #his_minsa
						 where	(substring(cod_item,1,4) BETWEEN 'C220' AND 'C229') AND
								 tipo_dx	= 'R'									AND
								(valor_lab	= '' OR valor_lab IS NULL)
					  )
		)										AND
		 edad_reg				>= 18			AND
		 id_tipedad_reg			 = 'A'			AND
		 year(fecha_atencion)	 = @año			AND
		 month(fecha_atencion)	<= @mes_inicio

	--1.7 Tratamiento contra Cáncer de estómago 
	IF OBJECT_ID('Tempdb..#his_minsa_Num_estomago') IS NOT NULL DROP TABLE #his_minsa_Num_estomago
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc,
		Num			= 1
	into #his_minsa_Num_estomago
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or 
			(cod_item between '96420' and '96423') or
			(cod_item in ('96402','96407','96549','90780','90782',
						  '90784','77305','77500','77501','77781',
						  '77295','77301','77315','77401','77784',
						  '77290','76499','77263','77418','77285'))
		   )														AND
		tipo_dx						= 'D'							AND
		try_convert(int,valor_lab)	= 1								AND
		( id_cita in (select
						 distinct
						 id_cita
						 from #his_minsa
						 where	(substring(cod_item,1,4) BETWEEN 'C160' AND 'C169') AND
								 tipo_dx	= 'R'									AND
								(valor_lab	= '' OR valor_lab IS NULL)
					  )
		)										AND
		 edad_reg				>= 18			AND
		 id_tipedad_reg			 = 'A'			AND
		 year(fecha_atencion)	 = @año			AND
		 month(fecha_atencion)	<= @mes_inicio

	--1.8 Tratamiento contra Cáncer de pulmón
	IF OBJECT_ID('Tempdb..#his_minsa_Num_pulmon') IS NOT NULL DROP TABLE #his_minsa_Num_pulmon
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc,
		Num			= 1
	into #his_minsa_Num_pulmon
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or 
			(cod_item between '96420' and '96423') or
			(cod_item in ('96402','96407','96549','90780','90782',
						  '90784','77305','77500','77501','77781',
						  '77295','77301','77315','77401','77784',
						  '77290','76499','77263','77418','77285'))
		   )														AND
		tipo_dx						= 'D'							AND
		try_convert(int,valor_lab)	= 1								AND
		( id_cita in (select
						 distinct
						 id_cita
						 from #his_minsa
						 where	(substring(cod_item,1,4) BETWEEN 'C340' AND 'C349') AND
								 tipo_dx	= 'R'									AND
								(valor_lab	= '' OR valor_lab IS NULL)
					  )
		)										AND
		 edad_reg				>= 18			AND
		 id_tipedad_reg			 = 'A'			AND
		 year(fecha_atencion)	 = @año			AND
		 month(fecha_atencion)	<= @mes_inicio

	--1.9 Tratamiento contra linfoma
	IF OBJECT_ID('Tempdb..#his_minsa_Num_linfoma') IS NOT NULL DROP TABLE #his_minsa_Num_linfoma
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc,
		Num			= 1
	into #his_minsa_Num_linfoma
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or 
			(cod_item between '96420' and '96423') or
			(cod_item in ('96402','96407','96549','90780','90782',
						  '90784','77305','77500','77501','77781',
						  '77295','77301','77315','77401','77784',
						  '77290','76499','77263','77418','77285'))
		   )														AND
		tipo_dx						= 'D'							AND
		try_convert(int,valor_lab)	= 1								AND
		( id_cita in (select
						 distinct
						 id_cita
						 from #his_minsa
						 where	(substring(cod_item,1,4) BETWEEN 'C810' AND 'C859') OR
								 cod_item	= 'C963'								AND
								 tipo_dx	= 'R'									AND
								(valor_lab	= '' OR valor_lab IS NULL)
					  )
		)										AND
		 year(fecha_atencion)	 = @año			AND
		 month(fecha_atencion)	<= @mes_inicio

	--1.10 Tratamiento contra Leucemia
	IF OBJECT_ID('Tempdb..#his_minsa_Num_leucemia') IS NOT NULL DROP TABLE #his_minsa_Num_leucemia
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc,
		Num			= 1
	into #his_minsa_Num_leucemia
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or 
			(cod_item between '96420' and '96423') or
			(cod_item in ('96402','96407','96549','90780','90782',
						  '90784','77305','77500','77501','77781',
						  '77295','77301','77315','77401','77784',
						  '77290','76499','77263','77418','77285'))
		   )														AND
		tipo_dx						= 'D'							AND
		try_convert(int,valor_lab)	= 1								AND
		( id_cita in (select
						 distinct
						 id_cita
						 from #his_minsa
						 where	(substring(cod_item,1,4) BETWEEN 'C910' AND 'C959') OR
								 cod_item	= 'C901'								AND
								 tipo_dx	= 'R'									AND
								(valor_lab	= '' OR valor_lab IS NULL)
					  )
		)										AND
		 year(fecha_atencion)	 = @año			AND
		 month(fecha_atencion)	<= @mes_inicio

	--Numerador Final.
	IF OBJECT_ID('Tempdb..#dpcan_Num_Parcial') IS NOT NULL DROP TABLE #dpcan_Num_Parcial
	select
		distinct
		renaes		,
		id_tipo_doc	,
		num_doc		,
		Num			= 1
	into #dpcan_Num_Parcial
	from (
			select a.* from			  #his_minsa_Num_colon a
			union all select b.* from #his_minsa_Num_estomago b
			union all select c.* from #his_minsa_Num_higado c
			union all select d.* from #his_minsa_Num_leucemia d
			union all select e.* from #his_minsa_Num_linfoma e
			union all select f.* from #his_minsa_Num_mama f
			union all select g.* from #his_minsa_Num_piel g
			union all select h.* from #his_minsa_Num_pulmon h
			union all select i.* from #his_minsa_Num_prostata i
			union all select j.* from #his_minsa_Num_uterino j
	) as t

	IF OBJECT_ID('Tempdb..#dpcan_Num') IS NOT NULL DROP TABLE #dpcan_Num
	SELECT
		t.*,
		nColon		= ISNULL(a.Num,0),
		nEstomago	= ISNULL(b.Num,0),
		nhigado		= ISNULL(c.Num,0),		
		nleucemia	= ISNULL(d.Num,0),
		nlinfoma	= ISNULL(e.Num,0),
		nmama		= ISNULL(f.Num,0),	
		npiel		= ISNULL(g.Num,0),	
		npulmon		= ISNULL(h.Num,0),	
		nprostata	= ISNULL(i.Num,0),
		nuterino	= ISNULL(j.Num,0)
	INTO #dpcan_Num
	FROM #dpcan_Num_Parcial				t
	LEFT JOIN #his_minsa_Num_colon		a ON t.num_doc = a.num_doc AND t.id_tipo_doc = a.id_tipo_doc AND t.renaes = a.renaes
	LEFT JOIN #his_minsa_Num_estomago	b ON t.num_doc = b.num_doc AND t.id_tipo_doc = b.id_tipo_doc AND t.renaes = b.renaes
	LEFT JOIN #his_minsa_Num_higado		c ON t.num_doc = c.num_doc AND t.id_tipo_doc = c.id_tipo_doc AND t.renaes = c.renaes
	LEFT JOIN #his_minsa_Num_leucemia	d ON t.num_doc = d.num_doc AND t.id_tipo_doc = d.id_tipo_doc AND t.renaes = d.renaes
	LEFT JOIN #his_minsa_Num_linfoma	e ON t.num_doc = e.num_doc AND t.id_tipo_doc = e.id_tipo_doc AND t.renaes = e.renaes
	LEFT JOIN #his_minsa_Num_mama		f ON t.num_doc = f.num_doc AND t.id_tipo_doc = f.id_tipo_doc AND t.renaes = f.renaes
	LEFT JOIN #his_minsa_Num_piel		g ON t.num_doc = g.num_doc AND t.id_tipo_doc = g.id_tipo_doc AND t.renaes = g.renaes
	LEFT JOIN #his_minsa_Num_pulmon		h ON t.num_doc = h.num_doc AND t.id_tipo_doc = h.id_tipo_doc AND t.renaes = h.renaes
	LEFT JOIN #his_minsa_Num_prostata	i ON t.num_doc = i.num_doc AND t.id_tipo_doc = i.id_tipo_doc AND t.renaes = i.renaes
	LEFT JOIN #his_minsa_Num_uterino	j ON t.num_doc = j.num_doc AND t.id_tipo_doc = j.id_tipo_doc AND t.renaes = j.renaes

--%%%%%%%%%%%%%%%%
--	INDICADOR
--%%%%%%%%%%%%%%%%
IF OBJECT_ID('Tempdb..#dpcan') IS NOT NULL DROP TABLE #dpcan
SELECT
	d.*,
	nColon = ISNULL(n.nColon,0),
	nEstomago = ISNULL(n.nEstomago,0),
	nhigado   = ISNULL(n.nhigado,0),
	nleucemia = ISNULL(n.nleucemia,0),
	nlinfoma  = ISNULL(n.nlinfoma,0),
	nmama	  = ISNULL(n.nmama,0),
	npiel	  = ISNULL(n.npiel,0),
	npulmon	  = ISNULL(n.npulmon,0),
	nprostata = ISNULL(n.nprostata,0),
	nuterino  = ISNULL(n.nuterino,0)
INTO #dpcan
FROM #dpcan_den d -- 3484
LEFT JOIN #dpcan_Num n ON d.id_tipo_doc = n.id_tipo_doc AND d.num_doc = n.num_doc AND d.renaes = n.renaes

IF OBJECT_ID('Tempdb..#dpcan_Den_Ind') IS NOT NULL DROP TABLE #dpcan_Den_Ind
SELECT
	d.*
INTO #dpcan_Den_Ind
FROM
	(SELECT
		d.*,
		id = ROW_NUMBER() OVER(PARTITION BY d.id_tipo_doc, d.num_doc
								   ORDER BY d.Num DESC)
	FROM
		(SELECT
			d.*,
			Num =	CASE
						WHEN d.dColon		= 1 AND d.ncolon	= 1 THEN 1
						WHEN d.dEstomago	= 1 AND d.nEstomago = 1 THEN 1
						WHEN d.dhigado		= 1 AND d.nhigado	= 1 THEN 1
						WHEN d.dleucemia	= 1 AND d.nleucemia = 1 THEN 1
						WHEN d.dlinfoma		= 1 AND d.nlinfoma	= 1 THEN 1
						WHEN d.dmama		= 1 AND d.nmama		= 1 THEN 1
						WHEN d.dpiel		= 1 AND d.npiel		= 1 THEN 1
						WHEN d.dpulmon		= 1 AND d.npulmon	= 1 THEN 1
						WHEN d.dprostata	= 1 AND d.nprostata = 1 THEN 1
						WHEN d.duterino		= 1 AND d.nuterino	= 1 THEN 1
						ELSE 0
					END
		FROM #dpcan d) d
	) d
WHERE d.id = 1


insert into #tabla_reporte
select
	año		= @año,
	mes		= @mes_inicio,
	renaes	,
	den		= sum(den),
	num		= sum(num)
from #dpcan_Den_Ind t
group by renaes

set @mes_inicio = @mes_inicio + 1
end

--===================
-- REPORTE
--===================

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
, case when b.DIRIS like '%diris%' then b.diris else B.DESC_RED end Red
, b.desc_mred MicroRed
, b.cat_estab
, b.desc_estab eess_nombre
, a.*
from #tabla_reporte a
inner join #renaes b on convert(int,a.renaes) = convert(int,b.COD_ESTAB)



