Anexo 1 Algoritmo para la extracción del indicador de Gestantes con paquete

integrado de servicios a partir de la base de datos del SIS.

De las bases de datos del SIS se usan y generan las siguientes tablas: ----=======================================================** -- Archivo SQL 01FiltraPlanC_actualiza.sql ----=======================================================** -- Inputs: Tablas -- Sate -> Tabla de Atenciones (SIS) -- Safi/Safiaus -> Tabla de Afiliaciones (SIS) -- Sins/Sinsaus -> Tabla de Inscripciones (SIS) -- Saapo -> Tabla de Procedimientos (SIS) -- Samed -> Tabla de Medicamentos (SIS) -- Sadia -> Tabla de Diagnosticos (SIS) -- Sasmi -> Tabla de Servicios Materno-Infantiles (SIS) -- -- Outputs: Tablas -- SafiC -> Tabla de Afiliaciones de Gestantes (antes plan C), restringida a los campos seleccionados -- SateC -> Tabla de Atenciones de Gestantes (antes plan C), restringida a los campos seleccionados -- SprocedC -> Tabla de Atenciones de Gestantes (antes plan C), restringida a registros de atenciones de gestantes -- SmedicC -> Tabla de Atenciones de Gestantes (antes plan C), restringida a registros de atenciones de gestantes -- SdiagC -> Tabla de Atenciones de Gestantes (antes plan C), restringida a registros de atenciones de gestantes -- SsmiC -> Tabla de Atenciones de Gestantes (antes plan C), restringida a registros de atenciones de gestantes -- SinsX -> Tabla de Inscripciones (todos los planes), restringida a los campos seleccionados -- -- Proposito: 1) Extraer los campos relevantes identificados de las tablas de Atenciones, afiliaciones e inscripciones, -- 2) Efectuar los filtros convenientes para restringirnos a la poblacion de interes (Niños menores de 5 años) -- 3) Eliminar los registros duplicados -- -- Nota: Los nombres de las tablas a los que se ha añadido la terminación "_act" representan tablas de actualización, -- es decir tablas que contienen registros nuevos, de uno o más meses (periodo de digitacion), de la base de datos. -- Estos registros nuevos son procesados y en pasos posteriores incorporados en las tablas analíticas que se van a -- actualizar. -- Dependencias directas de otros scripts: Ninguna --=======================================================** SET client_encoding TO 'latin1'; SET datestyle TO ISO, ymd; SET search_path TO sis_09xx; DROP TABLE IF EXISTS sis_09xx.SafiC1 ; DROP TABLE IF EXISTS sis_09xx.sins1 ; DROP TABLE IF EXISTS sis_09xx.safiC ; DROP TABLE IF EXISTS sis_09xx.sinsX ; DROP TABLE IF EXISTS sis_09xx.sateC1_act ; DROP TABLE IF EXISTS sis_09xx.sateC_act ; DROP TABLE sis_09xx.SprocedC_act ; DROP TABLE sis_09xx.SmedicC_act ;

DROP TABLE sis_09xx.SdiagC_act ; DROP TABLE sis_09xx.SsmiC_act ; -----Se seleccionan los campos requeridos de cada tabla (Afiliacion, Inscripcion, Atencion, Procedimientos y Medicamentos) --Actualizacion Atencion (plan C) WWVV CREATE TABLE sis_09xx.SateC1_act WITH(OIDS=TRUE) AS SELECT ate_disa, ate_idnumreg, ate_numregate, UPPER(ate_codest) as ate_codest, ate_disaafiins, EXTRACT( YEAR FROM ate_fecnac)*100+EXTRACT(MONTH FROM ate_fecnac) ::numeric(6) AS p_nac, ----WWVV ate_numregafiins, ate_tipate, ate_codsituacionafiins, ate_tipoformatoafiins, ate_loteafiins, ate_sexo, ate_fecnac, ate_fecate, ate_codser, ate_fecing, ate_fecalta, ate_tipprof, ate_coddist, ate_fecparto, ate_esgestante, -- ate_autogenerado, to_number(ate_periodo,'9999') as ate_periodo, to_number(ate_mes, '99') as ate_mes, ate_aus, ate_tablaafi FROM sis_09xx.sate_act WHERE ate_sexo='0' and ate_fecate-ate_fecnac>3650 ; -- Elimina Atenciones duplicadas (input ateC1) BEGIN; CREATE TEMPORARY TABLE f2 ON COMMIT DROP AS SELECT MAX(OID) AS reg FROM sis_09xx.SateC1_act GROUP BY ate_disa, ate_idnumreg, ate_numregate; CREATE TABLE sis_09xx.SateC_act AS SELECT s.* FROM sis_09xx.SateC1_act s LEFT JOIN f2 ON s.oid=f2.reg WHERE f2.reg>0; COMMIT; -------------------------------------------------------------------------- -- Afiliacion Gestantes (plan C) CREATE TABLE sis_09xx.SafiC1 WITH(OIDS=TRUE) AS SELECT afi_disa, EXTRACT(YEAR FROM afi_fecnac)*100+EXTRACT(MONTH FROM afi_fecnac) ::numeric(6) AS f_nac, afi_idnumreg, afi_numregafis, afi_fecafil, UPPER(afi_codest) as afi_codest , afi_sexo, afi_fecnac, afi_coddist, afi_idcentropoblado, -- afi_esgestante, to_number(afi_periodo,'9999') AS afi_periodo, to_number(afi_mes, '99') AS afi_mes -- afi_autogenerado FROM sis_09xx.safi WHERE afi_sexo='0' and afi_fecafil-afi_fecnac>3650 UNION ALL SELECT afi_disa, EXTRACT(YEAR FROM afi_fecnac)*100+EXTRACT(MONTH FROM afi_fecnac) ::numeric(6) AS f_nac, afi_idnumreg, afi_numregafis, afi_fecafil, CASE WHEN afi_codest_adsc IS NULL THEN UPPER(afi_codest) ELSE UPPER(afi_codest_adsc) END as afi_codest, afi_sexo, afi_fecnac, afi_coddist, afi_idcentropoblado, -- afi_esgestante, to_number(afi_periodo,'9999') AS afi_periodo, to_number(afi_mes, '99') AS afi_mes

-- afi_autogenerado FROM sis_09xx.safiaus WHERE afi_sexo='0' and afi_fecafil-afi_fecnac>3650 ; -- Inscripcion (todos) CREATE TABLE sis_09xx.Sins1 WITH(OIDS=TRUE) AS SELECT ins_disa, EXTRACT(YEAR FROM ins_fecnac)*100+EXTRACT(MONTH FROM ins_fecnac) ::numeric(6) AS i_nac, ins_idnumreg, ins_numregins, ins_fecinscripcion, UPPER(ins_codest) as ins_codest, ins_sexo, ins_fecnac, ins_coddist, ins_idcentropoblado, -- ins_autogenerado, to_number(ins_periodo,'9999') as ins_periodo, to_number(ins_mes, '99') as ins_mes FROM sis_09xx.sins UNION ALL SELECT ins_disa, EXTRACT(YEAR FROM ins_fecnac)*100+EXTRACT(MONTH FROM ins_fecnac) ::numeric(6) AS i_nac, ins_idnumreg, ins_numregins, ins_fecinscripcion, CASE WHEN ins_codest_adsc IS NULL THEN UPPER(ins_codest) ELSE UPPER(ins_codest_adsc) END as ins_codest, ins_sexo, ins_fecnac, ins_coddist, ins_idcentropoblado, -- ins_autogenerado, to_number(ins_periodo,'9999') as ins_periodo, to_number(ins_mes, '99') as ins_mes FROM sis_09xx.sinsaus ; ---- Depura registros duplicados -- Identifica Afiliaciones duplicadas (input afiC1) BEGIN; CREATE TEMPORARY TABLE e2 ON COMMIT DROP AS SELECT MAX(OID) AS reg FROM sis_09xx.SafiC1 GROUP BY afi_disa , f_nac, afi_numregafis; CREATE TABLE sis_09xx.SafiC AS SELECT s.* FROM sis_09xx.SafiC1 s LEFT JOIN e2 ON s.oid=e2.reg WHERE e2.reg>0; COMMIT; -- Identifica Inscripciones duplicadas (input ins1) BEGIN; CREATE TEMPORARY TABLE g1 ON COMMIT DROP AS SELECT MAX(OID) AS reg FROM sis_09xx.Sins1 GROUP BY ins_disa , i_nac, ins_numregins; CREATE TABLE sis_09xx.SinsX AS SELECT s.* FROM sis_09xx.Sins1 s LEFT JOIN g1 ON s.oid=g1.reg WHERE g1.reg>0; COMMIT; -------------------------------------------------------------------------- ---- Procedimientos (plan C) CREATE TABLE sis_09xx.SprocedC_act WITH(OIDS=TRUE) AS SELECT x.aapo_numregate, aapo_Idnumreg, LOWER(x.aapo_codapo) as ate_codapo, x.aapo_icantejecutada as ate_cant, y.ate_periodo, y.ate_mes, y.ate_disa, y.ate_codser FROM saapo_act x

RIGHT JOIN sis_09xx.SateC_act y ON x.aapo_numregate=y.ate_idnumreg WHERE x.aapo_codapo is not null ; ---- Medicamentos (plan C) CREATE TABLE sis_09xx.SmedicC_act WITH(OIDS=TRUE) AS SELECT x.amed_numregate, x.amed_idnumreg, x.amed_codmed as ate_codmed, x.amed_icantentregada as ate_cant, y.ate_periodo, y.ate_mes, y.ate_disa, y.ate_codser FROM samed_act x RIGHT JOIN sis_09xx.SateC_act y ON x.amed_numregate=y.ate_idnumreg WHERE x.amed_codmed is not null ; ---- Diagnosticos (plan C) CREATE TABLE sis_09xx.SdiagC_act WITH(OIDS=TRUE) AS SELECT x.adia_numregate, x.adia_idnumreg, x.adia_inrodia as ate_nrodia, x.adia_coddia as ate_coddia, y.ate_periodo, y.ate_mes, y.ate_disa, y.ate_codser FROM sadia_act x RIGHT JOIN sis_09xx.SateC_act y ON x.adia_numregate=y.ate_idnumreg WHERE x.adia_coddia is not null ; ---- SMI (plan C) CREATE TABLE sis_09xx.SsmiC_act WITH(OIDS=TRUE) AS SELECT x.asmi_numregate, x.asmi_idnumreg, x.asmi_codsmi, x.asmi_numero, y.ate_periodo, y.ate_mes, y.ate_disa, y.ate_codser FROM sasmi_act x RIGHT JOIN sis_09xx.SateC_act y ON x.asmi_numregate=y.ate_idnumreg WHERE x.asmi_codsmi is not null ; -------Fin------ DELETE from sis_09xx.SprocedC WHERE ate_periodo*100+ate_mes in (select ate_periodo*100+ate_mes from sis_09xx.SprocedC_act group by 1) ; INSERT INTO sis_09xx.SprocedC SELECT * FROM sis_09xx.SprocedC_act; DELETE from sis_09xx.SmedicC WHERE ate_periodo*100+ate_mes in (select ate_periodo*100+ate_mes from sis_09xx.SmedicC_act group by 1) ; INSERT INTO sis_09xx.SmedicC SELECT * FROM sis_09xx.SmedicC_act; DELETE from sis_09xx.SdiagC WHERE ate_periodo*100+ate_mes in (select ate_periodo*100+ate_mes from sis_09xx.SdiagC_act group by 1) ; INSERT INTO sis_09xx.SdiagC SELECT * FROM sis_09xx.SdiagC_act; DELETE from sis_09xx.SsmiC WHERE ate_periodo*100+ate_mes in (select ate_periodo*100+ate_mes from sis_09xx.SsmiC_act group by 1) ; INSERT INTO sis_09xx.SsmiC SELECT * FROM sis_09xx.SsmiC_act; **Creando el código de departamento**

