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