SELECT B.PERIODO_VAL
,A.EMPRESA_ID,
B.CAMPANHA_ID
,B.CAMPANHA_INSTANCIA_ID
,B.NOMBRE_DSC CAMPANHA
,A.CANAL_DSC
,B.INICIO_DT
,CAST(B.INICIO_DT AS DATE) INICIO_DATE
,to_char(CAST(B.INICIO_DT AS DATE), 'YYYY-MM-DD')INICIO_DATE_2
,B.FIN_DT
,CAST(B.FIN_DT AS DATE) FIN_DATE
,to_char(CAST(B.FIN_DT AS DATE), 'YYYY-MM-DD')FIN_DATE_2
,B.BANCA_DSC
,J.TIPO_CAMPANHA_DSC
,CASE WHEN B.NOMBRE_DSC = 'Nuevo Aprobado Agil' AND B.PERIODO_VAL IN ('202202', '202203') THEN A.ATRIBUTO07_VAL ELSE A.ATRIBUTO02_VAL END TASA
,C.TIPO_DOC_CD
, C.NUM_DOC_CD
, C.NUM_RUC_CD
, C.COD_UNICO_CD
, C.RAZON_SOCIAL_DSC
,D.GESTION_TIPO_VAL GESTION_TIPO_ID
,D.GESTION_SUBTIPO_VAL GESTION_SUBTIPO_ID
,E.NOMBRE_DSC GESTION_TIPO
,F.NOMBRE_DSC GESTION_SUBTIPO
,D.COMENTARIO_DSC GESTION_COMENTARIO
,D.FECHA_REGISTRO_DT
,CAST(D.FECHA_REGISTRO_DT AS DATE) FECHA_REGISTRO_DATE
,to_char(CAST(D.FECHA_REGISTRO_DT AS DATE), 'YYYY-MM-DD')FECHA_REGISTRO_DATE_2
, A.ACTIVO_FLG FLG_ACTIVO
, A.MOTIVONOACTIVO_DSC
, A.CAMPO_INFORMATIVO1_DSC
, A.CAMPO_INFORMATIVO2_DSC
,CASE WHEN E.NOMBRE_DSC IS NULL THEN 0 ELSE 1 END FLG_GESTION_VPCONNECT
,CASE WHEN E.NOMBRE_DSC IS NOT NULL AND E.NOMBRE_DSC NOT LIKE '%Sin Contacto%' THEN 1 ELSE 0 END FLG_CE_VPCONNECT
,CASE WHEN E.NOMBRE_DSC = 'Acepta Campaña' THEN 1 ELSE 0 END FLG_ACEPTA_VPCONNECT
,CASE WHEN E.NOMBRE_DSC = 'No Acepta Campaña' THEN 1 ELSE 0 END FLG_NO_ACEPTA_VPCONNECT
,CASE WHEN E.NOMBRE_DSC LIKE '%Lo Pensa%' THEN 1 ELSE 0 END FLG_LO_PENSARA_VPCONNECT
,CASE WHEN E.NOMBRE_DSC = 'No Califica' THEN 1 ELSE 0 END FLG_NO_CALIFICA_VPCONNECT
,CASE WHEN E.NOMBRE_DSC LIKE '%Desisti%' THEN 1 ELSE 0 END FLG_DEISTE_VPCONNECT
FROM E_DW_VIEWS.V_VPC_NEG_EMPRESAS_CAMP_VIG A
JOIN E_DW_VIEWS.V_VPC_NEG_CAMPANHAS_INST_VIG B ON B.CAMPANHA_INSTANCIA_ID=A.CAMPANHA_INSTANCIA_ID -- AND B.PERIODO_ACTUALIZACION_VAL=A.PERIODO_ACTUALIZACION_VAL AND
JOIN E_DW_VIEWS.V_VPC_NEG_EMPRESAS_VIG C ON C.EMPRESA_ID=A.EMPRESA_ID -- AND C.PERIODO_ACTUALIZACION_VAL=A.PERIODO_ACTUALIZACION_VAL
LEFT JOIN E_DW_VIEWS.V_VPC_NEG_CAMPANHA_GEST_VIG D ON D.EMPRESA_ID=A.EMPRESA_ID AND D.CAMPANHA_INSTANCIA_ID=A.CAMPANHA_INSTANCIA_ID -- D.PERIODO_ACTUALIZACION_VAL=A.PERIODO_ACTUALIZACION_VAL AND
LEFT JOIN E_DW_VIEWS.V_VPC_NEG_GEST_TIPO_VIG E ON E.GESTION_TIPO_ID=D.GESTION_TIPO_VAL -- E.PERIODO_ACTUALIZACION_VAL=A.PERIODO_ACTUALIZACION_VAL
LEFT JOIN E_DW_VIEWS.V_VPC_NEG_GEST_SUBTIPO_VIG F ON F.GESTION_SUBTIPO_ID=D.GESTION_SUBTIPO_VAL -- F.PERIODO_ACTUALIZACION_VAL=A.PERIODO_ACTUALIZACION_VAL
LEFT JOIN E_DW_VIEWS.V_VPC_NEG_CAMPANHAS_VIG J ON J.CAMPANHA_ID=B.CAMPANHA_ID -- J.PERIODO_ACTUALIZACION_VAL=A.PERIODO_ACTUALIZACION_VAL
WHERE B.PERIODO_VAL = '202304'
DROP TABLE IF EXISTS d_mdl_vpc_disc.MESTRA_GESTION_VPCONNECT
CREATE TABLE d_mdl_vpc_disc.MESTRA_GESTION_VPCONNECT
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_CAMPANIA_ADQ/'
)
AS (
SELECT AA.*,
CASE WHEN campania LIKE '%Nuevo Ap%' THEN 'NUEVOS AP'
WHEN campania LIKE '%Nuevo Pre%' THEN 'NUEVOS PRE'
WHEN campania LIKE '%Eco%' THEN 'ECOSISTEMAS'
WHEN campania LIKE '%Izi%' THEN 'IZIPAY'
WHEN campania LIKE '%Giro%' THEN 'GIROS'
WHEN campania LIKE '%Recurrente%' THEN 'RECURRENTE'
WHEN campania LIKE '%Débito%' THEN 'DEBITO AUTOMATICO'
WHEN campania LIKE '%Digi%' THEN 'DIGITAL'
WHEN campania LIKE '%Trading%' THEN 'TRADING'
WHEN campania LIKE '%BPI%' THEN 'BPI'
ELSE 'X' END campanha_formateada,
CASE WHEN campania LIKE '%Nuevo Ap%' THEN 'NUEVOS AP'
WHEN campania LIKE '%Nuevo Pre%' THEN 'NUEVOS PRE'
WHEN campania LIKE '%Eco%' THEN 'NUEVOS AP'
WHEN campania LIKE '%Izi%' THEN 'IZIPAY'
WHEN campania LIKE '%Giro%' THEN 'GIROS'
WHEN campania LIKE '%Recurrente%' THEN 'RECURRENTE'
WHEN campania LIKE '%Débito%' THEN 'DEBITO AUTOMATICO'
WHEN campania LIKE '%Digi%' THEN 'DIGITAL'
WHEN campania LIKE '%Trading%' THEN 'TRADING'
WHEN campania LIKE '%BPI%' THEN 'BPI'
ELSE 'X' END campania_formateada,
ROW_NUMBER() OVER(PARTITION BY periodo_campania,
CASE WHEN campania LIKE '%Nuevo Ap%' THEN 'NUEVOS AP'
WHEN campania LIKE '%Nuevo Pre%' THEN 'NUEVOS PRE'
WHEN campania LIKE '%Eco%' THEN 'ECOSISTEMAS'
ELSE 'X' END, num_ruc, fecha_gestion_dt_2
ORDER BY CASE WHEN gestion_tipo = 'Acepta Campaña' then 1
WHEN gestion_tipo = 'Desembolsado' then 1 ------ esta parte en un data enntry por futuras variaciones
WHEN gestion_tipo = 'Acepta' then 1
WHEN gestion_tipo = 'Girado' then 1
WHEN gestion_tipo = 'Enviando a GTP' then 1
WHEN gestion_tipo = 'Lo Pensará' then 2
WHEN gestion_tipo = 'No Acepta Campaña' then 4
WHEN gestion_tipo = 'No Califica' then 5
WHEN gestion_tipo = 'No contesta' then 6
WHEN gestion_tipo LIKE '%Error Tel%' then 7
WHEN gestion_tipo = 'Sin Contacto' then 8
WHEN gestion_tipo = 'Desistió' then 9
WHEN gestion_tipo like '%Acepta Visita%' then 10
WHEN gestion_tipo LIKE '%Bloqueado%' then 11
ELSE 12 END ASC, fecha_gestion_dt DESC
) ORDEN_DIARIO,
CASE WHEN CAST(CONCAT(CAST(EXTRACT(YEAR FROM fecha_gestion_dt_2) AS VARCHAR), LPAD(CAST(EXTRACT(MONTH FROM fecha_gestion_dt_2) AS VARCHAR), 2, '0')) AS INT) < CAST(periodo_campania AS INT) then 1 else 0 end flg_tuberia,
CASE WHEN CAST(CONCAT(CAST(EXTRACT(YEAR FROM fecha_gestion_dt_2) AS VARCHAR), LPAD(CAST(EXTRACT(MONTH FROM fecha_gestion_dt_2) AS VARCHAR), 2, '0')) AS INT) < CAST(periodo_campania AS INT) then BB.min_fecha_gestion_dt_2 else fecha_gestion_dt_2 end fecha_gestion_ajustado,
periodo_campania p_periodo
FROM d_mdl_vpc_disc.HM_GESTION_VPCONNECT AA
LEFT JOIN (
SELECT periodo_campania periodo_campania_2, min(fecha_gestion_dt_2) min_fecha_gestion_dt_2
FROM d_mdl_vpc_disc.HM_GESTION_VPCONNECT
WHERE CAST(CONCAT(CAST(EXTRACT(YEAR FROM fecha_gestion_dt_2) AS VARCHAR), LPAD(CAST(EXTRACT(MONTH FROM fecha_gestion_dt_2) AS VARCHAR), 2, '0')) AS INT) = CAST(periodo_campania AS INT)
GROUP BY periodo_campania
) BB
ON AA.periodo_campania = bb.periodo_campania_2
WHERE 1 = 1
AND gestion_tipo IS NOT NULL
AND gestion_tipo NOT LIKE ''
)Add a code snippet to your website: www.paste.org