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,
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
--SELECT *
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 (
SELECT ROW_NUMBER() OVER (PARTITION BY (NUM_RUC END)
ORDER BY NUM_RUC DESC, CODSBS
) ORDER_CODSBS,
CODSBS,
NUM_RUC,
NUMDOCTRIB,
CODDOC_TRDT,
CODDOC,
FROM NUEVA_MAE_PERSONA_RCC_VPC
) S
ON A.NUMRUC = S.NUM_RUC
LEFT JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY (
CASE WHEN LENGTH(CODDOC) = 11 AND CODDOC LIKE '10%' THEN SUBSTRING(CODDOC, 3, 8) ELSE CODDOC END)
ORDER BY CODDOC DESC, TIPDOC ASC, CODSBS
) RRLL_CODSBS,
CODSBS,
NUM_RUC,
NUMDOCTRIB,
CODDOC_TRDT,
CODDOC,
FROM NUEVA_MAE_PERSONA_RCC_VPC
WHERE CODDOC NOT LIKE '' AND CODDOC <> '.'
--LENGTH(CODDOC) = 8 OR LENGTH(CODDOC_TRDT) = 8
) R
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
) = (
CASE WHEN LENGTH(R.CODDOC) = 11 AND R.CODDOC LIKE '10%' THEN SUBSTRING(R.CODDOC, 3, 8)
ELSE R.CODDOC END
)
WHERE 1 = 1
GROUP BY A.FECPROCESO, TRIM(A.NUMRUC)
Revise this Paste