DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_GESTION_VPCONNECT_UP_SQAD
CREATE TABLE d_mdl_vpc_disc.HM_GESTION_VPCONNECT_UP_SQAD
WITH ( format = 'Parquet', 
         parquet_compression = 'SNAPPY', 
         partitioned_by = ARRAY['p_periodo'],
         external_location= 's3://sagemaker-us-east-1-058528764918/vpc/contactabilidad/athena_2/HM_GESTION_VPCONNECT_UP_SQAD/'
       )
AS (
    SELECT periodo_val periodo_campania,
        empresa_id,
        campanha_id,
        campanha_instancia_id,
        campanha campania,
        canal_dsc canal,
        cast(CASE WHEN inicio_dt LIKE '' THEN NULL else inicio_dt end as date) inicio_campania_dt,
        cast(CASE WHEN fin_date LIKE '' THEN NULL else fin_date end as date) fin_campania_dt,
        banca_dsc banca,
        tipo_campanha_dsc tipo_campania,
        CASE WHEN campanha='Nuevo Aprobado Agil' AND PERIODO_VAL IN ('202202', '202203') THEN atributo07_val ELSE atributo02_val END tasa,
        tipo_doc_cd,
        numdoc_val num_doc,
        CASE WHEN numruc_val IS NOT NULL AND numruc_val NOT LIKE '' THEN numruc_val else numdoc_val END num_ruc,
        cod_unico_cd cod_unico,
        razon_social_dsc razon_social,
        gestion_tipo_id,
        gestion_subtipo_id,
        gestion_tipo,
        gestion_subtipo,
        gestion_comentario,
        CAST(CASE WHEN fecha_registro_dt LIKE '' THEN NULL 
                  WHEN LENGTH(fecha_registro_dt) > 10 THEN registro_dsc
                  WHEN LENGTH(fecha_registro_dt) < 10 THEN registro_dsc
              else fecha_registro_dt end as date) fecha_gestion_dt_2,
        flg_activo,
        motivonoactivo_dsc motivo_no_activo,
        campo_informativo1_dsc,
        campo_informativo2_dsc,
         CASE WHEN gestion_tipo IS NOT NULL AND gestion_tipo NOT LIKE '' THEN 1 ELSE 0 END FLG_GESTION_VPCONNECT,
         CASE WHEN gestion_tipo IS NOT NULL AND gestion_tipo NOT LIKE '' AND gestion_tipo NOT LIKE '%Sin Contacto%' THEN 1 ELSE 0 END FLG_CE_VPCONNECT,
         CASE WHEN gestion_tipo LIKE '%Acepta%' AND gestion_tipo NOT LIKE '%No%' AND gestion_tipo NOT LIKE '%Visita%' THEN 1 ELSE 0 END FLG_ACEPTA_VPCONNECT,
         CASE WHEN gestion_tipo LIKE '%Acepta%' AND gestion_tipo LIKE '%No%' THEN 1 ELSE 0 END FLG_NO_ACEPTA_VPCONNECT,
         CASE WHEN gestion_tipo LIKE '%Lo Pensa%' THEN 1 ELSE 0 END FLG_LO_PENSARA_VPCONNECT    ,
         CASE WHEN gestion_tipo = 'No Califica' THEN 1 ELSE 0 END FLG_NO_CALIFICA_VPCONNECT,
         CASE WHEN gestion_tipo LIKE '%Desisti%' THEN 1 ELSE 0 END FLG_DEISTE_VPCONNECT,
         periodo_val p_periodo
         FROM e_perm_aws.t_vpc_neg_empresas_vig
    )

   
    SELECT * FROM d_mdl_vpc_disc.HM_GESTION_VPCONNECT_UP_SQAD 
    WHERE periodo_campania = (SELECT MAX(periodo_campania) FROM d_mdl_vpc_disc.HM_GESTION_VPCONNECT_UP_SQAD)
    LIMIT 100
  
  
   SELECT periodo_campania, campania, count(1) 
   FROM d_mdl_vpc_disc.HM_GESTION_VPCONNECT_UP_SQAD 
   GROUP BY periodo_campania, campania
   ORDER BY periodo_campania DESC, campania