SELECT A.FECPROCESO, TRIM(A.NUMRUC) NUMRUC, 
		COUNT(1) CNT_RRLL,
		AVG(CASE WHEN B.FEC_NACIMIENTO IS NULL THEN NULL
		        ELSE MONTHS_BETWEEN(CAST(A.FECPROCESO AS DATE FORMAT 'YYYYMM'),CAST(B.FEC_NACIMIENTO AS DATE FORMAT 'YYYYMM')) / 12
				END) promedio_edad_rrll,
		MAX(CASE WHEN B.FEC_NACIMIENTO IS NULL THEN NULL
		        ELSE MONTHS_BETWEEN(CAST(A.FECPROCESO AS DATE FORMAT 'YYYYMM'),CAST(B.FEC_NACIMIENTO AS DATE FORMAT 'YYYYMM')) / 12
				END) max_edad_rrll,
	    SUM(CASE WHEN B.EST_CIVIL = 'D' THEN 1 ELSE 0 END) CNT_CIVIL_DIVORCIADO,
		SUM(CASE WHEN B.EST_CIVIL = 'S' THEN 1 ELSE 0 END) CNT_CIVIL_SOLTERO,
		SUM(CASE WHEN B.EST_CIVIL ='C' THEN 1 ELSE 0 END) CNT_CIVIL_CASADO,	
		SUM(CASE WHEN B.EST_CIVIL = 'V' THEN 1 ELSE 0 END) CNT_CIVIL_VIUDO,
		SUM(CASE WHEN B.GENERO = 'F' THEN 1 ELSE 0 END) CNT_GEN_F,
		SUM(CASE WHEN B.GENERO = 'M' THEN 1 ELSE 0 END) CNT_GEN_M,
		SUM(CASE WHEN B.DON_ORGANOS = 'SI' THEN 1 ELSE 0 END) CNT_DON_ORGANOS,
		SUM(CASE WHEN B.DEP_UBI_DOMICILIO IN ( 'LIMA'  , 'CALLAO'  ) THEN 1 ELSE 0 END) cnt_residencia_lima_callao,
		SUM(CASE WHEN B.DEP_UBI_DOMICILIO IN ( 
		    'AMAZONAS', 'ANCASH' ,'APURIMAC' , 'AREQUIPA', 'AYACUCHO' , 'CAJAMARCA', 'CUSCO',  'HUANCAVELICA' , 'HUANUCO' , 'ICA','JUNIN', 
	        'LA LIBERTAD' , 'LAMBAYEQUE', 'LORETO' , 'MADRE DE DIOS', 'MOQUEGUA', 'PASCO' , 'PIURA', 'PUNO', 'SAN MARTIN', 'TACNA', 'TUMBES' , 'UCAYALI'
		)  THEN 1 ELSE 0 END) cnt_residencia_provincia,   
	    SUM(CASE WHEN B.DEP_UBI_DOMICILIO NOT IN (
		    'LIMA'  , 'CALLAO', 'AMAZONAS', 'ANCASH' ,'APURIMAC' , 'AREQUIPA', 'AYACUCHO' , 'CAJAMARCA', 'CUSCO',  'HUANCAVELICA' , 'HUANUCO' , 'ICA','JUNIN', 
			'LA LIBERTAD' , 'LAMBAYEQUE', 'LORETO' , 'MADRE DE DIOS', 'MOQUEGUA', 'PASCO' , 'PIURA', 'PUNO', 'SAN MARTIN', 'TACNA', 'TUMBES' , 'UCAYALI'
		)  THEN 1 ELSE 0 END) cnt_residencia_extranjera,
	  SUM(CASE WHEN B.EST_CIVIL = 'D' THEN 1 ELSE 0 END) / CAST(COUNT(1)  AS FLOAT)  PERCENT_CIVIL_DIVORCIADO,
	  SUM(CASE WHEN B.EST_CIVIL = 'S' THEN 1 ELSE 0 END) / CAST(COUNT(1)  AS FLOAT)  PERCENT_CIVIL_SOLTERO,
	  SUM(CASE WHEN B.EST_CIVIL ='C' THEN 1 ELSE 0 END) / CAST(COUNT(1)  AS FLOAT)  PERCENT_CIVIL_CASADO, 
	  SUM(CASE WHEN B.EST_CIVIL = 'V' THEN 1 ELSE 0 END) / CAST(COUNT(1)  AS FLOAT)  PERCENT_CIVIL_VIUDO,
	  SUM(CASE WHEN B.GENERO = 'F' THEN 1 ELSE 0 END) / CAST(COUNT(1)  AS FLOAT)  PERCENT_GEN_F,
	  SUM(CASE WHEN B.GENERO = 'M' THEN 1 ELSE 0 END) / CAST(COUNT(1)  AS FLOAT)  PERCENT_GEN_M,
	  SUM(CASE WHEN B.DON_ORGANOS = 'SI' THEN 1 ELSE 0 END) / CAST(COUNT(1)  AS FLOAT)  PERCENT_DON_ORGANOS,
	  SUM(CASE WHEN B.DEP_UBI_DOMICILIO IN ( 'LIMA'  , 'CALLAO'  ) THEN 1 ELSE 0 END) / CAST(COUNT(1)  AS FLOAT)  PERCENT_residencia_lima_callao,
	  SUM(CASE WHEN B.DEP_UBI_DOMICILIO IN ( 
	      'AMAZONAS', 'ANCASH' ,'APURIMAC' , 'AREQUIPA', 'AYACUCHO' , 'CAJAMARCA', 'CUSCO',  'HUANCAVELICA' , 'HUANUCO' , 'ICA','JUNIN', 
	        'LA LIBERTAD' , 'LAMBAYEQUE', 'LORETO' , 'MADRE DE DIOS', 'MOQUEGUA', 'PASCO' , 'PIURA', 'PUNO', 'SAN MARTIN', 'TACNA', 'TUMBES' , 'UCAYALI'
	  )  THEN 1 ELSE 0 END) / CAST(COUNT(1)  AS FLOAT)  PERCENT_residencia_provincia,   
	    SUM(CASE WHEN B.DEP_UBI_DOMICILIO NOT IN (
	      'LIMA'  , 'CALLAO', 'AMAZONAS', 'ANCASH' ,'APURIMAC' , 'AREQUIPA', 'AYACUCHO' , 'CAJAMARCA', 'CUSCO',  'HUANCAVELICA' , 'HUANUCO' , 'ICA','JUNIN', 
	    'LA LIBERTAD' , 'LAMBAYEQUE', 'LORETO' , 'MADRE DE DIOS', 'MOQUEGUA', 'PASCO' , 'PIURA', 'PUNO', 'SAN MARTIN', 'TACNA', 'TUMBES' , 'UCAYALI'
	  )  THEN 1 ELSE 0 END) / CAST(COUNT(1)  AS FLOAT)  PERCENT_residencia_extranjera,
	   SUM(COALESCE(V.INGRESO_BRUTO,0)) INGRESO_BRUTO_TOTAL_RRLL,
		AVG(V.INGRESO_BRUTO) INGRESO_BRUTO_PROMEDIO_RRLL,
		MAX(COALESCE(V.INGRESO_BRUTO, 0)) INGRESO_BRUTO_MAXIMO_RRLL,
		SUM(COALESCE(V.TENENCIA, 0)) TENENCIA_TOTAL_RRLL,
		AVG(V.TENENCIA) TENENCIA_PROMEDIO_RRLL,
		MAX(COALESCE(V.TENENCIA, 0)) TENENCIA_MAXIMA_RRLL,	
		SUM(COALESCE(V.PRODUCTOS, 0)) PRODUCTOS_TOTALES_RRLL,
		AVG(V.PRODUCTOS) PRODUCTO_PROMEDIO_RRLL,
		MAX(COALESCE(V.PRODUCTOS, 0)) PRODUCTO_MAXIMO_RRLL,	
		SUM(COALESCE(V.MESES_BLOQUEO_TC, 0)) MESES_BLOQUEO_TC_TOTALES_RRLL,
		AVG(V.MESES_BLOQUEO_TC) MESES_BLOQUEO_TC_PROMEDIO_RRLL,
		MAX(COALESCE(V.MESES_BLOQUEO_TC, 0)) MESES_BLOQUEO_TC_MAXIMO_RRLL,

		SUM(CASE WHEN V.FLG_GESTIONABLE = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_GESTIONABLES_RETAIL,
		SUM(CASE WHEN V.FLG_FOCO = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_FOCO_RETAIL
		SUM(CASE WHEN V.FLG_COLABORADOR = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_COLABORADOR_RETAIL,
		SUM(CASE WHEN V.FLG_PRINCIPALIDAD = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_PRINCIPALIDAD_RETAIL,
	    SUM(CASE WHEN V.FLG_CLIENTE_TXS_REC = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_TXS_REC_RETAIL,
		SUM(CASE WHEN V.FLG_CONSUMO_TC = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_CONSUMO_TC_RETAIL,
		SUM(CASE WHEN V.FLG_PA = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_PA_RETAIL,
		SUM(CASE WHEN V.FLG_CLIENTE_ALCANCIA = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_ALCANCIA_RETAIL,
		SUM(CASE WHEN V.FLG_CLIENTE_TXS = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_TXS_RETAIL,
		SUM(CASE WHEN V.FLG_CLIENTE_TC_ADICIONAL = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_TC_ADICIONAL_RETAIL,
		SUM(CASE WHEN V.FLG_CASTIGADO_SF = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_CASTIGO_SF_RETAIL,
		SUM(CASE WHEN V.FLG_PARQUE_TC = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_PARQUE_TC_RETAIL,
		SUM(CASE WHEN V.FLG_CLIENTE_SEGURO = 'S' THEN 1 ELSE 0 END) CANT_CLIENTES_SEGURO_RETAIL,

		AVG(COALESCE(V.SALDO_PROM_TOT_PASIVO, 0)) SALDO_PROM_TOT_PASIVO_RRLL,
	    AVG(COALESCE(V.SALDO_PROM_TOT_ACTIVO, 0)) SALDO_PROM_TOT_ACTIVO_RRLL,
		AVG(COALESCE(V.SALDO_PROM_TOT_TXS, 0)) SALDO_PROM_TOT_TXS_RRLL,
		AVG(COALESCE(V.SALDO_PROM_VIG_TC, 0)) SALDO_PROM_VIG_TC_RRLL,
		MAX(COALESCE(V.SALDO_PROM_TOT_PASIVO, 0)) SALDO_MAXIMO_PROM_PASIVO_RRLL,
	    MAX(COALESCE(V.SALDO_PROM_TOT_ACTIVO, 0)) SALDO_MAXIMO_PROM_ACTIVO_RRLL,
		MAX(COALESCE(V.SALDO_PROM_TOT_TXS, 0)) SALDO_MAXIMO_PROM_TXS_RRLL,
		MAX(COALESCE(V.SALDO_PROM_VIG_TC, 0)) SALDO_MAXIMO_PROM_VIG_TC_RRLL,

		MAX(COALESCE(V.RANGO_INGRESO, 0)) MAX_RANGO_INGRESO,
		AVG(COALESCE(V.SALDO_PROM_TOT_MILLONARIA, 0)) SALDO_PROM_TOT_MILLONARIA_RRLL,
		MAX(COALESCE(V.SALDO_PROM_TOT_MILLONARIA, 0)) SALDO_MAXIMO_PROM_MILLONARIA_RRLL,
		AVG(COALESCE(V.SALDO_PROM_VIG_VEHICULAR, 0)) SALDO_PROM_TOT_VEHICULAR_RRLL,
		MAX(COALESCE(V.SALDO_PROM_VIG_VEHICULAR, 0)) SALDO_MAXIMO_PROM_VEHICULAR_RRLL,				
		AVG(COALESCE(V.SALDO_PROM_TOT_EXTRACASH_TC, 0)) SALDO_PROM_TOT_EXTRACASH_RRLL,
		MAX(COALESCE(V.SALDO_PROM_TOT_EXTRACASH_TC, 0)) SALDO_MAXIMO_PROM_EXTRACASH_RRLL,		
		AVG(COALESCE(V.SALDO_PROM_TOT_TC, 0)) SALDO_PROM_TOT_TC_RRLL,
		MAX(COALESCE(V.SALDO_PROM_TOT_TC, 0)) SALDO_MAXIMO_PROM_TC_RRLL,					
		AVG(COALESCE(V.SALDO_PROM_TOT_HIPOTECARIO, 0)) SALDO_PROM_TOT_HIPOTECARIO_RRLL,
		MAX(COALESCE(V.SALDO_PROM_TOT_HIPOTECARIO, 0)) SALDO_MAXIMO_PROM_HIPOTECARIO_RRLL
		
	--SELECT COUNT(1)

	FROM E_DW_VIEWS.V_DATOS_PRINCIPALES_SUNAT_HIST A
	LEFT JOIN E_DW_VIEWS.V_REP_LEGALES_SUNAT_HIST C
	ON A.numruc = C.numruc
   AND A.fecproceso = C.fecproceso
	LEFT JOIN  E_DW_VIEWS.V_MAESTRA_RENIEC  B  
	ON (
	    CASE WHEN C.NUMRUC IS NOT NULL AND C.ESTADO = 0 THEN  C.CODDOC 
		            WHEN LENGTH(A.NUMRUC) = 11 AND A.NUMRUC LIKE '10%' THEN SUBSTRING(A.NUMRUC, 3, 8) 
					ELSE 'X' END
	 ) = B.CODDOC
	LEFT JOIN E_DW_VIEWS.V_360_CLIENTE V 
	ON  (
	    CASE WHEN C.NUMRUC IS NOT NULL AND C.ESTADO = 0  THEN  C.CODDOC 
		            WHEN LENGTH(A.NUMRUC) = 11 AND A.NUMRUC LIKE '10%' THEN SUBSTRING(A.NUMRUC, 3, 8) 
					ELSE 'X' END
	 )  = TRIM(V.NRO_DOCUMENTO) 
	 AND V.COD_MES = (SELECT MAX(COD_MES) FROM E_DW_VIEWS.V_360_CLIENTE WHERE FRECUENCIA = 1 )
	 AND V.FRECUENCIA = 1 
	WHERE 1 = 1
	AND A.FECPROCESO >= '23/11/2021'
	--AND A.ESTADO = 0  ---se retira
	AND SUBSTR(A.NUMRUC,1,2) IN ('15','17','20','10')
	AND (
	    C.NUMRUC IS NOT NULL OR (LENGTH(A.NUMRUC) = 11 AND A.NUMRUC LIKE '10%')
	 )
	 GROUP BY A.FECPROCESO,  TRIM(A.NUMRUC)

Add a code snippet to your website: www.paste.org