Paste code

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 &#039;%_PRE%&#039; THEN &#039;NUEVOS_PRE&#039;
	                       WHEN NOM_LISTA LIKE &#039;%_NA%&#039; THEN &#039;NUEVOS_AP&#039;
						   WHEN NOM_LISTA LIKE &#039;%ECO%&#039; THEN &#039;ECOSISTEMAS&#039;
						   ELSE &#039;X&#039; END CAMPANIA,
		  FEC_LLAMADA, NUM_DOCUMENTO,
		  RANK() OVER (PARTITION BY CONCAT(GESTION, CASE WHEN NOM_LISTA LIKE &#039;%_PRE%&#039; THEN &#039;NUEVOS_PRE&#039;
	                       WHEN NOM_LISTA LIKE &#039;%_NA%&#039; THEN &#039;NUEVOS_AP&#039;
						   WHEN NOM_LISTA LIKE &#039;%ECO%&#039; THEN &#039;ECOSISTEMAS&#039;
						   ELSE &#039;X&#039; END) ORDER BY FEC_LLAMADA ASC) ORDER_DIA_HABIL,
		  MAX(CASE WHEN CATEGORIA IN (&#039;CE&#039;) THEN 1 else 0 end) FLG_CE,
		  MAX(CASE WHEN CATEGORIA IN (&#039;ET&#039;, &#039;ERRT&#039;) THEN 1 else 0 end) FLG_ET,
		  MAX(CASE WHEN RESULTADO_LLAMADA LIKE &#039;%ACEPTA CAMPANA%&#039; AND RESULTADO_LLAMADA NOT LIKE &#039;%NO%&#039; THEN 1 ELSE 0 END) FLG_ACEPTA_CAMPANA
	FROM E_DW_VIEWS.V_FEEDBACK_TELEVENTAS
	WHERE 1 = 1
	AND (ID_CAMPANIA like &#039;%BPE%&#039; OR  ID_TRATAMIENTO like &#039;%BPE%&#039;  OR TIP_CLIENTE like &#039;%BPE%&#039;)                 --- BPE
	AND (NOM_LISTA like &#039;%_PRE%&#039; OR  NOM_LISTA like &#039;%_NA%&#039;  OR NOM_LISTA like &#039;%ECO%&#039;)                         --- NUEVOS
	AND (CATEGORIA IN (&#039;CE&#039;, &#039;NC&#039;, &#039;CNE&#039;, &#039;ET&#039;, &#039;ERRT&#039;, &#039;NT&#039;) OR ABS(HOR_FIN_LLAMADA - HOR_INICIO_LLAMADA) > 0) --- GESTIONADO
	AND NOM_LISTA not like &#039;%_RT%&#039;                                                                              --- EXCLUSION RECURRENTE
	AND GESTION >= &#039;202204&#039; 
	GROUP BY GESTION, CASE WHEN NOM_LISTA LIKE &#039;%_PRE%&#039; THEN &#039;NUEVOS_PRE&#039;
	                       WHEN NOM_LISTA LIKE &#039;%_NA%&#039; THEN &#039;NUEVOS_AP&#039;
						   WHEN NOM_LISTA LIKE &#039;%ECO%&#039; THEN &#039;ECOSISTEMAS&#039;
						   ELSE &#039;X&#039; END,
			 FEC_LLAMADA, NUM_DOCUMENTO
	) AA
	GROUP BY GESTION, CAMPANIA, NUM_DOCUMENTO