tostring v024, gen(DEP) replace DEP="0"+DEP if length(DEP)==1 **Creando el código de Provincia*** tostring sprovin, gen(PROVI) replace PROVI="0"+PROVI if length(PROVI)==1 **creando variable Distrito**** tostring sdistri, gen(DISTRI) replace DISTRI="0"+DISTRI if length(DISTRI)==1 **Creando el código de ubigeo.** gen ubigeo=DEP+PROVI+DISTRI label var ubigeo "UBIGEO" --=======================================================-- -- Archivo SQL 02MedProcAtePlanC_actualiza.sql --=======================================================-- -- Inputs: Tablas -- MEDIC -> Tabla de Medicamentos en atenciones del SIS -- PROCED -> Tabla de Procedimientos en Atenciones del SIS -- MedPrec -> Tabla Auxiliar con precios referenciales de los medicamentos -- MedATC -> Tabla Auxiliar con los DDDs de los Antiboticos -- ProPrec -> Tabla Auxiliar con precios referenciales de los procedimientos -- -- Outputs: Tablas -- MEDICagC -> Tabla de Medicamentos seleccionados (gestantes) agregadas por Atencion -- PROCEDagC -> Tabla de Procedimientos seleccionados (gestantes) agregadas por Atencion -- -- Proposito: A partir de los datos de Medicamentos y Procedimientos empleados en las -- atenciones de Gestantes, generar campos relevantes en la construccion de -- las tablas analiticas. -- -- Dependencias directas de otros scripts: Ninguna --=======================================================-- SET client_encoding TO 'latin1'; SET datestyle TO ISO, ymd; SET search_path TO sis_09xx; DROP TABLE IF EXISTS planc.Smedic1 ; DROP TABLE IF EXISTS planc.smedicagc_act ; DROP TABLE IF EXISTS planc.sproced1 ; DROP TABLE IF EXISTS planc.sprocedagc_act ; -------------MEDICAMENTOS ---- Join con la tabla Auxiliar MedPrec ---- Se adiciona una columna para el costo total de medicamentos por atencion (Costo unitario * cantidad)

---- Match con Tabla Auxiliar medATC.dbf (Se generan columnas para DDDs de Antiboticos) ---- Se obtienen columnas para medicamentos seleccionados EXPLAIN ANALYZE CREATE TABLE planc.Smedic1 AS SELECT x.ate_disa, ate_periodo, ate_mes, x.amed_numregate, x.ate_codmed, x.ate_cant, y.costo*x.ate_cant AS costo, CASE WHEN SUBSTRING(z.codatc,1,3)='J01' THEN x.ate_cant*z.numDDD ELSE 000.00 END AS j01, CASE WHEN x.ate_codmed IN ('00200','00201','03995','27.M.5.T') THEN x.ate_cant ELSE 0 END AS m1, CASE WHEN x.ate_codmed IN ('03322','03323','03324','03516','03517','03518','03519','03520','03521', '03522','03523','03524','03525','03526','03527','03528','03529','03530', '03531','03532','03533','03534','03535','03536','03537','03538','03539', '03540','03541','03542','03543','03544','03545','03546','03547','03548', '03549','03550','03551','03552','03553','08179','27.F.1.F','27.F.2.J', '27.F.3.J','27.F.4.J','27.F.5.G','27.F.6.T','27.G.0.T','27.S.1.J', '27.S.1.S','27.S.2.J','27.S.2.S','27.S.2.T','27.S.3.J','ACM482', 'ACM503','ACM952','AMZ0024','LCA0016','LNO0029') THEN x.ate_cant ELSE 0 END AS m2, CASE WHEN x.ate_codmed IN ('00148','00149','00194','00195','00196','00197','00198','03512','03513', '03514','27.S.1.T','27.X.1.T','ACM20','ACM944','00199') THEN x.ate_cant ELSE 0 END AS m3, CASE WHEN x.ate_codmed IN ('09.G.4.A','09.G.8.A','09.K.2.A','09.K.3.I','09.K.5.A','ACM294', 'ACM295','ACM43','ACM44','ACM831','03738', '03747','03748','03749','03750','03751','00624','00625','00626', '00627','ACM267','ACM866','03260','03261') THEN x.ate_cant ELSE 0 END AS m4, CASE WHEN x.ate_codmed IN ('01029','01030','01031','01032','01033','01034','01035','01043', '01044','01045','01046','01933','01934','01935','01936','08145', '09.B.0.A','09.B.1.A','09.J.1.A','09.P.0.A','09.P.1.A','09.S.1.A', '18048','ACM442','ACM443','CHO0004','CHO0005') -- (Penicilina) THEN x.ate_cant ELSE 0 END AS m51, CASE WHEN x.ate_codmed IN ('00707','00752','00753','00767','00768','ACM1121','ACM53','ACM57', 'ACM59','ACM737', -- (Amoxicilina parent) '00812','00813','00814','00822','00823','00824','00825','00826', '00827','00828','00829','00830','00831','00832','00833','00834', '00835','09.H.1.A','ACM61','ACM66','ACM67','ACM722','ACM785','HSB0008', --(Aminopenicilinas) '02816','08064','ACM649','ACM767', --(Dicloxacilina) '05211','05212','09.X.1.A','09.X.5.A') --(Oxaciclina) THEN x.ate_cant ELSE 0 END AS m52, CASE WHEN x.ate_codmed IN ('09.F.1.A','09.W.1.A','09.W.2.A','09.Z.1.A','ACM127','ACM133','ACM134', 'ACM136','ACM137','ACM143','ACM144','ACM494','ACM634','ACM647','ACM664', 'ACM665','ACM688', '01688','01659','01669','01682','01683','01684','01685','01687','01656', '01689','01690','01691','01692','01686','01649','01607','01608','01623', '01638','01639','01640','01641','01658','01643','01657','01650','01651', '01652','01653','01654','01655','01670','01642','ACM131','ACM824','01668', '17610','17615') THEN x.ate_cant ELSE 0 END AS m6,

CASE WHEN x.ate_codmed IN ('ACM156','HSB0014','01834','01840','01838','01836','01835','01839','01837','01831') THEN x.ate_cant ELSE 0 END AS m7, CASE WHEN x.ate_codmed IN ('09.I.3.A','09.I.6.A','01956','01957','01958') THEN x.ate_cant ELSE 0 END AS m8, CASE WHEN x.ate_codmed IN ('ACM363','ACM364','04443','04442') THEN x.ate_cant ELSE 0 END AS m9, CASE WHEN x.ate_codmed IN ('09.C.1.A','02031') THEN x.ate_cant ELSE 0 END AS m10, CASE WHEN x.ate_codmed IN ('09.V.5.A','06471','06470') THEN x.ate_cant ELSE 0 END AS m11, CASE WHEN x.ate_codmed IN ('09.M.5.A','04775','04776') THEN x.ate_cant ELSE 0 END AS m12, CASE WHEN x.ate_codmed IN ('35.C.0.F','35.C.0.S','35.C.5.S','35.C.9.F','ACM615','HSB0039', '05878','05880','05881','17681','05884','17684','05887','05873','05872', '05875','05883','05874','05888','05890','05893','05891') THEN x.ate_cant ELSE 0 END AS m13, CASE WHEN x.ate_codmed IN ('05.D.2.A','05.D.4.A','05.D.5.I','05.H.2.A','ACM212','ACM310','ACM93', '01213','01214','02641','02642','02643','02644') THEN x.ate_cant ELSE 0 END AS m14, CASE WHEN x.ate_codmed IN ('25.O.1.A','ACM436','ACM923','05253','05254') THEN x.ate_cant ELSE 0 END AS mo1, CASE WHEN x.ate_codmed IN ('25.E.2.A','25.E.3.I','ACM702','03140','03139','04703') THEN x.ate_cant ELSE 0 END AS mo2, CASE WHEN x.ate_codmed IN ('ACM627','ACM645','LCA0021','35.M.2.A','39.M.1.A', '04558','17719','08177','04557','04556','04555','17685') THEN x.ate_cant ELSE 0 END AS mo3, CASE -- (amox) Cantidad total entregada de Amoxicilina Oral a Gestantes en mg (Añadido 201111) WHEN x.ate_codmed IN ('00702','00749','00766','00807','09.A.2.T','ACM19') THEN x.ate_cant* 250 ----Tabs WHEN x.ate_codmed IN ('00527','00528','00703','00706','00750','00808','09.A.5.T', 'ACM1122','ACM1018','ACM51','ACM58','AMZ0004') THEN x.ate_cant* 500 ----Tabs ELSE 0 END as mamoxi, CASE -- (nitrof) Cantidad total entregada de Nitrofurantoina Oral a Gestantes en mg (Añadido 201111) WHEN x.ate_codmed IN ('05103','09.N.1.T') THEN x.ate_cant* 100 ----Tabs WHEN x.ate_codmed IN ('05104','ACM422') THEN x.ate_cant* 50 ELSE 0 END as mnitrof, CASE -- (cefalex) --- Cefalexina oral WHEN x.ate_codmed IN('ACM122','01636') THEN x.ate_cant* 500 WHEN x.ate_codmed IN('ACM132','01634') THEN x.ate_cant* 250 ELSE 0 END AS mcefalex FROM sis_09xx.SmedicC_act x LEFT JOIN public.MedPrec y ON x.ate_codmed=y.ate_codmed LEFT JOIN public.medATC z ON x.ate_codmed=z.ate_codmed ; -------- Lista de Medicamentos para el plan C ------- -- m1 'Acido folico VO' -- m2 'Sulfato Ferroso y otras sales ferrosas VO' -- m3 'Combinacion Sales ferrosas y Õcido folico VO' -- m4 'Aminoglicosidos EV' -- m51 'Penicilinas EV' -- m52 'Penicilinas Semisintéticas' -- m6 'Cefalosporinas EV' -- m7 'Ciprofloxacino EV' -- m8 'Clindamicina EV' -- m9 'Lincomicina EV'

-- m10 'Cloranfenicol EV' -- m11 'Vancomicina EV' -- m12 'Metronidazol EV' -- m13 'Cloruro de Sodio solucion x 1000 cc' -- m14 'Corticoides parenterales (Dexametasona & Betametasona)' -- mo1 'Oxitocina (parenteral)' -- mo2 'Ergometrina (parenteral)' -- mo3 'Sulfato Magnesio (parenteral)' -- mamoxi -- (amox) Cantidad total entregada de Amoxicilina Oral a Gestantes en mg (Añadido 201111) -- mnitrof -- (nitrof) Cantidad total entregada de Nitrofurantoina Oral a Gestantes en mg (Añadido 201111) -- mcefalex -- (cefalex) --- Cefalexina oral ----------------------------------------------------- ------ (m1) Acido Folico VO: Set08 Elimino '00199' pues es una combinacion con Fe ------ (m2) Sulfato ferroso VO: Set08 Se eliminan '03322','03323','03324' (Corresp. a Hierro en anticonceptivos) ------ (m3) Combinacion Sales ferrosas y Õcido folico VO ------ (m4 - m12) Antibioticos parenterales ------ (m13) Solucion salina 0.9% ------ (m14) Corticoides parenterales (Dexametasona & Betametasona) ---- Lista de OTROS corticoides ('03952','03953','03954','04714','04715','04716','04717','06283','06284', -- No usado ---- parenterales '06285','21.T.4.A','41.M.2.A','ACM382','ACM387','ACM521','ACM522','ACM660'); ------ (mo1) Oxitocina parenteral ------ (m02) Ergometrina parenteral ------ (mo3) MgSO4 parenteral -----------PROCEDIMIENTOS ---- Se obtienen columnas para el grupo de procedimientos seleccionados ---- Match con la tabla Auxiliar ProPrec.dbf ----- Se adiciona una columna para el costo total de procedimientos por atencion (Costo ----- unitario * cantidad) EXPLAIN ANALYZE CREATE TABLE planc.Sproced1 AS SELECT x.ate_disa, ate_periodo, ate_mes, x.aapo_numregate, x.ate_codapo, x.ate_cant, CASE WHEN x.ate_codapo IN ('58120') THEN x.ate_cant ELSE 0 END AS UP01, CASE WHEN x.ate_codapo IN ('59120','59150') THEN x.ate_cant ELSE 0 END AS UP02, CASE WHEN x.ate_codapo IN ('59160') THEN x.ate_cant ELSE 0 END AS UP03, CASE WHEN x.ate_codapo IN ('59350') THEN x.ate_cant ELSE 0 END AS UP04, CASE WHEN x.ate_codapo IN ('59414') THEN x.ate_cant ELSE 0 END AS UP05, CASE WHEN x.ate_codapo IN ('59872') THEN x.ate_cant ELSE 0 END AS UP06, CASE WHEN x.ate_codapo IN ('76805','76856','76830') THEN x.ate_cant ELSE 0 END AS UL01, CASE WHEN x.ate_codapo IN ('81005','81099') THEN x.ate_cant ELSE 0 END AS UL02, ---Añado Sedimento urinario CASE WHEN x.ate_codapo IN ('82947b','82947','.02.01.01.45.00','82951') THEN x.ate_cant ELSE 0 END AS UL03, CASE WHEN x.ate_codapo IN ('86899') THEN x.ate_cant ELSE 0 END AS UL04, CASE WHEN x.ate_codapo IN ('85013') THEN x.ate_cant ELSE 0 END AS UL05, CASE WHEN x.ate_codapo IN ('85018') THEN x.ate_cant ELSE 0 END AS UL06, CASE WHEN x.ate_codapo IN ('85031','85048') THEN x.ate_cant ELSE 0 END AS UL07, CASE WHEN x.ate_codapo IN ('82043') THEN x.ate_cant ELSE 0 END AS UL08, CASE WHEN x.ate_codapo IN ('80055') THEN x.ate_cant ELSE 0 END AS UL09,

