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 )
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