Home » SQL & PL/SQL » SQL & PL/SQL » date difference excluding weekends on a simple table!
icon9.gif  date difference excluding weekends on a simple table! [message #296669] Mon, 28 January 2008 10:51 Go to next message
g_srinivas
Messages: 10
Registered: January 2008
Junior Member
Hello,

I created a following simple table:

SQL> desc date_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 EMPNO                                     NOT NULL NUMBER(4)
 START_DATE                                         DATE
 END_DATE                                           DATE

SQL> 


Now, i am trying to calculate the number of days between end_date and start_date excluding weekends. For this i came up with the following query with the help from this post:

select empno, start_date, end_date, 
( select count(*)
  from ( select rownum rnum
         from all_objects
         where rownum <= (end_date-start_date+1) )
  where to_char( (start_date+rnum)-1, 'DY' ) 
        not in ( 'SAT', 'SUN' ) ) diff
from date_test;


But i don't understand why but i get the following error message for above query:

ERROR at line 4:
ORA-00904: "START_DATE": invalid identifier


so i modified the query in the following way:

select d1.empno, d1.start_date, d1.end_date, 
( select count(*)
  from ( select rownum rnum
         from all_objects
         where rownum <= (select end_date-start_date+1 
                          from date_test 
                          where empno = d1.empno ) )
  where to_char( (start_date+rnum)-1, 'DY' ) 
        not in ( 'SAT', 'SUN' ) ) diff
from date_test d1;


And for above query i get following error message, but which i guess is understandable because the sub-query is returning all the records from date_temp table:

ERROR at line 4:
ORA-01427: single-row subquery returns more than one row


Can anyone please help me with this query to get date differences from two date columns excluding weekends?

Thanks and Regards,

[Updated on: Mon, 28 January 2008 11:03] by Moderator

Report message to a moderator

Re: date difference excluding weekends on a simple table! [message #296672 is a reply to message #296669] Mon, 28 January 2008 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But i don't understand why but i get the following error message for above query:

ERROR at line 4:
ORA-00904: "START_DATE": invalid identifier

start_date is known in the immediate subquery not in the second level.

There are many threads on the subject and you take a solution that is 8 years old!

Regards
Michel
Re: date difference excluding weekends on a simple table! [message #296674 is a reply to message #296672] Mon, 28 January 2008 11:11 Go to previous messageGo to next message
g_srinivas
Messages: 10
Registered: January 2008
Junior Member
thanks for the quick reply Michel but this was the first search result i found through google and other results weren't that helpful to me.

Could you please provide some quick solution for this or post some helpful links on this?

Thanks and Regards,
Re: date difference excluding weekends on a simple table! [message #296676 is a reply to message #296674] Mon, 28 January 2008 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As this question has been asked not so long ago here, I think you would not have so much difficulties to find the answer (at least not much more than I can have).

Regards
Michel
Re: date difference excluding weekends on a simple table! [message #296774 is a reply to message #296676] Tue, 29 January 2008 00:56 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
You can use PL/SQL Code to find the Number of Days excluding weekends.. as

declare
 stdate date:=to_date('01-Jan-2008');
 eddate date:=to_date('13-Jan-2008');
 k number;
 cnt number;
 begin 
 k := eddate-stdate ;
 cnt := 0;
 for i in 1..k loop
 if to_char(stdate,'Dy') NOT IN ('Sat','Sun') THEN
 cnt := cnt +1;
 end if;
 stdate := stdate + 1;
 end loop;
 dbms_output.put_line('cnt is='||cnt);
 end;
Re: date difference excluding weekends on a simple table! [message #296782 is a reply to message #296669] Tue, 29 January 2008 01:11 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Why are you trying to give the answer in PL/SQL where there is straight SQL options are open ? Even OP is interested in SQL ways ..


Thumbs Up
Rajuvan.

[Updated on: Tue, 29 January 2008 01:11]

Report message to a moderator

Re: date difference excluding weekends on a simple table! [message #296786 is a reply to message #296669] Tue, 29 January 2008 01:20 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
 select d1.empno, d1.start_date, d1.end_date,
 ( select count(*)
   from ( select rownum rnum
          from all_objects
          where rownum <= (select end_date-start_date+1
                           from date_test
                           ) )
   where to_char( (start_date+rnum)-1, 'DY' )
         not in ( 'SAT', 'SUN' ) ) diff
 from date_test d1;

EMPNO START_DAT END_DATE        DIFF
----- --------- --------- ----------
 1000 01-JAN-08 13-JAN-08          9
Re: date difference excluding weekends on a simple table! [message #296789 is a reply to message #296669] Tue, 29 January 2008 01:32 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Suppose I agree with the solution ,

What will happe to your query if ... ?

1. there exists Multiple records in date_test
2. the Number of records in all_objects is less compared to date difference between end_date and start_date

Ie, OP needs a generalised Solution .

Thumbs Up
Rajuvan.

[Updated on: Tue, 29 January 2008 01:32]

Report message to a moderator

Re: date difference excluding weekends on a simple table! [message #296922 is a reply to message #296789] Tue, 29 January 2008 08:45 Go to previous messageGo to next message
g_srinivas
Messages: 10
Registered: January 2008
Junior Member
Thanks to all for the responses. Following the is query that i'm currently using that i got from this link and it also works:

SELECT start_date, 
       end_date,
       end_date - start_date              AS  total_days,
       end_date - start_date - COUNT (*)  AS  saturdays_and_sundays,
       COUNT (*)                          AS  weekdays
  FROM  ( SELECT ROWNUM rn
            FROM   all_objects),
  test_table
  WHERE  rn <= end_date - start_date AND 
         TO_CHAR (start_date + rn - 1, 'dy') NOT IN ('sat', 'sun')
  GROUP BY start_date, 
           end_date;


But when i use the above query in my actual table which has at least 2 million records, it takes about 10 mins for execution. Is there any way to tweak this to increase the performance?

Thanks and Regards,
Re: date difference excluding weekends on a simple table! [message #296924 is a reply to message #296922] Tue, 29 January 2008 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes there is a way that is called arithmetic.
There are 7 days per week.
There are only 1 saturday and 1 sunday per week.
So there are 5 working days per week.
So in the average between 2 dates d1 and d2, there are (d2-d1)*5/7 working days.
In the end, you just have to adjust following the week day of d1 and d2.
No join, no row generator.

Regards
Michel
Re: date difference excluding weekends on a simple table! [message #296955 is a reply to message #296669] Tue, 29 January 2008 11:43 Go to previous messageGo to next message
g_srinivas
Messages: 10
Registered: January 2008
Junior Member
Hello Michel,

Your arithmetic solution does seem simple and interesting when reading but i don't understand how can we adjust the weekdays of d1 and d2 from the average result (i.e., d2-d1*5/7?

Could you please give more details?

Thanks & Regards,
Re: date difference excluding weekends on a simple table! [message #296956 is a reply to message #296955] Tue, 29 January 2008 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many sunday and saturday or working days in 31 days months?
It depends on which week day is first...
Same thing with d1 and d2. For the same d2-d1, number of working days depends on week day of d1 (and d2).

Regards
Michel

[Updated on: Tue, 29 January 2008 11:47]

Report message to a moderator

Re: date difference excluding weekends on a simple table! [message #296964 is a reply to message #296669] Tue, 29 January 2008 12:47 Go to previous messageGo to next message
g_srinivas
Messages: 10
Registered: January 2008
Junior Member
Why don't you give a direct answer instead of giving hints? And i don't think that the solution which you are speaking of can be written using a simple query and will require some programming in PL/SQL which i don't want.

Regards,
Re: date difference excluding weekends on a simple table! [message #296965 is a reply to message #296964] Tue, 29 January 2008 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why don't you give a direct answer instead of giving hints?

Just to make you think and/or learn to think.

Quote:
i don't think that the solution which you are speaking of can be written using a simple query

It can.

Regards
Michel
Re: date difference excluding weekends on a simple table! [message #296966 is a reply to message #296669] Tue, 29 January 2008 12:52 Go to previous messageGo to next message
g_srinivas
Messages: 10
Registered: January 2008
Junior Member

hmmm... now i'm curious... and i give up... could you now please give the code?

Re: date difference excluding weekends on a simple table! [message #297001 is a reply to message #296669] Tue, 29 January 2008 21:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What did you try so far as per Michel's explanation ?


Thumbs Up
Rajuvan
Re: date difference excluding weekends on a simple table! [message #297002 is a reply to message #296669] Tue, 29 January 2008 21:58 Go to previous messageGo to next message
g_srinivas
Messages: 10
Registered: January 2008
Junior Member

Just (d2-d1)*5/7... later part i couldn't figure out using simple query.
Re: date difference excluding weekends on a simple table! [message #297005 is a reply to message #296669] Tue, 29 January 2008 22:15 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi srinivas,

Is it correct ?
SELECT start_date, 
       end_date,
       end_date - start_date              AS  total_days,
       end_date - start_date - COUNT (*)  AS  saturdays_and_sundays,
       COUNT (*)                          AS  weekdays
  FROM  ( SELECT ROWNUM rn
            FROM   all_objects),
  test_table
  WHERE  rn <= end_date - start_date AND 
         TO_CHAR (start_date + rn - 1, 'dy') NOT IN ('sat', 'sun')
  GROUP BY start_date, 
           end_date;


wbr
kanish
Re: date difference excluding weekends on a simple table! [message #297007 is a reply to message #297005] Tue, 29 January 2008 22:17 Go to previous messageGo to next message
g_srinivas
Messages: 10
Registered: January 2008
Junior Member

Yes it is Kanish.
Re: date difference excluding weekends on a simple table! [message #297015 is a reply to message #296669] Tue, 29 January 2008 22:56 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi

Take this examble.

SQL> SELECT sdt, 
  2         edt,
  3         edt - sdt              AS  total_days,
  4         edt - sdt - COUNT (*)  AS  saturdays_and_sundays,
  5         COUNT (*)                          AS  weekdays
  6    FROM  ( SELECT ROWNUM rn
  7              FROM   all_objects),
  8    date_test
  9    WHERE  rn <= edt - sdt AND 
 10           TO_CHAR (sdt + rn - 1, 'dy') NOT IN ('sat', 'sun')
 11    GROUP BY sdt, 
 12             edt;

SDT       EDT       TOTAL_DAYS SATURDAYS_AND_SUNDAYS  WEEKDAYS
--------- --------- ---------- --------------------- ---------
01-JAN-08 10-JAN-08          9                     2         7
11-JAN-08 20-JAN-08          9                     3         6
21-JAN-08 30-JAN-08          9                     2         7


how many sun,sat between 2 row sdt and edt
wbr

kanish
Re: date difference excluding weekends on a simple table! [message #297076 is a reply to message #296669] Wed, 30 January 2008 01:06 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi srinivas,

Did u find our my previous replu.

wbr.

Kanish
Re: date difference excluding weekends on a simple table! [message #297160 is a reply to message #296966] Wed, 30 January 2008 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
could you now please give the code?

Yes as soon as you tell what you tried and why this does not work.

Here's a plan/formula:
days from start_date to first saturday
+ days from last sunday to end_date
+ 5 * number of weeks between this saturday and this sunday (number of weeks given by the difference divided by 7)

This is ONE way there are many others.

Regards
Michel

[Updated on: Wed, 30 January 2008 06:52]

Report message to a moderator

Re: date difference excluding weekends on a simple table! [message #297208 is a reply to message #296669] Wed, 30 January 2008 11:49 Go to previous messageGo to next message
g_srinivas
Messages: 10
Registered: January 2008
Junior Member
Hello Michel and Kanish,

Thanks for the hints guys, but seriously Michel and Kanish, i don't know how to build a query from this. I understood the formula you have given Michel but the fact is that i am not a PL/SQLer and so i am not aware of all the date/time functions and features available in PL/SQL. I'm basically an ABAPer and if this was a task for me in ABAP then i would have definitely come up with some solution instead of dragging this topic this long.

And for your detailed hint Michel:

Start Date  End Date   Difference  Days from   Days from start   +5 * No. of weeks     Excluding Weekends
                                   Start Date  of WeekDay        between this 
                                   to Friday   to End Date       Saturday and Sunday
1/1/2008    1/31/2008  30          4           4                 =3*5 (Result: 15)     =15+4+4 (Result: 23)
2/1/2008    2/29/2008  28          1           5                 =3*5 (Result: 15)     =15+1+5 (Result: 21)
1/1/2008    2/29/2008  59          4           5                 =7*5 (Result: 35)     =35+4+5 (Result: 44)


But like i have no clue on how to get "Days from Start Date to Friday", "Days from Start of Weekday to End Date" and "No. of weeks between this Saturday and Sunday Excluding Weekends" in normal query of PL/SQL.

Also, i really appreciate spending time for my post.

Thanks and Regards,

[Updated on: Wed, 30 January 2008 12:49] by Moderator

Report message to a moderator

Re: date difference excluding weekends on a simple table! [message #297215 is a reply to message #297208] Wed, 30 January 2008 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i am not aware of all the date/time functions and features available in PL/SQL

This is a real pool excuse. SQL documentation is available online.

You only need:
* minus (-): difference between 2 dates in unit of days
* next_day

Regards
Michel
Re: date difference excluding weekends on a simple table! [message #297221 is a reply to message #297215] Wed, 30 January 2008 13:13 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
hi
code below can be a solution

select
(end_date-start_date+1)-
((next_day(end_date,‘saturday’)-next_day(start_date,‘saturday’))/7*2)
from all_objects;

but if end_date or start_date is either sunday or saturday then it wont work. Hope in practical condition these can't be weekends.

can ne1 give a generalized querry??

Regards

[Updated on: Wed, 30 January 2008 13:15]

Report message to a moderator

Re: date difference excluding weekends on a simple table! [message #297234 is a reply to message #297221] Wed, 30 January 2008 14:27 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ridhi_sundar wrote on Wed, 30 January 2008 14:13
hi
code below can be a solution

select
(end_date-start_date+1)-
((next_day(end_date,‘saturday’)-next_day(start_date,‘saturday’))/7*2)
from all_objects;




Why would you post something do grossly incorrect that would not even execute?
Re: date difference excluding weekends on a simple table! [message #297236 is a reply to message #297234] Wed, 30 January 2008 14:45 Go to previous messageGo to next message
g_srinivas
Messages: 10
Registered: January 2008
Junior Member
Somebody seems to have spoiled all the fun... Razz

Other than table name (all_objects instead for date_test) i don't see anything wrong with the query.

I don't have Oracle installed on my machine to prove but logically it does seem like it will work.

Also like said, the query will work only for the dates which don't have the start date and end date in a weekend.

I'm not being sarcastic but seriously Michel any suggestions or other tips to improve this query and make it work for any start and end date?
Re: date difference excluding weekends on a simple table! [message #297244 is a reply to message #297236] Wed, 30 January 2008 15:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still waiting for what you tried before posting any solution.

Regards
Michel
Re: date difference excluding weekends on a simple table! [message #297276 is a reply to message #296669] Wed, 30 January 2008 23:43 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
SQL> select * from date_test;

     EMPNO SDT       EDT
---------- --------- ---------
      1000 01-JAN-08 13-JAN-08
      2000 06-JAN-08 20-JAN-08

SQL> ed
Wrote file afiedt.buf

  1  select empno,sdt,edt,edt-sdt+1 as total_days,
  2  trunc((edt-sdt+1)/7)*2+decode(to_char(sdt, 'dy'),
  3  'tue',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),5,1,6,2,0),
  4  'wed',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),4,1,5,2,6,2,0),
  5  'thu',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),1,0,2,0,3,1,2),
  6  'fri',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),1,0,2,1,2),
  7  'sat',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),1,1,2),
  8  'sun',1,
  9  'mon',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),6,1,0)) as total_sun_sat,
 10  (edt-sdt+1)-(trunc((edt-sdt+1)/7)*2+decode(to_char(sdt, 'dy'),
 11  'tue',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),5,1,6,2,0),
 12  'wed',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),4,1,5,2,6,2,0),
 13  'thu',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),1,0,2,0,3,1,2),
 14  'fri',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),1,0,2,1,2),
 15  'sat',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),1,1,2),
 16  'sun',1,
 17  'mon',decode( (edt-sdt+1)-7*trunc((edt-sdt+1)/7),6,1,0))) as total_working_days
 18*  from date_test
SQL> /

     EMPNO SDT       EDT       TOTAL_DAYS TOTAL_SUN_SAT TOTAL_WORKING_DAYS
---------- --------- --------- ---------- ------------- ------------------
      1000 01-JAN-08 13-JAN-08         13             4                  9
      2000 06-JAN-08 20-JAN-08         15             5                 10



dear michel, thanks for your algo.
i also think that taking all_objects table is not a good way.
Re: date difference excluding weekends on a simple table! [message #297301 is a reply to message #297276] Thu, 31 January 2008 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's another solution which may seem complicated because it is independant of your language and territory.
If this is not the case (always AMERICAN.AMERICA for instance) you can change saturday/sunday names and numbers by constants and simplify the formulas in the final part and start the query with "data" subquery.
drop table t purge;
create table t (id integer, start_date date, end_date date);
insert into t 
select level, 
       trunc(sysdate-dbms_random.value(1,30)),
       trunc(sysdate+dbms_random.value(1,50))
from dual
connect by level <= 10
/

SQL> select * from t order by 1;
        ID START_DA END_DATE
---------- -------- --------
         1 15/01/08 19/02/08
         2 07/01/08 27/02/08
         3 21/01/08 12/03/08
         4 02/01/08 17/02/08
         5 01/01/08 08/02/08
         6 28/01/08 02/02/08
         7 16/01/08 10/03/08
         8 18/01/08 13/02/08
         9 06/01/08 19/02/08
        10 26/01/08 03/03/08

10 rows selected.

SQL> with 
  2    names as ( 
  3      select to_char(to_date('01/01/1956','DD/MM/YYYY'),'Day') sunday,
  4             to_char(to_date('01/01/2000','DD/MM/YYYY'),'Day') saturday           
  5      from dual
  6    ),
  7    days as ( 
  8      select to_number(to_char(next_day(sysdate,sunday),'d')) sunday,
  9             to_number(to_char(next_day(sysdate,saturday),'d')) saturday
 10      from names
 11    ),
 12    data as (
 13      select id, start_date, end_date, 
 14             to_number(to_char(start_date,'d')) start_day,
 15             to_number(to_char(end_date,'d')) end_day
 16      from t
 17    )
 18  select id, start_date, end_date,
 19           least(saturday-start_day+decode(sign(saturday-start_day),-1,7,0),5)
 20         + 5*(next_day(end_date,sunday)-7-next_day(start_date,sunday))/7
 21         + least(end_day-sunday+decode(sign(end_day-sunday),-1,7,0),5)
 22          working_days
 23  from data, days
 24  order by 1
 25  /
        ID START_DA END_DATE WORKING_DAYS
---------- -------- -------- ------------
         1 15/01/08 19/02/08           26
         2 07/01/08 27/02/08           38
         3 21/01/08 12/03/08           38
         4 02/01/08 17/02/08           33
         5 01/01/08 08/02/08           29
         6 28/01/08 02/02/08            5
         7 16/01/08 10/03/08           39
         8 18/01/08 13/02/08           19
         9 06/01/08 19/02/08           32
        10 26/01/08 03/03/08           26

10 rows selected.

Regards
Michel

[Updated on: Thu, 31 January 2008 01:25]

Report message to a moderator

Re: date difference excluding weekends on a simple table! [message #297381 is a reply to message #297301] Thu, 31 January 2008 05:20 Go to previous messageGo to next message
pinhit
Messages: 10
Registered: November 2006
Junior Member
Why not use a table 'NO_WORKINGDAYS' (reference_date, reason) that contains SUNDAYS and SATURDAYS, and could also contain holidays like New Years's Day eg.
Re: date difference excluding weekends on a simple table! [message #297388 is a reply to message #297381] Thu, 31 January 2008 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is another question.

Regards
Michel
Re: date difference excluding weekends on a simple table! [message #297390 is a reply to message #297388] Thu, 31 January 2008 05:47 Go to previous messageGo to next message
pinhit
Messages: 10
Registered: November 2006
Junior Member
It's not a question but a solution that can be used for more than 1 purpose. Cool
Re: date difference excluding weekends on a simple table! [message #297394 is a reply to message #297390] Thu, 31 January 2008 05:57 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant this is another point.
It is not a solution to the question, maybe a solution to the underlying problem (who knows?) but this is not the question. Cool

Regards
Michel

[Updated on: Thu, 31 January 2008 05:57]

Report message to a moderator

Previous Topic: Inserting BLOB (image file) in database through SQL Query
Next Topic: Null and Empty
Goto Forum:
  


Current Time: Sun Dec 04 13:00:06 CST 2016

Total time taken to generate the page: 0.16725 seconds