Psst.. new poll here.
[email protected] web/email 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 GESTION, 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,
FEC_LLAMADA, NUM_DOCUMENTO,
RANK() OVER (PARTITION BY CONCAT(GESTION, 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) ORDER BY FEC_LLAMADA ASC) ORDER_DIA_HABIL,
MAX(CASE WHEN CATEGORIA IN ('CE') THEN 1 else 0 end) FLG_CE,
MAX(CASE WHEN CATEGORIA IN ('ET', 'ERRT') THEN 1 else 0 end) FLG_ET,
MAX(CASE WHEN 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
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 (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, 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,
FEC_LLAMADA, NUM_DOCUMENTO
) AA
GROUP BY GESTION, CAMPANIA, NUM_DOCUMENTO
Revise this Paste