CASE WHEN x.ate_codapo IN ('86592','.02.04.01.02.00') THEN x.ate_cant ELSE 0 END AS UL10, CASE WHEN x.ate_codapo IN ('80055','80057') THEN x.ate_cant ELSE 0 END AS UL11, CASE WHEN x.ate_codapo IN ('86701','86702','86689') THEN x.ate_cant ELSE 0 END AS UL12, --Set 2014 y.costo*x.ate_cant AS costo FROM sis_09xx.SprocedC_act x LEFT JOIN public.ProPrec y ON x.ate_codapo=y.ate_codapo ; --------Lista de Procedimientos para Gestantes ------- -- UP01 'Dilatacion y legrado, diagnostico y/o terapéutico' -- UP02 'Tratamiento quirurgico de embarazo ectopico' -- UP03 'Legrado postparto' -- UP04 'Histerorrafia por ruptura uterina' -- UP05 'Extraccion de placenta retenida' -- UP06 'Evacuacion uterina de Obito fetal + legrado uterino' -- UL01 'Ecografia pelvica/obstetrica/transvaginal' -- UL02 'Examen completo de orina' -- UL03 'Glucosa' -- UL04 'Grupo sanguineo y factor Rh' -- UL05 'Hematocrito' -- UL06 'Hemoglobina' -- UL07 'Hemograma completo (Formula, Hb, Hto, C corpus, Plaq)' -- UL08 'Proteinuria/Albuminuria' -- UL09 'Perfil Prenatal (Hmg, Glu, VDRL, HIV, ECO, Rubeola)' -- UL10 'Prueba de Sifilis cualitativa (VDRL, RPR, ART)' -- UL11 'Perfil Preoperatorio (Hemograma, GS y factor, Tpo Coagulacion y sangria, TP, Glucosa, HIV, VDRL, Ex de orina) -- UL12 'HIV' ---Nuevo Set 2014 ----------------------------------------------------- ----Indices --CREATE INDEX ix_planc_smedic1 ON planc.smedic1 (ate_disa, amed_numregate) tablespace pgdata2 -- WHERE m1+m2+m3+m4+m51+m52+m6+m7+m8+m9+m10+m11+m12+m13+m14 + mo1+mo2+mo3 + coalesce(mamoxi,0) + coalesce(mnitrof,0) + coalesce(mcefalex,0) + j01 >0 ; -- -- --CREATE INDEX ix_planc_sproced1 ON planc.sproced1 (ate_disa, aapo_numregate) tablespace pgdata2 -- WHERE UP01+UP02+UP03+UP04+UP05+UP06 + UL01+UL02+UL03+UL04+UL05+UL06+UL07+UL08+UL09+ UL10+UL11 >0 ; ---- Se agrega la tabla de MEDICAMENTOS a nivel de atenciones EXPLAIN ANALYZE CREATE TABLE planc.SmedicagC_act AS SELECT ate_disa, amed_numregate, ate_periodo, ate_mes, SUM(J01) ::numeric(8,2) AS J01, SUM(costo) ::numeric(10,2) AS costo, CASE WHEN SUM(m2)+SUM(m3)>=1 THEN 1 ELSE 0 END ::numeric(1) AS sf, CASE WHEN SUM(m1)+SUM(m3)>=1 THEN 1 ELSE 0 END ::numeric(1) AS af, CASE WHEN SUM(m4)+SUM(m51)+SUM(m52)+SUM(m6)+SUM(m7)+SUM(m8)+SUM(m9)+SUM(m10)+SUM(m11)+SUM(m12)>=1 THEN 1 ELSE 0 END ::numeric(1) AS atbp, CASE WHEN SUM(m13)>=1 THEN 1 ELSE 0 END ::numeric(1) AS m13, CASE WHEN SUM(m14)>=1 THEN 1 ELSE 0 END ::numeric(1) AS m14, CASE WHEN SUM(mo1)>=1 THEN 1 ELSE 0 END ::numeric(1) AS mo1, CASE WHEN SUM(mo2)>=1 THEN 1 ELSE 0 END ::numeric(1) AS mo2, CASE WHEN SUM(mo3)>=1 THEN 1 ELSE 0 END ::numeric(1) AS mo3, SUM(coalesce(mamoxi,0)) ::integer AS mamoxi, ----<<<< Coalesce parche para valores nulos

SUM(coalesce(mnitrof,0)) ::integer AS mnitrof, SUM(coalesce(mcefalex,0)) ::integer AS mcefalex FROM planc.Smedic1 WHERE m1+m2+m3+m4+m51+m52+m6+m7+m8+m9+m10+m11+m12+m13+m14 + mo1+mo2+mo3 + coalesce(mamoxi,0) + coalesce(mnitrof,0) + coalesce(mcefalex,0) + j01 >0 GROUP BY ate_disa, amed_numregate, ate_periodo, ate_mes ; ---- Se agrega la tabla de PROCEDIMIENTOS a nivel de atenciones EXPLAIN ANALYZE CREATE TABLE planc.SprocedagC_act AS SELECT ate_disa, aapo_numregate, ate_periodo, ate_mes, CASE WHEN SUM(UP01)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UP01, CASE WHEN SUM(UP02)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UP02, CASE WHEN SUM(UP03)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UP03, CASE WHEN SUM(UP04)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UP04, CASE WHEN SUM(UP05)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UP05, CASE WHEN SUM(UP06)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UP06, CASE WHEN SUM(UL01)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL01, CASE WHEN SUM(UL02)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL02, CASE WHEN SUM(UL03)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL03, CASE WHEN SUM(UL04)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL04, CASE WHEN SUM(UL05)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL05, CASE WHEN SUM(UL06)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL06, CASE WHEN SUM(UL07)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL07, CASE WHEN SUM(UL08)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL08, CASE WHEN SUM(UL09)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL09, CASE WHEN SUM(UL10)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL10, CASE WHEN SUM(UL11)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL11, CASE WHEN SUM(UL12)>=1 THEN 1 ELSE 0 END ::numeric(1,0) AS UL12, SUM(costo) ::numeric(9,2) AS costo FROM planc.Sproced1 WHERE UP01+UP02+UP03+UP04+UP05+UP06 + UL01+UL02+UL03+UL04+UL05+UL06+UL07+UL08+UL09+UL10+UL11+UL12 >0 GROUP BY ate_disa, aapo_numregate, ate_periodo, ate_mes ; -------Fin------- --=======================================================** -- Archivo SQL 03DiagPlanC_actualiza.sql --=======================================================** -- Inputs: Tablas -- DIAG -> Tabla de Diagnosticos en atenciones del SIS -- SMI -> Tabla de Servicios MaternoInfantiles en atenciones del SIS -- -- Outputs: Tablas -- DIAGagC -> Tabla de Diagnósticos seleccionados (gestantes) agregados por atención -- SMIagC -> Tabla de Servicios MaternoInfantiles seleccionados (gestantes) agregados por atención -- -- Propósito: A partir de los datos de Diagnosticos registrados en las atenciones de Gestantes, -- generar campos relevantes en la construcción de las tablas analíticas. -- -- Dependencias directas de otros scripts: Ninguna --=======================================================** SET client_encoding TO 'latin1'; SET datestyle TO ISO, ymd; SET search_path TO sis_09xx;

DROP TABLE IF EXISTS planc.sdiag1 ; DROP TABLE IF EXISTS planc.sdiagagC_act ; DROP TABLE IF EXISTS planc.sSmi1 ; DROP TABLE IF EXISTS planc.sSmiagC_act ; ----------DIAGNOSTICOS ----- Se obtienen columnas para Diagnósticos seleccionados EXPLAIN ANALYZE CREATE TABLE planc.Sdiag1 AS SELECT ate_disa, ate_periodo, ate_mes, adia_numregate, ate_coddia, ------ Aborto (O0X, O20) CASE WHEN POSITION('O0' in ate_coddia)>0 OR POSITION('O20' in ate_coddia)>0 THEN 1 ELSE 0 END AS d1, ------ Enfermedad hipertensiva gestacional (O1X. Incluye PE severa y Eclampsia) CASE WHEN POSITION('O1' in ate_coddia)>0 THEN 1 ELSE 0 END AS d2, ------ solo PREECLAMPSIA SEVERA (O141) CASE WHEN POSITION('O141' in ate_coddia)>0 THEN 1 ELSE 0 END AS d2_1, ------ solo ECLAMPSIA (O15) CASE WHEN POSITION('O15' in ate_coddia)>0 THEN 1 ELSE 0 END AS d2_2, ------ Patologias que complican del embarazo (O21-O28 , O98-O99). Se exceptua ITU (O23) CASE WHEN (SUBSTRING(ate_coddia,1,3) BETWEEN 'O21' AND 'O22') OR (SUBSTRING(ate_coddia,1,3) BETWEEN 'O24' AND 'O28') OR (SUBSTRING(ate_coddia,1,3) BETWEEN 'O98' AND 'O99') THEN 1 ELSE 0 END AS d3, ------ ITUs en el embarazo (O23) CASE WHEN POSITION('O23' in ate_coddia)>0 THEN 1 ELSE 0 END AS d3_1, ------ Embarazo multiple (O30-O31, O84, Z372-Z377) CASE WHEN (SUBSTRING(ate_coddia,1,3) BETWEEN 'O30' AND 'O31') OR POSITION('O84' in ate_coddia)>0 OR (SUBSTRING(ate_coddia,1,4) BETWEEN 'Z372' AND 'Z377') THEN 1 ELSE 0 END AS d4, ------ Complicaciones fetales (O35-O36, O40-O41,O43,O68-O69) CASE WHEN (SUBSTRING(ate_coddia,1,3) BETWEEN 'O35' AND 'O36') OR (SUBSTRING(ate_coddia,1,3) BETWEEN 'O40' AND 'O41') OR POSITION('O43' in ate_coddia)>0 OR (SUBSTRING(ate_coddia,1,3) BETWEEN 'O68' AND 'O69') THEN 1 ELSE 0 END AS d5, ------ Ruptura prematura de membranas (O42) CASE WHEN POSITION('O42' in ate_coddia)>0 THEN 1 ELSE 0 END AS d6, ------ Hemorragia/trauma obstetrico (HTT, rupt/inversión uterina) (O44-O46,O67,O710-O712) CASE WHEN (SUBSTRING(ate_coddia,1,3) BETWEEN 'O44' AND 'O46') OR POSITION('O67' in ate_coddia)>0 OR (SUBSTRING(ate_coddia,1,4) BETWEEN 'O710' AND 'O712') THEN 1 ELSE 0 END AS d7, ------ Hemorragia Postparto y/o Retencion placentaria (O72,O73) CASE WHEN (SUBSTRING(ate_coddia,1,3) BETWEEN 'O72' AND 'O73') THEN 1 ELSE 0 END AS d8, ------ Parto pretermino (O60)

