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 )
USE [MARKET]
GO
/****** Object: StoredProcedure [dbo].[SP_NUEVO_MES_ADQ_CREDITOS] Script Date: 30/05/2022 16:09:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Víctor Villacorta, Jhosep vega
-- Create date: 14/12/2021
-- Description: Universo nuevo mes agenda
-- exec SP_NUEVO_MES_ADQ_CREDITOS
-- =============================================
ALTER PROCEDURE [dbo].[SP_NUEVO_MES_ADQ_CREDITOS]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--SELECT BANCA_FIN, COUNT(1)
--FROM ODS.HM_MERCADO
--WHERE PERIODO = (SELECT MAX(PERIODO) FROM ODS.HM_MERCADO)
--GROUP BY BANCA_FIN
------------------------------------------------------------------------------------------------------
---------- GENERACION NUEVO UNIVERSO MES -------------------------------------------------------------
------------------------------------------------------------------------------------------------------
IF OBJECT_ID('TMP_NUM_DOC_MERCADO_VPC','U') IS NOT NULL DROP TABLE TMP_NUM_DOC_MERCADO_VPC
SELECT NUM_DOC
INTO TMP_NUM_DOC_MERCADO_VPC --31min --10702397 rows affected
FROM ODS.HM_MERCADO
WHERE PERIODO >= '202101' --= (SELECT MAX(PERIODO) FROM ODS.HM_MERCADO)
AND (
BANCA_FIN IN ('BPE', 'BE', 'BC', 'INMB', 'INMBL', 'BI')
OR
BANCA_MES IN ('BPE', 'BE', 'BC', 'INMB', 'INMBL', 'BI')
)
IF OBJECT_ID('TMP_NUM_DOC_PROVEEDORES','U') IS NOT NULL DROP TABLE TMP_NUM_DOC_PROVEEDORES
SELECT CASE WHEN LEN(NUM_RUC) = 11 AND NUM_RUC LIKE '10%' THEN SUBSTRING(NUM_RUC, 3, 8) ELSE NUM_RUC END NUM_DOC
INTO TMP_NUM_DOC_PROVEEDORES
FROM T_GESCOM_UNIV_NUEVOS_ECOSISTEMA
--select top 100* from TMP_CREDITOS_MERCADO_V2
IF OBJECT_ID('TMP_CREDITOS_MERCADO_V2','U') IS NOT NULL DROP TABLE TMP_CREDITOS_MERCADO_V2
SELECT NUMRUC,
CASE WHEN LEN(NUMRUC) = 11 AND NUMRUC LIKE '10%' THEN SUBSTRING(NUMRUC, 3, 8) ELSE NUMRUC END NUM_DOC,
CASE WHEN LEN(NUMRUC) = 11 AND NUMRUC LIKE '20%' THEN 1 ELSE 0 END PERSONA_JURIDICA,
CASE WHEN LEN(NUMRUC) = 11 AND NUMRUC LIKE '10%' THEN 1 ELSE 0 END PERSONA_NATURAL
INTO TMP_CREDITOS_MERCADO_V2
--select *
FROM ODS.MM_SUNAT
WHERE 1 = 1
--AND NUMRUC IS NOT NULL
AND LEN(NUMRUC) = 11
AND (
NUMRUC LIKE '20%'
OR
NUMRUC LIKE '15%'
OR
NUMRUC LIKE '17%'
--OR
--(
-- NUMRUC IN (SELECT NUM_RUC FROM ODS.HM_ADQ_UNIVERSO_RIESGOS_CRED_NUEVOS --- UNIVERSO RIESGOS NUEVOS
-- union all
-- SELECT NUM_RUC FROM TMP_UNIV_PRICING_BPE_3 --- HISTORICO CAMAPÑAS BPE
-- )
--)
--OR
--(
-- NUMRUC LIKE '10%' AND SUBSTRING(NUMRUC, 3, 8) IN (SELECT NUM_DOC FROM TMP_NUM_DOC_MERCADO_VPC --- MERCADO VPC
-- union all
-- SELECT NUM_DOC FROM TMP_NUM_DOC_PROVEEDORES --- PROVEEDORES DE CORPO
-- )
--)
)
---
insert into TMP_CREDITOS_MERCADO_V2 (NUMRUC,NUM_DOC,PERSONA_JURIDICA,PERSONA_NATURAL)
SELECT NUMRUC,
CASE WHEN LEN(NUMRUC) = 11 AND NUMRUC LIKE '10%' THEN SUBSTRING(NUMRUC, 3, 8) ELSE NUMRUC END NUM_DOC,
CASE WHEN LEN(NUMRUC) = 11 AND NUMRUC LIKE '20%' THEN 1 ELSE 0 END PERSONA_JURIDICA,
CASE WHEN LEN(NUMRUC) = 11 AND NUMRUC LIKE '10%' THEN 1 ELSE 0 END PERSONA_NATURAL
FROM ODS.MM_SUNAT
WHERE 1 = 1
and NUMRUC IN (SELECT NUM_RUC FROM ODS.HM_ADQ_UNIVERSO_RIESGOS_CRED_NUEVOS --- UNIVERSO RIESGOS NUEVOS
union all
SELECT NUM_RUC FROM TMP_UNIV_PRICING_BPE_3 --- HISTORICO CAMAPÑAS BPE
)
---
insert into TMP_CREDITOS_MERCADO_V2 (NUMRUC,NUM_DOC,PERSONA_JURIDICA,PERSONA_NATURAL)
SELECT NUMRUC,
CASE WHEN LEN(NUMRUC) = 11 AND NUMRUC LIKE '10%' THEN SUBSTRING(NUMRUC, 3, 8) ELSE NUMRUC END NUM_DOC,
CASE WHEN LEN(NUMRUC) = 11 AND NUMRUC LIKE '20%' THEN 1 ELSE 0 END PERSONA_JURIDICA,
CASE WHEN LEN(NUMRUC) = 11 AND NUMRUC LIKE '10%' THEN 1 ELSE 0 END PERSONA_NATURAL
FROM ODS.MM_SUNAT
WHERE 1 = 1
and NUMRUC LIKE '10%' AND SUBSTRING(NUMRUC, 3, 8) IN (SELECT NUM_DOC FROM TMP_NUM_DOC_MERCADO_VPC --- MERCADO VPC
union all
SELECT NUM_DOC FROM TMP_NUM_DOC_PROVEEDORES --- PROVEEDORES DE CORPO
)
--SELECT COUNT(1) FROM ODS.MM_SUNAT WHERE LEN(NUMRUC) = 11 AND NUMRUC LIKE '20%' --- 1712629 30/12/2021 1733538 28/02/2022
--SELECT COUNT(1) FROM ODS.MM_SUNAT WHERE LEN(NUMRUC) = 11 AND NUMRUC LIKE '10%'
--SELECT COUNT(1) FROM T_GESCOM_UNIV_NUEVOS_ECOSISTEMA --- 467533 30/12/2021 -- 479533
--select count(1), count(distinct numruc) from TMP_CREDITOS_MERCADO_V2
IF OBJECT_ID('TMP_CREDITOS_MERCADO','U') IS NOT NULL DROP TABLE TMP_CREDITOS_MERCADO
SELECT M.PERIODO,
M.COD_SBS,
A.NUM_DOC,
A.PERSONA_JURIDICA,
A.PERSONA_NATURAL
INTO TMP_CREDITOS_MERCADO
--select *
FROM (select distinct A.NUM_DOC, A.PERSONA_JURIDICA, A.PERSONA_NATURAL from TMP_CREDITOS_MERCADO_V2 A ) A
LEFT JOIN(
SELECT *
, CASE WHEN LEN(M.NUM_DOC) = 11 AND M.NUM_DOC LIKE '10%' THEN SUBSTRING(M.NUM_DOC, 3, 8) ELSE M.NUM_DOC END NUM_DOC_2
FROM ODS.HM_MERCADO M
WHERE PERIODO = (SELECT MAX(PERIODO) FROM ODS.HM_MERCADO)
--and CASE WHEN LEN(M.NUM_DOC) = 11 AND M.NUM_DOC LIKE '10%' THEN SUBSTRING(M.NUM_DOC, 3, 8) ELSE M.NUM_DOC END='20525030602'
) M
ON A.NUM_DOC = m.NUM_DOC_2
IF OBJECT_ID('TMP_NUM_DOC_MERCADO_VPC','U') IS NOT NULL DROP TABLE TMP_NUM_DOC_MERCADO_VPC
IF OBJECT_ID('TMP_NUM_DOC_PROVEEDORES','U') IS NOT NULL DROP TABLE TMP_NUM_DOC_PROVEEDORES
SELECT COUNT(1), count(distinct NUM_DOC)
--select num_doc
FROM TMP_CREDITOS_MERCADO group by NUM_DOC having count(num_doc)>1
Revise this Paste