Number of business days within a given period

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
Rangel
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Wed, 02 May 2007 3:24 pm
Location: Rio de Janeiro

Hello everyone, I need a function or procedure that calculates the number of business days in a given period.

Cordially,
Diego.
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother, below follow an example.

Important note: I did not compete in this function the holidays. To do this, you would have to have the registration of them and add in this Function.

Select all

 
DECLARE  
   
  V_DATA_INI DATE            := TO_DATE('01/05/2007','dd/mm/rrrr'); 
  V_DATA_FIM DATE            := TO_DATE('02/05/2007','dd/mm/rrrr'); 
  V_QT_DIAS_UTEIS NUMBER(10) := 0; 
   
	FUNCTION FUN_DIAS_UTEIS (P_DATA_INICIAL IN DATE,  
	                         P_DATA_FINAL   IN DATE) 
	RETURN NUMBER                           
	 
	IS                          
	 
	  v_dia_semana NUMBER(10) := 0; 
	  v_dias_uteis NUMBER(10) := 0; 
	   
	BEGIN 
	 
	  IF to_number(TO_char( P_DATA_INICIAL , 'D' )) NOT IN (1,7) 
	  THEN  
	     v_dias_uteis := 1; -- Conto a própria data inicial, se não for sábado ou domingo 
	  END IF; 
	 
	  FOR x in 1..( P_DATA_FINAL - P_DATA_INICIAL ) 
	  LOOP 
	 
	     v_dia_Semana := to_number(TO_char( P_DATA_INICIAL + x, 'D' )) ; 
	      
	     IF v_dia_semana NOT IN (1,7) -- 1,7 não somo sábado e domingo. 
	     THEN  
	        v_dias_uteis := v_dias_uteis + 1; 
	     END IF; 
	      
	  END LOOP; 
	   
	  RETURN v_dias_uteis ; 
	EXCEPTION  
	  WHEN OTHERS THEN  
	    raise_application_error(-20001,'Erro ao selecionar dias úteis: ' || SQLERRM );   
	END FUN_DIAS_UTEIS; 
 
BEGIN 
   V_QT_DIAS_UTEIS := FUN_DIAS_UTEIS (V_DATA_INI, V_DATA_FIM); 
   dbms_output.put_line('Qtd. Dias Úteis: ' || V_QT_DIAS_UTEIS); 
END; 
 
I hope I have helped.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 15 guests