CASE WHEN POSITION('O60' in ate_coddia)>0 THEN 1 ELSE 0 END AS d9, ------ Malpresentaciones fetales/parto obstruido (O32-O34,O63-O66,O81,O83,O801,O808) CASE WHEN (SUBSTRING(ate_coddia,1,3) BETWEEN 'O32' AND 'O34') OR (SUBSTRING(ate_coddia,1,3) BETWEEN 'O63' AND 'O66') OR POSITION('O81' in ate_coddia)>0 OR POSITION('O83' in ate_coddia)>0 OR POSITION('O801' in ate_coddia)>0 OR POSITION('O808' in ate_coddia)>0 THEN 1 ELSE 0 END AS d10, ------ Cesarea (O82) CASE WHEN POSITION('O82' in ate_coddia)>0 THEN 1 ELSE 0 END AS d11, ------ Sepsis puerperal (O85X) CASE WHEN POSITION('O85' in ate_coddia)>0 THEN 1 ELSE 0 END AS d12, ------ Otras patol Puerperio (O86-O92) CASE WHEN (SUBSTRING(ate_coddia,1,3) BETWEEN 'O86' AND 'O92') THEN 1 ELSE 0 END AS d13, ------ Parto normal (O800,O809) -- Pendiente definición satisfactoria CASE WHEN POSITION('O809' in ate_coddia)>0 OR POSITION('O800' in ate_coddia)>0 THEN 1 ELSE 0 END AS d14, ------ Muerte intrauterina (Z371,O364) -- En desuso -- CASE WHEN POSITION('Z371' in ate_coddia)>0 OR -- POSITION('O364' in ate_coddia)>0 -- THEN 1 ELSE 0 END AS d15, ------================================= DIAGNÓSTICOS ADICIONALES 01 ------ Infección de herida quirúrgica obstetrica (separada de d13) CASE WHEN POSITION('O860' in ate_coddia)>0 THEN 1 ELSE 0 END AS c1, ------ Dehiscencia de sutura de cesarea (separada de d13) CASE WHEN POSITION('O900' in ate_coddia)>0 THEN 1 ELSE 0 END AS c2, ------ Hematoma de herida quirúrgica obstetrica (separada de d13) CASE WHEN POSITION('O902' in ate_coddia)>0 THEN 1 ELSE 0 END AS c3, -----================================= DIAGNÓSTICOS ADICIONALES 02 ------ Sifilis CASE WHEN POSITION('A51' in ate_coddia)>0 or POSITION('A52' in ate_coddia)>0 or POSITION('A53' in ate_coddia)>0 or POSITION('O981' in ate_coddia)>0 -- Sífilis que complica el embarazo, el parto y el puerperio THEN 1 ELSE 0 END AS d16, ------ Anemia CASE WHEN POSITION('O990' in ate_coddia)>0 OR -- Anemia que complica embarazo y puerperio POSITION('D500' in ate_coddia)>0 OR -- Anemia por deficiencia de hierro secundaria a perdida de sangre (crónica) POSITION('D508' in ate_coddia)>0 OR -- Otras anemias por deficiencia de hierro POSITION('D509' in ate_coddia)>0 OR -- Anemia por deficiencia de hierro sin otra especificación POSITION('D520' in ate_coddia)>0 OR -- Anemia por deficiencia dietetica de folatos POSITION('D648' in ate_coddia)>0 OR -- Otras anemias especificadas POSITION('D649' in ate_coddia)>0 OR -- Anemia de tipo no especificado POSITION('D638' in ate_coddia)>0 OR -- Anemia en otras enfermedades crónicas clasificadas en otra parte POSITION('D539' in ate_coddia)>0 OR -- Anemia nutricional, no especificada POSITION('D538' in ate_coddia)>0 -- Otras anemias nutricionales especificadas

THEN 1 ELSE 0 END AS d17 FROM sis_09xx.SdiagC_act ; -------- Lista de Diagnósticos para el plan C ------- -- d1 'aborto (O0X, O20)' -- d2 'Enfermedad hipertensiva gestacional (O1X. Incluye PE severa y Eclampsia)' -- d2_1 'solo PREECLAMPSIA SEVERA (O141)' -- d2_2 'solo ECLAMPSIA (O15)' -- d3 'Patologias que complican del embarazo (021-028, 098, 099). Se exceptua ITU (O23)' -- d3_1 'ITUs en el embarazo (O23)' -- d4 'Embarazo multiple (O30, O31, O84, Z372-Z377)' -- d5 'Complicaciones fetales (O35-O36, O40,O41,O43)' -- d6 'Ruptura prematura de membranas (O42)' -- d7 'Hemorragia/trauma obstetrico (HTT, rupt/inversión uterina) (O44-O46,O67,O710-O712)' -- d8 'Hemorragia Postparto y/o Retencion placentaria (O72,O73)' -- d9 'Parto pretermino (O60)' -- d10 'Malpresentaciones fetales/parto obstruido (O32-O34,O63-O66,O81,O83,O801,O808)' -- d11 'Cesarea (O82)' -- d12 'Sepsis puerperal (O85X)' -- d13 'Otras patol Puerperio (O86-O92)' -- d14 'Parto normal (O800,O809)' -- --d15 'Muerte intrauterina (Z371,O364)' --------Dxs adicionales -- c1 'Infección de herida quirúrgica obstetrica (separada de d13)' -- c2 'Dehiscencia de sutura de cesarea (separada de d13)' -- c3 'Hematoma de herida quirúrgica obstetrica (separada de d13)' -- d16 'Sifilis' -- d17 'Anemia' ----------------------------------------------------- --------SERVICIOS MATERNOINFANTILES ----- Se obtienen columnas para Servicios Maternoinfantiles seleccionados EXPLAIN ANALYZE CREATE TABLE planc.Ssmi1 AS SELECT ate_disa, ate_periodo, ate_mes, asmi_numregate, asmi_codsmi, asmi_numero, CASE WHEN asmi_codsmi IN('005') THEN to_number(asmi_numero,'99999999.9') ELSE 0 END ::numeric(7,1) AS sm_eg, --Identifica la edad gestacional CASE WHEN asmi_codsmi IN('005','207','300','010','209','303','201','202','203','204') THEN 1 ELSE 0 END AS sm_gest, --Identifica a gestantes (CPN, puerperio, etc) CASE WHEN asmi_codsmi IN('308') THEN 1 ELSE 0 END AS sm_ppff -- (Consejeria PPFF) FROM sis_09xx.SsmiC_act ; --Indices --CREATE INDEX ix_planc_sdiag1 ON planc.Sdiag1 (ate_disa, adia_numregate) tablespace pgdata2 -- WHERE d1+ d2+d2_1+d2_2 + d3+d3_1 + d4+d5+d6+d7+d8+d9+d10+d11+d12+d13+d14 + c1+c2+c3 + d16+d17 >0 ; -- --CREATE INDEX ix_planc_ssmi1 ON planc.Ssmi1 (ate_disa, asmi_numregate) tablespace pgdata2 -- WHERE sm_eg+sm_gest+sm_ppff >0 ; ------- Se agrega la tabla de DIAGNÓSTICOS a nivel de atenciones EXPLAIN ANALYZE CREATE TABLE planc.SdiagagC_act AS SELECT ate_disa, adia_numregate, ate_periodo, ate_mes, CASE WHEN SUM(d1)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d1, CASE WHEN SUM(d2)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d2,

CASE WHEN SUM(d2_1)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d2_1, CASE WHEN SUM(d2_2)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d2_2, CASE WHEN SUM(d3)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d3, CASE WHEN SUM(d3_1)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d3_1, CASE WHEN SUM(d4)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d4, CASE WHEN SUM(d5)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d5, CASE WHEN SUM(d6)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d6, CASE WHEN SUM(d7)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d7, CASE WHEN SUM(d8)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d8, CASE WHEN SUM(d9)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d9, CASE WHEN SUM(d10)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d10, CASE WHEN SUM(d11)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d11, CASE WHEN SUM(d12)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d12, CASE WHEN SUM(d13)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d13, CASE WHEN SUM(d14)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d14, -- CASE WHEN SUM(d15)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d15, -- En desuso CASE WHEN SUM(c1)>=1 THEN 1 ELSE 0 END ::numeric(1) AS c1, CASE WHEN SUM(c2)>=1 THEN 1 ELSE 0 END ::numeric(1) AS c2, CASE WHEN SUM(c3)>=1 THEN 1 ELSE 0 END ::numeric(1) AS c3, CASE WHEN SUM(d16)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d16, CASE WHEN SUM(d17)>=1 THEN 1 ELSE 0 END ::numeric(1) AS d17 FROM planc.Sdiag1 WHERE d1+ d2+d2_1+d2_2 + d3+d3_1 + d4+d5+d6+d7+d8+d9+d10+d11+d12+d13+d14 + c1+c2+c3 + d16+d17 >0 GROUP BY ate_disa, adia_numregate, ate_periodo, ate_mes ; ------- Se agrega la tabla de SERVICIOS MATERNOINFANTILES a nivel de atenciones EXPLAIN ANALYZE CREATE TABLE planc.SsmiagC_act as SELECT ate_disa, asmi_numregate, ate_periodo, ate_mes, max(sm_eg) AS sm_eg, max(sm_gest) AS sm_gest, max(sm_ppff) AS sm_ppff FROM planc.Ssmi1 WHERE sm_eg+sm_gest+sm_ppff >0 GROUP BY ate_disa, asmi_numregate, ate_periodo, ate_mes ; -------Fin------- --=======================================================** -- Archivo SQL 04ServPlanC_actualiza.sql --=======================================================** -- Inputs: Tablas -- ATEC -> Tabla de Atenciones del plan C depurada y modificada en el script 01 -- AFIC -> Tabla de Afiliaciones del plan C depurada y modificada en el script 02 -- MEDICagC -> Tabla de Medicamentos seleccionados (gestantes) agregadas por Atención -- PROCEDagC -> Tabla de Procedimientos seleccionados (gestantes) agregada por Atención -- DIAGagC -> Tabla de Diagnósticos de Gestantess con campos generados y agregada por atención -- SMIagC -> Tabla de Servicios MaternoInfantiles (Gestantes) con campos generados y agregada por atención -- -- Outputs: Tablas -- ATEN -> Tabla de Atenciones aumentada con columnas para Servicios, Diagnosticos, Medicamentos, Procedimientos y SMI -- -- Proposito: A partir de los datos de los Servicios (tipos de atencion) de las Gestantes, -- generar campos relevantes en la construccion de las tablas analiticas.

-- -- Dependencias directas de otros scripts: Ninguna --=======================================================** -------Servicios------* SET client_encoding TO 'latin1'; SET datestyle TO ISO, ymd; SET search_path TO sis_09xx; DROP TABLE planc.saten_act ; -- Nota (201212) para construir el cubo de atenciones se actualizan solo datos modernos. La exportacion de tablas analiticas se realiza en otro Script ---1) Tabla SATEN (Atenciones) ----Act 201304 ---- Join con Medicamentos, Procedimientos, Diagnosticos y Servicios MaternoInfantiles ---- Join con la tabla Auxiliar TarServ ---- Se añade una columna para el valor del reembolso esperado para las atenciones, de acuerdo con el tarifario del SIS ---- Se obtienen columnas para los Servicios seleccionados EXPLAIN ANALYZE CREATE TABLE planc.Saten_act AS SELECT x.ate_disa, '999' ::varchar(3) as ate_subdisa, '999' ::varchar(3) as ate_pdig, '99' ::varchar(2) as ate_lote, x.ate_idnumreg as ate_nroate, x.ate_coddist, -- x.afi_coddist, -- x.afi_ambito, -- CASE when x.afi_ambito=0 AND cp.nviv<400 THEN 3 WHEN x.afi_ambito=0 AND cp.nviv>=400 THEN 1 ELSE x.afi_ambito END as afi_ambito, -- 1= urbano, 3=rural -- x.afi_idcentropoblado, -- x.afi_fecafil, -- x.AFI_CODEST, CASE WHEN (ate_codsituacionafiins='1' and ate_tipoformatoafiins in ('6','3') and (ate_tablaafi='0' or ate_tablaafi is null) ) or -- 2009 ate_tablaafi in('7','3') THEN 1 WHEN (ate_codsituacionafiins='1' and ate_tipoformatoafiins in('5','2') and (ate_tablaafi='0' or ate_tablaafi is null) ) or -- 2009, 2010, 201107 ate_tablaafi in ('8','2') THEN 2 END AS sitben, x.ate_fecnac, x.ate_codest, x.ate_fecate, x.ate_codser, x.ate_tipprof, x.ate_periodo, x.ate_mes, x.ate_codsituacionafiins, x.ate_tipoformatoafiins, x.ate_tablaafi, x.ate_disaafiins, x.ate_numregafiins, EXTRACT(YEAR FROM x.ate_fecate) ::numeric(4) AS panno1, EXTRACT(YEAR FROM x.ate_fecate)*100+EXTRACT(MONTH FROM x.ate_fecate) ::numeric(6) AS pmes1, CASE WHEN EXTRACT(MONTH FROM x.ate_fecate) IN (3,6,9,12) THEN EXTRACT(YEAR FROM x.ate_fecate)*100+(EXTRACT(MONTH FROM x.ate_fecate)/3) ELSE EXTRACT(YEAR FROM x.ate_fecate)*100+TRUNC(EXTRACT(MONTH FROM x.ate_fecate)/3)+1 END ::numeric(6) AS ptri1, ts.costo as tarif, CASE WHEN x.ate_codser IN ('308','322','619','054') THEN 1 ELSE 0 END AS s1, CASE WHEN x.ate_codser IN ('309','323','620','055') THEN 1 ELSE 0 END AS s2,

