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