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

Add a code snippet to your website: www.paste.org