WITH POSITIVOS4 AS (
SELECT 
FECHA_DE_INGRESO,
CASO,
NEXO,
FECHA_NOTIFICACION, 
CASE WHEN DEPARTAMENTONOTIFICACION ='BOGOTA DC' THEN 'BOGOTA' ELSE DEPARTAMENTONOTIFICACION END DEPARTAMENTONOTIFICACION,
CASE WHEN CIUDAD_MUNICIPIONOTIFICACION ='BOGOTA D.C.' THEN 'BOGOTA' ELSE CIUDAD_MUNICIPIONOTIFICACION END CIUDAD_MUNICIPIONOTIFICACION, 
EDAD,
UNI_MED,
SEXO,
FUENTE_O_TIPO_DE_CONTAGIO,
UBICACION, 
ESTADO,
CASE WHEN nombre_nacionalidad IS NOT NULL THEN nombre_nacionalidad
ELSE NACIONALIDAD END NACIONALIDAD,
**********************************************************************
NOMBRE1,
CASE WHEN NOMBRE2 ='NA' THEN NULL ELSE NOMBRE2 END NOMBRE2,
APELLIDO1,
APELLIDO2,
CASE WHEN (EDAD > 0 AND EDAD < 7) AND (UNI_MED IN ('1','2','3')) AND (TIPODOCUMENTO NOT IN ('RC', 'Registro civil')) AND (NACIONALIDAD='COLOMBIA') THEN 'RC'
          WHEN (EDAD > 6 AND EDAD < 18) AND UNI_MED IN ('1')  AND (TIPODOCUMENTO NOT IN ('TI', 'Tarjeta de identidad')) AND  NACIONALIDAD='COLOMBIA' THEN 'TI'
          WHEN ((EDAD > 17) AND (UNI_MED IN ('1'))  AND (TIPODOCUMENTO NOT IN ('CC','Cédula de ciudadania'))  AND (NACIONALIDAD='COLOMBIA')) THEN 'CC'
          WHEN ((EDAD > 17) AND (UNI_MED IN ('1'))  AND (TIPODOCUMENTO IN ('SI','Adulto sin identificación'))  AND (NACIONALIDAD IN ('COLOMBIA', 'VENEZUELA','',NULL))) THEN 'AS'
          WHEN ((EDAD < 18) AND (UNI_MED IN ('1'))  AND (TIPODOCUMENTO IN ('SI','Adulto sin identificación'))  AND (NACIONALIDAD IN ('COLOMBIA', 'VENEZUELA','',NULL))) THEN 'MS'

ELSE TIPODOCUMENTO END TIPODOCUMENTO,
DOCUMENTODEIDENTIFICACION_CORRECTO,
FI_PAIS,
FECHADEINICIODESINTOMAS,
FECHADECONSULTA, 
FECHARESULTADO, 
FECHA_HOSPITALIZACION, 
FECHADEALTA, 
FECHADEMUERTE,
LUGARREPORTE,
PAISADONDEVIAJO1,
PAISADONDEVIAJO2,
PAISADONDEVIAJO3, 
RECUPERADO,
FECHA_2DA_MUESTRA_2DIAS,
RESULTADO_2DA_MUESTRA,
FECHA_3ER_MUESTRA_2DIAS,
RESULTADO_3RA_MUESTRA, 
EAPB,
IPS_NOTIFICACION,
CASE WHEN DIR_RES_ IS NOT NULL THEN DIR_RES_
ELSE DIRECCION_DE_PACIENTE END DIRECCION_DE_PACIENTE,
CASE WHEN LOCALIDAD_ASIS IS NOT NULL THEN (SELECT CONCAT(LOCALIDAD_DE_RESIDENCIA,' - ',LOCALIDAD_ASIS)) 
WHEN LOCALIDAD_ASIS IS NULL THEN '21 - Localidad desconocida'
ELSE LOCALIDAD_DE_RESIDENCIA END LOCALIDAD_DE_RESIDENCIA,
SUBRED_UPGD_NOTIFICADORA,  
CASE WHEN (EDAD = 0) AND (UNI_MED='1') THEN '< 1 año'
          WHEN (EDAD = 0) AND (UNI_MED='2') THEN '< 1 año'
          WHEN (EDAD = 0) AND (UNI_MED='3') THEN '< 1 año'
          WHEN (EDAD > 0 AND EDAD < 30) AND (UNI_MED='2') THEN '< 1 año'
          WHEN (EDAD > 0 AND EDAD < 12) AND (UNI_MED='2') THEN '< 1 año'
          WHEN (EDAD > 0 AND EDAD < 5) AND (UNI_MED='1') THEN '01 a 04 años'
          WHEN (EDAD > 4 AND EDAD < 10) AND (UNI_MED='1') THEN '05 a 09 años'
          WHEN (EDAD > 9 AND EDAD < 15) AND (UNI_MED='1') THEN '10 a 14 años'
          WHEN (EDAD > 14 AND EDAD < 20) AND (UNI_MED='1') THEN '15 a 19 años'
          WHEN (EDAD > 19 AND EDAD < 25) AND (UNI_MED='1') THEN '20 a 24 años'
          WHEN (EDAD > 24 AND EDAD < 30) AND (UNI_MED='1') THEN '25 a 29 años'
          WHEN (EDAD > 29 AND EDAD < 35) AND (UNI_MED='1') THEN '30 a 34 años'
          WHEN (EDAD > 34 AND EDAD < 40) AND (UNI_MED='1') THEN '35 a 39 años'
          WHEN (EDAD > 39 AND EDAD < 45) AND (UNI_MED='1')THEN '40 a 44 años'
          WHEN (EDAD > 44 AND EDAD < 50) AND (UNI_MED='1')THEN '45 a 49 años'
          WHEN (EDAD > 49 AND EDAD < 55) AND (UNI_MED='1')THEN '50 a 54 años'
          WHEN (EDAD > 54 AND EDAD < 60) AND (UNI_MED='1')THEN '55 a 59 años'
          WHEN (EDAD > 59 AND EDAD < 65) AND (UNI_MED='1')THEN '60 a 64 años'
          WHEN (EDAD > 64 AND EDAD < 70) AND (UNI_MED='1')THEN '65 a 69 años'
          WHEN (EDAD > 69 AND EDAD < 75) AND (UNI_MED='1')THEN '70 a 74 años'
          WHEN (EDAD > 74 AND EDAD < 80) AND (UNI_MED='1')THEN '75 a 79 años'
          WHEN (EDAD > 79 AND EDAD < 110) THEN'80 a mas años'ELSE QUINQUNIOS END QUINQUNIOS,


CASE WHEN CUAL_OT_DC IS NOT NULL THEN CUAL_OT_DC
ELSE ANTECEDENTES_MEDICOS END ANTECEDENTES_MEDICOS,

TRABAJAENSALUD,P3.OCUPACION, 
PROFESIONALDELASALUD, 
LUGAR_DE_TRABAJO__TRABAJADORES_DE_LA_SALUD, 
OBSERVACIONES__TRABAJADORES_DE_LA_SALUD, 
LUGARDEREPORTE_BOGOTA, 
RADICAD01, 
ENSIVIGILA,
FECHADETOMADEMUESTRA, 
TELEFONO, 
RECUPERADO_SALUDATA,
FECHA_RECUPERADO_SALUDATA,
UBICACIONALINGRESO,
UBICACIONEVOLUCION,
INGRESOUCI,
UBICACIONMOMENTODELAMUERTE,
CASE WHEN ESTRATO_ IS NOT NULL THEN ESTRATO_
ELSE ESTRATO END ESTRATO,
CONDICIONGESTANTE, 
HABITANTEDECALLE,
ETNIA,
CEDULA_INS, 
CONCATENARNOMBRE, 
DIRECCION,
X,
Y,
LOCGEO,
NOMLOCGEO,
UPZGEO,
NOMUPZ,
MENSAJE_S,
TIPO_DIREC, 
VALIDACIONLOCA,
CODIGO_BARRIO, 
NOMBRE_BARRIO,
TIPO_DE_PRUEBA,
--CASO_INS, FECHAINS, 
--FECHA_PUBLICACION_FALLECIDOS, 

CASE WHEN tip_ss_ IS NOT NULL THEN tip_ss_
ELSE REGIMEN END REGIMEN,
SINTOMAT AS SINTOMATICO,
FECHA_NOT,
FECHA_CON,
FECHA_INI_SIN,
FECHA_HOSP,
FECHA_ING_UCI

FROM proyecto-covid-sds.caso_positivo.POSITIVOS_3 P3 
LEFT JOIN 
proyecto-covid-sds.caso_positivo.localidades_fijas
ON LOCALIDAD_DE_RESIDENCIA = NUMERO_DE_LOCALIDAD_
LEFT JOIN 
(
SELECT * EXCEPT (RN)
            FROM (
                  SELECT *, ROW_NUMBER() OVER (PARTITION BY num_ide_ ORDER BY fec_con_ DESC) AS RN
                  FROM `proyecto-covid-sds.salud_publica.sivigila` WHERE DATE_DIFF(CURRENT_DATE(),fec_con_, DAY)<12 
                  )
            WHERE RN=1 ) SV1
ON DOCUMENTODEIDENTIFICACION_CORRECTO = SV1.num_ide_
)

SELECT * FROM POSITIVOS4 WHERE LOCALIDAD_DE_RESIDENCIA != '22 - Fuera de Bogotá'

