Psst.. new poll here.
[email protected] web/email 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 Plain Text by registered user vvillacorta ( 3 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