Psst.. new poll here.
[email protected] webmail now available. Want one? Go here.
Cannot use outlook/hotmail/live here to register as they blocking our mail servers. #microsoftdeez
Obey the Epel!
Paste
Pasted as SQL by registered user vvillacorta ( 2 years ago )
SELECT GESTION, CAMPANIA, NUM_DOCUMENTO,
1 FLG_G,
MAX(FLG_CE) FLG_CE,
MAX(FLG_ET) FLG_ET,
MAX(FLG_ACEPTA_CAMPANA) FLG_AC,
MAX(CASE WHEN ORDER_DIA_HABIL <= 3 THEN 1 ELSE 0 END) FLG_G_3DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 5 THEN 1 ELSE 0 END) FLG_G_5DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 7 THEN 1 ELSE 0 END) FLG_G_7DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 10 THEN 1 ELSE 0 END) FLG_G_10DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 15 THEN 1 ELSE 0 END) FLG_G_15DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 20 THEN 1 ELSE 0 END) FLG_G_20DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 3 THEN FLG_CE ELSE 0 END) FLG_CE_3DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 5 THEN FLG_CE ELSE 0 END) FLG_CE_5DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 7 THEN FLG_CE ELSE 0 END) FLG_CE_7DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 10 THEN FLG_CE ELSE 0 END) FLG_CE_10DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 15 THEN FLG_CE ELSE 0 END) FLG_CE_15DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 20 THEN FLG_CE ELSE 0 END) FLG_CE_20DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 3 THEN FLG_ET ELSE 0 END) FLG_ET_3DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 5 THEN FLG_ET ELSE 0 END) FLG_ET_5DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 7 THEN FLG_ET ELSE 0 END) FLG_ET_7DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 10 THEN FLG_ET ELSE 0 END) FLG_ET_10DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 15 THEN FLG_ET ELSE 0 END) FLG_ET_15DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 20 THEN FLG_ET ELSE 0 END) FLG_ET_20DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 3 THEN FLG_ACEPTA_CAMPANA ELSE 0 END) FLG_AC_3DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 5 THEN FLG_ACEPTA_CAMPANA ELSE 0 END) FLG_AC_5DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 7 THEN FLG_ACEPTA_CAMPANA ELSE 0 END) FLG_AC_7DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 10 THEN FLG_ACEPTA_CAMPANA ELSE 0 END) FLG_AC_10DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 15 THEN FLG_ACEPTA_CAMPANA ELSE 0 END) FLG_AC_15DH,
MAX(CASE WHEN ORDER_DIA_HABIL <= 20 THEN FLG_ACEPTA_CAMPANA ELSE 0 END) FLG_AC_20DH
FROM(
SELECT F.GESTION, CASE WHEN F.NOM_LISTA LIKE '%_PRE%' THEN 'NUEVOS_PRE'
WHEN F.NOM_LISTA LIKE '%_NA%' THEN 'NUEVOS_AP'
WHEN F.NOM_LISTA LIKE '%ECO%' THEN 'ECOSISTEMAS'
ELSE 'X' END CAMPANIA,
F.FEC_LLAMADA, F.NUM_DOCUMENTO,
H.ORDER_DIA_HABIL,
MAX(CASE WHEN F.CATEGORIA IN ('CE') THEN 1 else 0 end) FLG_CE,
MAX(CASE WHEN F.CATEGORIA IN ('ET', 'ERRT') THEN 1 else 0 end) FLG_ET,
MAX(CASE WHEN F.RESULTADO_LLAMADA LIKE '%ACEPTA CAMPANA%' AND RESULTADO_LLAMADA NOT LIKE '%NO%' THEN 1 ELSE 0 END) FLG_ACEPTA_CAMPANA
FROM E_DW_VIEWS.V_FEEDBACK_TELEVENTAS F
LEFT JOIN(
SELECT RANK() OVER (PARTITION BY CONCAT(GESTIONX, CAMPANIA_VPC) ORDER BY FEC_LLAMADA ASC) ORDER_DIA_HABIL,
GESTIONX, CAMPANIA_VPC, FEC_LLAMADA, conteo
FROM(
SELECT GESTION GESTIONX, FEC_LLAMADA, CASE WHEN NOM_LISTA LIKE '%_PRE%' THEN 'NUEVOS_PRE'
WHEN NOM_LISTA LIKE '%_NA%' THEN 'NUEVOS_AP'
WHEN NOM_LISTA LIKE '%ECO%' THEN 'ECOSISTEMAS'
ELSE 'X' END CAMPANIA_VPC, count(1) conteo
FROM E_DW_VIEWS.V_FEEDBACK_TELEVENTAS
WHERE 1 = 1
AND (ID_CAMPANIA like '%BPE%' OR ID_TRATAMIENTO like '%BPE%' OR TIP_CLIENTE like '%BPE%') --- BPE
AND (NOM_LISTA like '%_PRE%' OR NOM_LISTA like '%_NA%' OR NOM_LISTA like '%ECO%') --- NUEVOS
AND FEC_LLAMADA <> '2022-07-01' --- FALSO INICIO DE GESTION
AND (CATEGORIA IN ('CE', 'NC', 'CNE', 'ET', 'ERRT', 'NT') OR ABS(HOR_FIN_LLAMADA - HOR_INICIO_LLAMADA) > 0) --- GESTIONADO
AND NOM_LISTA not like '%_RT%' --- EXCLUSION RECURRENTE
AND GESTION >= '202204'
GROUP BY GESTION, FEC_LLAMADA, CASE WHEN NOM_LISTA LIKE '%_PRE%' THEN 'NUEVOS_PRE'
WHEN NOM_LISTA LIKE '%_NA%' THEN 'NUEVOS_AP'
WHEN NOM_LISTA LIKE '%ECO%' THEN 'ECOSISTEMAS'
ELSE 'X' END
) ZZ
) H
ON F.GESTION = H.GESTIONX AND
CASE WHEN F.NOM_LISTA LIKE '%_PRE%' THEN 'NUEVOS_PRE'
WHEN F.NOM_LISTA LIKE '%_NA%' THEN 'NUEVOS_AP'
WHEN F.NOM_LISTA LIKE '%ECO%' THEN 'ECOSISTEMAS'
ELSE 'X' END = H.CAMPANIA_VPC
AND F.FEC_LLAMADA = H.FEC_LLAMADA
WHERE 1 = 1
AND (F.ID_CAMPANIA like '%BPE%' OR F.ID_TRATAMIENTO like '%BPE%' OR F.TIP_CLIENTE like '%BPE%') --- BPE
AND (F.NOM_LISTA like '%_PRE%' OR F.NOM_LISTA like '%_NA%' OR F.NOM_LISTA like '%ECO%') --- NUEVOS
AND F.FEC_LLAMADA <> '2022-07-01' --- FALSO INICIO DE GESTION
AND (F.CATEGORIA IN ('CE', 'NC', 'CNE', 'ET', 'ERRT', 'NT') OR ABS(F.HOR_FIN_LLAMADA - F.HOR_INICIO_LLAMADA) > 0) --- GESTIONADO
AND F.NOM_LISTA not like '%_RT%' --- EXCLUSION RECURRENTE
AND F.GESTION >= '202204'
GROUP BY F.GESTION, CASE WHEN F.NOM_LISTA LIKE '%_PRE%' THEN 'NUEVOS_PRE'
WHEN F.NOM_LISTA LIKE '%_NA%' THEN 'NUEVOS_AP'
WHEN F.NOM_LISTA LIKE '%ECO%' THEN 'ECOSISTEMAS'
ELSE 'X' END,
F.FEC_LLAMADA, F.NUM_DOCUMENTO, H.ORDER_DIA_HABIL
) AA
GROUP BY GESTION, CAMPANIA, NUM_DOCUMENTO
Revise this Paste