CASE WHEN x.ate_codser IN ('202','302','450','451','507','601','806','056','072') THEN 1 ELSE 0 END AS s3, CASE WHEN x.ate_codser IN ('208','311','457','501','602', -- Consulta EMG '209','312','502','062','063') -- Consulta EMG con Obs THEN 1 ELSE 0 END AS s4, CASE WHEN x.ate_codser IN ('310','324','010') THEN 1 ELSE 0 END AS s6, CASE WHEN x.ate_codser IN ('321','618','301','009') THEN 1 ELSE 0 END AS s7, CASE WHEN x.ate_codser IN ('327','407','478','807','013') THEN 1 ELSE 0 END AS s8, CASE WHEN x.ate_codser IN ('401','314','326','313','325','011') THEN 1 ELSE 0 END AS s9, -- Incluye s10 (codser '313' y '325') CASE WHEN x.ate_codser IN ('104','205','305','462','505','607','067') THEN 1 ELSE 0 END AS s11, -- Internam c/Int Qx (Asumo Qx Mayor) CASE WHEN x.ate_codser IN ('204','304','461','504','606','066') THEN 1 ELSE 0 END AS s12, CASE WHEN x.ate_codser IN ('203','303','460','503','605','065') THEN 1 ELSE 0 END AS s13, -- Eliminado codser 103 (Hosp RN) CASE WHEN x.ate_codser IN ('206','306','463','506','608','068') THEN 1 ELSE 0 END AS s14, -- Codser 105 == HospUCI RN CASE WHEN x.ate_codser IN ('408','409','410','414','415','466', '467','468','469','612','104','106','108','109') --No considera cods 473 y 465 (Traslado Urb y Periurb) THEN 1 ELSE 0 END AS s15, CASE WHEN x.ate_codser IN ('411','464','069') THEN 1 ELSE 0 END AS s16, CASE WHEN x.ate_codser IN ('212','403','404','405','452','453','454','471','473','475','609','703', '482','483','057','058','059','070') -- (Identico a TAnalitica Niños) THEN 1 ELSE 0 END AS s17, -- No incluye cod '474' profilaxis destartraje & odontograma m.J01, m.costo AS costMed, m.sf, m.af, m.atbp, m.m14, m.mo1, m.mo2, m.mo3, m.mamoxi, m.mnitrof, m.mcefalex, p.UP01, p.UP02, p.UP03, p.UP04, p.UP05, p.UP06, p.UL01, p.UL02, p.UL03, p.UL04, p.UL05, p.UL06, p.UL07, p.UL08, p.UL09, p.UL10, p.UL11, p.UL12, p.costo AS costPro, d.d1, d.d2, d.d2_1, d.d2_2, d.d3, d.d3_1, d.d4, d.d5, d.d6, d.d7, d.d8, d.d9, d.d10, d.d11, d.d12, d.d13, d.d14, d.c1, d.c2, d.c3, d.d16, d.d17, s.sm_eg, s.sm_gest , CASE WHEN x.ate_codser='018' then 1 else 0 end as sreprod, s.sm_ppff FROM sis_09xx.SateC_act x LEFT JOIN planc.SmedicagC_act m ON x.ate_disa=m.ate_disa AND x.ate_idnumreg=m.amed_numregate LEFT JOIN planc.SprocedagC_act p ON x.ate_disa=p.ate_disa AND x.ate_idnumreg=p.aapo_numregate LEFT JOIN planc.SdiagagC_act d ON x.ate_disa=d.ate_disa AND x.ate_idnumreg=d.adia_numregate LEFT JOIN planc.SsmiagC_act s ON x.ate_disa=s.ate_disa AND x.ate_idnumreg=s.asmi_numregate -- LEFT JOIN public.ccpp07y02 cp ON x.afi_idcentropoblado=cp.idcartogr LEFT JOIN public.TarServ ts ON x.ate_codser=ts.ate_codser ; --------ACTUALIZACION DE LA TABLA SATEN (insercion de registros nuevos) -- Primero se eliminan registros de la tabla receptora para asegurarse de que no se inserten registros duplicados) DELETE from planc.Saten x USING planc.Saten_act y WHERE x.ate_disa=y.ate_disa AND x.ate_nroate=y.ate_nroate ; INSERT INTO planc.saten SELECT * from planc.saten_act ; -- CREATE TABLE planc.Saten_v1 AS -- SELECT x.*, -- CASE WHEN ate_codser='018' then 1 else 0 end as sreprod,

-- s.sm_ppff -- FROM planc.saten x -- LEFT JOIN planc.SsmiagC s ON x.ate_disa=s.ate_disa AND x.ate_nroate=s.asmi_numregate ; -------FIN------ ---------- Lista de Servicios para el plan C ------** -- s1 'Parto vaginal' -- s2 'Cesarea' -- s3 'Consultas externas' -- s4 'Consultas emergencia & Emergencia con Observcion' --(Incluye s5) -- --s5 'Consulta emergencia observacion' -- Pasa a s4 -- s6 'Consulta puerperio' -- s7 'Control prenatal' -- s8 'Ecografia' -- s9 'Exam comp lab embarazo' -- s10 'Exam comp lab embarazo +VIH' -- s11 'Cirugia mayor con hospitalizacion' -- s12 'Cirugia menor con hospitalizacion' -- s13 'Hospitalizacion sin Cirugía' -- s14 'Hospitalizacion en UCI' (Nuevo) -- s15 'Referencia (traslados)' -- s16 'Transfusiones ' -- s17 'Atencion odontologica' ---------------------------------------------------** --=======================================================** -- Archivo SQL _05ConsolidaPlanC.sql --=======================================================** -- Inputs: Tablas -- ATEN (SATEN) -> Tabla de Atenciones del plan C depurada y modificada en scripts previos -- -- Outputs: Tablas -- ATENXagg (SATENXagg) -> Tabla de Atenciones aumentada con columnas para Servicios, -- Diagnosticos, Medicamentos, Procedimientos y SMIs -- -- Proposito: A partir de los datos de los Servicios (tipos de atencion) de las Gestantes, -- generar campos relevantes en la construccion de las tablas analíticas. -- -- Dependencias directas de otros scripts: Ninguna --=======================================================** SET client_encoding TO 'latin1'; SET datestyle TO ISO, ymd; --DROP TABLE IF EXISTS planc.atenX; --DROP TABLE IF EXISTS planc.atenX_comp; --DROP TABLE IF EXISTS planc.atenXagg; DROP TABLE IF EXISTS planc.satenX; DROP TABLE IF EXISTS planc.satenXagg; --$$---- -------------------------------------------------------------------- --$$---- --$$---- --------Tabla analitica intermedia Versión 2002-2008 (incluye articulacion con Tablas analitica posterior) (YA ESTA PROCESADA) !!!!! --$$---- --EXPLAIN ANALYZE --$$---- CREATE TABLE planc.atenX as

--$$---- SELECT --$$---- afi_disa, f_nac, afi_nroafil, afi_fecafil, afi_coddist, afi_ambito, '9999999999' ::text as afi_idcentropoblado, --$$---- afi_codest, --$$---- case when extract(YEAR FROM afi_fecpar)>=2002 then afi_fecpar else null end as afi_fecpar, --$$---- case when extract(YEAR FROM afi_fecpar)>=2002 then afi_fecpar else null end as afi_fecpar1, -- FPP consistente --$$---- null ::integer as sm_eg, -- Campo generado para compatibilizar estructura de tablas --$$---- CASE WHEN ate_fecate>=afi_fecafil AND afi_fecpar >= date '2002-01-01' THEN trunc((ate_fecate-afi_fecpar+287)/7) ELSE null END as eg, -- EG consistente --$$---- MAX(CASE WHEN s1+s2>0 THEN ate_fecate ELSE null END) OVER (PARTITION BY afi_disa, f_nac, afi_nroafil, afi_coddist ) as fparto, --$$---- -- trunc(40-(afi_fecpar-ate_fecate)/7) as sm_eg, --$$---- -- CASE WHEN sm_eg>=20 AND sm_eg<=45 AND s1+s2>0 THEN sm_eg ELSE 000 END as egparto, ---- Defino Edad Gestacional en el parto --$$---- -- CASE WHEN eg>=20 AND eg<=45 AND s1+s2>0 THEN eg ELSE 000 END as egparto, ---- Defino Edad Gestacional en el parto --$$---- to_number(ate_sitben, '9') as sitben, --$$---- ate_disa, ate_subdisa, ate_pdig, ate_lote, ate_nroate, --$$---- ate_codser, ate_codest, ate_fecate, ate_tipprof, ate_fecnac, --$$---- pmes1, --$$---- ptri1, --$$---- panno1, --$$---- s1, s2, s3, s4, s6, s7, s8, s9, s11, s12, s13, s14, s15, s16, s17, --$$---- CASE WHEN ate_codser in('330','329','328','325','709','014') THEN 1 ELSE 0 END as tamVIH, --$$---- CASE WHEN ate_codser in('330','326','325') THEN 1 ELSE 0 END as tamSifilis, --$$---- j01, sf, af, atbp, m14, mo1, mo2, mo3, --$$---- UP01, UP02, UP03, UP04, UP05, UP06, UL01, UL02, UL03, UL04, UL05, UL06, UL07, UL08, UL09, UL10, --$$---- d1, d2, d2_1, d2_2, d3, d3_1, d4, d5, d6, d7, d8, d9, d10, d11, d12, d13, d14, c1, c2, c3, d16, d17, --$$---- IIF(d12>0 AND atbp>0,1,0) as atbp_sep, --$$---- IIF(d6>0 AND atbp>0,1,0) as atbp_rpm, --$$---- IIF(d9>0 AND m14>0,1,0) as cor_pret, --$$---- IIF(s1+s2>0 AND --$$---- CASE WHEN ate_tipprof ~ e'^\\d+$' THEN ate_tipprof::int END IN(1,4,5), 1,0) AS parto_pf, -- Parto x profesional --$$---- IIF(s7>0 AND --$$---- CASE WHEN ate_tipprof ~ e'^\\d+$' THEN ate_tipprof::int END IN(1,4,5), 1,0) AS cpn_pf, -- CPN x profesional --$$---- IIF(UL06>0 AND s7+s9>0,1,0) as cpn_hb, --$$---- IIF(UL02>0 AND s7+s9>0,1,0) as cpn_eco, --$$---- IIF(UL09>0 AND s7+s9>0,1,0) as cpn_ppre, --$$---- IIF(UL10>0 AND s7+s9>0,1,0) as cpn_sifi, --$$---- y.mamoxi, y.mnitrof, y.mcefalex --$$---- FROM planc.atenc4c x --$$---- LEFT JOIN planc.atbagC y using (ate_disa, ate_subdisa, ate_pdig, ate_lote, ate_nroate) ; --$$---- --$$---- -------Se obtiene tabla complementaria (Afiliaciones 2002-2008) --$$---- --EXPLAIN ANALYZE --$$---- CREATE TABLE planc.atenX_comp as --$$---- SELECT --$$---- afi_disa, f_nac, afi_nroafil, afi_fecafil, afi_coddist, afi_ambito, '9999999999' ::text as afi_idcentropoblado, --$$---- afi_codest, --$$---- CASE WHEN (sm_eg>0 AND sm_eg<=42) AND (EXTRACT(YEAR FROM afi_fecpar)=1999 or afi_fecpar IS NULL) THEN ate_fecate+ (40-sm_eg ::integer)*7

