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 ( 4 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