Psst.. new poll here.
Psst.. new forums here.
Microsoft is blocking us again (TY IP Reputation!) so dont bother with any of their useless mail servers here and just use oauth login instead. Thank the nice Russians for causing that. :)
Paste
Pasted as SQL by registered user vvillacorta ( 3 years ago )
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 ''
)
Revise this Paste