--$$---- WHEN (sm_eg>0 AND sm_eg =43) AND (EXTRACT(YEAR FROM afi_fecpar)=1999 or afi_fecpar IS NULL) THEN ate_fecate+ (40-42 ::integer)*7 -- Identico a 42semanas --$$---- WHEN EXTRACT(YEAR FROM afi_fecpar)>=2002 THEN afi_fecpar --$$---- ELSE null END AS afi_fecpar, --FPP derivado de sm_eg (no consistente) --$$---- DATE 'epoch' + AVG(extract(epoch FROM --$$---- CASE WHEN (sm_eg>0 AND sm_eg<=42) AND (EXTRACT(YEAR FROM afi_fecpar)=1999 or afi_fecpar IS NULL) THEN ate_fecate+ (40-sm_eg ::integer)*7 --$$---- WHEN (sm_eg>0 AND sm_eg =43) AND (EXTRACT(YEAR FROM afi_fecpar)=1999 or afi_fecpar IS NULL) THEN ate_fecate+ (40-42 ::integer)*7 -- Identico a 42semanas --$$---- WHEN EXTRACT(YEAR FROM afi_fecpar)>=2002 THEN afi_fecpar --$$---- ELSE null END) * interval '1 second') --$$---- OVER (PARTITION BY afi_disa, f_nac, afi_nroafil, afi_coddist ) AS afi_fecpar1, -->FPP promedio (consistente) --$$---- CASE WHEN sm_eg BETWEEN 1 AND 43 THEN sm_eg ELSE null END as sm_eg , -- EG no consistente --$$---- null ::numeric(8,3) as eg, --Compatibilidad estrutura de tabla --$$---- MAX(CASE WHEN s1+s2>0 then ate_fecate else null END) OVER (PARTITION BY afi_disa, f_nac, afi_nroafil, afi_coddist ) as fparto, --$$---- -- CASE WHEN sm_eg>=20 AND sm_eg<=45 AND s1+s2>0 THEN sm_eg ELSE 000 END as egparto, ---- Define Edad Gestacional en el parto --$$---- sitben, --$$---- ate_disa, '999' ::text as ate_subdisa, '999' ::text as ate_pdig, '99' ::text as ate_lote, ate_nroate, ----ate_idnumreg as ate_nroate, --$$---- ate_codser, ate_codest, ate_fecate, ate_tipprof, ate_fecnac, --$$---- pmes1, --$$---- ptri1, --$$---- panno1, --$$---- s1, s2, s3, s4, s6, s7, s8, s9, s11, s12, s13, s14, s15, s16, s17, --$$---- CASE WHEN ate_codser in('330','329','328','325','709','014') THEN 1 ELSE 0 END as tamVIH, --$$---- CASE WHEN ate_codser in('330','326','325') THEN 1 ELSE 0 END as tamSifilis, --$$---- j01, sf, af, atbp, m14, mo1, mo2, mo3, --$$---- UP01, UP02, UP03, UP04, UP05, UP06, UL01, UL02, UL03, UL04, UL05, UL06, UL07, UL08, UL09, UL10, --$$---- d1, d2, d2_1, d2_2, d3, d3_1, d4, d5, d6, d7, d8, d9, d10, d11, d12, d13, d14, c1, c2, c3, d16, d17, --$$---- IIF(d12>0 AND atbp>0,1,0) as atbp_sep, --$$---- IIF(d6>0 AND atbp>0,1,0) as atbp_rpm, --$$---- IIF(d9>0 AND m14>0,1,0) as cor_pret, --$$---- IIF(s1+s2>0 AND --$$---- CASE WHEN ate_tipprof ~ e'^\\d+$' THEN ate_tipprof::int END IN(1,4,5), 1,0) AS parto_pf, --$$---- IIF(s7>0 AND --$$---- CASE WHEN ate_tipprof ~ e'^\\d+$' THEN ate_tipprof::int END IN(1,4,5), 1,0) AS cpn_pf, --$$---- IIF(UL06>0 AND s7+s9>0,1,0) as cpn_hb, --$$---- IIF(UL02>0 AND s7+s9>0,1,0) as cpn_eco, --$$---- IIF(UL09>0 AND s7+s9>0,1,0) as cpn_ppre, --$$---- IIF(UL10>0 AND s7+s9>0,1,0) as cpn_sifi, --$$---- mamoxi, mnitrof, mcefalex, --$$---- -- sm_ppff, sreprod --$$---- FROM (SELECT * FROM planc.saten -- _v1 --$$---- WHERE ate_codsituacionafiins='3' AND (ate_tablaafi='0' or ate_tablaafi is null) ) x ---Atenciones antiguas --$$---- LEFT JOIN sis_0208.afiC y --$$---- ON x.ate_disaafiins=y.afi_disa AND --$$---- x.ate_numregafiins=y.afi_nroafil AND EXTRACT(YEAR FROM x.ate_fecnac)*100+EXTRACT(MONTH FROM x.ate_fecnac)=y.f_nac; --$$---- --$$----

--$$---- --$$---- UPDATE planc.atenX_comp SET eg= trunc((ate_fecate-date(afi_fecpar1)+287)/7) --$$---- WHERE eg IS null AND ate_fecate>=afi_fecafil AND afi_fecpar1 >= date '2002-01-01'; -- EG consistente --$$---- --$$---- --$$---- --$$---- ---Se consolidan las dos tablas previas --$$---- DROP TABLE planc.atenX2; --$$---- --$$---- --CREATE TABLE planc.atenX2 (LIKE planc.atenX INCLUDING ALL); --$$---- --$$---- CREATE TABLE planc.atenX2 AS SELECT * from planc.atenX_comp ; --$$---- --$$---- INSERT INTO planc.atenX2 SELECT *, 0 as sm_ppff, case when ate_codser='018' then 1 else 0 end as sreprod from planc.atenX ; --$$---- --$$---- --$$---- -- CREATE TABLE planc.atenX2 AS SELECT * from planc.atenX ; --$$---- -- --$$---- -- INSERT INTO planc.atenX2 SELECT * from planc.atenX_comp ; --$$---- --$$---- --$$---- --Indice --$$---- CREATE INDEX ix_planc_atenX2 ON planc.atenX2 (afi_disa, f_nac, afi_nroafil, afi_fecafil, afi_coddist, afi_ambito, afi_idcentropoblado, afi_codest) tablespace pgdata3; --$$---- --$$---- --$$---- ------ (TAnalitica final Versión 2002-2008) --$$---- --EXPLAIN ANALYZE --$$---- CREATE TABLE planc.atenXagg as --$$---- SELECT --$$---- afi_disa, f_nac, afi_nroafil, afi_coddist, afi_ambito, afi_idcentropoblado, afi_codest, afi_fecafil, --$$---- MAX(EXTRACT(YEAR FROM afi_fecafil)) AS fanno1, --$$---- MAX(EXTRACT(YEAR FROM afi_fecafil)*100+EXTRACT(MONTH FROM afi_fecafil)) AS fmes1, --$$---- MAX(ate_fecate) as mxfate, --$$---- MAX(CASE WHEN s7+s1+s2>0 THEN ate_fecate ELSE null END) AS mxfate_obst, --$$---- MAX(afi_fecpar1) AS afi_fecpar1, --$$---- MAX(fparto) as fparto, --$$---- MAX(CASE WHEN s1+s2>0 THEN ate_codest ELSE null END) as eessparto, --$$---- MAX(EXTRACT(YEAR FROM afi_fecpar1)) AS fpanno1, --$$---- MAX(EXTRACT(YEAR FROM afi_fecpar1)*100+EXTRACT(MONTH FROM afi_fecpar1)) AS fpmes1, --$$---- MAX(CASE WHEN EXTRACT(MONTH FROM afi_fecpar1) IN(3,6,9,12) --$$---- THEN EXTRACT(YEAR FROM afi_fecpar1)*100+(EXTRACT(MONTH FROM afi_fecpar1)/3) --$$---- ELSE EXTRACT(YEAR FROM afi_fecpar1)*100+TRUNC(EXTRACT(MONTH FROM afi_fecpar1)/3)+1 END) AS fptri1, --$$---- MAX(CASE WHEN s1+s2>0 THEN eg ELSE null END) as egparto, --$$---- CASE WHEN SUM(s1)>0 THEN 1 ELSE 0 END AS s1, --$$---- CASE WHEN SUM(s2)>0 THEN 1 ELSE 0 END AS s2, --$$---- CASE WHEN SUM(s3)>0 THEN 1 ELSE 0 END AS s3, --$$---- CASE WHEN SUM(s4)>0 THEN 1 ELSE 0 END AS s4, --$$---- CASE WHEN SUM(s6)>0 THEN 1 ELSE 0 END AS s6, --$$---- CASE WHEN SUM(s7)>=1 THEN 1 ELSE 0 END AS s7_1, --$$---- CASE WHEN SUM(s7)>=4 THEN 1 ELSE 0 END AS s7_4,

--$$---- CASE WHEN SUM(s7)>=6 THEN 1 ELSE 0 END AS s7_6, --$$---- CASE WHEN SUM(s8) >0 THEN 1 ELSE 0 END AS s8, --$$---- CASE WHEN SUM(s9) >0 THEN 1 ELSE 0 END AS s9, --$$---- CASE WHEN SUM(s11)>0 THEN 1 ELSE 0 END AS s11, --$$---- CASE WHEN SUM(s12)>0 THEN 1 ELSE 0 END AS s12, --$$---- CASE WHEN SUM(s13)>0 THEN 1 ELSE 0 END AS s13, --$$---- CASE WHEN SUM(s14)>0 THEN 1 ELSE 0 END AS s14, --$$---- CASE WHEN SUM(s15)>0 THEN 1 ELSE 0 END AS s15, --$$---- CASE WHEN SUM(s16)>0 THEN 1 ELSE 0 END AS s16, --$$---- CASE WHEN SUM(s17)>0 THEN 1 ELSE 0 END AS s17, --$$---- CASE WHEN SUM(tamVIH)>0 THEN 1 ELSE 0 END AS tamVIH, --$$---- CASE WHEN SUM(tamSifilis)>0 THEN 1 ELSE 0 END AS tamSifilis, --$$---- SUM(j01) as j01, --$$---- CASE WHEN SUM(sf)>0 THEN 1 ELSE 0 END AS sf, --$$---- CASE WHEN SUM(sf)>=4 THEN 1 ELSE 0 END AS sf4, --Modif 201404 para FED --$$---- CASE WHEN SUM(af)>0 THEN 1 ELSE 0 END as af, --$$---- CASE WHEN SUM(af)>=4 THEN 1 ELSE 0 END as af4, --Modif 201404 para FED (Indicador Gestantes con 4 atenciones con Hierro y AFolico) --$$---- CASE WHEN SUM(atbp)>0 THEN 1 ELSE 0 END as atbp, --$$---- CASE WHEN SUM(m14)>0 THEN 1 ELSE 0 END AS m14, --$$---- CASE WHEN SUM(mo1)>0 THEN 1 ELSE 0 END AS mo1, --$$---- CASE WHEN SUM(mo2)>0 THEN 1 ELSE 0 END AS mo2, --$$---- CASE WHEN SUM(mo3)>0 THEN 1 ELSE 0 END AS mo3, --$$---- CASE WHEN SUM(UP01)>0 THEN 1 ELSE 0 END AS UP01, --$$---- CASE WHEN SUM(UP02)>0 THEN 1 ELSE 0 END AS UP02, --$$---- CASE WHEN SUM(UP03)>0 THEN 1 ELSE 0 END AS UP03, --$$---- CASE WHEN SUM(UP04)>0 THEN 1 ELSE 0 END AS UP04, --$$---- CASE WHEN SUM(UP05)>0 THEN 1 ELSE 0 END AS UP05, --$$---- CASE WHEN SUM(UP06)>0 THEN 1 ELSE 0 END AS UP06, --$$---- CASE WHEN SUM(UL01)>0 THEN 1 ELSE 0 END AS UL01, --$$---- CASE WHEN SUM(UL02)>0 THEN 1 ELSE 0 END AS UL02, --$$---- CASE WHEN SUM(UL03)>0 THEN 1 ELSE 0 END AS UL03, --$$---- CASE WHEN SUM(UL04)>0 THEN 1 ELSE 0 END AS UL04, --$$---- CASE WHEN SUM(UL05)>0 THEN 1 ELSE 0 END AS UL05, --$$---- CASE WHEN SUM(UL06)>0 THEN 1 ELSE 0 END AS UL06, --$$---- CASE WHEN SUM(UL07)>0 THEN 1 ELSE 0 END AS UL07, --$$---- CASE WHEN SUM(UL08)>0 THEN 1 ELSE 0 END AS UL08, --$$---- CASE WHEN SUM(UL09)>0 THEN 1 ELSE 0 END AS UL09, --$$---- CASE WHEN SUM(UL10)>0 THEN 1 ELSE 0 END AS UL10, --$$---- CASE WHEN SUM(d1)>0 THEN 1 ELSE 0 END AS d1, --$$---- CASE WHEN SUM(d2)>0 THEN 1 ELSE 0 END AS d2, --$$---- CASE WHEN SUM(d2_1)>0 THEN 1 ELSE 0 END AS d2_1, --$$---- CASE WHEN SUM(d2_2)>0 THEN 1 ELSE 0 END AS d2_2, --$$---- CASE WHEN SUM(d3)>0 THEN 1 ELSE 0 END AS d3, --$$---- CASE WHEN SUM(d3_1)>0 THEN 1 ELSE 0 END AS d3_1, --$$---- CASE WHEN SUM(d4)>0 THEN 1 ELSE 0 END AS d4, --$$---- CASE WHEN SUM(d5)>0 THEN 1 ELSE 0 END AS d5, --$$---- CASE WHEN SUM(d6)>0 THEN 1 ELSE 0 END AS d6, --$$---- CASE WHEN SUM(d7)>0 THEN 1 ELSE 0 END AS d7, --$$---- CASE WHEN SUM(d8)>0 THEN 1 ELSE 0 END AS d8, --$$---- CASE WHEN SUM(d9)>0 THEN 1 ELSE 0 END AS d9, --$$---- CASE WHEN SUM(d10)>0 THEN 1 ELSE 0 END AS d10, --$$---- CASE WHEN SUM(d11)>0 THEN 1 ELSE 0 END AS d11, --$$---- CASE WHEN SUM(d12)>0 THEN 1 ELSE 0 END AS d12, --$$---- CASE WHEN SUM(d13)>0 THEN 1 ELSE 0 END AS d13, --$$---- CASE WHEN SUM(d14)>0 THEN 1 ELSE 0 END AS d14, --$$---- CASE WHEN SUM(c1 )>0 THEN 1 ELSE 0 END AS c1,

