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 Plain Text by registered user vvillacorta ( 5 years ago )
SELECT A.FECCARGA, TRIM(A.NUMRUC) NUMRUC,
CASE WHEN SUBSTR(NUMRUC,1,2) = '10' AND (TRIM(A.CODDOCRELE) LIKE '' OR A.CODDOCRELE IS NULL) THEN TRIM(SUBSTR(NUMRUC,3,8))
ELSE TRIM(A.CODDOCRELE) END CODDOCRELE,
MAX(CASE WHEN S.ORDER_CODSBS = 1 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_1,
MAX(CASE WHEN S.ORDER_CODSBS = 2 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_2,
MAX(CASE WHEN S.ORDER_CODSBS = 3 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_3,
MAX(CASE WHEN S.ORDER_CODSBS = 4 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_4,
MAX(CASE WHEN S.ORDER_CODSBS = 5 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_5,
MAX(CASE WHEN S.ORDER_CODSBS = 6 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_6,
MAX(CASE WHEN R.RRLL_CODSBS = 1 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_1,
MAX(CASE WHEN R.RRLL_CODSBS = 2 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_2,
MAX(CASE WHEN R.RRLL_CODSBS = 3 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_3,
MAX(CASE WHEN R.RRLL_CODSBS = 4 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_4,
MAX(CASE WHEN R.RRLL_CODSBS = 5 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_5,
MAX(CASE WHEN R.RRLL_CODSBS = 6 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_6,
MAX(S.CODUNICOCLI) COD_UNICO,
MAX(RAZONSOCIAL) RAZONSOCIAL,
MAX(TIPCONTRIBUYENTE) TIPCONTRIBUYENTE,
MAX(TIPPERSONA) TIPPERSONA,
MAX(CIIU) CIIU,
MAX(UBIGEO) UBIGEO,
CASE WHEN MAX(NUMTRABAJADORES) LIKE '' THEN 0
WHEN MAX(NUMTRABAJADORES) LIKE 'NE' THEN 0
ELSE CAST(MAX(NUMTRABAJADORES) AS INTEGER)
END NUMTRABAJADORES,
CASE WHEN MAX(RANGOVENTAS) LIKE '' OR MAX(RANGOVENTAS) IS NULL THEN 0 ELSE MAX(RANGOVENTAS) END RANGOVENTAS,
MAX(CONDICIONDOMICILIO) CONDICIONDOMICILIO,
MAX(ESTADOCONTRIBUYENTE) ESTADOCONTRIBUYENTE,
MAX(FECALTA) FECALTA,
CASE WHEN MAX(FECALTA) IS NULL THEN NULL
ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT 'YYYYMM'),CAST(MAX(FECALTA) AS DATE FORMAT 'YYYYMM')) / 12 END TIEMPO_ALTA,
MAX(FECBAJA) FECBAJA,
CASE WHEN MAX(FECBAJA) IS NULL THEN 0
ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT 'YYYYMM'),CAST(MAX(FECBAJA) AS DATE FORMAT 'YYYYMM')) / 12 END TIEMPO_BAJA,
MAX(CONTABILIDAD) CONTABILIDAD,
MAX(FACTURACION) FACTURACION,
MAX(COMERCIOEXTERIOR) COMERCIOEXTERIOR,
MAX(ESTADO) ESTADO,
MAX(CASE WHEN TIENEDIR = 'SI' THEN 1 ELSE 0 END) FLG_TIENEDIR,
MAX(CASE WHEN TIENETELEF = 'SI' THEN 1 ELSE 0 END) FLG_TIENETELEF,
MAX(ESTADORELE) ESTADORELE,
MAX(CASE WHEN SUBSTR(NUMRUC,1,2) = '10' AND (TRIM(A.CODDOCRELE) LIKE '' OR A.CODDOCRELE IS NULL) THEN FECALTA ELSE FECVIGENCIARELE END) FECVIGENCIARELE,
MAX(CASE WHEN SUBSTR(NUMRUC,1,2) = '10' AND (TRIM(A.CODDOCRELE) LIKE '' OR A.CODDOCRELE IS NULL) THEN (
CASE WHEN FECALTA IS NULL THEN NULL
ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT 'YYYYMM'),CAST(FECALTA AS DATE FORMAT 'YYYYMM')) / 12
END
)
WHEN FECVIGENCIARELE IS NULL THEN NULL
ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT 'YYYYMM'),CAST(FECVIGENCIARELE AS DATE FORMAT 'YYYYMM')) / 12
END) TIEMPO_VIGENCIARELE,
MAX(B.EST_CIVIL) EST_CIVIL,
MAX(B.FEC_NACIMIENTO) FECHA_NACIMIENTO,
MAX(CASE WHEN B.FEC_NACIMIENTO IS NULL THEN NULL
ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT 'YYYYMM'),CAST(B.FEC_NACIMIENTO AS DATE FORMAT 'YYYYMM')) / 12
END) EDAD_RRLL,
MAX(CASE WHEN EST_CIVIL = 'D' THEN 1 ELSE 0 END) EST_CIVIL_DIVORCIADO,
MAX(CASE WHEN EST_CIVIL = 'S' THEN 1 ELSE 0 END) EST_CIVIL_SOLTERO,
MAX(CASE WHEN EST_CIVIL ='C' THEN 1 ELSE 0 END) EST_CIVIL_CASADO,
MAX(CASE WHEN EST_CIVIL = 'V' THEN 1 ELSE 0 END) EST_CIVIL_VIUDO,
MAX(GENERO) GENERO,
MAX(CASE WHEN GENERO = 'F' THEN 1 ELSE 0 END) EST_CIVIL_FEMENINO,
MAX(CASE WHEN GENERO = 'M' THEN 1 ELSE 0 END) EST_CIVIL_MASCULINO,
MAX(CASE WHEN DON_ORGANOS = 'SI' THEN 1 ELSE 0 END) DON_ORGANOS,
MAX(CASE WHEN DEP_UBI_DOMICILIO IN ( 'LIMA' , 'CALLAO' ) THEN 'LIMA y CALLAO'
WHEN 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 'PROVINCIA'
ELSE 'EXTRANJERO' END) LUGAR_RESIDENCIA,
MAX(CASE WHEN DEP_UBI_DOMICILIO IN ( 'LIMA' , 'CALLAO' ) THEN 1 ELSE 0 END) RESIDENCIA_LIMA_CALLAO,
MAX(CASE WHEN 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) RESIDENCIA_PROVICNCIA,
MAX(CASE WHEN 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) RESIDENCIA_EXTRANJERA,
'202109' PERIODO_VAL -- MES PARAMETRO (mes actual - 1 , a que se debe correr el 1ero de cada mes)
FROM E_DW_VIEWS.V_CONSOLIDADO_SUNAT_HIST A
LEFT JOIN E_DW_VIEWS.V_MAESTRA_RENIEC B
ON ( CASE WHEN A.NUMRUC LIKE '10%' THEN A.CODDOC ELSE A.CODDOCRELE END) = B.CODDOC
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.CODDOCRELE = (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 ESTADORELE <> 9
AND SUBSTR(NUMRUC,1,2) IN ('15','17','20','10')
AND A.FECCARGA = '202107' --'PERIODO_A_COPIAR DE SUNAT HIST'
GROUP BY A.FECCARGA, TRIM(A.NUMRUC),
CASE WHEN SUBSTR(NUMRUC,1,2) = '10' AND (TRIM(A.CODDOCRELE) LIKE '' OR A.CODDOCRELE IS NULL) THEN TRIM(SUBSTR(NUMRUC,3,8))
ELSE TRIM(A.CODDOCRELE) END
Revise this Paste