use datahis;


DECLARE @year INT ,@mes_eval INT = 1,@MES_FINAL INT ;

SET @YEAR=2025
SET @mes_eval= 1
SET @MES_FINAL= 4



create table #tabla_reporte
(
mes int,
anio int,
COD_EESS_PRENATAL  NVARCHAR (20),
NOM_ESTABLEC nvarchar(100),
dni varchar(15),
nombre_paciente NVARCHAR (200),
SEM_GEST INT,
fecha_v1	date,
V1_98_195 int,
fecha_v2 date,
V1_196_2945 int,
ESTADO INT
) 

--while  @mes_eval <= @MES_FINAL
--begin

DECLARE @fechaI DATE = DATEFROMPARTS(@year, @mes_eval, 1);
DECLARE @fechaF DATE = EOMONTH(DATEFROMPARTS(@year, @MES_FINAL, 1));

SELECT * 
into #HIS 
FROM his
WHERE datediff(mm,fecha,@fechaF) < 8 AND COD_ITEM IN('99402.03','C0011') and FECHA_ULT_REGLA is not null and COND = '1'


select Cod_EESS_Prenatal, EESS_Prenatal NOM_ESTABLEC, Doc_Madre NRO_DOC_MAD, concat( Nombres_Madre,' ', Ap_Paterno_Madre, ' ', Ap_Materno_Madre) as NOMBRE_PACIENTE, 
	Fecha as Fecha_Parto,Sem_Gest, MONTH(fecha)mes,year(Fecha) anio,
					DATEADD(dd,-(c.sem_gest *7),fecha) Fi_gest
	into #padron
from cnv_2024 c 
inner join  ESTABLECIMIENTOS e ON try_convert(int,c.Cod_EESS_Prenatal) = try_convert(int,e.Codigo_Unico) 
	where Fecha 
	between @fechaI and @fechaF 
	and e.Codigo_Disa=30 and e.Codigo_Red='05' 

	SELECT * FROM #padron

	DROP TABLE IF EXISTS #ssr1
SELECT FECHA, RENAES, NOM_ESTABLEC,NRO_DOC,FECHA FECHA_V1,V1_98_195
			INTO #ssr1
		FROM (SELECT A.FECHA, A.RENAES, A.NOM_ESTABLEC,A.NRO_DOC,c.sem_gest, ROW_NUMBER() OVER (PARTITION BY A.NRO_DOC ORDER BY A.FECHA DESC) ID_AT,
		DATEDIFF(dd,c.Fi_gest,A.fecha) V1_98_195
		FROM 

		#his AS a
				INNER JOIN #his AS B ON B.ID_CITA=A.ID_CITA
				inner join #padron as c on b.NRO_DOC=c.NRO_DOC_MAD
						  WHERE (A.COD_ITEM='99402.03' AND A.LAB='1' AND
			  B.COD_ITEM IN('99499.08','C0011') AND (B.LAB IS NULL OR B.LAB=''))
			  and (DATEDIFF(dd,c.Fi_gest,A.fecha) between 98 and 195)
		
		GROUP BY A.FECHA, a.RENAES,a.NOM_ESTABLEC, A.NRO_DOC,c.Sem_Gest,c.Fi_gest)w
		WHERE ID_AT=1
		GROUP BY FECHA, RENAES, NOM_ESTABLEC,V1_98_195,NRO_DOC

		SELECT * FROM #ssr1
SELECT FECHA, ANIO, MES, RENAES, NOM_ESTABLEC,NRO_DOC, V1_196_294
			INTO #ssr2
		FROM (SELECT A.FECHA, A.ANIO, A.MES, A.RENAES, A.NOM_ESTABLEC,A.NRO_DOC,ROW_NUMBER() OVER (PARTITION BY A.NRO_DOC ORDER BY A.FECHA DESC) ID_AT,
		DATEDIFF(dd,c.Fi_gest ,A.fecha) V1_196_294
		FROM #HIS AS a
				INNER JOIN #HIS AS B ON B.ID_CITA=A.ID_CITA
						inner join #padron as c on b.NRO_DOC=c.NRO_DOC_MAD
			  WHERE (A.COD_ITEM='99402.03' AND A.LAB='2') AND
			  B.COD_ITEM IN('99499.08','C0011') AND (B.LAB IS NULL OR B.LAB='')
			    and (DATEDIFF(dd,c.Fi_gest,A.fecha) between 196 and 294)
		GROUP BY a.ANIO, a.mes, a.RENAES,a.NOM_ESTABLEC, A.NRO_DOC,A.FECHA,c.Sem_Gest,c.Fi_gest)w
		WHERE ID_AT=1
		GROUP BY FECHA, ANIO, MES, RENAES, NOM_ESTABLEC,NRO_DOC,V1_196_294

	--	SELECT * FROM #ssr2

	DROP TABLE IF EXISTS #SSR
select  P.anio,P.MES, p.Cod_EESS_Prenatal, p.NOM_ESTABLEC, p.NRO_DOC_MAd, p.NOMBRE_PACIENTE ,p.Sem_Gest, p.Fi_gest,S1.fecha s1Fecha,S1.V1_98_195,S2.fecha s2Fecha,s2.V1_196_294, p.Fecha_Parto,
	case 
		when (p.Fecha_Parto > s1.FECHA) and (Fecha_Parto > s2.FECHA) and (s2.FECHA > s1.FECHA) then 1  else 0 END Estado
	INTO #SSR
	from #padron p 	left join #ssr1 S1 on p.NRO_DOC_MAD = S1.NRO_DOC
	left join #ssr2 S2 on p.NRO_DOC_MAD = S2.NRO_DOC 

	DROP TABLE IF EXISTS #ssr_cumple 
select * 
--into #ssr_cumple 
from #ssr 
where Estado = 1

--set @mes_eval = @mes_eval + 1

--end
--SELECT e.Codigo_MicroRed MR,e.Codigo_Unico RENAES,e.Nombre_Establecimiento ESTABLECIMIENTO, sum(tm.Estado)Total 
--		FROM #ssr_cumple tm right join ESTABLECIMIENTOS e ON try_convert(int,tm.Cod_EESS_Prenatal) = try_convert(int,e.Codigo_Unico)
--	WHERE e.Codigo_Disa=30 and e.Codigo_Red='05' 
--	GROUP BY Codigo_MicroRed,Codigo_Unico,Nombre_Establecimiento

--insert into  TB_PROMSA_PAQUETES 
SELECT '5' AREA,'1' AS INDICADOR, ANIO, MES,Cod_EESS_Prenatal,NOM_ESTABLEC,
SUM(CASE WHEN V1_98_195 IS NOT NULL AND V1_196_294 IS NOT NULL THEN 1 ELSE 0 END)  TOTAL
--INTO TB_PROMSA_PAQUETES
FROM #ssr 
GROUP BY ANIO,MES,Cod_EESS_Prenatal,NOM_ESTABLEC