--$$---- CASE WHEN SUM(c2 )>0 THEN 1 ELSE 0 END AS c2, --$$---- CASE WHEN SUM(c3 )>0 THEN 1 ELSE 0 END AS c3, --$$---- CASE WHEN SUM(d16)>0 THEN 1 ELSE 0 END AS d16, --$$---- CASE WHEN SUM(d17)>0 THEN 1 ELSE 0 END AS d17, --$$---- CASE WHEN SUM(atbp_sep)>0 THEN 1 ELSE 0 END AS atbp_sep, --$$---- CASE WHEN SUM(atbp_rpm)>0 THEN 1 ELSE 0 END AS atbp_rpm, --$$---- CASE WHEN SUM(cor_pret)>0 THEN 1 ELSE 0 END AS cor_pret, --$$---- CASE WHEN SUM(parto_pf)>0 THEN 1 ELSE 0 END AS parto_pf, --$$---- CASE WHEN SUM(cpn_pf )>0 THEN 1 ELSE 0 END AS cpn_pf , --$$---- CASE WHEN SUM(cpn_hb )>0 THEN 1 ELSE 0 END AS cpn_hb , --$$---- CASE WHEN SUM(cpn_eco )>0 THEN 1 ELSE 0 END AS cpn_eco , --$$---- CASE WHEN SUM(cpn_sifi)>0 THEN 1 ELSE 0 END AS cpn_sifi, --$$---- CASE WHEN SUM(cpn_ppre)>0 THEN 1 ELSE 0 END AS cpn_ppre , --$$---- CASE WHEN SUM(mamoxi*d3_1)>4500 OR SUM(mnitrof*d3_1)>750 OR SUM(mcefalex*d3_1)>4500 OR SUM(atbp*d3_1)>0 THEN 1 ELSE 0 END as itu_atb, --$$---- CASE WHEN SUM(mamoxi*d3_1)>4500 THEN 1 ELSE 0 END as itu_amox, --$$---- CASE WHEN SUM(mamoxi) >4500 THEN 1 ELSE 0 END AS mamoxi, --$$---- CASE WHEN SUM(mcefalex)>4500 THEN 1 ELSE 0 END AS mcefalex, --$$---- CASE WHEN SUM(mnitrof) >750 THEN 1 ELSE 0 END AS mnitrof, --$$---- CASE WHEN SUM(s1+s2)>0 THEN 1 ELSE 0 END AS parto, --$$---- CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 THEN 1 ELSE 0 END) >0 THEN 1 ELSE 0 END as cpn_1tr, --$$---- CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL06>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_hb_1tr, --$$---- CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL05>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_hto_1tr, --hematocrito en 1er trimestre --$$---- CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL03>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_glu_1tr, --glucosa en 1er trimestre --$$---- CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL02>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_eco_1tr, --$$---- CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL09>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_ppre_1tr, --Perf prent(hb,gluc,eco,hiv,sifi)--no hb/hto --$$---- CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL10>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_sifi_1tr, --$$---- CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND tamSifilis>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as tamSifilis_1tr, --$$---- CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND tamVIH>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as tamVIH_1tr, --$$---- SUM(s7) AS ncpn, ---Numero de APNs --$$---- trunc((afi_fecafil - MAX(date(afi_fecpar1)) + 287)/7) as eg_afil , --$$---- CASE WHEN trunc((afi_fecafil - MAX(date(afi_fecpar1)) + 287)/7) <=15 THEN 1 ELSE 0 END as afi_1tr , ---Cambio de <=15 a <15 --$$---- CASE WHEN SUM(sm_ppff)>0 THEN 1 ELSE 0 END AS sm_ppff, --$$---- CASE WHEN SUM(sreprod)>0 THEN 1 ELSE 0 END AS sreprod --$$---- FROM planc.atenX2 --$$---- GROUP BY afi_disa, f_nac, afi_nroafil, afi_fecafil, afi_coddist, afi_ambito, afi_idcentropoblado, afi_codest ; --$$---- --------------------------------------------------------------- CREATE OR REPLACE FUNCTION _moda_final(anyarray) RETURNS anyelement AS $BODY$ SELECT a FROM unnest($1) a GROUP BY 1 ORDER BY COUNT(1) DESC, 1

LIMIT 1; $BODY$ LANGUAGE 'sql' IMMUTABLE; DROP AGGREGATE IF EXISTS moda(anyelement); CREATE AGGREGATE moda(anyelement) ( SFUNC=array_append, STYPE=anyarray, FINALFUNC=_moda_final, INITCOND='{}' ); ---------------------------------------------------------------- CREATE OR REPLACE FUNCTION _final_range(numeric[]) RETURNS numeric AS $$ SELECT MAX(val) - MIN(val) FROM unnest($1) val; $$ LANGUAGE 'sql' IMMUTABLE; -- Add aggregate CREATE AGGREGATE range(numeric) ( SFUNC=array_append, --Function to call for each row. Just builds the array STYPE=numeric[], FINALFUNC=_final_range, --Function to call after everything has been added to array INITCOND='{}' --Initialize an empty array when starting ); --------Tabla analitica Versión 2009+ --EXPLAIN ANALYZE CREATE TABLE planc.satenX AS SELECT afi_disa, f_nac, afi_numregafis as afi_nroafil, afi_fecafil, afi_coddist, CASE when cp.nviv<400 THEN 3 WHEN cp.nviv>=400 THEN 1 ELSE 0 END as afi_ambito, afi_idcentropoblado, afi_codest, CASE WHEN (sm_eg>0 AND sm_eg<=42) THEN ate_fecate+ (40-sm_eg ::integer)*7 WHEN (sm_eg>0 AND sm_eg =43) THEN ate_fecate+ (40-42 ::integer)*7 -- Identico a 42semanas ELSE null END AS afi_fecpar, --FPP derivado de sm_eg (no consistente) DATE 'epoch' + AVG(extract(epoch FROM CASE WHEN (sm_eg>0 AND sm_eg<=42) THEN ate_fecate+ (40-sm_eg ::integer)*7 WHEN (sm_eg>0 AND sm_eg =43) THEN ate_fecate+ (40-42 ::integer)*7 -- Identico a 42semanas ELSE null END) * interval '1 second') OVER (PARTITION BY afi_disa, f_nac, afi_numregafis, afi_coddist ) AS afi_fecpar1, -->FPP promedio (consistente) -- MODA( -- CASE WHEN (sm_eg>0 AND sm_eg<=42) THEN ate_fecate+ (40-sm_eg ::integer)*7 -- WHEN (sm_eg>0 AND sm_eg =43) THEN ate_fecate+ (40-42 ::integer)*7 -- Identico a 42semanas -- ELSE null END ) -- OVER (PARTITION BY afi_disa, f_nac, afi_numregafis, afi_coddist ) AS afi_fecpar2, -->FPP modal (consistente) CASE WHEN sm_eg BETWEEN 1 AND 43 THEN sm_eg ELSE null END as sm_eg , -- EG no consistente null ::smallint as eg, --Compatibilidad estrutura de tabla MAX(CASE WHEN s1+s2>0 then ate_fecate else null END) OVER (PARTITION BY afi_disa, f_nac, afi_numregafis, afi_coddist ) as fparto, -- CASE WHEN sm_eg>=20 AND sm_eg<=45 AND s1+s2>0 THEN sm_eg ELSE 000 END as egparto, ---- Defino Edad Gestacional en el parto sitben, ate_disa, '999' ::text as ate_subdisa, '999' ::text as ate_pdig, '99' ::text as ate_lote, ate_nroate, --

ate_codser, ate_codest, ate_fecate, ate_tipprof, ate_fecnac, pmes1, ptri1, panno1, s1, s2, s3, s4, s6, s7, s8, s9, s11, s12, s13, s14, s15, s16, s17, CASE WHEN ate_codser in('330','329','328','325','709','014') THEN 1 ELSE 0 END as tamVIH, ---Codigos antiguos CASE WHEN ate_codser in('330','326','325') THEN 1 ELSE 0 END as tamSifilis, ---Codigos antiguos j01, sf, af, atbp, m14, mo1, mo2, mo3, UP01, UP02, UP03, UP04, UP05, UP06, UL01, UL02, UL03, UL04, UL05, UL06, UL07, UL08, UL09, UL10, UL11, UL12, ----Nuevo Set 2014 (Añadido UL11 y UL12) d1, d2, d2_1, d2_2, d3, d3_1, d4, d5, d6, d7, d8, d9, d10, d11, d12, d13, d14, c1, c2, c3, d16, d17, IIF(d12>0 AND atbp>0,1,0) as atbp_sep, IIF(d6>0 AND atbp>0,1,0) as atbp_rpm, IIF(d9>0 AND m14>0,1,0) as cor_pret, IIF(s1+s2>0 AND CASE WHEN ate_tipprof ~ e'^\\d+$' THEN ate_tipprof::int END IN(1,4,5), 1,0) AS parto_pf, IIF(s7>0 AND CASE WHEN ate_tipprof ~ e'^\\d+$' THEN ate_tipprof::int END IN(1,4,5), 1,0) AS cpn_pf, IIF(UL06>0 AND s7+s9>0,1,0) as cpn_hb, IIF(UL02>0 AND s7+s9>0,1,0) as cpn_eco, IIF(UL09>0 AND s7+s9>0,1,0) as cpn_ppre, IIF(UL10>0 AND s7+s9>0,1,0) as cpn_sifi, mamoxi, mnitrof, mcefalex , ----AÑADIR ESTOS CAMPOS A LA TABLA INSUMO sm_ppff, sreprod --<<<----- FROM (SELECT * FROM planc.saten WHERE (ate_codsituacionafiins='1' AND ate_tipoformatoafiins IN ('6','3') AND (ate_tablaafi='0' OR ate_tablaafi IS NULL) ) OR ate_tablaafi IN('7','3') ) x ----- Atenciones nuevas LEFT JOIN sis_09xx.safiC y ON x.ate_disaafiins=y.afi_disa AND x.ate_numregafiins=y.afi_numregafis AND EXTRACT(YEAR FROM x.ate_fecnac)*100+EXTRACT(MONTH FROM x.ate_fecnac)=y.f_nac LEFT JOIN public.ccpp07y02 cp ON y.afi_idcentropoblado=cp.idcartogr ; UPDATE planc.satenX SET eg= trunc((ate_fecate-date(afi_fecpar1)+287)/7) WHERE eg IS null AND ate_fecate>=afi_fecafil AND afi_fecpar1 >= date '2002-01-01'; -- EG consistente --select date '2013-04-01' as afi_fecpar1, '2013-04-01' as ate_fecate, -- trunc((date '2013-04-01'- date '2013-04-01' + 287)/7) as eg ; --CASE WHEN trunc((ate_fecafil-date(afi_fecpar1)+287)/7) ------Se obtiene las tablas analíticas finales mediante agregacion, obteniendo un registro por cada gestante ------ (TAnalitica final Versión 2009+) EXPLAIN ANALYZE CREATE TABLE planc.satenXagg as SELECT afi_disa, f_nac, afi_nroafil, afi_coddist, afi_ambito, afi_idcentropoblado, afi_codest, afi_fecafil, MAX(EXTRACT(YEAR FROM afi_fecafil)) AS fanno1, MAX(EXTRACT(YEAR FROM afi_fecafil)*100+EXTRACT(MONTH FROM afi_fecafil)) AS fmes1, MAX(ate_fecate) as mxfate, MAX(CASE WHEN s7+s1+s2>0 THEN ate_fecate ELSE null END) AS mxfate_obst, MAX(afi_fecpar1) AS afi_fecpar1, MAX(fparto) as fparto, MAX(CASE WHEN s1+s2>0 THEN ate_codest ELSE null END) as eessparto, MAX(EXTRACT(YEAR FROM afi_fecpar1)) AS fpanno1,

