Quantity of days between two dates without considering SAB and gift

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
51011934
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Thu, 12 Mar 2009 4:53 pm
Location: PORTO ALEGRE
Diego Bernardes

Personnel,

I have a table with multiple records, each record has a date x. I need to calculate sysdate - Datax, but you have to bring the amount disregarding Sat (7) and gift (1).

Would anyone know how to help me?
Thanks in advance
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

I had the following idea:

Select all

select dt, to_char(dt,'d') 
from 
  (select trunc(sysdate+1)-level dt 
  from dual connect by level <=1000 
  ) x 
where dt >= '01-jan-2009'   
  and dt <= '20-jan-2009' 
  and to_char(dt,'d') not in (1,7)
See, he will get every day from Monday to Friday, between 01-Jan and 20-Jan: (from there Just make a count).

Select all

SQL> select dt, to_char(dt,'d') 
  2  from 
  3    (select trunc(sysdate)-level dt 
  4    from dual connect by level <=1000 
  5    ) x 
  6  where dt >= '01-jan-2009'   
  7    and dt <= '20-jan-2009' 
  8    and to_char(dt,'d') not in (1,7) 
  9  / 
 
DT        T 
--------- - 
20-JAN-09 3 
19-JAN-09 2 
16-JAN-09 6 
15-JAN-09 5 
14-JAN-09 4 
13-JAN-09 3 
12-JAN-09 2 
09-JAN-09 6 
08-JAN-09 5 
07-JAN-09 4 
06-JAN-09 3 
05-JAN-09 2 
02-JAN-09 6 
01-JAN-09 5 
 
14 rows selected. 
 
SQL> 
I do not know if this is the best way, but it looks like it works :-) Remembering that he is picking up today until 1000 days ago only.
51011934
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Thu, 12 Mar 2009 4:53 pm
Location: PORTO ALEGRE
Diego Bernardes

Thomas,

The count is OK. The problem is that I can not mount the SELECT by entering my dates list in this variable.

of the table I have:



For each record I need this count

I do not know if I made me understand.

From now on, thanks for the help!
51011934
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Thu, 12 Mar 2009 4:53 pm
Location: PORTO ALEGRE
Diego Bernardes

I was able to do the select the way I wanted:

Select all

 
select X.CASO, X.DATA_RECEBIMENTO,  
( 
select count(dt) ATRASO  
from  
  (select trunc(sysdate+1)-level dt  
  from dual connect by level <=1000  
  ) x  
where dt >= X.DATA_RECEBIMENTO  
  and dt <= sysdate  
  and to_char(dt,'d') not in (1,7) 
 ) ATRASO 
  
 from TABELA X 
Thomas, thank you for the help! Hugs!
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 16 guests