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 F.GESTION, CASE WHEN F.NOM_LISTA LIKE &#039;%_PRE%&#039; THEN &#039;NUEVOS_PRE&#039;
	                       WHEN F.NOM_LISTA LIKE &#039;%_NA%&#039; THEN &#039;NUEVOS_AP&#039;
						   WHEN F.NOM_LISTA LIKE &#039;%ECO%&#039; THEN &#039;ECOSISTEMAS&#039;
						   ELSE &#039;X&#039; END CAMPANIA,
		  F.FEC_LLAMADA, F.NUM_DOCUMENTO,
		  H.ORDER_DIA_HABIL,
		  MAX(CASE WHEN F.CATEGORIA IN (&#039;CE&#039;) THEN 1 else 0 end) FLG_CE,
		  MAX(CASE WHEN F.CATEGORIA IN (&#039;ET&#039;, &#039;ERRT&#039;) THEN 1 else 0 end) FLG_ET,
		  MAX(CASE WHEN F.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 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 &#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_VPC, count(1) conteo
			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 FEC_LLAMADA <> &#039;2022-07-01&#039;                                                                             --- FALSO INICIO DE GESTION
			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, FEC_LLAMADA, 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 
	       
		) ZZ
	) H
	ON F.GESTION = H.GESTIONX AND 
	CASE WHEN F.NOM_LISTA LIKE &#039;%_PRE%&#039; THEN &#039;NUEVOS_PRE&#039;
       WHEN F.NOM_LISTA LIKE &#039;%_NA%&#039; THEN &#039;NUEVOS_AP&#039;
	   WHEN F.NOM_LISTA LIKE &#039;%ECO%&#039; THEN &#039;ECOSISTEMAS&#039;
	   ELSE &#039;X&#039; END = H.CAMPANIA_VPC 
	AND F.FEC_LLAMADA = H.FEC_LLAMADA
	WHERE 1 = 1
	AND (F.ID_CAMPANIA like &#039;%BPE%&#039; OR  F.ID_TRATAMIENTO like &#039;%BPE%&#039;  OR F.TIP_CLIENTE like &#039;%BPE%&#039;)                   --- BPE
	AND (F.NOM_LISTA like &#039;%_PRE%&#039; OR F.NOM_LISTA like &#039;%_NA%&#039;  OR F.NOM_LISTA like &#039;%ECO%&#039;)                            --- NUEVOS
	AND F.FEC_LLAMADA <> &#039;2022-07-01&#039;                                                                                   --- FALSO INICIO DE GESTION
	AND (F.CATEGORIA IN (&#039;CE&#039;, &#039;NC&#039;, &#039;CNE&#039;, &#039;ET&#039;, &#039;ERRT&#039;, &#039;NT&#039;) OR ABS(F.HOR_FIN_LLAMADA - F.HOR_INICIO_LLAMADA) > 0)   --- GESTIONADO
	AND F.NOM_LISTA not like &#039;%_RT%&#039;                                                                                    --- EXCLUSION RECURRENTE
	AND F.GESTION >= &#039;202204&#039; 
	GROUP BY F.GESTION, CASE WHEN F.NOM_LISTA LIKE &#039;%_PRE%&#039; THEN &#039;NUEVOS_PRE&#039;
	                       WHEN F.NOM_LISTA LIKE &#039;%_NA%&#039; THEN &#039;NUEVOS_AP&#039;
						   WHEN F.NOM_LISTA LIKE &#039;%ECO%&#039; THEN &#039;ECOSISTEMAS&#039;
						   ELSE &#039;X&#039; END,
			 F.FEC_LLAMADA, F.NUM_DOCUMENTO, H.ORDER_DIA_HABIL
	) AA
	GROUP BY GESTION, CAMPANIA, NUM_DOCUMENTO