Psst.. new poll here.
Psst.. new forums here.
Microsoft is blocking us again (TY IP Reputation!) so dont bother with any of their useless mail servers here and just use oauth login instead. Thank the nice Russians for causing that. :)
Paste
Pasted as SQL by registered user vvillacorta ( 4 years ago )
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 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 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
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)
Revise this Paste