Home » SQL & PL/SQL » SQL & PL/SQL » Date Diff exclude weekends in SQL (Oracle 10g)
Date Diff exclude weekends in SQL [message #352251] Mon, 06 October 2008 21:45 Go to next message
pristeo
Messages: 7
Registered: October 2008
Junior Member
All SQL experts,

Is there a sql function to find out duration exclude weekend? Example : Oct1 to Oct 8 returns 6 days instead of 8 days?

Thanks in advance!

Cheers,
pristeo
Re: Date Diff exclude weekends in SQL [message #352253 is a reply to message #352251] Mon, 06 October 2008 21:54 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


There is no built-in functions for that . You have to create it.

clues :

1. Generate the dates between ..
2. check the days
3. exclude the Saturday and Sunday while counting the days in between.

Thumbs Up
Rajuvan
Re: Date Diff exclude weekends in SQL [message #352284 is a reply to message #352251] Mon, 06 October 2008 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom, Counting the number of business days between 2 dates

Regards
Michel
Re: Date Diff exclude weekends in SQL [message #352292 is a reply to message #352284] Tue, 07 October 2008 00:09 Go to previous messageGo to next message
pristeo
Messages: 7
Registered: October 2008
Junior Member
Thanks, Michel and rajavu1 for the prompt reply!

In Tom's solution, how do I incorporate the logic in a select sql?

Example:
select error_type, error_date, <sysdate - error_date - weekend>
from interface_tbl


ops$tkyte@8i> @test 29-feb-2000 01-feb-2000
ops$tkyte@8i> set echo on
ops$tkyte@8i>
ops$tkyte@8i> select count(*)
2 from ( select rownum rnum
3 from all_objects
4 where rownum <= to_date('&1') - to_date('&2')+1 )
5 where to_char( to_date('&2')+rnum-1, 'DY' )
not in ( 'SAT', 'SUN' )
6 /
old 4: where rownum <= to_date('&1') - to_date('&2')+1 )
new 4: where rownum <= to_date('29-feb-2000') -
to_date('01-feb-2000')+1 )
old 5: where to_char( to_date('&2')+rnum-1, 'DY' )
not in ( 'SAT', 'SUN' )
new 5: where to_char( to_date('01-feb-2000')+rnum-1, 'DY' )
not in ( 'SAT', 'SUN' )

COUNT(*)
----------
21
Re: Date Diff exclude weekends in SQL [message #352295 is a reply to message #352251] Tue, 07 October 2008 00:21 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You have lots of options like

Creating a function and call in SQL
separate inline view to get the the output in the same SQL

check this for reference for row generator technique

Thumbs Up
Rajuvan.
Re: Date Diff exclude weekends in SQL [message #352300 is a reply to message #352292] Tue, 07 October 2008 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your case, &1 is sysdate and &2 is error_date.

Regards
Michel
Re: Date Diff exclude weekends in SQL [message #352307 is a reply to message #352300] Tue, 07 October 2008 00:51 Go to previous messageGo to next message
pristeo
Messages: 7
Registered: October 2008
Junior Member
pardon me, i am sql newbie.

I tried to incorporate the code in my sql but its invalid, see below.

select count(rnum),a.error_date, a.error_type
from (select rownum rnum
from all_objects
where rownum <= to_date(sysdate) - to_date(a.error_date)+1 )
where to_char( to_date(a.error_date)+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' ), interface_tbl a

Re: Date Diff exclude weekends in SQL [message #352310 is a reply to message #352307] Tue, 07 October 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Tue, 07 October 2008 00:58]

Report message to a moderator

Re: Date Diff exclude weekends in SQL [message #352312 is a reply to message #352310] Tue, 07 October 2008 01:06 Go to previous messageGo to next message
pristeo
Messages: 7
Registered: October 2008
Junior Member
Hi Michel,

I was trying to pass the &2 dates directly from a table to &d2, so I will get result like,

Error Type Error Date Duration(exclude weekend)
001 01-OCT-2008 6
002 02-OCT-2008 5
003 03-OCT-2008 4
004 06-OCT-2008 0

Hence, thought the sql would look something like the following but its invalid Sad

select a.error_type,a.error_date,count(rnum)
from (select rownum rnum
from all_objects
where rownum <= to_date(sysdate) - to_date(a.error_date)+1 )
where to_char( to_date(a.error_date)+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' ), interface_tbl a

Please advise. Thanks!

Re: Date Diff exclude weekends in SQL [message #352314 is a reply to message #352312] Tue, 07 October 2008 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 07 October 2008 07:56
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel


Re: Date Diff exclude weekends in SQL [message #352315 is a reply to message #352251] Tue, 07 October 2008 01:30 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Everybody starts as Newbie.

Now One home work .

can you re-write your current query somewhat like ..

SELECT  col1, col2, col3,
       (SELECT COUNT(*) FROM BLAHBLAH ) cnt
FROM
    BLAH


Here ,

SELECT COUNT(*) FROM BLAHBLAH

is you inline query for calculating count .


NB: Try to beautify the code as above using tags available .

Thumbs Up
Rajuvan.

[Updated on: Tue, 07 October 2008 01:31]

Report message to a moderator

Re: Date Diff exclude weekends in SQL [message #352318 is a reply to message #352315] Tue, 07 October 2008 01:43 Go to previous messageGo to next message
pristeo
Messages: 7
Registered: October 2008
Junior Member
Heres the rewrite sql, the error now is "a.error_date".invalid identifier

select a.error_type, a.error_date, ( select count(*)
from ( select rownum rnum
from all_objects
where rownum <= to_date(sysdate) - to_date(a.error_date)+1 )
where to_char( to_date(a.error_date)+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' )) cnt
from interface_tbl a
Re: Date Diff exclude weekends in SQL [message #352322 is a reply to message #352318] Tue, 07 October 2008 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 07 October 2008 08:28
Michel Cadot wrote on Tue, 07 October 2008 07:56
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel




Re: Date Diff exclude weekends in SQL [message #352327 is a reply to message #352251] Tue, 07 October 2008 02:27 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


This is because a.error_date was 'trapped' inside the inner loop from where main query element is not accessible.

Please Please Please Please format your post next time to get the proper answer .

You can incorporate the following logic in your query .

SELECT EMPNO ,ENAME ,HIREDATE ,
        ( SELECT TO_DATE(SYSDATE) - (HIREDATE-10) FROM DUAL) CNT_TOT,
        ( SELECT   SUM(DECODE( TO_CHAR(  (TO_DATE(HIREDATE)-10)+ROWNUM-1, 'DY' ), 
                         'SUN',0,
                         'SAT',0,
                         1)   )
          FROM ALL_OBJECTS AO
            WHERE   ROWNUM <= TO_DATE(SYSDATE) - (TO_DATE(HIREDATE)-10) 
        )CNT_EXCL_HOLIDAY 
 FROM  EMP E



Thumbs Up
Rajuvan.
Re: Date Diff exclude weekends in SQL [message #352331 is a reply to message #352327] Tue, 07 October 2008 02:54 Go to previous messageGo to next message
pristeo
Messages: 7
Registered: October 2008
Junior Member
Awesome!! It works!! Thank you very much Rajuvan and Michel! Laughing
Re: Date Diff exclude weekends in SQL [message #352345 is a reply to message #352331] Tue, 07 October 2008 03:54 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear Rajuvan,

Can you please explain why you have taken hiredate-10.

TO_DATE(HIREDATE)-10


What's the logic behind this.

Regards,
Rajat Ratewal
Re: Date Diff exclude weekends in SQL [message #352348 is a reply to message #352251] Tue, 07 October 2008 04:09 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ooops .

Dear Rajat ,

Actually I copied what OP has posted.

where rownum <= to_date(sysdate) - to_date(a.error_date)+1 )

I didn't check in detail.

Anyway , it was a nice catch !!!

Thumbs Up
Rajuvan.


Re: Date Diff exclude weekends in SQL [message #352533 is a reply to message #352348] Wed, 08 October 2008 04:59 Go to previous messageGo to next message
pristeo
Messages: 7
Registered: October 2008
Junior Member
Dear Rajuvan,

The result doesnt seems right for all rows. See sql and result below. Those with * are not correct. Please advise. Thanks.

select error_date ,( SELEct TO_DATE(SYSDATE) - (COB_DATE-1) FROM DUAL) CNT_TOT, 
( SELECT   SUM(DECODE( TO_CHAR(  (TO_DATE(A.COB_DATE)-1)+ROWNUM-1, 'DY' ), 
                           'SUN',0,
                           'SAT',0,
                           1)   )
            FROM ALL_OBJECTS AO
              WHERE   ROWNUM <= TO_DATE(SYSDATE) - (TO_DATE(A.COB_DATE)-1) 
          )  Aging
from interface_tbl

ERROR_DATE	CNT_TOT	AGING
25-Sep-08	14	10
*29-Sep-08	10	7
30-Sep-08	9	7
1-Oct-08	8	6
2-Oct-08	7	5
3-Oct-08	6	4
*6-Oct-08	3	2
*7-Oct-08	2	2

Re: Date Diff exclude weekends in SQL [message #352541 is a reply to message #352251] Wed, 08 October 2008 05:37 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


I never asked you to copy paste but to implement the logic .

Implement it properly .


SQL> SELECT  TRUNC(SYSDATE)+1 -  TO_DATE('29-Sep-08', 'DD-Mon-YY') CNT_TOT ,
  2             ( SELECT   SUM( DECODE ( TO_CHAR ( TO_DATE('29-Sep-08', 'DD-Mon-YY') +ROWNUM-1, 'DY') ,
  3                             'SUN',0,
  4                             'SAT',0,
  5                             1) )   CNT_EXCL_HOLIDAY
  6              FROM ALL_OBJECTS AO
  7                WHERE   ROWNUM <=  TRUNC(SYSDATE)+1 -  TO_DATE('29-Sep-08', 'DD-Mon-YY')) CNT_EXCL_HOLIDAY
  8         FROM DUAL;

   CNT_TOT CNT_EXCL_HOLIDAY
---------- ----------------
        10                8

SQL>


Moreover , we don't have any clue whether error_date is same as COB_DATE in your case .

Oracle never tells lies


Thumbs Up
Rajuvan.
Previous Topic: help with CASE
Next Topic: call an exe file from a DB Procedure
Goto Forum:
  


Current Time: Sat Dec 10 08:44:23 CST 2016

Total time taken to generate the page: 0.27518 seconds