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 ( 5 months ago )
SELECT
CAST(CAST(A.FECPROCESO AS date format 'YYYYMMDD') AS VARCHAR(6)) AS PERIODO
, CAST(CAST(A.FECPROCESO AS date format 'YYYYMMDD') AS VARCHAR(8)) AS FECCARGA
, A.NUMRUC numruc_val
, CASE WHEN SUBSTR(A.NUMRUC,1,2) = '10' AND LENGTH(A.NUMRUC) = 11 THEN 1 END FLG_PN
, CASE WHEN SUBSTR(A.NUMRUC,1,2) = '20' AND LENGTH(A.NUMRUC) = 11 THEN 1 END FLG_PJ
, C.cnt_rrll
, D.monto_deuda_tributo_amt
, D.tiempo_deuda_tributaria_amt
, D.cnt_deudas_tributarias
, E.cod_sbs_empresa_1
, E.cod_sbs_empresa_2
, E.cod_sbs_empresa_3
, E.cod_sbs_empresa_4
, E.cod_sbs_empresa_5
, E.cod_sbs_empresa_6
, E.cod_sbs_rrll_1
, E.cod_sbs_rrll_2
, E.cod_sbs_rrll_3
, E.cod_sbs_rrll_4
, E.cod_sbs_rrll_5
, E.cod_sbs_rrll_6
, E.cod_unico_val
, A.TIPCONTRIBUYENTE tip_contribuyente_val
, A.CIIU ciiu_val
, A.UBIGEO ubigeo_val
, B.NUMTRABAJADORES num_trabajadores
, A.RANGOVENTAS rango_ventas
, A.CONDICIONDOMICILIO condicion_domicilio
, A.ESTADOCONTRIBUYENTE estado_contribuyente
, CASE WHEN FECALTA IS NULL THEN NULL
ELSE MONTHS_BETWEEN(CAST(A.FECPROCESO AS DATE FORMAT 'YYYYMM'),CAST(A.FECALTA AS DATE FORMAT 'YYYYMM')) / 12 END tiempo_alta
, CASE WHEN FECBAJA IS NULL THEN 0
ELSE MONTHS_BETWEEN(CAST(A.FECPROCESO AS DATE FORMAT 'YYYYMM'),CAST(A.FECBAJA AS DATE FORMAT 'YYYYMM')) / 12 END tiempo_baja
, A.ESTADO
, A.TIENEDIR flg_tiene_dir
, A.TIENETELF flg_tiene_telef
, C.promedio_edad_rrll
, C.CNT_CIVIL_DIVORCIADO
, C.CNT_CIVIL_SOLTERO
, C.CNT_CIVIL_CASADO
, C.CNT_CIVIL_VIUDO
, C.CNT_GEN_F
, C.CNT_GEN_M
, C.CNT_DON_ORGANOS
, C.cnt_residencia_lima_callao
, C.cnt_residencia_provincia
, C.cnt_residencia_extranjera
, C.PERCENT_CIVIL_DIVORCIADO
, C.PERCENT_CIVIL_SOLTERO
, C.PERCENT_CIVIL_CASADO
, C.PERCENT_CIVIL_VIUDO
, C.PERCENT_GEN_F
, C.PERCENT_GEN_M
, C.PERCENT_DON_ORGANOS
, C.PERCENT_residencia_lima_callao
, C.PERCENT_residencia_provincia
, C.PERCENT_residencia_extranjera
, C.INGRESO_BRUTO_TOTAL_RRLL
, C.INGRESO_BRUTO_PROMEDIO_RRLL
, C.INGRESO_BRUTO_MAXIMO_RRLL
, C.TENENCIA_TOTAL_RRLL
, C.TENENCIA_PROMEDIO_RRLL
, C.TENENCIA_MAXIMA_RRLL
, C.PRODUCTOS_TOTALES_RRLL
, C.PRODUCTO_PROMEDIO_RRLL
, C.PRODUCTO_MAXIMO_RRLL
, C.MESES_BLOQUEO_TC_TOTALES_RRLL
, C.MESES_BLOQUEO_TC_PROMEDIO_RRLL
, C.MESES_BLOQUEO_TC_MAXIMO_RRLL
, C.CANT_CLIENTES_GESTIONABLES_RETAIL
, C.CANT_CLIENTES_FOCO_RETAIL
, C.CANT_CLIENTES_COLABORADOR_RETAIL
, C.CANT_CLIENTES_PRINCIPALIDAD_RETAIL
, C.CANT_CLIENTES_TXS_REC_RETAIL
, C.CANT_CLIENTES_CONSUMO_TC_RETAIL
, C.CANT_CLIENTES_PA_RETAIL
, C.CANT_CLIENTES_ALCANCIA_RETAIL
, C.CANT_CLIENTES_TXS_RETAIL
, C.CANT_CLIENTES_TC_ADICIONAL_RETAIL
, C.CANT_CLIENTES_CASTIGO_SF_RETAIL
, C.CANT_CLIENTES_PARQUE_TC_RETAIL
, C.CANT_CLIENTES_SEGURO_RETAIL
, C.INGRESO_BRUTO_TOTAL_RRLL
, C.INGRESO_BRUTO_TOTAL_RRLL
, C.SALDO_PROM_TOT_PASIVO_RRLL
, C.SALDO_PROM_TOT_ACTIVO_RRLL
, C.SALDO_PROM_TOT_TXS_RRLL
, C.SALDO_PROM_VIG_TC_RRLL
, C.SALDO_MAXIMO_PROM_PASIVO_RRLL
, C.SALDO_MAXIMO_PROM_ACTIVO_RRLL
, C.SALDO_MAXIMO_PROM_TXS_RRLL
, C.SALDO_MAXIMO_PROM_VIG_TC_RRLL
, C.MAX_RANGO_INGRESO
, C.SALDO_PROM_TOT_MILLONARIA_RRLL
, C.SALDO_MAXIMO_PROM_MILLONARIA_RRLL
, C.SALDO_PROM_TOT_VEHICULAR_RRLL
, C.SALDO_MAXIMO_PROM_VEHICULAR_RRLL
, C.SALDO_PROM_TOT_EXTRACASH_RRLL
, C.SALDO_MAXIMO_PROM_EXTRACASH_RRLL
, C.SALDO_PROM_TOT_TC_RRLL
, C.SALDO_MAXIMO_PROM_TC_RRLL
, C.SALDO_PROM_TOT_HIPOTECARIO_RRLL
, C.SALDO_MAXIMO_PROM_HIPOTECARIO_RRLL
FROM E_DW_VIEWS.V_DATOS_PRINCIPALES_SUNAT_HIST A
LEFT JOIN E_DW_VIEWS.V_DATOS_SECUNDARIOS_SUNAT_HIST B
ON A.numruc = B.numruc
AND A.fecproceso = B.fecproceso
LEFT JOIN (
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 E_DW_VIEWS.V_REP_LEGALES_SUNAT_HIST A
LEFT JOIN E_DW_VIEWS.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
WHERE 1 = 1
AND A.FECPROCESO >= '23/11/2021'
AND A.ESTADO = 0
AND SUBSTR(A.NUMRUC,1,2) IN ('15','17','20','10')
GROUP BY A.FECPROCESO, TRIM(A.NUMRUC)
) C
ON A.numruc = C.NUMRUC
AND A.fecproceso = C.fecproceso
LEFT JOIN (
SELECT
A.FECPROCESO, TRIM(A.NUMRUC) NUMRUC,
MAX(S.CODUNICOCLI) cod_unico_val,
MAX(CASE WHEN S.ORDER_CODSBS = 1 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_1,
MAX(CASE WHEN S.ORDER_CODSBS = 2 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_2,
MAX(CASE WHEN S.ORDER_CODSBS = 3 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_3,
MAX(CASE WHEN S.ORDER_CODSBS = 4 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_4,
MAX(CASE WHEN S.ORDER_CODSBS = 5 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_5,
MAX(CASE WHEN S.ORDER_CODSBS = 6 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_6,
MAX(CASE WHEN R.RRLL_CODSBS = 1 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_1,
MAX(CASE WHEN R.RRLL_CODSBS = 2 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_2,
MAX(CASE WHEN R.RRLL_CODSBS = 3 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_3,
MAX(CASE WHEN R.RRLL_CODSBS = 4 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_4,
MAX(CASE WHEN R.RRLL_CODSBS = 5 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_5,
MAX(CASE WHEN R.RRLL_CODSBS = 6 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_6
FROM E_DW_VIEWS.V_REP_LEGALES_SUNAT_HIST A
LEFT JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY (
CASE WHEN NUMDOCTRIB LIKE '.' OR NUMDOCTRIB LIKE '' OR NUMDOCTRIB IS NULL THEN NUMDOCTRIB_TRDT ELSE NUMDOCTRIB END)
ORDER BY NUMDOCTRIB DESC, TIPPERSONA ASC, CODSBS
) ORDER_CODSBS,
CODSBS,
NUMDOCTRIB_TRDT,
NUMDOCTRIB,
CODDOC_TRDT,
CODDOC,
CODUNICOCLI
FROM E_DW_VIEWS.V_RSK_FCT_MAEPERSONA_RCC
) S
ON A.NUMRUC = (CASE WHEN S.NUMDOCTRIB LIKE '.' OR S.NUMDOCTRIB LIKE '' OR S.NUMDOCTRIB IS NULL THEN S.NUMDOCTRIB_TRDT ELSE S.NUMDOCTRIB END)
LEFT JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY (
CASE WHEN CODDOC LIKE '.' OR CODDOC LIKE '' OR CODDOC IS NULL THEN CODDOC_TRDT ELSE CODDOC END)
ORDER BY NUMDOCTRIB DESC, TIPPERSONA ASC, CODSBS
) RRLL_CODSBS,
CODSBS,
NUMDOCTRIB_TRDT,
NUMDOCTRIB,
CODDOC_TRDT,
CODDOC,
CODUNICOCLI
FROM E_DW_VIEWS.V_RSK_FCT_MAEPERSONA_RCC
WHERE CODDOC NOT LIKE '.' OR CODDOC_TRDT NOT LIKE '.'
) R
ON A.CODDOC = (CASE WHEN R.CODDOC LIKE '.' OR R.CODDOC LIKE '' OR R.CODDOC IS NULL THEN R.CODDOC_TRDT ELSE R.CODDOC END)
WHERE 1 = 1
AND A.FECPROCESO >= '23/11/2021'
AND A.ESTADO = 0
AND SUBSTR(A.NUMRUC,1,2) IN ('15','17','20','10')
GROUP BY A.FECPROCESO, TRIM(A.NUMRUC)
) E
ON A.numruc = E.NUMRUC
AND A.fecproceso = E.fecproceso
LEFT JOIN (
SELECT A.FECPROCESO, TRIM(A.CODDOC) NUMRUC,
SUM(MONTODEUDA) monto_deuda_tributo_amt,
MIN(FECINICIOPROCESO) FECINICIOPROCESOTRIBUTO,
CASE WHEN MAX(FECINICIOPROCESO) IS NULL THEN 0
ELSE MONTHS_BETWEEN(CAST(FECPROCESO AS DATE FORMAT 'YYYYMM'),CAST(MIN(FECINICIOPROCESO) AS DATE FORMAT 'YYYYMM')) / 12
END tiempo_deuda_tributaria_amt,
CASE WHEN SUM(MONTODEUDA) = 0 THEN 0 ELSE COUNT(CODDOC) END cnt_deudas_tributarias
FROM E_DW_VIEWS.V_TRIBUTOS_SUNAT_HIST A
WHERE 1 = 1
AND ESTADO = 0
AND SUBSTR(CODDOC,1,2) IN ('15','17','20','10')
AND A.FECPROCESO >= '23/11/2021'
GROUP BY A.FECPROCESO, TRIM(A.CODDOC)
) D
ON A.numruc = D.NUMRUC
AND A.fecproceso = D.fecproceso
WHERE 1 = 1
AND A.fecproceso >= '23/11/2021'
Revise this Paste