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 ( 3 years 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