cálculo entre duas data considerando apenas os dias de seman

[Tinho]

Preciso efetuar um cálculo entre duas data considerando apenas os dias de semana, exemplo:

Saber o número de dias úteis entre

01-maio-2010 a 30-may-2010

Na verdade o que eu preciso é calcular o número de dias entre uma data inicial e uma data final para saber qual foi o tempo pendente entre uma tarefa e outra. Portanto, não necessariamente os períodos pertencerão a o mesmo.

Grato.

[rogenaro]

Costumo realizar isso gerando n+1 linhas, referentes à diferença entre as datas fim e início:

Selecionar tudo

select level from dual connect by level <= n+1, select * from all_objects where rownum <= n+1

, função pipelined para gerar linhas, etc.
E em seguida somo o rownum à data início.. com isso você tem todos os dias entre a data inicio e fim (inclusive).
Então, basta colocar no where o que você pretende filtrar (excluir sábados e domingos, excluir feriados, apenas segundas-feiras, etc etc), e você terá todas as datas que preenchem seus critérios.
Por fim, é só adicionar um count para retornar o número de linhas (dias) que satisfazem suas condições:

Selecionar tudo

select count(1) from
(
  select dia, to_char(dia, 'D') dia_semana
  from
  (
    select &dat_ini + level - 1 dia
    from   dual
    connect by level < &dat_fim - &dat_ini + 1
  ) d
)
where  dia_semana not in ( '1', '7' )

[Tinho]

Caro rogenaro, agradeço a atenção.

Realmente parece ser uma boa solução, mas primeiro vou ter que dar uma estudada pois este método utilizado está além dos meus conhecimentos iniciais em oracle (pl/sql). Se puder me ajudar, por exemplo, desconheço as funcionalidades do "connect by" e do pipelined.

At.,

[rogenaro]

Aqui neste link tem alguns exemplos de algumas técnicas para geração de linhas, que seria a base de tudo: http://www.orafaq.com/wiki/Oracle_Row_G ... Techniques
Acho que fica mais fácil se visualizarmos apenas a parte mais interna da query:

Selecionar tudo

    -- Usando a tabela dual com connect by level (funciona na versão 10g em diante)
    select &ini + level - 1 dia 
    from   dual 
    connect by level <= &fim - &ini + 1 

Se usarmos, por exemplo, &ini = to_date('01/01/2010', 'dd/mm/yyyy') e &fim = to_date('10/01/2010', 'dd/mm/yyyy'), obtemos o seguinte:

Selecionar tudo

DIA
---------
01-JAN-10
02-JAN-10
03-JAN-10
04-JAN-10
05-JAN-10
06-JAN-10
07-JAN-10
08-JAN-10
09-JAN-10
10-JAN-10

10 rows selected.

Tendo cada data em uma linha, basta aplicar a função to_char(<data>, 'D') para saber qual o dia da semana (1 é domingo, 2 é segunda-feira, etc) e filtrar o que você precisa.

Outras formas de gerar as linhas acima seriam:

Selecionar tudo

    -- Usando rownum em uma tabela qualquer com um número suficiente de registros (funciona em qualquer versão não pré-histórica do banco)

    select &ini + rownum - 1 dia 
    from   all_objects -- all_objects ou qualquer tabela sua com linhas suficientes
    where rownum <= &fim - &ini + 1 

Selecionar tudo

-- Usando uma função pipelined (funciona a partir da versão 9i)

-- Criamos um novo tipo na base de dados
create or replace type number_table as table of number;

-- Criamos a função que retorna o tipo criado
create or replace function gera_linhas
( n in number
) return number_table deterministic pipelined as
begin
  if n < 1 then
    return;
  end if;

  for x in 1..n loop
    pipe row(x);
  end loop;

  return;
end gera_linhas;

-- A consulta em si
select &ini + column_value - 1 
from  table( gera_linhas( &fim - &ini + 1 ) )

Pessoalmente, prefiro usar o connect by quando possível, já que não envolve a criação de novos objetos no banco (a não ser que eles já estejam lá... nesse caso tanto faz), e não é necessário se preocupar com o tamanho da tabela usada como base para a geração das linhas (embora não seja muito viável usar os métodos que expliquei caso você precise calcular a diferença entre duas datas *muito* distantes...)

[Tinho]

Maravilha. Vou estudar e em breve chegarei em uma solução ideal e adequada, dai quando der eu posto aqui.

Valeu!

[fsitja]

Se você precisar considerar apenas dias úteis será necessário utilizar uma tabela de feriados.

[Tinho]

Ok obrigado, vou me informar para ver se eu encontro uma, ou monto eu mesmo.

Grato.

[Tinho]

Estava pensando, o único inconveniente desta tabela é que a cada virada de ano, terei que atualizá-la. Ou não?

At.,

[fsitja]

Alguns feriados sim. Você poderia ter uma tabela abaixo:

Selecionar tudo

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 
Connected as FSITJA
 
SQL> 
SQL> create table tb_feriado (cd_feriado  number(10) primary key,
  2                           dsc_feriado varchar2(100),
  3                           dia         integer not null,
  4                           mês         integer not null check (mês between 1 and 12),
  5                           ano         integer);
 
Table created
SQL> create or replace trigger valida_feriado after insert or update on tb_feriado
  2  for each row
  3  declare
  4    v_dia varchar2(2) := to_char(:new.dia, 'FM00');
  5    v_mês varchar2(2) := to_char(:new.mês, 'FM00');
  6    v_ano varchar2(4) := to_char(nvl(:new.ano, to_char(sysdate, 'YYYY')), 'FM0000');
  7    v_feriado date;
  8  begin
  9    v_feriado := to_date(v_ano || v_mês || v_dia, 'YYYYMMDD');
 10  exception
 11    when others then
 12      raise_application_error(-20000, 'Erro inserindo feriado: Data inválida '
 13                                      || v_dia || '/' || v_mês || '/' || v_ano);
 14  end;
 15  /
 
Trigger created
SQL> insert into tb_feriado values (1, 'PROCLAMAÇÃO DA REPÚBLICA', 15, 11, null);
 
1 row inserted
SQL> insert into tb_feriado values (2, 'MEU FERIADO MALUCO', 30, 02, null);
 
insert into tb_feriado values (2, 'MEU FERIADO MALUCO', 30, 02, null)
 
ORA-20000: Erro inserindo feriado: Data inválida 30/02/2010
ORA-06512: at "FSITJA.VALIDA_FERIADO", line 10
ORA-04088: error during execution of trigger 'FSITJA.VALIDA_FERIADO'
SQL> insert into tb_feriado values (3, 'CARNAVAL', 16, 02, 2010);
 
1 row inserted
 

Lá teria feriados fixos, tipo Natal (25/12), Dia do Trabalho (01/05), Independência (07/09) etc. e também os que mudam de data.

[Marcos Geraldelli]

Boa tarde,

Realmente essa script funciona perfeitamente, porém uma dúvida.

Como seria feito o script para buscar a data fim quando se tem apenas a data inicial e a quantidade de dias uteis a contar?

Exemplo:
Contar 5 dias uteis a partir de 02/02/2021.


Obrigado.