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

    FROM ........V_REP_LEGALES_SUNAT_HIST A
    LEFT JOIN  .........V_MAESTRA_RENIEC  B  
    ON A.CODDOC = B.CODDOC
    LEFT JOIN E_DW_VIEWS.V_360_CLIENTE V 
    ON TRIM(A.CODDOC)  = TRIM(V.NRO_DOCUMENTO) AND 
           CAST(CAST(A.FECPROCESO AS date format 'YYYYMMDD')  AS VARCHAR(6)) = V.COD_MES AND 
           V.FRECUENCIA = 1   -- OJO SI NO ESTA HABIOITADO V.FRECUENCIA = 1 TOMAR EL FRECUENCIA 2 MAS CERCANO
    WHERE 1 = 1
    AND A.ESTADO = 0
    GROUP BY A.FECPROCESO,  TRIM(A.NUMRUC)