MAX(EXTRACT(YEAR FROM afi_fecpar1)*100+EXTRACT(MONTH FROM afi_fecpar1)) AS fpmes1, MAX(CASE WHEN EXTRACT(MONTH FROM afi_fecpar1) IN(3,6,9,12) THEN EXTRACT(YEAR FROM afi_fecpar1)*100+(EXTRACT(MONTH FROM afi_fecpar1)/3) ELSE EXTRACT(YEAR FROM afi_fecpar1)*100+TRUNC(EXTRACT(MONTH FROM afi_fecpar1)/3)+1 END) AS fptri1, MAX(CASE WHEN s1+s2>0 THEN eg ELSE null END) as egparto, CASE WHEN SUM(s1)>0 THEN 1 ELSE 0 END AS s1, CASE WHEN SUM(s2)>0 THEN 1 ELSE 0 END AS s2, CASE WHEN SUM(s3)>0 THEN 1 ELSE 0 END AS s3, CASE WHEN SUM(s4)>0 THEN 1 ELSE 0 END AS s4, CASE WHEN SUM(s6)>0 THEN 1 ELSE 0 END AS s6, CASE WHEN SUM(s7)>=1 THEN 1 ELSE 0 END AS s7_1, CASE WHEN SUM(s7)>=4 THEN 1 ELSE 0 END AS s7_4, CASE WHEN SUM(s7)>=6 THEN 1 ELSE 0 END AS s7_6, CASE WHEN SUM(s8) >0 THEN 1 ELSE 0 END AS s8, CASE WHEN SUM(s9) >0 THEN 1 ELSE 0 END AS s9, CASE WHEN SUM(s11)>0 THEN 1 ELSE 0 END AS s11, CASE WHEN SUM(s12)>0 THEN 1 ELSE 0 END AS s12, CASE WHEN SUM(s13)>0 THEN 1 ELSE 0 END AS s13, CASE WHEN SUM(s14)>0 THEN 1 ELSE 0 END AS s14, CASE WHEN SUM(s15)>0 THEN 1 ELSE 0 END AS s15, CASE WHEN SUM(s16)>0 THEN 1 ELSE 0 END AS s16, CASE WHEN SUM(s17)>0 THEN 1 ELSE 0 END AS s17, CASE WHEN SUM(tamVIH)>0 THEN 1 ELSE 0 END AS tamVIH, CASE WHEN SUM(tamSifilis)>0 THEN 1 ELSE 0 END AS tamSifilis, SUM(j01) as j01, CASE WHEN SUM(sf)>0 THEN 1 ELSE 0 END AS sf, CASE WHEN SUM(sf)>=4 THEN 1 ELSE 0 END AS sf4, --Modif 201404 para FED CASE WHEN SUM(af)>0 THEN 1 ELSE 0 END as af, CASE WHEN SUM(af)>=4 THEN 1 ELSE 0 END AS af4, --Modif 201404 para FED (Indicador Gestantes con 4 atenciones con Hierro y AFolico) CASE WHEN SUM(atbp)>0 THEN 1 ELSE 0 END as atbp, CASE WHEN SUM(m14)>0 THEN 1 ELSE 0 END AS m14, CASE WHEN SUM(mo1)>0 THEN 1 ELSE 0 END AS mo1, CASE WHEN SUM(mo2)>0 THEN 1 ELSE 0 END AS mo2, CASE WHEN SUM(mo3)>0 THEN 1 ELSE 0 END AS mo3, CASE WHEN SUM(UP01)>0 THEN 1 ELSE 0 END AS UP01, CASE WHEN SUM(UP02)>0 THEN 1 ELSE 0 END AS UP02, CASE WHEN SUM(UP03)>0 THEN 1 ELSE 0 END AS UP03, CASE WHEN SUM(UP04)>0 THEN 1 ELSE 0 END AS UP04, CASE WHEN SUM(UP05)>0 THEN 1 ELSE 0 END AS UP05, CASE WHEN SUM(UP06)>0 THEN 1 ELSE 0 END AS UP06, CASE WHEN SUM(UL01)>0 THEN 1 ELSE 0 END AS UL01, CASE WHEN SUM(UL02)>0 THEN 1 ELSE 0 END AS UL02, CASE WHEN SUM(UL03)>0 THEN 1 ELSE 0 END AS UL03, CASE WHEN SUM(UL04)>0 THEN 1 ELSE 0 END AS UL04, CASE WHEN SUM(UL05)>0 THEN 1 ELSE 0 END AS UL05, CASE WHEN SUM(UL06)>0 THEN 1 ELSE 0 END AS UL06, CASE WHEN SUM(UL07)>0 THEN 1 ELSE 0 END AS UL07, CASE WHEN SUM(UL08)>0 THEN 1 ELSE 0 END AS UL08, CASE WHEN SUM(UL09)>0 THEN 1 ELSE 0 END AS UL09, CASE WHEN SUM(UL10)>0 THEN 1 ELSE 0 END AS UL10, CASE WHEN SUM(d1)>0 THEN 1 ELSE 0 END AS d1, CASE WHEN SUM(d2)>0 THEN 1 ELSE 0 END AS d2, CASE WHEN SUM(d2_1)>0 THEN 1 ELSE 0 END AS d2_1, CASE WHEN SUM(d2_2)>0 THEN 1 ELSE 0 END AS d2_2,

CASE WHEN SUM(d3)>0 THEN 1 ELSE 0 END AS d3, CASE WHEN SUM(d3_1)>0 THEN 1 ELSE 0 END AS d3_1, CASE WHEN SUM(d4)>0 THEN 1 ELSE 0 END AS d4, CASE WHEN SUM(d5)>0 THEN 1 ELSE 0 END AS d5, CASE WHEN SUM(d6)>0 THEN 1 ELSE 0 END AS d6, CASE WHEN SUM(d7)>0 THEN 1 ELSE 0 END AS d7, CASE WHEN SUM(d8)>0 THEN 1 ELSE 0 END AS d8, CASE WHEN SUM(d9)>0 THEN 1 ELSE 0 END AS d9, CASE WHEN SUM(d10)>0 THEN 1 ELSE 0 END AS d10, CASE WHEN SUM(d11)>0 THEN 1 ELSE 0 END AS d11, CASE WHEN SUM(d12)>0 THEN 1 ELSE 0 END AS d12, CASE WHEN SUM(d13)>0 THEN 1 ELSE 0 END AS d13, CASE WHEN SUM(d14)>0 THEN 1 ELSE 0 END AS d14, CASE WHEN SUM(c1 )>0 THEN 1 ELSE 0 END AS c1, CASE WHEN SUM(c2 )>0 THEN 1 ELSE 0 END AS c2, CASE WHEN SUM(c3 )>0 THEN 1 ELSE 0 END AS c3, CASE WHEN SUM(d16)>0 THEN 1 ELSE 0 END AS d16, CASE WHEN SUM(d17)>0 THEN 1 ELSE 0 END AS d17, CASE WHEN SUM(atbp_sep)>0 THEN 1 ELSE 0 END AS atbp_sep, CASE WHEN SUM(atbp_rpm)>0 THEN 1 ELSE 0 END AS atbp_rpm, CASE WHEN SUM(cor_pret)>0 THEN 1 ELSE 0 END AS cor_pret, CASE WHEN SUM(parto_pf)>0 THEN 1 ELSE 0 END AS parto_pf, CASE WHEN SUM(cpn_pf )>0 THEN 1 ELSE 0 END AS cpn_pf , CASE WHEN SUM(cpn_hb )>0 THEN 1 ELSE 0 END AS cpn_hb , CASE WHEN SUM(cpn_eco )>0 THEN 1 ELSE 0 END AS cpn_eco , CASE WHEN SUM(cpn_sifi)>0 THEN 1 ELSE 0 END AS cpn_sifi, CASE WHEN SUM(cpn_ppre)>0 THEN 1 ELSE 0 END AS cpn_ppre , CASE WHEN SUM(mamoxi*d3_1)>4500 OR SUM(mnitrof*d3_1)>750 OR SUM(mcefalex*d3_1)>4500 or SUM(atbp*d3_1)>0 THEN 1 ELSE 0 END as itu_atb, CASE WHEN SUM(mamoxi*d3_1)>4500 THEN 1 ELSE 0 END as itu_amox, CASE WHEN SUM(mamoxi) >4500 THEN 1 ELSE 0 END AS mamoxi, CASE WHEN SUM(mcefalex)>4500 THEN 1 ELSE 0 END AS mcefalex, CASE WHEN SUM(mnitrof) >750 THEN 1 ELSE 0 END AS mnitrof, CASE WHEN SUM(s1+s2)>0 THEN 1 ELSE 0 END AS parto, CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 THEN 1 ELSE 0 END) >0 THEN 1 ELSE 0 END as cpn_1tr, CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL06>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_hb_1tr, CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL05>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_hto_1tr, --hematocrito en 1er trimestre CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL03>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_glu_1tr, --glucosa en 1er trimestre CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL02>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_eco_1tr, CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL09>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_ppre_1tr, --Perf prenat(hmg,gluc,eco,hiv,sifi)--no hb/hto ---CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s9>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_ppre_1tr_v2, --Perf prenat(hmg,gluc,eco,hiv,sifi)--no hb/hto CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL10>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_sifi_1tr, CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND s7+s9>0 AND UL12>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as cpn_VIH_1tr, CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND tamSifilis>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as tamSifilis_1tr, CASE WHEN SUM(CASE WHEN eg>0 AND eg<=15 AND tamVIH>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END as tamVIH_1tr, SUM(s7) AS ncpn, ---Numero de APNs

trunc((afi_fecafil - MAX(date(afi_fecpar1)) + 287)/7) as eg_afil , --EG en la afiliacion CASE WHEN trunc((afi_fecafil - MAX(date(afi_fecpar1)) + 287)/7) <=15 THEN 1 ELSE 0 END as afi_1tr, --, -- Afiliacion en el 1er trimestre CASE WHEN SUM(sm_ppff)>0 THEN 1 ELSE 0 END AS sm_ppff, CASE WHEN SUM(sreprod)>0 THEN 1 ELSE 0 END AS sreprod, MIN(CASE WHEN sm_ppff>0 THEN ate_fecate ELSE null END) as f1_ppff FROM planc.satenX GROUP BY afi_disa, f_nac, afi_nroafil, afi_fecafil, afi_coddist, afi_ambito, afi_idcentropoblado, afi_codest ;