Psst.. new poll here.
[email protected] webmail now available. Want one? Go here.
Cannot use outlook/hotmail/live here to register as they blocking our mail servers. #microsoftdeez
Obey the Epel!
Paste
Pasted as SQL by registered user vvillacorta ( 2 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 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)
Revise this Paste