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 ( 4 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