NUMBER OF DAYS BETWEEN TWO DATES WITHOUT REGARD FOR SAT AND SUN

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 12 Mar 2009 5:07 pm

Guys, I have a table with multiple records, each record has a date x. I need calculate sysdate-datax, however have to bring the amount excluding sat (7) and dom (1).

Someone could help me?
Now, thanks
51011934
Location: PORTO ALEGRE

Diego Bernardes

Poston Thu, 12 Mar 2009 5:28 pm

I had the following idea:
Code: 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)
look, he's going to get every day from Monday to Friday, between 01-jan and 20-jan: (hence is just doing a count).
Code: 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 don't know if this is the best way, but it seems to work:-) Remembering that he's going on today until 1000 days ago only.
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Thu, 12 Mar 2009 5:43 pm

Thomas, the count is ok. The problem is that I can not assemble the select inserting my list of dates in this variable.

I have table's Ex: select ID, DATAX, (variable) For each record I need this count do not know if I made myself clear.

In advance, thanks for your help!
51011934
Location: PORTO ALEGRE

Diego Bernardes

Poston Fri, 13 Mar 2009 1:29 pm

I managed to make the select the way I wanted:
Code: 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 very much for your help! Hugs!
51011934
Location: PORTO ALEGRE

Diego Bernardes


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 6 guests