none
Ayuda con optimización de un Store Procedure. RRS feed

  • Pregunta

  • Bueno, necesito ayuda con la optimización de un store (no tengo mucha experiencia en esto)

    La cosa es que este sp lo utilizo para copiar toda la tabla A a la tabla B y luego añadir campos como subconsultas de otras tablas (x,y,z,etc).

    En total son la tabla principal A tiene cerca de 30 campos + las sub consultas 40, En total esta ultima tabla generada cuenta son unos 70 campos.

    El motivo de esta tabla es que la organización quiere un reporte en excel, yo en el modelo de datos enlazo por bigquery y muestro todos los datos de esta tabla (no son muchos registros, solo 500  día pero se cambia el filtro del modelo de datos para mostrar solo la data del mes en curso).

    Solo necesito una alternativa de optimizar el sp ya que demora 1 hora con 20 minutos en ejecutar, trate de explicar bien el problema para los que no pueden leer el código ya que es largo (para mi lo es).

    Muchas Gracias de antemano!

    DECLARE @RETROCESO Int 
    IF DAY(getdate())='1' OR DAY(getdate())='2' 
    BEGIN 
    	SET @RETROCESO=-1
    	PRINT @RETROCESO
    END 
    ELSE 
    
    BEGIN 
    
    
    	SET @RETROCESO=-1
    END 
    
    	WHILE @RETROCESO <0
    	BEGIN 
    	DECLARE @FECHA DATEtime =GETDATE ()+@RETROCESO
    	print @fecha
    
    
    
    
    BEGIN
    --DECLARE @FECHA DATEtime =GETDATE()-1
    --BORRAR EN CASO SE AUTOMATICE VARIAS VECES AL DIA--delete from H_BASE_PRODUCTIVIDAD where FEC_HOJA_CASTEADA =@FECHA
    
    
    --h_cre_credito creditos en curso data clientes y saldo capital
    		  IF OBJECT_ID('TEMPDB..#credito_cliente_actual') IS NOT NULL
    		DROP TABLE #credito_cliente_actual
    
    		select sum(IMP_SAL_CAPITAL) as saldo_capital_actual,count(cod_credito) as n_credito_actual,de.COD_PERSONAL 
    		into #credito_cliente_actual
    		from H_CRE_CREDITO hcc
    		left join DIM_EJECUTIVO de on de.COD_EJECUTIVO = hcc.COD_EJECUTIVO
    		where COD_PRD not in ('663',
    		'660',
    		'661',
    		'662')
    		and TIP_SITUACION <> '07'
    		group by de.COD_PERSONAL
    
    --cuadre cartera data clientes y saldo capital
    		  IF OBJECT_ID('TEMPDB..#credito_cliente_mes_pasado') IS NOT NULL
    		DROP TABLE #credito_cliente_mes_pasado
    
    		select de.[COD_PERSONAL],sum(IMP_SAL_CAPITAL) AS SALDO_CAPITAL_Anterior,
    		count(distinct(cod_cli)) AS CLIENTES_anterior  
    		into #credito_cliente_mes_pasado
    		from CUADRE_CARTERA  cc
    		left join DIM_EJECUTIVO de on de.DES_EJECUTIVO =cc.[EJECUTIVO DE NEGOCIOS]
    		where  
    		 [año_proce] = year(dateadd(month,-1,getdate())) and MES_PROCE = month(dateadd(month,-1,getdate())) and
    		(producto <> 'PROFAE' and  producto <> 'PROREACTIVA30' and producto <> 'PROREACTIVA10' and producto <> 'PROREACTIVA300' )
    		and F_CALIDAD_CARTERA_XRM <> '40. CASTIGADOS'
    		group by de.[COD_PERSONAL]
    
    
    
    --creditos mora dia 0 de cre credito
    --cuadre cartera data clientes y saldo capital
    		IF OBJECT_ID('TEMPDB..#credito_mora_dia_0') IS NOT NULL
    		DROP TABLE #credito_mora_dia_0
    
    		select 
    		 sum(IMP_SAL_CAPITAL) as saldo_capital_actual_0,de.COD_PERSONAL
    		 into #credito_mora_dia_0
    		 from H_CRE_CREDITO hcc left join DIM_EJECUTIVO de on de.COD_EJECUTIVO = hcc.COD_EJECUTIVO
    		 WHERE F_CALIDAD_CARTERA = '10. NORMAL (0)'  and
    		 COD_PRD not in ('663',
    		'660',
    		'661',
    		'662')
    		and TIP_SITUACION <> '07'
    		group by de.COD_PERSONAL
    
    
    --dia 0 cuadre cartera
    		  IF OBJECT_ID('TEMPDB..#credito_mora_dia_0_mes_anterior') IS NOT NULL
    		DROP TABLE #credito_mora_dia_0_mes_anterior
    
    		select de.[COD_PERSONAL],sum(IMP_SAL_CAPITAL) AS saldo_capita_al_dia 
    		into #credito_mora_dia_0_mes_anterior
    		from CUADRE_CARTERA  cc
    		left join DIM_EJECUTIVO de on de.DES_EJECUTIVO =cc.[EJECUTIVO DE NEGOCIOS]
    		where  
    		 [año_proce] = year(dateadd(month,-1,getdate())) and MES_PROCE = month(dateadd(month,-1,getdate())) and
    		(producto <> 'PROFAE' and  producto <> 'PROREACTIVA30' and producto <> 'PROREACTIVA10' and producto <> 'PROREACTIVA300' )
    		and F_CALIDAD_CARTERA_XRM = '10. NORMAL (0)'
    		and F_CALIDAD_CARTERA_XRM <> '40. CASTIGADOS'
    		group by de.[COD_PERSONAL]
    
    
    --tabla telefonos
    		  IF OBJECT_ID('TEMPDB..#tel_val') IS NOT NULL
    		DROP TABLE #tel_val
    
    		SELECT COD_PERSONAL,SUM(CONVERT(DECIMAL,VALIDADOS))/COUNT(CONVERT(DECIMAL,VALIDADOS)) as valido
    		into #tel_val
    		FROM (
    		select  DE.COD_PERSONAL,
    		case when validado = 'SI' THEN 1
    		WHEN VALIDADO = 'SIN REVISAR' THEN 0 END AS VALIDADOS
    
    		from REL_TLF_CLIENTE_REPORTE tel left join DIM_EJECUTIVO de on tel.des_ejecutivo = de.des_ejecutivo)A
    		GROUP BY COD_PERSONAL
    
    
    --TABLA %REGISTRO_DIRECCION
    		  IF OBJECT_ID('TEMPDB..#DIR_CLI') IS NOT NULL
    		DROP TABLE #DIR_CLI
    		-- DECLARE @FECHA DATEtime =GETDATE()
    		SELECT COD_PERSONAL , SUM (
    		CONVERT(DECIMAL,CASE WHEN NUM_COORD_X_REF_DOM IS NOT NULL OR NUM_COORD_X_REF_NEG IS NOT NULL THEN 1 ELSE 0 END))
    		/ COUNT (
    		CONVERT(DECIMAL,CASE WHEN NUM_COORD_X_REF_DOM IS NOT NULL OR NUM_COORD_X_REF_NEG IS NOT NULL THEN 1 ELSE 0 END))
    		AS TOTAL
    		into #DIR_CLI
    		FROM DIR_REFERENCIADA DR LEFT JOIN  DIM_EJECUTIVO de on DR.des_ejecutivo = de.des_ejecutivo
    		WHERE CONVERT(DATE,CONVERT(DATE,FEC_PROCESO,105)) = CONVERT(DATE,getdate(),105)
    		GROUP BY COD_PERSONAL
    
    --TABLA MEGAS
     		IF OBJECT_ID('TEMPDB..#TEMP_MEGAS') IS NOT NULL	
    		DROP TABLE #TEMP_MEGAS
    		
    		select deviceid,(sum(convert(decimal,bytesdownloaded))+sum(convert(decimal,bytesuploaded)))/1000000 as Megas  
    		INTO #TEMP_MEGAS
    		from [192.168.87.31\SVRMSSQL].MDM.[dbo].[TRAFFIC_CELULAR]
    		where CONVERT(CHAR(10),convert(date,timestamp),112) = CONVERT(CHAR(10),@FECHA-1,112) 
    		group by deviceid
    
    
    	
    --TABLA STORAGE
     		IF OBJECT_ID('TEMPDB..#TEMP_MEMORY') IS NOT NULL	
    		DROP TABLE #TEMP_MEMORY
    	select DISTINCT(DEVICEID),(CONVERT(DECIMAL,AVAILABLEMEMORY)/CONVERT	(DECIMAL,TOTALMEMORY)) AS MEMORY  
    	INTO #TEMP_MEMORY
    	FROM [192.168.87.31\SVRMSSQL].MDM.[dbo].[memory]
    	where CONVERT(CHAR(10),FECHA,112) = CONVERT(CHAR(10),@FECHA,112)  
    	ORDER BY MEMORY
    
    	
    
    
    
    
    
    
    
     --TABLA DESEMBOLSOS TEMPORALES
     		IF OBJECT_ID('TEMPDB..#TEMP_DESEMBOLSO') IS NOT NULL	
    		DROP TABLE #TEMP_DESEMBOLSO
    	--	DECLARE @FECHA DATEtime =GETDATE ()-1
    		select count(MONTO_DESEMBOLSO) as  NRO_DESEMB ,sum(MONTO_DESEMBOLSO) MONTO_DESEM ,Fecha,D.DES_EJECUTIVO,W.COD_PERSONAL
    		INTO #TEMP_DESEMBOLSO
    		FROM FACT_DESEMBOLSO A
    		LEFT JOIN DIM_TIEMPO B ON A.ID_TIEMPO=B.IdTiempo
    		LEFT JOIN DIM_CREDITO C ON C.ID_CREDITO=A.ID_CREDITO 
    		LEFT JOIN DIM_EJECUTIVO D ON D.ID_EJECUTIVO=A.ID_EJECUTIVO
    		LEFT JOIn DIM_EJECUTIVO w ON A.ID_EJECUTIVO=w.ID_EJECUTIVO	
    		where convert(date,Fecha) = convert(date,@FECHA)
    		group by w.COD_PERSONAL,d.DES_EJECUTIVO,Fecha
    
    --TABLA BATERIAS
    		--DECLARE @FECHA DATEtime =GETDATE ()
     		IF OBJECT_ID('TEMPDB..#TEMP_BATERIA') IS NOT NULL	
    		DROP TABLE #TEMP_BATERIA
    		 SELECT deviceid,fecha,value as BATERIA
    		 INTO #TEMP_BATERIA
    		 FROM (
    		 select ROW_NUMBER() OVER(PARTITION BY deviceid ORDER BY fecha desc) AS ORDEN ,* 
    		 from (select deviceid,convert(datetime,timestamp) as fecha,value from [192.168.87.31\SVRMSSQL].MDM.[dbo].[bATTERY_STATUS] 
    		 where CONVERT(CHAR(10),convert(date,timestamp),112)  =  CONVERT(CHAR(10),@FECHA,112) 
    		 ) A ) B WHERE   b.ORDEN='1' 
    		
    --CONSULTA LICENCIAS ORACLE (SQL ESTA MAL)
    
    		 if OBJECT_ID('TEMPDB..#LICENCIAS_HR_TOTAL') IS NOT NULL
    		 DROP TABLE #LICENCIAS_HR_TOTAL
    		 SELECT
    		 R.COD_PERSONAL,R.FEC_INICIO,R.FEC_FIN
    		 INTO #LICENCIAS_HR_TOTAL
    		 FROM PROD..CACTUS_INTEGRAL.PLA_SUBS_REG_DESC R
    		 WHERE R.COD_EMPRESA = '0001' AND  @FECHA BETWEEN FEC_INICIO AND 
    		 ISNULL(FEC_FIN, CONVERT(datetime,(DATEADD(MONTH,1,concat(year(@FECHA),'-', month(@FECHA),'-', '01')) - 1)))
    		 ORDER BY 1, 3
    
    --CONSULTA FERIADOS
    --select * from #FERIADO
    
    		if OBJECT_ID('TEMPDB..#FERIADO') IS NOT NULL
    		DROP TABLE #FERIADO
    
    --DECLARE @FECHA DATEtime =GETDATE ()-5
    		SELECT fecha,'FERIADO' as FERIADO INTO #FERIADO 
    		FROM DIM_FERIADO
    		WHERE convert(date,@FECHA) = convert(date,Fecha)
    
    
    --CONSULTA VACACIONES ORACLE (SQL ESTA MAL)
    		if OBJECT_ID('TEMPDB..#VACACIONES_HR_TOTAL') IS NOT NULL
    		DROP TABLE #VACACIONES_HR_TOTAL
    
    --DECLARE @FECHA DATE =GETDATE ()
    
    		SELECT COD_PERSONAL,FEC_INICIO,FEC_FINAL
    		INTO #VACACIONES_HR_TOTAL
    		FROM PROD..CACTUS_INTEGRAL.PLA_VACACIONES_MES 
    		WHERE COD_EMPRESA = '0001' AND  @FECHA BETWEEN FEC_INICIO AND 
    		ISNULL(FEC_FINAL, CONVERT(datetime,(DATEADD(MONTH,1,concat(year(@FECHA),'-', month(@FECHA),'-', '01')) - 1)))
    
    
    --CONSULTA SOLICITUDES
    		if OBJECT_ID('TEMPDB..#SOLICITUDES_HR_TOTAL') IS NOT NULL
    		DROP TABLE #SOLICITUDES_HR_TOTAL
    		SELECT (select cod_personal from DIM_EJECUTIVO cen where cen.cod_ejecutivo = soli.cod_ejecutivo ) as COD_PERSONAL, COUNT(COD_PRO_CRE) as SOLICITUDES
    		INTO #SOLICITUDES_HR_TOTAL
    		FROM PROD..CACTUS_SAFI.CRE_PRO_CRE soli
    		WHERE COD_EMPRESA = '0001'  AND FEC_REGISTRO = CONVERT(DATE,@FECHA)
    		GROUP BY COD_EJECUTIVO
    
    
    	--	DECLARE @FECHA DATEtime =GETDATE ()-1
    --CONSULTA RECUPERACIONES
    		if OBJECT_ID('TEMPDB..#MONTO_RECUPERADO') IS NOT NULL
    		DROP TABLE #MONTO_RECUPERADO
    
    		select SUM(IMP_RECUP_TOTAL) AS RECUPERADO ,COD_PERSONAL,DIA_PROCESO 
    		INTO #MONTO_RECUPERADO
    		from PAGO_VS_GESTION_DIA 
    		where RANGO_MORA IN ( 
    		'(2-3)', 
    		'(4-8)',
    		'(9-15)',
    		'(16-30)',
    		'(31-44)',
    		'(45-60)'
    		)
    		AND CONVERT (CHAR(10),@FECHA,112) = DIA_PROCESO
    		GROUP BY COD_PERSONAL,DIA_PROCESO
    
    
    
    --ELIMINA LA DATA PARA REPROCESAR
    		 delete from HR_TOTAL where convert(date,FEC_HOJA_ruta) =convert(date,@FECHA)
    
    
     --DECLARE @FECHA DATEtime =GETDATE ()-1
    		INSERT INTO HR_TOTAL
    --DECLARE @FECHA DATEtime =GETDATE ()-1
    
    		select
    
    		EDN,
    		DES_CARGO_PLANILLA AS DES_CARGO_PLANILLA,
    		bp.COD_PERSONAL AS COD_PERSONAL,
    		TRY_CONVERT(datetime,bp.fec_hoja_ruta) AS FEC_HOJA_RUTA,
    		ENCARGATURA AS ENCARGATURA,
    		bp.REGION AS REGION,
    		convert(varchar,bp.AGENCIA,200) AS AGENCIA,
    		[PROSPECCIÓN] AS PROSPECCION,
    		[FIDELIZACIÓN] AS FIDELIZACION,
    		EVALUACIÓN AS EVALUACION,
    		RECUPERACIÓN AS RECUPERACION,
    		[REC. COMISIÓN] AS REC_COMISION,
    		[GESTIONES ADMINISTRATIVAS] AS GESTION_ADM,
    		[VISITA JUDICIAL] AS VISITA_JUDICIAL,
    		Preventiva AS PREVENTIVA,
    		[Acompañamiento] AS ACOMPANAMIENTO,
    		[Post-Desembolso] AS POST_DESEMBOLSO,
    		 convert(datetime, bp.FEC_INGRESO, 105) as FEC_INGRESO,
    		CARGO AS CARGO,
    		CANTIDAD_LLAMADAS AS CANTIDAD_LLAMADAS,
    		META AS META,
    		NDiaSemana AS NDiaSemana,
    		META_REAL AS META_REAL,
    		LICENCIA AS LICENCIA,
    		VACACIONES AS VACACIONES,
    		DESCANSOS AS DESCANSOS,
    		[PROSPECCIÓN VALIDA] AS PROSP_VALIDA,
    		[FIDELIZACIÓN VALIDA] AS FID_VALIDA,
    		[EVALUACIÓN VALIDA] AS EVAL_VALIDA,
    		[RECUPERACIÓN VALIDA] AS REC_VALIDA,
    		[REC. COMISIÓN VALIDA] AS REC_COMISION_VALIDA,
    		CUMPLIMIENTO AS CUMPLIMIENTO,
    		[VISITA JUDICIAL VALIDA] AS VISITA_JUDICIAL_VALIDA,
    		[POST DESEMBOLSO VALIDO] AS POST_DESEMBOLSO_VALIDO,
    		[ACOMPANAMIENTO VALIDO] AS ACOMPANAMIENTO_VALIDO,
    		[PREVENTIVA VALIDA] AS PREVENTIVA_VALIDA,
    
    -- FERIADO
    		(SELECT 'FERIADOS' as FERIADO FROM #FERIADO fer WHERE BP.fec_hoja_ruta =fer.Fecha) as FERIADOS_FILTRO,
    
    -- VACACIONES
    		(SELECT 'VACACIONES' as VACACIONES FROM #VACACIONES_HR_TOTAL fer WHERE BP.COD_PERSONAL = FER.COD_PERSONAL AND BP.fec_hoja_ruta  BETWEEN fer.FEC_INICIO AND FER.FEC_FINAL ) as VACACIONES_FILTRO,
    
    --LICENCIAS
    		(SELECT 'LICENCIAS' as LICENCIAS FROM #LICENCIAS_HR_TOTAL fer WHERE BP.COD_PERSONAL = FER.COD_PERSONAL AND BP.fec_hoja_ruta  BETWEEN fer.FEC_INICIO AND FER.FEC_FIN ) as LICENCIAS_FILTRO,
    
    --MONTO RECUPERADO
    		(SELECT RECUPERADO FROM #MONTO_RECUPERADO fer WHERE BP.COD_PERSONAL = FER.COD_PERSONAL  ) as RECUPERACIONES,
    
    --QUINTILES
    		(select Q_PROD from temporal..quintil_resultados_nov qr WHERE QR.COD_PERSONAL = BP.COD_PERSONAL ) AS Q_PROD_MES,
    		(select Q_DET from temporal..quintil_resultados_nov qr WHERE QR.COD_PERSONAL = BP.COD_PERSONAL ) AS Q_DET_MES,
    --(SELECT FEC_INGRESO FROM H_PLA_PERSONAL HPP WHERE HPP.COD_PERSONAL = BP.COD_PERSONAL) as FECHA_INGRESO,
    --(SELECT  CASE WHEN bp.cod_personal in (select distinct(COD_PERSONAL) from H_PLA_VACACIONES_MES where GETDATE() BETWEEN FEC_INICIO AND FEC_FINAL) then 'SI' end) AS VACACIONES,
    --(SELECT  CASE WHEN bp.cod_personal in (select distinct(COD_PERSONAL) from H_PLA_PERS_DESCANSOS where GETDATE() BETWEEN FEC_INICIO AND FEC_FIN) then 'SI' end) AS DESCANSO
    
    --CUMPLIMIENTO CARTERA>60
    		(SELECT    CAST(CAST(VISITA AS FLOAT)/CAST(CANTIDAD AS FLOAT) AS FLOAT) AS PORCENTAJE
    
    		FROM (
    		SELECT COD_PERSONAL , COUNT (COD_CREDITO) AS CANTIDAD, SUM (CANTIDAD) AS VISITA FROM (
    		SELECT  COD_PERSONAL, COD_CREDITO ,CASE WHEN VISITADO ='SI' THEN 1 ELSE 0 END AS CANTIDAD
    		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
    		and RANGO_MORA in ('(31-44)','(45-60)') ) A GROUP BY COD_PERSONAL) 
    		B WHERE b.cod_personal = bp.cod_personal) AS CUMPLIMIENTO_CARTERA_60,
    
    
    
    
    
    
    --select * from HR_TOTAL where fec_hoja_ruta = '2020-12-12' order by edn
    
    --select distinct (cod_personal) from HR_TOTAL where fec_hoja_ruta = '2020-12-12'
    
    --MORA >1 MONTO
    		(SELECT    SUM40 
    		FROM (
    		--DECLARE @FECHA DATEtime =GETDATE ()-1
    		SELECT COD_PERSONAL , SUM(IMP_CAP_DEUDOR)AS SUM40 FROM (
    		SELECT  COD_PERSONAL,IMP_CAP_DEUDOR
    		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
    		and RANGO_MORA in ('(2-3)','(4-8)','(9-15)','(16-30)','(31-44)','(45-60)') 
    		) A GROUP BY COD_PERSONAL) B WHERE b.cod_personal = bp.cod_personal) AS MM_1,
    
    --MORA >30 MONTO
    		(SELECT    SUM30 
    		FROM (
    		--DECLARE @FECHA DATEtime =GETDATE ()-1
    		SELECT COD_PERSONAL ,SUM(IMP_CAP_DEUDOR) AS SUM30 FROM (
    		SELECT  COD_PERSONAL,IMP_CAP_DEUDOR ,SALDO_TOTAL
    		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
    		and RANGO_MORA in ('(31-44)','(45-60)') 
    		) A GROUP BY COD_PERSONAL) B WHERE b.cod_personal = bp.cod_personal) AS MM_30,
    
    --MORA >1 PORCENTAJE
    
    		(SELECT    PROCENTAJE40
    		FROM (
    		--DECLARE @FECHA DATEtime =GETDATE ()-1
    		SELECT COD_PERSONAL ,((SUM(IMP_CAP_DEUDOR)/SUM(SALDO_TOTAL))*100) AS PROCENTAJE40 FROM (
    		SELECT  COD_PERSONAL,IMP_CAP_DEUDOR ,SALDO_TOTAL
    		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
    		and RANGO_MORA in ('(2-3)','(4-8)','(9-15)','(16-30)','(31-44)','(45-60)') 
    		) A GROUP BY COD_PERSONAL) B WHERE b.cod_personal = bp.cod_personal) AS PM_1,
    
    
    --MORA >30 PORCENTAJE
    
    		(SELECT    PORCENTAJE30 
    		FROM (
    		--DECLARE @FECHA DATEtime =GETDATE ()-1
    		SELECT COD_PERSONAL ,((SUM(IMP_CAP_DEUDOR)/SUM(SALDO_TOTAL))*100) AS PORCENTAJE30 FROM (
    		SELECT  COD_PERSONAL,IMP_CAP_DEUDOR ,SALDO_TOTAL
    		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
    		and RANGO_MORA in ('(31-44)','(45-60)') 
    		) A GROUP BY COD_PERSONAL) B WHERE b.cod_personal = bp.cod_personal) AS PM_30,
    
    
    --CALIDAD CARTERA MORA
    --DATA
    --DECLARE @FECHA DATEtime =GETDATE ()-1
    --select COD_PERSONAL,CALIDAD_CARTERA,COUNT(CALIDAD_CARTERA) AS NRO,SUM(SALDO_CAPITAL) AS SALDO_CAP,SUM(SALDO_TOTAL) as SALDO_TOT,SUM(SALDO_TOTAL)-SUM(SALDO_CAPITAL) as SALDO_DEU,
    --((SUM(IMP_CAP_DEUDOR)/SUM(SALDO_TOTAL))*100) AS MORA1
    
    --from PAGO_VS_GESTION_DIA
    --WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112)
    --GROUP BY CALIDAD_CARTERA, COD_PERSONAL
    
    --SOLICITUDES
    
    		(SELECT SOLICITUDES FROM #SOLICITUDES_HR_TOTAL SHT WHERE SHT.COD_PERSONAL = BP.COD_PERSONAL ) AS SOLICITUDES,
    
    
    --ESTO ES PARA CREAR LA TABLA TEMPORAL Y JALAR LAS BATERIAS PROMEDIADAS
    --DECLARE @FECHA DATEtime =GETDATE ()
    --SELECT distinct(COD_PERSONAL),value,MAX(convert(datetime,timestamp)) AS FECHA
    --FROM [192.168.87.31\SVRMSSQL].MDM.[dbo].[bATTERY_STATUS] vs
    --left join temporal..CELULARES_IMEI ci on  vs.deviceid = ci.DES_IMEI
    --where cod_personal is not null  AND CONVERT(CHAR(10),timestamp,121)  between CONVERT(CHAR(10),@FECHA-10,121) and CONVERT(CHAR(10),@FECHA,121)
    --group by CI.COD_PERSONAL,convert(datetime,timestamp),value
    
    
    		--STORAGE
    				(select MEMORY from #TEMP_MEMORY X 
    				left join temporal..CELULARES_IMEI ci on  X.deviceid = ci.DES_IMEI
    				where CI.COD_PERSONAL = BP.COD_PERSONAL) AS STORAGE,
    
    
    
    --TEMPORAL BATERIAS
    --	IF OBJECT_ID('TEMPDB..#TEMP_BATERIA') IS NOT NULL	
    --		DROP TABLE #TEMP_BATERIA
    
    --select deviceid,AVG(value) AS VALUE
    --INTO #TEMP_BATERIA
    --from (
    --select deviceid,fecha,max(value) as value from(
    --select deviceid,convert(date,timestamp) as fecha,value 
    --from [192.168.87.31\SVRMSSQL].MDM.[dbo].[bATTERY_STATUS]
    --where CONVERT(CHAR(10),convert(date,timestamp),112)  between  CONVERT(CHAR(10),getdate()-3,112) and CONVERT(CHAR(10),getdate()-1,112) 
    --) a group by a.deviceid, a.fecha, a.value
    --)b group by deviceid
    --order by deviceid
    
    
    
    
    
    		--METO TODO DESEMBOLSOS (# Y $)
    
    --CONSULTA DESEMBOLSOS DE LA TEMPORAL
    		(select NRO_DESEMB from #TEMP_DESEMBOLSO X where X.COD_PERSONAL = BP.COD_PERSONAL) as NRO_DESEMB,
    		--CONSULTA DESEMBOLSOS MONTO DE LA TEMPORAL
    		(select MONTO_DESEM from #TEMP_DESEMBOLSO X where X.COD_PERSONAL = BP.COD_PERSONAL) as MONTO_DESEMB,
    
    --BATERIAS
    			(select BATERIA from #TEMP_BATERIA X 
    			left join temporal..CELULARES_IMEI ci on  X.deviceid = ci.DES_IMEI
    			where CI.COD_PERSONAL = BP.COD_PERSONAL) as BATERIA,
    
    
    
    
    
    
    --from H_BASE_PRODUCTIVIDAD bp  WHERE COD_PERSONAL IS NOT NULL AND convert(date,bp.fec_hoja_ruta) =convert(date,@FECHA)  
    
    --ESTADO DEL EDN
    bp.ESTADO AS ESTADO,
    
    
    		--NOTA REGULATORIO
    
    		(SELECT NOTA FROM T_CREDITO..HR_NOTA_REGULATORIO HNR
    		WHERE HNR.COD_PERSONAL = BP.COD_PERSONAL) AS NOTA_REGULATORIO,
    
    		--NOTA NORMATIVO
    		(SELECT NOTA FROM T_CREDITO..HR_NOTA_NORMATIVO HNR
    		WHERE HNR.COD_PERSONAL = BP.COD_PERSONAL) AS NOTA_NORMATIVO,
    
    
    
    
    
    	--MEGAS
    				(select Megas from #TEMP_MEGAS X 
    				left join temporal..CELULARES_IMEI ci on  X.deviceid = ci.DES_IMEI
    				where CI.COD_PERSONAL = BP.COD_PERSONAL) AS MEGAS,
    
    				    --dir_cli
    (select total from #DIR_CLI abc
    where abc.COD_PERSONAL = BP.COD_PERSONAL) AS porc_referenciadas,
    
    
     --tel_valido
    (select valido from #tel_val abc
    where abc.COD_PERSONAL = BP.COD_PERSONAL) AS telf_validos,
    --Gestor de recuperaciones
    GS.CAN_CLI_ASIG, GS.REG_CLI_HR, GS.NO_REG_CLI_HR, GS.NUM_PAGOS, GS.IMP_PAGOS, 
    GS.REG_CLI_HR_MES, GS.NO_REG_CLI_HR_MES, GS.NUM_PAGOS_MES, GS.IMP_PAGOS_MES,
    GS.TOT_CLI_TRANSADO, GS.TRS_VIGENTE, GS.TRS_VENCIDO, GS.TRS_INCUMPLIDO, GS.NUM_TRANSADOS, GS.NUM_TRANSADOS_MES,
    --Abogado interno
    AB.CAN_CLI_ASIG, AB.REG_CLI_APP_JUD, AB.NO_REG_CLI_APP_JUD, AB.NUM_PAGOS, AB.IMP_PAGOS, 
    AB.REG_CLI_APP_JUD_MES, AB.NO_REG_CLI_APP_JUD_MES, AB.NUM_PAGOS_MES, AB.IMP_PAGOS_MES,
    AB.TOT_CLI_TRANSADO, AB.TRS_VIGENTE, AB.TRS_VENCIDO, AB.TRS_INCUMPLIDO, AB.NUM_TRANSADOS, AB.NUM_TRANSADOS_MES,
    
    
      --saldo_capital_actual - en curso
    (select saldo_capital_actual from #credito_cliente_actual abc
    where abc.COD_PERSONAL = BP.COD_PERSONAL) AS SALDO_CAPITAL_EN_CURSO,
    
    				    --nro_clientes actual - en curso
    (select n_credito_actual from #credito_cliente_actual abc
    where abc.COD_PERSONAL = BP.COD_PERSONAL) AS NRO_CLIENTE_EN_CURSO,
    
    				    --saldo_capital_mes_anterior 
    (select SALDO_CAPITAL_Anterior from #credito_cliente_mes_pasado abc
    where abc.COD_PERSONAL = BP.COD_PERSONAL) AS SALDO_CAPITAL_MES_ANTERIOR,
    
    				    --nro_clientes actual - MES ANTERIOR
    (select CLIENTES_anterior from #credito_cliente_mes_pasado abc
    where abc.COD_PERSONAL = BP.COD_PERSONAL) AS NRO_CLIENTE_MES_ANTERIOR,
    
    --select * from #credito_mora_dia_0
    --select * from #credito_mora_dia_0_mes_anterior
    
    				    --saldo_capital_dia 0 mes anterior
    (select saldo_capita_al_dia from #credito_mora_dia_0_mes_anterior abc
    where abc.COD_PERSONAL = BP.COD_PERSONAL) AS SALDO_CAPITAL_MORA_DIA_0_MES_ANTERIOR,
    
    				    --saldo_capital_dia 0 - en curso
    (select saldo_capital_actual_0 from #credito_mora_dia_0 abc
    where abc.COD_PERSONAL = BP.COD_PERSONAL) AS SALDO_CAPITAL_MORA_DIA_0_MES_ACTUAL,
    
    					--EXPEDIENTES
    (select EXPEDIENTE from H_EXPEDIENTE HE
    where HE.COD_PERSONAL = BP.COD_PERSONAL) AS EXPEDIENTE,
    
    
    --CUMPLIMIENTO CARTERA>1
    		(SELECT    CAST(CAST(VISITA AS FLOAT)/CAST(CANTIDAD AS FLOAT) AS FLOAT) AS PORCENTAJE
    
    		FROM (
    		SELECT COD_PERSONAL , COUNT (COD_CREDITO) AS CANTIDAD, SUM (CANTIDAD) AS VISITA FROM (
    		SELECT  COD_PERSONAL, COD_CREDITO ,CASE WHEN VISITADO ='SI' THEN 1 ELSE 0 END AS CANTIDAD
    		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
    		and RANGO_MORA in ('(1)', '(2-3)','(4-8)','(9-15)','(16-30)') ) A GROUP BY COD_PERSONAL) 
    		B WHERE b.cod_personal = bp.cod_personal) AS CUMPLIMIENTO_CARTERA_1,
    
     --dias de vacaciones disponible
    
     '' as  dias_vac_disponible
    --(select disponibles from RENOVACION_HIST aaa
    --where aaa.COD_PERSONAL = BP.COD_PERSONAL) AS dias_vac_disponible
    
    
    from H_BASE_PRODUCTIVIDAD bp
    LEFT JOIN TMP_REP_GESTOR_RECUP GS ON BP.COD_PERSONAL=GS.COD_PERSONAL AND CAST(BP.FEC_HOJA_RUTA AS DATE)=GS.FEC_PROCESO
    LEFT JOIN TMP_REP_ABOG_INTERNO AB ON BP.COD_PERSONAL=AB.COD_PERSONAL AND CAST(BP.FEC_HOJA_RUTA AS DATE)=AB.FEC_PROCESO
    WHERE bp.COD_PERSONAL IS NOT NULL AND convert(date,bp.fec_hoja_ruta) =convert(date,@FECHA)  
    
    
    
    
    --VALIDAR DESEMBOLSOS
    --select sum(monto_desem) from #TEMP_DESEMBOLSO2 where fecha = '2020-12-29'
    --select sum(monto_desemb) from HR_TOTAL where CONVERT(date,fec_hoja_ruta) = '2020-12-29'
    
    
    END
    SET @RETROCESO=@RETROCESO+1
    PRINT @FECHA
    END 
    
    
    

    miércoles, 3 de febrero de 2021 0:53

Todas las respuestas

  • Hola AlexanderC1992:

    Es demasiado "infumable" el procedimiento para poder ayudarte, pero hay muchas cosas que puedes cambiar para mejorar.

    Elimina el código innecesario, solo mancha el procedimiento y te puede llevar a errores.

    Un bucle, no es un buen camino, Tienes que replantear el código para no utilizar ese bucle.

    Utiliza ctes correlativos y no tablas temporales, si el volumen de datos no es brutal. Ya que las consultas que he visto, lo que están es retornando funciones de agrupación, el número de filas será pequeño. Pero si usas tablas temporales porque tienen gran número de filas, indexa.

    Creo que estás utilizando un montón de subconsultas como columnas de la query, y eso no es óptimo. Dudo mucho que el optimizador de consultas, con la query que has puesto, pueda sacar algo de provecho de ellas.

    Si los datos que obtienes de otras bases de datos, e incluso de otros servidores devuelven pocas filas, son mucho más rápidas las variables de tabla que las tablas temporales. La creación de una tabla temporal, lleva de 2 a 3 decimas (en mi opinión), y en función del uso de tempdb. Pero la variable de tabla es instantánea. 

    En resumen, yo haría lo siguiente. Para empezar.

    Crearía todos las variables que necesitase.

    Llenaría estas variables con los datos de los servidores vinculados, etc.

    Haría un cte correlativo para obtener el resto de conjuntos y en la salida lo mezclaría con las variables de tabla.

    Como apoyo, si necesitas generar ese bucle (no está claro del código), para los días del mes, puedes hacer un conjunto que te devuelva ese rango.

    ;With c as (
    	Select T.n
    		From (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
    					 (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
    					 (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),
    					(31)
    			 )T(n)
    		)select * from c 
    Con eso y haciendo un cross join a lo que fuera, ya tienes 31 filas, que por supuesto puedes restringir al día en el que estás. (supongo que es la lógica del bucle).

    Ctes correlativos

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

    Variables de tabla

    https://javifer2.wordpress.com/2019/11/16/variables-de-tabla/

    Por último, utiliza una herramienta para dar formato al código. Puedes personalizarlo y te será mucho más fácil de seguir.

    Ej, con tú procedure y pulsando un simple botón.

    Declare @Retroceso Int;
    
    If Day(GetDate()) = '1' Or Day(GetDate()) = '2'
    Begin
    	Set @Retroceso = -1;
    	Print @Retroceso;
    End;
    Else
    Begin
    	Set @Retroceso = -1;
    End;
    
    While @Retroceso < 0
    Begin
    	Declare @Fecha DateTime = GetDate() + @Retroceso;
    	Print @Fecha;
    Begin
    	--DECLARE @FECHA DATEtime =GETDATE()-1
    	--BORRAR EN CASO SE AUTOMATICE VARIAS VECES AL DIA--delete from H_BASE_PRODUCTIVIDAD where FEC_HOJA_CASTEADA =@FECHA
    	--h_cre_credito creditos en curso data clientes y saldo capital
    	If Object_Id('TEMPDB..#credito_cliente_actual') Is Not Null
    	Begin
    		Drop Table #credito_cliente_actual
    	End;
    	Select Sum(imp_sal_capital) As saldo_capital_actual
    		 , Count(cod_credito) As n_credito_actual
    		 , de.cod_personal
    	Into #credito_cliente_actual
    		   From h_cre_credito As hcc
    				Left Join dim_ejecutivo As de On de.cod_ejecutivo = hcc.cod_ejecutivo
    		   Where cod_prd Not In('663', '660', '661', '662') And tip_situacion <> '07'
    		   Group By de.cod_personal;
    
    	--cuadre cartera data clientes y saldo capital
    	If Object_Id('TEMPDB..#credito_cliente_mes_pasado') Is Not Null
    	Begin
    		Drop Table #credito_cliente_mes_pasado
    	End;
    	Select de.cod_personal
    		 , Sum(imp_sal_capital) As saldo_capital_anterior
    		 , Count(Distinct cod_cli) As clientes_anterior
    	Into #credito_cliente_mes_pasado
    		   From cuadre_cartera As cc
    				Left Join dim_ejecutivo As de On de.des_ejecutivo = cc.[ejecutivo de negocios]
    		   Where año_proce = Year(DateAdd(month, -1, GetDate())) And mes_proce = Month(DateAdd(month, -1, GetDate())) And producto <> 'PROFAE' And producto <> 'PROREACTIVA30' And producto <> 'PROREACTIVA10' And producto <> 'PROREACTIVA300' And f_calidad_cartera_xrm <> '40. CASTIGADOS'
    		   Group By de.cod_personal;
    
    	--creditos mora dia 0 de cre credito
    	--cuadre cartera data clientes y saldo capital
    	If Object_Id('TEMPDB..#credito_mora_dia_0') Is Not Null
    	Begin
    		Drop Table #credito_mora_dia_0
    	End;
    	Select Sum(imp_sal_capital) As saldo_capital_actual_0
    		 , de.cod_personal
    	Into #credito_mora_dia_0
    		   From h_cre_credito As hcc
    				Left Join dim_ejecutivo As de On de.cod_ejecutivo = hcc.cod_ejecutivo
    		   Where f_calidad_cartera = '10. NORMAL (0)' And cod_prd Not In('663', '660', '661', '662') And tip_situacion <> '07'
    		   Group By de.cod_personal;
    
    	--dia 0 cuadre cartera
    	If Object_Id('TEMPDB..#credito_mora_dia_0_mes_anterior') Is Not Null
    	Begin
    		Drop Table #credito_mora_dia_0_mes_anterior
    	End;
    	Select de.cod_personal
    		 , Sum(imp_sal_capital) As saldo_capita_al_dia
    	Into #credito_mora_dia_0_mes_anterior
    		   From cuadre_cartera As cc
    				Left Join dim_ejecutivo As de On de.des_ejecutivo = cc.[ejecutivo de negocios]
    		   Where año_proce = Year(DateAdd(month, -1, GetDate())) And mes_proce = Month(DateAdd(month, -1, GetDate())) And producto <> 'PROFAE' And producto <> 'PROREACTIVA30' And producto <> 'PROREACTIVA10' And producto <> 'PROREACTIVA300' And f_calidad_cartera_xrm = '10. NORMAL (0)' And f_calidad_cartera_xrm <> '40. CASTIGADOS'
    		   Group By de.cod_personal;
    
    	--tabla telefonos
    	If Object_Id('TEMPDB..#tel_val') Is Not Null
    	Begin
    		Drop Table #tel_val
    	End;
    	Select cod_personal
    		 , Sum(Convert(Decimal, validados)) / Count(Convert(Decimal, validados)) As valido
    	Into #tel_val
    		   From (
    				  Select de.cod_personal
    					   , Case
    							 When validado = 'SI' Then 1
    							 When validado = 'SIN REVISAR' Then 0
    						 End As validados
    						 From rel_tlf_cliente_reporte As tel
    							  Left Join dim_ejecutivo As de On tel.des_ejecutivo = de.des_ejecutivo
    				) As a
    		   Group By cod_personal;
    
    	--TABLA %REGISTRO_DIRECCION
    	If Object_Id('TEMPDB..#DIR_CLI') Is Not Null
    	Begin
    		Drop Table #dir_cli
    	End;
    	-- DECLARE @FECHA DATEtime =GETDATE()
    	Select cod_personal
    		 , Sum(Convert(Decimal,
    					   Case
    						   When num_coord_x_ref_dom Is Not Null Or num_coord_x_ref_neg Is Not Null Then 1
    					   Else 0
    					   End)) / Count(Convert(Decimal,
    											 Case
    												 When num_coord_x_ref_dom Is Not Null Or num_coord_x_ref_neg Is Not Null Then 1
    											 Else 0
    											 End)) As total
    	Into #dir_cli
    		   From dir_referenciada As dr
    				Left Join dim_ejecutivo As de On dr.des_ejecutivo = de.des_ejecutivo
    		   Where Convert(Date, Convert(Date, fec_proceso, 105)) = Convert(Date, GetDate(), 105)
    		   Group By cod_personal;
    
    	--TABLA MEGAS
    	If Object_Id('TEMPDB..#TEMP_MEGAS') Is Not Null
    	Begin
    		Drop Table #temp_megas
    	End;
    	Select deviceid
    		 , (Sum(Convert(Decimal, bytesdownloaded)) + Sum(Convert(Decimal, bytesuploaded))) / 1000000 As megas
    	Into #temp_megas
    		   From [192.168.87.31\svrmssql].mdm.dbo.traffic_celular
    		   Where Convert(Char(10), Convert(Date, timestamp), 112) = Convert(Char(10), @Fecha - 1, 112)
    		   Group By deviceid;
    
    	--TABLA STORAGE
    	If Object_Id('TEMPDB..#TEMP_MEMORY') Is Not Null
    	Begin
    		Drop Table #temp_memory
    	End;
    	Select Distinct 
    		   deviceid
    		 , Convert(Decimal, availablememory) / Convert(Decimal, totalmemory) As memory
    	Into #temp_memory
    		   From [192.168.87.31\svrmssql].mdm.dbo.memory
    		   Where Convert(Char(10), fecha, 112) = Convert(Char(10), @Fecha, 112)
    		   Order By memory;
    
    	--TABLA DESEMBOLSOS TEMPORALES
    	If Object_Id('TEMPDB..#TEMP_DESEMBOLSO') Is Not Null
    	Begin
    		Drop Table #temp_desembolso
    	End;
    	--	DECLARE @FECHA DATEtime =GETDATE ()-1
    	Select Count(monto_desembolso) As nro_desemb
    		 , Sum(monto_desembolso) As monto_desem
    		 , fecha
    		 , d.des_ejecutivo
    		 , w.cod_personal
    	Into #temp_desembolso
    		   From fact_desembolso As a
    				Left Join dim_tiempo As b On a.id_tiempo = b.idtiempo
    				Left Join dim_credito As c On c.id_credito = a.id_credito
    				Left Join dim_ejecutivo As d On d.id_ejecutivo = a.id_ejecutivo
    				Left Join dim_ejecutivo As w On a.id_ejecutivo = w.id_ejecutivo
    		   Where Convert(Date, fecha) = Convert(Date, @Fecha)
    		   Group By w.cod_personal
    				  , d.des_ejecutivo
    				  , fecha;
    
    	--TABLA BATERIAS
    	--DECLARE @FECHA DATEtime =GETDATE ()
    	If Object_Id('TEMPDB..#TEMP_BATERIA') Is Not Null
    	Begin
    		Drop Table #temp_bateria
    	End;
    	Select deviceid
    		 , fecha
    		 , value As bateria
    	Into #temp_bateria
    		   From (
    				  Select Row_Number() Over(Partition By deviceid
    						 Order By fecha Desc) As orden
    					   , *
    						 From (
    								Select deviceid
    									 , Convert(DateTime, timestamp) As fecha
    									 , value
    									   From [192.168.87.31\svrmssql].mdm.dbo.battery_status
    									   Where Convert(Char(10), Convert(Date, timestamp), 112) = Convert(Char(10), @Fecha, 112)
    							  ) As a
    				) As b
    		   Where b.orden = '1';
    
    	--CONSULTA LICENCIAS ORACLE (SQL ESTA MAL)
    
    	If Object_Id('TEMPDB..#LICENCIAS_HR_TOTAL') Is Not Null
    	Begin
    		Drop Table #licencias_hr_total
    	End;
    	Select r.cod_personal
    		 , r.fec_inicio
    		 , r.fec_fin
    	Into #licencias_hr_total
    		   From prod..cactus_integral.pla_subs_reg_desc As r
    		   Where r.cod_empresa = '0001' And @Fecha Between fec_inicio And ISNULL(fec_fin, Convert(DateTime, DateAdd(MONTH, 1, concat(Year(@Fecha), '-', Month(@Fecha), '-', '01')) - 1))
    		   Order By 1
    				  , 3;
    
    	--CONSULTA FERIADOS
    	--select * from #FERIADO
    
    	If Object_Id('TEMPDB..#FERIADO') Is Not Null
    	Begin
    		Drop Table #feriado
    	End;
    
    	--DECLARE @FECHA DATEtime =GETDATE ()-5
    	Select fecha
    		 , 'FERIADO' As feriado
    	Into #feriado
    		   From dim_feriado
    		   Where Convert(Date, @Fecha) = Convert(Date, fecha);
    
    	--CONSULTA VACACIONES ORACLE (SQL ESTA MAL)
    	If Object_Id('TEMPDB..#VACACIONES_HR_TOTAL') Is Not Null
    	Begin
    		Drop Table #vacaciones_hr_total
    	End;
    
    	--DECLARE @FECHA DATE =GETDATE ()
    
    	Select cod_personal
    		 , fec_inicio
    		 , fec_final
    	Into #vacaciones_hr_total
    		   From prod..cactus_integral.pla_vacaciones_mes
    		   Where cod_empresa = '0001' And @Fecha Between fec_inicio And ISNULL(fec_final, Convert(DateTime, DateAdd(MONTH, 1, concat(Year(@Fecha), '-', Month(@Fecha), '-', '01')) - 1));
    
    	--CONSULTA SOLICITUDES
    	If Object_Id('TEMPDB..#SOLICITUDES_HR_TOTAL') Is Not Null
    	Begin
    		Drop Table #solicitudes_hr_total
    	End;
    	Select (
    			 Select cod_personal
    					From dim_ejecutivo As cen
    					Where cen.cod_ejecutivo = soli.cod_ejecutivo
    		   ) As cod_personal
    		 , Count(cod_pro_cre) As solicitudes
    	Into #solicitudes_hr_total
    		   From prod..cactus_safi.cre_pro_cre As soli
    		   Where cod_empresa = '0001' And fec_registro = Convert(Date, @Fecha)
    		   Group By cod_ejecutivo;
    
    	--	DECLARE @FECHA DATEtime =GETDATE ()-1
    	--CONSULTA RECUPERACIONES
    	If Object_Id('TEMPDB..#MONTO_RECUPERADO') Is Not Null
    	Begin
    		Drop Table #monto_recuperado
    	End;
    	Select Sum(imp_recup_total) As recuperado
    		 , cod_personal
    		 , dia_proceso
    	Into #monto_recuperado
    		   From pago_vs_gestion_dia
    		   Where rango_mora In('(2-3)', '(4-8)', '(9-15)', '(16-30)', '(31-44)', '(45-60)') And Convert(Char(10), @Fecha, 112) = dia_proceso
    		   Group By cod_personal
    				  , dia_proceso;
    
    	--ELIMINA LA DATA PARA REPROCESAR
    	Delete From hr_total
    	Where Convert(Date, fec_hoja_ruta) = Convert(Date, @Fecha);
    
    	--DECLARE @FECHA DATEtime =GETDATE ()-1
    	Insert Into hr_total
    	--DECLARE @FECHA DATEtime =GETDATE ()-1
    
    	Select edn
    		 , des_cargo_planilla As des_cargo_planilla
    		 , bp.cod_personal As cod_personal
    		 , Try_Convert( DateTime, bp.fec_hoja_ruta) As fec_hoja_ruta
    		 , encargatura As encargatura
    		 , bp.region As region
    		 , Convert(VarChar, bp.agencia, 200) As agencia
    		 , prospección As prospeccion
    		 , fidelización As fidelizacion
    		 , evaluación As evaluacion
    		 , recuperación As recuperacion
    		 , [rec. comisión] As rec_comision
    		 , [gestiones administrativas] As gestion_adm
    		 , [visita judicial] As visita_judicial
    		 , preventiva As preventiva
    		 , acompañamiento As acompanamiento
    		 , [post-desembolso] As post_desembolso
    		 , Convert(DateTime, bp.fec_ingreso, 105) As fec_ingreso
    		 , cargo As cargo
    		 , cantidad_llamadas As cantidad_llamadas
    		 , meta As meta
    		 , ndiasemana As ndiasemana
    		 , meta_real As meta_real
    		 , licencia As licencia
    		 , vacaciones As vacaciones
    		 , descansos As descansos
    		 , [prospección valida] As prosp_valida
    		 , [fidelización valida] As fid_valida
    		 , [evaluación valida] As eval_valida
    		 , [recuperación valida] As rec_valida
    		 , [rec. comisión valida] As rec_comision_valida
    		 , cumplimiento As cumplimiento
    		 , [visita judicial valida] As visita_judicial_valida
    		 , [post desembolso valido] As post_desembolso_valido
    		 , [acompanamiento valido] As acompanamiento_valido
    		 , [preventiva valida] As preventiva_valida
    
    		   -- FERIADO
    		 , (
    			 Select 'FERIADOS' As feriado
    					From #feriado As fer
    					Where bp.fec_hoja_ruta = fer.fecha
    		   ) As feriados_filtro
    
    		   -- VACACIONES
    		 , (
    			 Select 'VACACIONES' As vacaciones
    					From #vacaciones_hr_total As fer
    					Where bp.cod_personal = fer.cod_personal And bp.fec_hoja_ruta Between fer.fec_inicio And fer.fec_final
    		   ) As vacaciones_filtro
    
    		   --LICENCIAS
    		 , (
    			 Select 'LICENCIAS' As licencias
    					From #licencias_hr_total As fer
    					Where bp.cod_personal = fer.cod_personal And bp.fec_hoja_ruta Between fer.fec_inicio And fer.fec_fin
    		   ) As licencias_filtro
    
    		   --MONTO RECUPERADO
    		 , (
    			 Select recuperado
    					From #monto_recuperado As fer
    					Where bp.cod_personal = fer.cod_personal
    		   ) As recuperaciones
    
    		   --QUINTILES
    		 , (
    			 Select q_prod
    					From temporal..quintil_resultados_nov As qr
    					Where qr.cod_personal = bp.cod_personal
    		   ) As q_prod_mes
    		 , (
    			 Select q_det
    					From temporal..quintil_resultados_nov As qr
    					Where qr.cod_personal = bp.cod_personal
    		   ) As q_det_mes
    		   --(SELECT FEC_INGRESO FROM H_PLA_PERSONAL HPP WHERE HPP.COD_PERSONAL = BP.COD_PERSONAL) as FECHA_INGRESO,
    		   --(SELECT  CASE WHEN bp.cod_personal in (select distinct(COD_PERSONAL) from H_PLA_VACACIONES_MES where GETDATE() BETWEEN FEC_INICIO AND FEC_FINAL) then 'SI' end) AS VACACIONES,
    		   --(SELECT  CASE WHEN bp.cod_personal in (select distinct(COD_PERSONAL) from H_PLA_PERS_DESCANSOS where GETDATE() BETWEEN FEC_INICIO AND FEC_FIN) then 'SI' end) AS DESCANSO
    		   --CUMPLIMIENTO CARTERA>60
    		 , (
    			 Select Cast(Cast(visita As Float) / Cast(cantidad As Float) As Float) As porcentaje
    					From (
    						   Select cod_personal
    								, Count(cod_credito) As cantidad
    								, Sum(cantidad) As visita
    								  From (
    										 Select cod_personal
    											  , cod_credito
    											  , Case
    													When visitado = 'SI' Then 1
    												Else 0
    												End As cantidad
    												From pago_vs_gestion_dia
    												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(31-44)', '(45-60)')
    									   ) As a
    								  Group By cod_personal
    						 ) As b
    					Where b.cod_personal = bp.cod_personal
    		   ) As cumplimiento_cartera_60
    
    	--select * from HR_TOTAL where fec_hoja_ruta = '2020-12-12' order by edn
    	--select distinct (cod_personal) from HR_TOTAL where fec_hoja_ruta = '2020-12-12'
    	--MORA >1 MONTO
    		 , (
    			 Select sum40
    					From (
    						   --DECLARE @FECHA DATEtime =GETDATE ()-1
    						   Select cod_personal
    								, Sum(imp_cap_deudor) As sum40
    								  From (
    										 Select cod_personal
    											  , imp_cap_deudor
    												From pago_vs_gestion_dia
    												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(2-3)', '(4-8)', '(9-15)', '(16-30)', '(31-44)', '(45-60)')
    									   ) As a
    								  Group By cod_personal
    						 ) As b
    					Where b.cod_personal = bp.cod_personal
    		   ) As mm_1
    
    	--MORA >30 MONTO
    		 , (
    			 Select sum30
    					From (
    						   --DECLARE @FECHA DATEtime =GETDATE ()-1
    						   Select cod_personal
    								, Sum(imp_cap_deudor) As sum30
    								  From (
    										 Select cod_personal
    											  , imp_cap_deudor
    											  , saldo_total
    												From pago_vs_gestion_dia
    												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(31-44)', '(45-60)')
    									   ) As a
    								  Group By cod_personal
    						 ) As b
    					Where b.cod_personal = bp.cod_personal
    		   ) As mm_30
    
    	--MORA >1 PORCENTAJE
    		 , (
    			 Select procentaje40
    					From (
    						   --DECLARE @FECHA DATEtime =GETDATE ()-1
    						   Select cod_personal
    								, (Sum(imp_cap_deudor) / Sum(saldo_total)) * 100 As procentaje40
    								  From (
    										 Select cod_personal
    											  , imp_cap_deudor
    											  , saldo_total
    												From pago_vs_gestion_dia
    												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(2-3)', '(4-8)', '(9-15)', '(16-30)', '(31-44)', '(45-60)')
    									   ) As a
    								  Group By cod_personal
    						 ) As b
    					Where b.cod_personal = bp.cod_personal
    		   ) As pm_1
    
    	--MORA >30 PORCENTAJE
    		 , (
    			 Select porcentaje30
    					From (
    						   --DECLARE @FECHA DATEtime =GETDATE ()-1
    						   Select cod_personal
    								, (Sum(imp_cap_deudor) / Sum(saldo_total)) * 100 As porcentaje30
    								  From (
    										 Select cod_personal
    											  , imp_cap_deudor
    											  , saldo_total
    												From pago_vs_gestion_dia
    												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(31-44)', '(45-60)')
    									   ) As a
    								  Group By cod_personal
    						 ) As b
    					Where b.cod_personal = bp.cod_personal
    		   ) As pm_30
    
    	--CALIDAD CARTERA MORA
    	--DATA
    	--DECLARE @FECHA DATEtime =GETDATE ()-1
    	--select COD_PERSONAL,CALIDAD_CARTERA,COUNT(CALIDAD_CARTERA) AS NRO,SUM(SALDO_CAPITAL) AS SALDO_CAP,SUM(SALDO_TOTAL) as SALDO_TOT,SUM(SALDO_TOTAL)-SUM(SALDO_CAPITAL) as SALDO_DEU,
    	--((SUM(IMP_CAP_DEUDOR)/SUM(SALDO_TOTAL))*100) AS MORA1
    	--from PAGO_VS_GESTION_DIA
    	--WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112)
    	--GROUP BY CALIDAD_CARTERA, COD_PERSONAL
    	--SOLICITUDES
    		 , (
    			 Select solicitudes
    					From #solicitudes_hr_total As sht
    					Where sht.cod_personal = bp.cod_personal
    		   ) As solicitudes
    
    	--ESTO ES PARA CREAR LA TABLA TEMPORAL Y JALAR LAS BATERIAS PROMEDIADAS
    	--DECLARE @FECHA DATEtime =GETDATE ()
    	--SELECT distinct(COD_PERSONAL),value,MAX(convert(datetime,timestamp)) AS FECHA
    	--FROM [192.168.87.31\SVRMSSQL].MDM.[dbo].[bATTERY_STATUS] vs
    	--left join temporal..CELULARES_IMEI ci on  vs.deviceid = ci.DES_IMEI
    	--where cod_personal is not null  AND CONVERT(CHAR(10),timestamp,121)  between CONVERT(CHAR(10),@FECHA-10,121) and CONVERT(CHAR(10),@FECHA,121)
    	--group by CI.COD_PERSONAL,convert(datetime,timestamp),value
    	--STORAGE
    		 , (
    			 Select memory
    					From #temp_memory As x
    						 Left Join temporal..celulares_imei As ci On x.deviceid = ci.des_imei
    					Where ci.cod_personal = bp.cod_personal
    		   ) As storage
    
    		   --TEMPORAL BATERIAS
    		   --	IF OBJECT_ID('TEMPDB..#TEMP_BATERIA') IS NOT NULL	
    		   --		DROP TABLE #TEMP_BATERIA
    		   --select deviceid,AVG(value) AS VALUE
    		   --INTO #TEMP_BATERIA
    		   --from (
    		   --select deviceid,fecha,max(value) as value from(
    		   --select deviceid,convert(date,timestamp) as fecha,value 
    		   --from [192.168.87.31\SVRMSSQL].MDM.[dbo].[bATTERY_STATUS]
    		   --where CONVERT(CHAR(10),convert(date,timestamp),112)  between  CONVERT(CHAR(10),getdate()-3,112) and CONVERT(CHAR(10),getdate()-1,112) 
    		   --) a group by a.deviceid, a.fecha, a.value
    		   --)b group by deviceid
    		   --order by deviceid
    		   --METO TODO DESEMBOLSOS (# Y $)
    		   --CONSULTA DESEMBOLSOS DE LA TEMPORAL
    		 , (
    			 Select nro_desemb
    					From #temp_desembolso As x
    					Where x.cod_personal = bp.cod_personal
    		   ) As nro_desemb
    		   --CONSULTA DESEMBOLSOS MONTO DE LA TEMPORAL
    		 , (
    			 Select monto_desem
    					From #temp_desembolso As x
    					Where x.cod_personal = bp.cod_personal
    		   ) As monto_desemb
    
    		   --BATERIAS
    		 , (
    			 Select bateria
    					From #temp_bateria As x
    						 Left Join temporal..celulares_imei As ci On x.deviceid = ci.des_imei
    					Where ci.cod_personal = bp.cod_personal
    		   ) As bateria
    
    		   --from H_BASE_PRODUCTIVIDAD bp  WHERE COD_PERSONAL IS NOT NULL AND convert(date,bp.fec_hoja_ruta) =convert(date,@FECHA)  
    		   --ESTADO DEL EDN
    		 , bp.estado As estado
    
    		   --NOTA REGULATORIO
    		 , (
    			 Select nota
    					From t_credito..hr_nota_regulatorio As hnr
    					Where hnr.cod_personal = bp.cod_personal
    		   ) As nota_regulatorio
    
    		   --NOTA NORMATIVO
    		 , (
    			 Select nota
    					From t_credito..hr_nota_normativo As hnr
    					Where hnr.cod_personal = bp.cod_personal
    		   ) As nota_normativo
    
    		   --MEGAS
    		 , (
    			 Select megas
    					From #temp_megas As x
    						 Left Join temporal..celulares_imei As ci On x.deviceid = ci.des_imei
    					Where ci.cod_personal = bp.cod_personal
    		   ) As megas
    
    		   --dir_cli
    		 , (
    			 Select total
    					From #dir_cli As abc
    					Where abc.cod_personal = bp.cod_personal
    		   ) As porc_referenciadas
    
    		   --tel_valido
    		 , (
    			 Select valido
    					From #tel_val As abc
    					Where abc.cod_personal = bp.cod_personal
    		   ) As telf_validos
    		   --Gestor de recuperaciones
    		 , gs.can_cli_asig
    		 , gs.reg_cli_hr
    		 , gs.no_reg_cli_hr
    		 , gs.num_pagos
    		 , gs.imp_pagos
    		 , gs.reg_cli_hr_mes
    		 , gs.no_reg_cli_hr_mes
    		 , gs.num_pagos_mes
    		 , gs.imp_pagos_mes
    		 , gs.tot_cli_transado
    		 , gs.trs_vigente
    		 , gs.trs_vencido
    		 , gs.trs_incumplido
    		 , gs.num_transados
    		 , gs.num_transados_mes
    		   --Abogado interno
    		 , ab.can_cli_asig
    		 , ab.reg_cli_app_jud
    		 , ab.no_reg_cli_app_jud
    		 , ab.num_pagos
    		 , ab.imp_pagos
    		 , ab.reg_cli_app_jud_mes
    		 , ab.no_reg_cli_app_jud_mes
    		 , ab.num_pagos_mes
    		 , ab.imp_pagos_mes
    		 , ab.tot_cli_transado
    		 , ab.trs_vigente
    		 , ab.trs_vencido
    		 , ab.trs_incumplido
    		 , ab.num_transados
    		 , ab.num_transados_mes
    
    		   --saldo_capital_actual - en curso
    		 , (
    			 Select saldo_capital_actual
    					From #credito_cliente_actual As abc
    					Where abc.cod_personal = bp.cod_personal
    		   ) As saldo_capital_en_curso
    
    		   --nro_clientes actual - en curso
    		 , (
    			 Select n_credito_actual
    					From #credito_cliente_actual As abc
    					Where abc.cod_personal = bp.cod_personal
    		   ) As nro_cliente_en_curso
    
    		   --saldo_capital_mes_anterior 
    		 , (
    			 Select saldo_capital_anterior
    					From #credito_cliente_mes_pasado As abc
    					Where abc.cod_personal = bp.cod_personal
    		   ) As saldo_capital_mes_anterior
    
    		   --nro_clientes actual - MES ANTERIOR
    		 , (
    			 Select clientes_anterior
    					From #credito_cliente_mes_pasado As abc
    					Where abc.cod_personal = bp.cod_personal
    		   ) As nro_cliente_mes_anterior
    
    		   --select * from #credito_mora_dia_0
    		   --select * from #credito_mora_dia_0_mes_anterior
    		   --saldo_capital_dia 0 mes anterior
    		 , (
    			 Select saldo_capita_al_dia
    					From #credito_mora_dia_0_mes_anterior As abc
    					Where abc.cod_personal = bp.cod_personal
    		   ) As saldo_capital_mora_dia_0_mes_anterior
    
    		   --saldo_capital_dia 0 - en curso
    		 , (
    			 Select saldo_capital_actual_0
    					From #credito_mora_dia_0 As abc
    					Where abc.cod_personal = bp.cod_personal
    		   ) As saldo_capital_mora_dia_0_mes_actual
    
    		   --EXPEDIENTES
    		 , (
    			 Select expediente
    					From h_expediente As he
    					Where he.cod_personal = bp.cod_personal
    		   ) As expediente
    
    		   --CUMPLIMIENTO CARTERA>1
    		 , (
    			 Select Cast(Cast(visita As Float) / Cast(cantidad As Float) As Float) As porcentaje
    					From (
    						   Select cod_personal
    								, Count(cod_credito) As cantidad
    								, Sum(cantidad) As visita
    								  From (
    										 Select cod_personal
    											  , cod_credito
    											  , Case
    													When visitado = 'SI' Then 1
    												Else 0
    												End As cantidad
    												From pago_vs_gestion_dia
    												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(1)', '(2-3)', '(4-8)', '(9-15)', '(16-30)')
    									   ) As a
    								  Group By cod_personal
    						 ) As b
    					Where b.cod_personal = bp.cod_personal
    		   ) As cumplimiento_cartera_1
    
    		   --dias de vacaciones disponible
    		 , '' As dias_vac_disponible
    	--(select disponibles from RENOVACION_HIST aaa
    	--where aaa.COD_PERSONAL = BP.COD_PERSONAL) AS dias_vac_disponible
    
    		   From h_base_productividad As bp
    				Left Join tmp_rep_gestor_recup As gs On bp.cod_personal = gs.cod_personal And Cast(bp.fec_hoja_ruta As Date) = gs.fec_proceso
    				Left Join tmp_rep_abog_interno As ab On bp.cod_personal = ab.cod_personal And Cast(bp.fec_hoja_ruta As Date) = ab.fec_proceso
    		   Where bp.cod_personal Is Not Null And Convert(Date, bp.fec_hoja_ruta) = Convert(Date, @Fecha);
    
    	--VALIDAR DESEMBOLSOS
    	--select sum(monto_desem) from #TEMP_DESEMBOLSO2 where fecha = '2020-12-29'
    	--select sum(monto_desemb) from HR_TOTAL where CONVERT(date,fec_hoja_ruta) = '2020-12-29'
    
    End;
    	Set @Retroceso = @Retroceso + 1;
    	Print @Fecha;
    End;


    miércoles, 3 de febrero de 2021 6:48
  • Otra cosa, para poder optimizar hay que entrar más en detalle en cada una de las consultas. Divide y vencerás.

    Por ejemplo esos not in. Aquí tienes una posible mejora.

    https://www.sqlshack.com/t-sql-commands-performance-comparison-not-vs-not-exists-vs-left-join-vs-except/

    Ojo que tienes que analizar el plan de ejecución de ellos, porque no sabemos ni tú motor ni el plan que se ha generado para cada consulta.

    También hay muchos distinct y group by e incluso ambos, que no se podrían realizar un poco más óptimos.

    Suerte te queda trabajo por delante.

    miércoles, 3 de febrero de 2021 7:09