Skip to Main Content
  • Questions
  • Counting the number of business days between 2 dates.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, RAGHAVENDRA .

Asked: May 02, 2000 - 1:21 pm UTC

Answered by: Tom Kyte - Last updated: May 12, 2018 - 1:28 am UTC

Category: - Version:

Viewed 50K+ times! This question is

You Asked

how to get the number of days between
two dates excluding saturdays and sundays using pl/sql?



and we said...

something like:

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

will do it. Make sure the table in the inline view (all_objects in my example) has at least as many rows as you plan on having dates span (eg: it needs at least 366 rows to do 1 year, 3660 to do 10 and so on). all_objects, typically with thousands of rows, is a good generic candidate -- you can always build your own very compact table as well to make this faster.


A logical extension would be to add 'holidays' and other non-business days. We would use a table with dates to exclude. For example:


ops$tkyte@8i> create table exclude_dates ( no_work date primary key ) organization index;

Table created.

ops$tkyte@8i> insert into exclude_dates values ( '14-FEB-2000' );

1 row created.

ops$tkyte@8i>
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 and not exists
( select null from exclude_dates where no_work =
7 trunc(to_date('&2')+rnum-1) )
8 /

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' )
old 7: trunc(to_date('&2')+rnum-1) )
new 7: trunc(to_date('01-feb-2000')+rnum-1) )

COUNT(*)
----------
20


and you rated our response

  (111 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

-1, +1, -1 why?

October 07, 2001 - 9:36 pm UTC

Reviewer: Sam

Tom, you are Golden

select count(*)
2 from ( select rownum-1 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' )

Why are we adding -1 to rownum, and then adding one below
where rownum <= to_date('&1') - to_date('&2')+1 ), and then
subtracting -1 again below
where to_char( to_date('&2')+rnum-1, 'DY' )
not in ( 'SAT', 'SUN' )

Thank you

IN

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:185012348071 <code>

How can we handle in the same Sql the following situation.

Uncle Jim was paid untill March 15th 2001, from Jan2001 but only half at a rate of 45 dollars per hour. Now how can we accomodate the same caluculation in the same sql , and get the total amount due to uncle JIM.


You seem to be quite busy these days, not to be seen much on ASKTOM.ORACLE.COM.

I have an interesting question, if you decide to take off from this project , and do something different , will ASKTOM.ORACLE.COM come to an end.


Tom Kyte

Followup  

October 08, 2001 - 7:59 am UTC

Hint: use a "UNION ALL", another inline view, and a SUM

select sum(sal)
from ( select ... query above for jan-mar
union all
select .... query from above for mar-now )


or use decode/case in the select to determine the amount to be paid (left as an exercise for the reader, since they didn't leave behind the link to the original question ;)



I've been on site here as much as usual (look at the archives available from the home page -- the counts are there). I have been traveling, doing some keynotes, which does obviously impact my ability to be here. This is just sort of a "hobby", I have my real job as well.

If I stop doing asktom, I don't know what would happen to it. It would most likely "go away".

Need to Correct the Query

October 08, 2001 - 2:54 am UTC

Reviewer: Asif Momen from Riyadh

Hi Tom,

The above query is just wonderful. But, you need to correct it. Instead of using "rownum-1", you need to select only "rownum". The reason is, for the first record "rownum-1" will return 0 and the Where Clause to_char(to_date('&2')+rnum-1, 'DY') not in ('SAT', 'SUN') will evaluate for a day before the range specified.

Lets take your example of 01-feb-2000 and 29-feb-2000. The Where Calsuse will execute for 31-jan-2000. In the above case as 31-jan-2000 is neither a sunday nor a saturday, the output no. of records is correct, but will fail if you consider 01-may-2000 and 31-my-2000. for that matter any date range starting from monday.

Regards,

Asif Momen

Tom Kyte

Followup  

October 08, 2001 - 8:07 am UTC

Yes, you are correct, thanks for pointing that out. I've corrected the code.

October 08, 2001 - 12:04 pm UTC

Reviewer: A reader

Why are we adding -1 to rownum, and then adding one below
where rownum <= to_date('&1') - to_date('&2')+1 ), and then
subtracting -1 again below
where to_char( to_date('&2')+rnum-1, 'DY' )
not in ( 'SAT', 'SUN' )


Tom Kyte

Followup  

October 08, 2001 - 1:37 pm UTC

I corrected that.

Will this work, one of our customer says that this is fastest one

June 04, 2002 - 1:45 pm UTC

Reviewer: Sonali Kelkar from Newton, MA USA

CREATE OR REPLACE FUNCTION num_business_days(start_date IN DATE, end_date IN DATE)
RETURN NUMBER IS
busdays NUMBER := 0;
stDate DATE;
enDate DATE;

BEGIN

stDate := TRUNC(start_date);
enDate := TRUNC(end_date);

if enDate >= stDate
then
-- Get the absolute date range
busdays := enDate - stDate
-- Now subtract the weekends
-- this statement rounds the range to whole weeks (using
-- TRUNC and determines the number of days in the range.
-- then it divides by 7 to get the number of weeks, and
-- multiplies by 2 to get the number of weekend days.
- ((TRUNC(enDate,'D')-TRUNC(stDate,'D'))/7)*2
-- Add one to make the range inclusive
+ 1;

/* Adjust for ending date on a saturday */
IF TO_CHAR(enDate,'D') = '7' THEN
busdays := busdays - 1;
END IF;

/* Adjust for starting date on a sunday */
IF TO_CHAR(stDate,'D') = '1' THEN
busdays := busdays - 1;
END IF;
else
busdays := 0;
END IF;

RETURN(busdays);
END;
/


Tom Kyte

Followup  

June 05, 2002 - 6:18 am UTC

Understand it,

Test it,

Validate it....


Then you will know "if it works"... Looks similar to another example here on asktom somewhere.

But -- as with all software -- before you use it make sure you

o Understand it
o Test it
o Validate it



It is good only for English Speaking databases

December 09, 2002 - 4:41 pm UTC

Reviewer: Vladimir from Albany, NY

The approach with 'SAT', 'SUN' will not work if you have the language other than English. Moreover, you cannot use TO_CHAR(date, 'D') either, because some countries start their week at Sunday, other - at Monday.
I think the approach with calendar table where you store your business days and mark them as working days or non-working days and/or holidays would work.


Tom Kyte

Followup  

December 09, 2002 - 7:13 pm UTC

sorry, if they cannot make the minor adjustment for their system -- well, they probably shouldn't have a keyboard and be programming?

Anyway, I have demonstrated more then once on this site howto do this "generically"

not in ( to_char( to_date('20000101', 'yyyymmdd' ), 'DY' ),
to_char( to_date('20000102', 'yyyymmdd' ), 'DY' ) )


that'll work in any language, anytime. Regardless of what day of week the week starts on.


Asktom Archives

April 23, 2003 - 12:53 pm UTC

Reviewer: Randy Schneider from Warren

Hi Tom,
Do you use something similiar to generate your dates in the archive section? I'm trying to do something similar but not having much luck.

Tom Kyte

Followup  

April 23, 2003 - 7:34 pm UTC

that's much easier, we just trunc(date,'w') to the week and count.

sat and sun don't have any special meaning to me other then I definitely work at home instead of driving in ;)

what does "not much luck" mean

Is this the best way to do something similar...using the all_objects to drive it ?

April 28, 2003 - 5:39 am UTC

Reviewer: A Reader from UK

Tom,

Rather than use pl/sql loops etc. (which I know you hate if there's another way)... I'm trying to do this:

Accounting Period table has a start and end date in it. For April 2003 this might be START: 28/03/03, END: 02/05/03. I usually interpret the end date to be 23:59:59.

What I want to do is process each calendar month within that accounting period. i.e. end up with a table with:

CALENDAR_PERIOD ACCOUNTING_PERIOD SOME_VALUE
MARCH 03 APRIL 03 100
APRIL 03 APRIL 03 150
MAY 03 APRIL 03 170

Based on what you've done above, I came up with the followign to give me the dates to work with:

select ACC_MONTH, ACC_YEAR, GREATEST(ACC_FIRST_DAY_OF_PERIOD, TRUNC(ADD_MONTHS(acc_first_day_of_period,rownum - 1),'MM')) start_of_period, LEAST(LAST_DAY(ADD_MONTHS(acc_first_day_of_period,rownum - 1)),TRUNC(acc_last_day_of_period)) + 1 - 1/24/60/60 end_of_period
from accounting_periods, all_objects
where acc_month = 4 and acc_year = 2003
and ADD_MONTHS(TRUNC(acc_first_day_of_period,'MM'),rownum - 1) between TRUNC(acc_first_day_of_period,'MM') and TRUNC(acc_last_day_of_period,'MM')

Is this the best way to do that ? I'm thinking I could create a view on this across the accounting periods and use that in my sql statements to avoid PL/SQL.

Thanks in advance,

Paul

Tom Kyte

Followup  

April 28, 2003 - 8:17 am UTC

probably rather something like:

select
ACC_MONTH, ACC_YEAR,
GREATEST(ACC_FIRST_DAY_OF_PERIOD,
TRUNC(ADD_MONTHS(acc_first_day_of_period,r),'MM')) start_of_period,
LEAST(LAST_DAY(ADD_MONTHS(acc_first_day_of_period,r)),
TRUNC(acc_last_day_of_period)) + 1 - 1/24/60/60 end_of_period
from accounting_periods,
(select rownum-1 r from all_objects where rownum <= 24)
where acc_month = 4
and acc_year = 2003
and ADD_MONTHS(TRUNC(acc_first_day_of_period,'MM'),r)
between TRUNC(acc_first_day_of_period,'MM') and
TRUNC(acc_last_day_of_period,'MM')


where 24 is "some reasonable" number that represents the max spread you would see (eg: here I have 24 months as the max spread).

Rather then cartesian product with a sometimes 30,000 plus row table...

Damn......re: above response

April 28, 2003 - 5:48 am UTC

Reviewer: A Reader from UK

Tom,

My idea above works fine if I restrict on a particular period but if I want the results across several periods, it screws up because I need rownum to start from 0 for EACH accounting period. For april it gives:

ACC_MONTH ACC_YEAR START_OF_PERIOD END_OF_PERIOD
4 2003 29/03/2003 31/03/2003 23:59:59
4 2003 01/04/2003 30/04/2003 23:59:59
4 2003 01/05/2003 02/05/2003 23:59:59

However, if I take off the April 2003 restriction I get some weird results. Any ideas ?


Tom Kyte

Followup  

April 28, 2003 - 8:28 am UTC

you would have to give an entire example showing the good with the bad.

OK...I think I have the answer....but it may not be the most performant way of doing it...

April 28, 2003 - 6:22 am UTC

Reviewer: A Reader from UK

I know that in an accounting period, there can be at most 3 months involved - as they are more-or-less months anyway so at most a period can start early and end late spanning 3 months...so I use a sub-query to give me each accounting period with 3 rownums:

SELECT ACC_MONTH, ACC_YEAR, GREATEST(ACC_FIRST_DAY_OF_PERIOD, TRUNC(ADD_MONTHS(acc_first_day_of_period, therownum - 1), 'MM')) start_of_period, LEAST(LAST_DAY(ADD_MONTHS(acc_first_day_of_period, therownum)), TRUNC(acc_last_day_of_period)) + 1 - 1/24/60/60 end_of_period
FROM accounting_periods, (SELECT MOD(rownum,3) therownum FROM ALL_OBJECTS WHERE rownum <= 3)
WHERE ADD_MONTHS(TRUNC(acc_first_day_of_period,'MM'),therownum) between TRUNC(acc_first_day_of_period,'MM') and TRUNC(acc_last_day_of_period,'MM')
ORDER BY ACC_YEAR, ACC_MONTH

Not the nicest query - any tips on making it better ?

Regards,

Paul

Query Returns 3 instead of 2

June 04, 2003 - 2:08 am UTC

Reviewer: A reader

This query returns 3 days instead of 2 days when i do a 16-14, mathemattically speaking it should return 2
Following is the query,
Note:- Holidays is table where my Holidays are stored




select count(*)
from ( select rownum rnum
from all_objects
where rownum <= to_date('16-MAY-2003') - to_date('14-MAY-2003')+1 )
where to_char( to_date('14-MAY-2003')+rnum-1, 'DY' )
not in ( 'SAT', 'SUN' )
and NOT EXISTS ( SELECT NULL FROM HOLIDAYS WHERE HOLIDAYDATE =
TRUNC(TO_DATE(NVL('16-MAY-2003',TO_DATE(SYSDATE)))+RNUM-1) ) ;




Tom Kyte

Followup  

June 04, 2003 - 7:54 am UTC

why should it return 2?

may 14, may 15, may 16

wed, thur, fri

3 days to me?

just put a solution to this too, TOM

June 04, 2003 - 8:26 am UTC

Reviewer: debjit from india

Can u tell how to select all days between two date which are not business dates in a single sql

Tom Kyte

Followup  

June 04, 2003 - 8:53 am UTC

you cannot figure that out from this? I mean, we generate all of those days so that in a where clause we can filter them -- it is not much more work then removing the count(*) and replacing with the to_date in the predicate.

Go away?

June 04, 2003 - 2:09 pm UTC

Reviewer: Kashif from Houston, TX

Tom,

Since for some reason I thought AskTom would exist always and forever, I never thought I would need to know how to save this invaluable information. But now I do. How do I save all these archives to a CD or something? And I sure hope that 'going away' thing happens, umm, never in my lifetime! Thanks again for the invaluable service you provide.

Kashif

Tom Kyte

Followup  

June 04, 2003 - 2:26 pm UTC

who said anything about going away?

there is an archive link on the home page, you can download by week.

July 09, 2003 - 7:05 pm UTC

Reviewer: A reader

The difference between 17 and 16 is 1 day the query returns 2,i am a little amazed as how didnt anyone notice...or am i missing something.
posted below are 2 examples 1 with weekend and 1 without week end.

SQL> SELECT 17-16 FROM DUAL ;
     17-16                                                 
----------                                                                    
         1                                                                      

SQL> SPOOL OFF
 




SQL> SELECT COUNT(*)
  2         FROM ( SELECT ROWNUM-1 RNUM
  3          FROM ALL_OBJECTS
  4          WHERE ROWNUM <= TO_DATE(NVL(NULL,TO_DATE('17-JUN-2003'))) - TO_DATE('16-JUN-2003') + 1 )
  5    WHERE TO_CHAR(TO_DATE(NVL(NULL,TO_DATE('17-JUN-2003')))+RNUM -1 , 'DY' ) NOT IN ( 'SAT', 'SUN' )
  6          AND NOT EXISTS ( SELECT NULL FROM HOLIDAYS WHERE HOLIDAYDATE =
  7              TRUNC(TO_DATE(NVL(NULL,TO_DATE('17-JUN-2003')))+RNUM-1) )
  8  /

  COUNT(*)                                                                      
----------                                                                      
         2                                                                      

SQL> SELECT COUNT(*)
  2         FROM ( SELECT ROWNUM-1 RNUM
  3          FROM ALL_OBJECTS
  4          WHERE ROWNUM <= TO_DATE(NVL(NULL,TO_DATE('27-JUN-2003'))) - TO_DATE('25-JUN-2003') + 1 )
  5    WHERE TO_CHAR(TO_DATE(NVL(NULL,TO_DATE('27-JUN-2003')))+RNUM -1 , 'DY' ) NOT IN ( 'SAT', 'SUN' )
  6          AND NOT EXISTS ( SELECT NULL FROM HOLIDAYS WHERE HOLIDAYDATE =
  7              TRUNC(TO_DATE(NVL(NULL,TO_DATE('27-JUN-2003')))+RNUM-1) )
  8  /

  COUNT(*)                                                                      
----------                                                                      
         2                                                                      

SQL> spool off
 

Tom Kyte

Followup  

July 09, 2003 - 8:25 pm UTC

somethings wrong -- but not the query.  I returned the number of days -- 16-jun/17-jun is two days.  for the second query -- I return 3 (there is no weekend there)

ops$tkyte@ORA920> @test 17-jun-2003 16-jun-2003
ops$tkyte@ORA920>   select rownum rnum, to_date( '&2')+rownum-1
  2             from all_objects
  3            where rownum <= to_date('&1') - to_date('&2')+1
  4  /
old   1:   select rownum rnum, to_date( '&2')+rownum-1
new   1:   select rownum rnum, to_date( '16-jun-2003')+rownum-1
old   3:           where rownum <= to_date('&1') - to_date('&2')+1
new   3:           where rownum <= to_date('17-jun-2003') - to_date('16-jun-2003')+1

      RNUM TO_DATE('
---------- ---------
         1 16-JUN-03
         2 17-JUN-03

ops$tkyte@ORA920>
ops$tkyte@ORA920> 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' )
  6                   not in ( 'SAT', 'SUN' );
old   4:           where rownum <= to_date('&1') - to_date('&2')+1 )
new   4:           where rownum <= to_date('17-jun-2003') - to_date('16-jun-2003')+1 )
old   5:  where to_char( to_date('&2')+rnum-1, 'DY' )
new   5:  where to_char( to_date('16-jun-2003')+rnum-1, 'DY' )

  COUNT(*)
----------
         2

ops$tkyte@ORA920> @test 27-jun-2003 25-jun-2003
ops$tkyte@ORA920>   select rownum rnum, to_date( '&2')+rownum-1
  2             from all_objects
  3            where rownum <= to_date('&1') - to_date('&2')+1
  4  /
old   1:   select rownum rnum, to_date( '&2')+rownum-1
new   1:   select rownum rnum, to_date( '25-jun-2003')+rownum-1
old   3:           where rownum <= to_date('&1') - to_date('&2')+1
new   3:           where rownum <= to_date('27-jun-2003') - to_date('25-jun-2003')+1

      RNUM TO_DATE('
---------- ---------
         1 25-JUN-03
         2 26-JUN-03
         3 27-JUN-03

ops$tkyte@ORA920>
ops$tkyte@ORA920> 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' )
  6                   not in ( 'SAT', 'SUN' );
old   4:           where rownum <= to_date('&1') - to_date('&2')+1 )
new   4:           where rownum <= to_date('27-jun-2003') - to_date('25-jun-2003')+1 )
old   5:  where to_char( to_date('&2')+rnum-1, 'DY' )
new   5:  where to_char( to_date('25-jun-2003')+rnum-1, 'DY' )

  COUNT(*)
----------
         3

ops$tkyte@ORA920>


Perhaps you loaded a date into the holidays table.

If you don't like my results, simply subtract 1. 

July 10, 2003 - 12:54 am UTC

Reviewer: A reader

Hi Tom,
subtracting one doesn't seem to resolve the problem,thats what i had done to initially resolve the problem, but the users came back with this week end scenario which i hadnt thought about.
Below are the eg, on days other then the week end it gives 0 days but on week ends it gives 1.
Can you pls let me know if there is something i am doing wrong, i have posted the query and the result below.

Thanks,







SQL> SELECT COUNT(*) - 1
  2             FROM ( SELECT ROWNUM-1 RNUM
  3              FROM ALL_OBJECTS
  4        WHERE ROWNUM <= TO_DATE(NVL(NULL,TO_DATE('17-JUN-2003'))) - TO_DATE('16-JUN-2003') + 1 )
  5        WHERE TO_CHAR(TO_DATE(NVL(NULL,TO_DATE('16-JUN-2003')))+RNUM -1 , 'DY' )
  6     NOT IN ( 'SAT', 'SUN' )
  7              AND NOT EXISTS ( SELECT NULL FROM HOLIDAYS WHERE HOLIDAYDATE =
  8                  TRUNC(TO_DATE(NVL(NULL,TO_DATE('16-JUN-2003')))+RNUM-1) )
  9  
SQL> 
SQL> /

COUNT(*)-1                                                                      
----------                                                                      
         0                                                                      

SQL> SELECT COUNT(*) - 1
  2             FROM ( SELECT ROWNUM-1 RNUM
  3              FROM ALL_OBJECTS
  4        WHERE ROWNUM <= TO_DATE(NVL(NULL,TO_DATE('16-JUN-2003'))) - TO_DATE('13-JUN-2003') + 1 )
  5        WHERE TO_CHAR(TO_DATE(NVL(NULL,TO_DATE('13-JUN-2003')))+RNUM -1 , 'DY' )
  6     NOT IN ( 'SAT', 'SUN' )
  7              AND NOT EXISTS ( SELECT NULL FROM HOLIDAYS WHERE HOLIDAYDATE =
  8                  TRUNC(TO_DATE(NVL(NULL,TO_DATE('13-JUN-2003')))+RNUM-1) )
  9  
SQL> 
SQL> /

COUNT(*)-1                                                                      
----------                                                                      
         1                                                                      

SQL> SELECT * FROM HOLIDAYS ;

HOLIDAYD                                                                        
--------                                                                        
01/01/02                                                                        
02/01/02                                                                        
06/02/02                                                                        
29/03/02                                                                        
01/04/02                                                                        
25/04/02                                                                        
03/06/02                                                                        
28/10/02                                                                        
25/12/02                                                                        
26/12/02                                                                        

10 rows selected.

SQL> SELECT 17-16 FROM DUAL;

     17-16                                                                      
----------                                                                      
         1                                                                      

SQL> SPOOL OFF
 

Tom Kyte

Followup  

July 10, 2003 - 8:08 am UTC

something is not adding up here "reader"

you holiday table contains nothing relevant.  all of the data is for 2002, your input dates are 2003.

If you run the queries:

ops$tkyte@ORA920>   select rownum rnum, to_date( '&2')+rownum-1
  2             from all_objects
  3            where rownum <= to_date('&1') - to_date('&2')+1
  4  /
old   1:   select rownum rnum, to_date( '&2')+rownum-1
new   1:   select rownum rnum, to_date( '16-jun-2003')+rownum-1
old   3:           where rownum <= to_date('&1') - to_date('&2')+1
new   3:           where rownum <= to_date('17-jun-2003') - to_date('16-jun-2003')+1

      RNUM TO_DATE('16
---------- -----------
         1 16-jun-2003
         2 17-jun-2003

<b>that one shows the dates that were generated</b>


ops$tkyte@ORA920>
ops$tkyte@ORA920> 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' )
  6                   not in ( 'SAT', 'SUN' )
  7     AND NOT EXISTS ( SELECT NULL FROM HOLIDAYS WHERE HOLIDAYDATE =
  8            TRUNC(TO_DATE(NVL(NULL,TO_DATE('&2')))+RNUM-1) )
  9  /
old   4:           where rownum <= to_date('&1') - to_date('&2')+1 )
new   4:           where rownum <= to_date('17-jun-2003') - to_date('16-jun-2003')+1 )
old   5:  where to_char( to_date('&2')+rnum-1, 'DY' )
new   5:  where to_char( to_date('16-jun-2003')+rnum-1, 'DY' )
old   8:           TRUNC(TO_DATE(NVL(NULL,TO_DATE('&2')))+RNUM-1) )
new   8:           TRUNC(TO_DATE(NVL(NULL,TO_DATE('16-jun-2003')))+RNUM-1) )

  COUNT(*)
----------
         2

<b>and that gives the counts</b>


You have a mistake somewhere in your test case here.  I'm not making this up -- look at my results, look at yours.  You scare me a bit with the reliance on default date formats.  perhaps that be your bug -- I see alot of nvl,to_date,trunc going on that is not necessary.  Most likely your bug introduced by reliance in implicit date formats and not having the default date format in use (you seem to have dd/mm/yy)

try using MY original queries.
add to them EXPLICIT to_dates -- using dd-mon-yyyy as the format mask.

Then post the results if you believe them to be "wrong" -- but use both queries ok -- so we can see the data that is generated AND the result of counting such data.

 

on a rownum

July 10, 2003 - 7:39 am UTC

Reviewer: Vj from in

In our webapplicabtion we have an lookup. We show 25 records each...there was a debate on which is the right way to get those 25 records..

i suggested

SELECT * FROM (
SELECT p.*, ROWNUM rnum
FROM (SELECT * FROM <TABLENAME> ) p
WHERE ROWNUM < <ENDROW>
) WHERE rnum >= <STARTROW> AND MYCOL1 = 'X' AND MYCOL2 = 'YES'

this shown an better plan, as compared to using MINUS Etc..but do you think i get a much better plan using analytical functions...because the table which we would be hitting would have some 1,000,000 rows !!!

Rgds

Tom Kyte

Followup  

July 10, 2003 - 10:01 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:127412348064 <code>

I believe you have the predicate AND MYCOL1 = 'X' AND MYCOL2 = 'YES' in the wrong place. but see that link, you are on the right path.

July 15, 2003 - 7:58 pm UTC

Reviewer: A reader

Hi Tom,
There is nothing wrong with either of the queries, the problem and solution are diff, i am looking for the difference and you are giving me the count, is there way i can get a difference between 2 business days.


Tom Kyte

Followup  

July 15, 2003 - 8:13 pm UTC

*there is obviously something wrong with your queries.*

<b>obviously wrong</b>

Use mine, as is, as presented.


Yours has a BUG
Mine, does not.

You are totally relying on default date formats, implicit conversions -- nasty stuff.

Proof that YOUR query is wrong:

ops$tkyte@ORA920LAP> SELECT COUNT(*)
  2             FROM ( SELECT ROWNUM-1 RNUM
  3              FROM ALL_OBJECTS
  4        WHERE ROWNUM <= TO_DATE(NVL(NULL,TO_DATE('17-JUN-2003'))) - TO_DATE('16-JUN-2003') + 1 )
  5        WHERE TO_CHAR(TO_DATE(NVL(NULL,TO_DATE('16-JUN-2003')))+RNUM -1 , 'DY' )
  6     NOT IN ( 'SAT', 'SUN' )
  7              AND NOT EXISTS ( SELECT NULL FROM HOLIDAYS WHERE HOLIDAYDATE =
  8                  TRUNC(TO_DATE(NVL(NULL,TO_DATE('16-JUN-2003')))+RNUM-1) )
  9  /

  COUNT(*)
----------
         1

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select count(*)
  2    from ( select rownum rnum
  3             from all_objects
  4            where rownum <= to_date('17-jun-2003') - to_date('16-jun-2003')+1 )
  5   where to_char( to_date('16-jun-2003')+rnum-1, 'DY' )
  6                   not in ( 'SAT', 'SUN' )
  7     AND NOT EXISTS ( SELECT NULL FROM HOLIDAYS WHERE HOLIDAYDATE =
  8            TRUNC(TO_DATE(NVL(NULL,TO_DATE('16-jun-2003')))+RNUM-1) )
  9  /

  COUNT(*)
----------
         2



<b>see, different answer, it is not my query -- stop using YOURS and use MINE</b>

that is how to get the difference between 2 business days.

<b>I honestly don't know how else to say this -- you have a bug in your query, stop using your query -- I'm baffled at constructs like this:

TRUNC(TO_DATE(NVL(NULL,TO_DATE('13-JUN-2003')))+RNUM-1) 

no clues why you are going to such convulted lengths to perform implict and explicit conversions</b>




 

Replacing Null with sysdate

July 16, 2003 - 4:26 am UTC

Reviewer: A reader

Hi tom,
The only thing i want different from your query is to be able to replace Null with the sysdate, As per requirement it may be possible that the todate or the fromdate may be Null in which case the system needs to use the sysdate, using nvl function is the only difference.

Tom Kyte

Followup  

July 16, 2003 - 9:33 am UTC

so, you have a requirement to default the dates to null.

so, what was the point of your code??? it doesn't do that.

this is

select count(*)-1
from ( select rownum rnum
from all_objects
where rownum <= nvl(to_date('&1'),sysdate) - nvl(to_date('&2'),sysdate)+1 )
where to_char( nvl(to_date('&2'),sysdate)+rnum-1, 'DY' )
not in ( 'SAT', 'SUN' )
and not exists
( select null from holidays where holidaydate =
trunc(nvl(to_date('&2'),sysdate)+rnum-1) )
/


is it very very simple.


Now, your homework -- explain to us all what this does:

TO_DATE(NVL(NULL,TO_DATE('17-JUN-2003')))


that is your code, I can tell you -- but can you tell us, do you understand what you coded there. did you figure out the error you made, why your query returns the wrong answer?

July 16, 2003 - 4:37 am UTC

Reviewer: A reader

Hi tom,
My idea is to get the difference and the difference between 17Jun and 16Jun is one day, Using ur query it gives me 2 days.



Tom Kyte

Followup  

July 16, 2003 - 9:34 am UTC

seems SIMPLE enought to me

"subtract one"


(oh wait, i already said that before.....)

Genius

August 19, 2003 - 10:55 am UTC

Reviewer: Mike from England

I'd just like to thank you for saving me a few hours of mental strain. I'll toast your name while I'm down the boozer when I should be in the office coming up with a solution to this problem.

Cheers!



Great, but how do I....

September 08, 2003 - 10:34 am UTC

Reviewer: Nick from NYC

Hi Tom,

I find your solution very helpful, but I'm having trouble with the implementaion.
I have a requirement to count records from a status-history table where the status transition takes X business days:

create table STATUS_HISTORY
(
STATUS_ID VARCHAR2(12) not null,
APP_ID VARCHAR2(12),
STATUS_TYPE VARCHAR2(10),
STATUS VARCHAR2(30),
START_DATE DATE,
END_DATE DATE
) tablespace tables;

create index IDX_NAMESTATUSTYPE on STATUS_HISTORY(STATUS,STATUS_TYPE,START_DATE) tablespace tables;

create index IDX_NAMETYPE on STATUS_HISTORY (APP_ID,STATUS_TYPE) tablespace tables;

create unique index PK_NAMESTATUS on STATUS_HISTORY (STATUS_ID) tablespace tables;
alter table STATUS_HISTORY add primary key (STATUS_ID);


I created a function using your busuness day calculator:
---
create or replace function NUM_BUSINESS_DAYS (START_DATE IN DATE, END_DATE IN DATE) return number as
n number;
begin
if( start_date is null or end_date is null ) then
n := NULL;
else
select count(*) into n
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' );
end if;

return n;
end;
---


The requirement is to give the percentage of YTD (actually Year to End-Of-Previous-Month) applications (APP_ID) that started in a certain STATUS and moved to the next STATUS in 2 days or less . Briefly, the STATUS_TYPE has to be 'Program' and the application has to start in STATUS='M39' and move to 'M52' within 2 days.

Here is what I came up with using the function:

SELECT n.total,d.total,DECODE (n.total, 0, 0, n.total / d.total * 100)
FROM (SELECT COUNT (DISTINCT (s.app_id)) total
FROM status_history s
WHERE s.status_type = 'Program'
AND s.status = 'M39'
AND TRUNC (s.start_date, 'YY') = TRUNC (ADD_MONTHS (SYSDATE, -1), 'YY')) d,
(SELECT COUNT (DISTINCT (strt.app_id)) total
FROM status_history strt
WHERE strt.status_type = 'Program'
AND strt.status = 'M39'
AND strt.start_date BETWEEN
TRUNC (ADD_MONTHS (SYSDATE, -1), 'YY') AND TRUNC (ADD_MONTHS (SYSDATE, -1),'MM')
AND EXISTS (
SELECT NULL
FROM status_history end
WHERE end.app_id = strt.app_id
AND end.status = 'M52'
AND end.status_type = 'Program'
AND end.start_date >= strt.start_date
AND NUM_BUSINESS_DAYS (strt.start_date, end.start_date)
BETWEEN 0
AND 2) ) n;


I'd now like to test this method against a straight SQL implementation (no function).

The question (finally) is how can I remove the function and stick your original query in there?

Thanks.


Tom Kyte

Followup  

September 08, 2003 - 12:38 pm UTC

tell you what -- this looks like a new question. I've got to cut back on taking new -- BIG -- questions here. takes too much time away from answering NEW questions.

An Alternate Approach

October 10, 2003 - 6:45 pm UTC

Reviewer: Andrew Rye from Chicago, IL

Hi Tom -

First time writer and big fan of yours. This thread caught my interest, and I wanted to suggest an approach similar to one mentioned above that seems to have significantly better performance than the initial suggestion (not so dependent on interval length).

Not enough room to post the entire script here, but I've cut out the more significant pieces and pasted below. See embedded comments for further explanation, but the basic idea is that there's no need to iterate through each day in the interval if you only want to count the business days. For an interval of 1 year, my function shaves a couple of orders of magnitude off the performance of the initial solution. I've verified that the calculation is equivalent, but left off those details for brevity's sake as well. I also have a "SQL Built-Ins"-only expression version of my function, but the performance was virtually identical to that of the stored user-defined function, so again left off for space reasons.

/*
|| holiday_table holds the dates to be excluded from the interval calculation
*/
CREATE TABLE holiday_table
( holiday_date DATE )
/*
|| Inserted 12/31/02, 1/1/03, 2/17/03, 5/26/03, 7/4/03, 9/1/03, 11/27/03,
|| 11/28/03, 12/24/03, 12/25/03, 12/31/03, and 1/1/04 into holiday_table
*/
CREATE TABLE test_interval
( start_date DATE,
end_date DATE
)
/*
|| Inserted 500 rows into test_interval, with dates ranging from 12/02 into 5/04
|| Tested intervals ranged from 1 day apart to beyond 1 year apart
*/
/*
|| The PL/SQL implementation of the business day logic
*/
CREATE OR REPLACE FUNCTION busdays_between
( p_start_dt DATE,
p_end_dt DATE )
RETURN NUMBER
IS
cln_start_dt DATE := TRUNC( p_start_dt );
cln_end_dt DATE := TRUNC( p_end_dt );
first_dt DATE;
last_dt DATE;
orientation PLS_INTEGER := 1;
first_dowk PLS_INTEGER;
count_first PLS_INTEGER := 1;
last_dowk PLS_INTEGER;
wks_btwn PLS_INTEGER;
sat_adjst PLS_INTEGER := 0;
count_hdays PLS_INTEGER := 0;
return_value NUMBER;
BEGIN
IF cln_start_dt <= cln_end_dt
THEN
first_dt := cln_start_dt;
last_dt := cln_end_dt;
ELSE
first_dt := cln_end_dt;
last_dt := cln_start_dt;
orientation := -1;
END IF;
SELECT COUNT( * )
INTO count_hdays
FROM holiday_table
WHERE holiday_date BETWEEN first_dt
AND last_dt;
first_dowk := TO_NUMBER( TO_CHAR( first_dt, 'D' ) );
last_dowk := TO_NUMBER( TO_CHAR( last_dt, 'D' ) );
wks_btwn := TRUNC( ( last_dt - first_dt ) / 7 );
IF first_dowk > last_dowk
THEN
wks_btwn := wks_btwn + 1;
END IF;
IF first_dowk = 7
THEN
sat_adjst := 1;
count_first := 0;
ELSIF first_dowk = 1
THEN
count_first := 0;
ELSE
NULL;
END IF;
IF last_dowk = 7
THEN
sat_adjst := sat_adjst - 1;
END IF;
return_value := ( ( wks_btwn * 5 ) +
( last_dowk - first_dowk + count_first ) +
sat_adjst - count_hdays
) * orientation;
RETURN return_value;
END; /* busdays_between */
/*
|| PL/SQL block to loop through the test_interval table and test performance using
|| discussed method in the thread
*/
DECLARE
next_interval NUMBER;
max_interval NUMBER := 0;
BEGIN
FOR date_rec IN
( SELECT *
FROM test_interval )
LOOP
SELECT COUNT( * )
INTO next_interval
FROM ( SELECT ROWNUM - 1 rnum
FROM ALL_OBJECTS
WHERE ROWNUM <= date_rec.end_date - date_rec.start_date + 1
)
WHERE TO_CHAR( date_rec.start_date + rnum, 'DY' ) NOT IN ( 'SAT', 'SUN' )
AND NOT EXISTS
( SELECT NULL
FROM holiday_table
WHERE holiday_date = TRUNC( date_rec.start_date + rnum )
);
max_interval := GREATEST( next_interval, max_interval );
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( max_interval ) );
END;
/*
344

Elapsed: 00:00:27.89 */
/*
|| Same basic PL/SQL block as above, using function instead
*/
DECLARE
next_interval NUMBER;
max_interval NUMBER := 0;
BEGIN
FOR date_rec IN
( SELECT *
FROM test_interval )
LOOP
SELECT busdays_between( date_rec.start_date,
date_rec.end_date )
INTO next_interval
FROM DUAL;
max_interval := GREATEST( next_interval, max_interval );
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( max_interval ) );
END;
/*
344

Elapsed: 00:00:00.21 */
/*
|| Showing dependence of performance upon interval length
*/
DECLARE
/*
|| One day interval
*/
date1 DATE := TO_DATE( '030102', 'YYMMDD' );
date2 DATE := TO_DATE( '030103', 'YYMMDD' );
next_interval NUMBER;
BEGIN
FOR i IN 1..1000
LOOP
SELECT busdays_between( date1, date2 )
INTO next_interval
FROM DUAL;
END LOOP;
END;
/* Elapsed: 00:00:00.31 */
DECLARE
/*
|| One year interval
*/
date1 DATE := TO_DATE( '030102', 'YYMMDD' );
date2 DATE := TO_DATE( '040102', 'YYMMDD' );
next_interval NUMBER;
BEGIN
FOR i IN 1..1000
LOOP
SELECT busdays_between( date1, date2 )
INTO next_interval
FROM DUAL;
END LOOP;
END;
/* Elapsed: 00:00:00.41 */
DECLARE
/*
|| One day interval
*/
date1 DATE := TO_DATE( '030102', 'YYMMDD' );
date2 DATE := TO_DATE( '030103', 'YYMMDD' );
next_interval NUMBER;
BEGIN
FOR i IN 1..1000
LOOP
SELECT COUNT( * )
INTO next_interval
FROM ( SELECT ROWNUM - 1 rnum
FROM ALL_OBJECTS
WHERE ROWNUM <= date2 - date1 + 1
)
WHERE TO_CHAR( date1 + rnum, 'DY' ) NOT IN ( 'SAT', 'SUN' )
AND NOT EXISTS
( SELECT NULL
FROM holiday_table
WHERE holiday_date = TRUNC( date1 + rnum )
);
END LOOP;
END;
/* Elapsed: 00:00:01.82 */
DECLARE
/*
|| One year interval
*/
date1 DATE := TO_DATE( '030102', 'YYMMDD' );
date2 DATE := TO_DATE( '040102', 'YYMMDD' );
next_interval NUMBER;
BEGIN
FOR i IN 1..1000
LOOP
SELECT COUNT( * )
INTO next_interval
FROM ( SELECT ROWNUM - 1 rnum
FROM ALL_OBJECTS
WHERE ROWNUM <= date2 - date1 + 1
)
WHERE TO_CHAR( date1 + rnum, 'DY' ) NOT IN ( 'SAT', 'SUN' )
AND NOT EXISTS
( SELECT NULL
FROM holiday_table
WHERE holiday_date = TRUNC( date1 + rnum )
);
END LOOP;
END;
/* Elapsed: 00:01:85.83 */


November 18, 2003 - 3:05 am UTC

Reviewer: vj from in

SELECT
REPDATE,CLOSEDDATE,
TRUNC(CLOSEDDATE)-TRUNC(REPDATE)+1 TOTDAYS,
CRITICALITY
FROM ISSUE
WHERE
TISSUE = 'Defect'

This is my select statement..

REPORTEDDATE CLOSEDATE TOTDAYS CRITICALITY
05-Feb-02 05-Feb-02 1 High
08-Feb-02 08-Feb-02 1 Medium
30-Jan-02 09-Feb-02 11 Medium -->i want 9
09-Feb-02 09-Feb-02 1 Medium
11-Feb-02 11-Feb-02 1 Medium
11-Feb-02 11-Feb-02 1 Medium
11-Feb-02 11-Feb-02 1 Medium
28-Sep-02 28-Sep-02 1 Medium
30-Sep-02 30-Sep-02 1 Medium
30-Sep-02 03-Oct-02 4 Medium
30-Sep-02 03-Oct-02 4 Medium
30-Sep-02 30-Sep-02 1 Medium
30-Sep-02 04-Oct-02 5 Medium

This is the output...when i get the totdays i wanted to exclude the sat and sun..
Rgds

Tom Kyte

Followup  

November 21, 2003 - 7:41 am UTC

great -- that is exactly what this page shows how to do?? just write a function that does the logic?

I need help with this query using number of business days

March 19, 2004 - 8:33 am UTC

Reviewer: sonali from waltham, ma

I need help with this query..
Here is the original query which runs very very slow,
mwebcounter table has only one column which stores number from 1 to say 2000.
I am trying to get one record per resource per day.

SELECT Res.Res_ID ,
COUNT (Distinct(a.AuthAttr_Start_Date + b.Counter_ID)) * (Res.Res_Work_Week / 5) ,
SUM(a.AuthAttr_Amount)
FROM mwebRes Res, mwebAuthAttrib a, mwebCounter b
WHERE Res.Res_ID = a.AuthAttr_Res_ID AND
a.AuthAttr_Finish_Date >= (a.AuthAttr_Start_Date + b.Counter_ID) AND
to_char(AuthAttr_start_date + counter_ID, 'D') NOT IN (7,1) AND
(a.AuthAttr_Category = 567) AND
(a.AuthAttr_Amount <> 0)
GROUP BY Res.Res_ID, Res.Res_Work_Week

SO I changed it to this query which uses number of business days function..
but it does not return same number of rows. I think the problem is I need to somehow use number of
business days function.. and get one record for every resource starting from authattr_start_date and
ending at authattr_start_date + number of business days.. how do I do it ? Like a loop ?
SELECT Res.Res_ID ,
num_business_days(a.AuthAttr_Start_Date ,a.AuthAttr_Finish_Date) * (Res.Res_Work_Week / 5) ,
SUM(a.AuthAttr_Amount)
FROM mwebRes Res, mwebAuthAttrib a
WHERE Res.Res_ID = a.AuthAttr_Res_ID AND
a.AuthAttr_Finish_Date >= a.AuthAttr_Start_Date AND
(a.AuthAttr_Category = 567) AND
(a.AuthAttr_Amount <> 0)
GROUP BY Res.Res_ID, Res.Res_Work_Week, a.AuthAttr_Start_Date ,a.AuthAttr_Finish_Date

Translating a 7 digit binary string to Days of Week

July 23, 2004 - 3:46 pm UTC

Reviewer: Logan Palanisamy from Santa Clara, CA, USA

Tom,

Is there simple way to translate a 7 digit binary string into days of week? i.e. Simple as in SQL without writing a PL/SQL function. Thought of using CASE, but it might be very lenghty.

Input Required output
======== ===============
0100010 -M---F-
1000001 S-----S
0000011 -----FS
1111111 SMTWTFS
0000000 -------

These are sample inputs. It could be any of those 128 (2 to the power 7) possible combinations.

Thanks in advance.

Tom Kyte

Followup  

July 23, 2004 - 5:12 pm UTC

the first and easiest thing that pops into mind is

decode(substr(input,1,1),'0','-','S') ||
decode(substr(input,2,1),'0','-','M') ||
decode(substr(input,3,1),'0','-','T') ||
.....
decode(substr(input,7,1),'0','-','S')




Translating binary string into Days of Week

July 23, 2004 - 4:15 pm UTC

Reviewer: A reader

SQL> create table t (f1 varchar2(7));

Table created.

SQL> insert into t values ('0000000');

1 row created.

SQL> insert into t values ('0001001');

1 row created.

SQL> insert into t values ('0010001');

1 row created.

SQL> insert into t values ('0001101');

1 row created.

SQL> insert into t values ('1111111');

1 row created.

SQL> insert into t values ('1010011');

1 row created.

SQL> insert into t values ('0000101');

1 row created.

SQL> insert into t values ('0001001');

1 row created.

SQL> insert into t values ('0010001');

1 row created.

SQL> insert into t values ('1100001');

1 row created.

SQL> 
SQL> select f1,
  2  decode(substr(f1, 1,1), 1, 'S', '-') ||
  3  decode(substr(f1, 2,1), 1, 'M', '-') ||
  4  decode(substr(f1, 3,1), 1, 'T', '-') ||
  5  decode(substr(f1, 4,1), 1, 'W', '-') ||
  6  decode(substr(f1, 5,1), 1, 'T', '-') ||
  7  decode(substr(f1, 6,1), 1, 'F', '-') ||
  8  decode(substr(f1, 7,1), 1, 'S', '-') DOW
  9  from t
 10  /

F1      DOW
------- -------
0000000 -------
0001001 ---W--S
0010001 --T---S
0001101 ---WT-S
1111111 SMTWTFS
1010011 S-T--FS
0000101 ----T-S
0001001 ---W--S
0010001 --T---S
1100001 SM----S
 

Tom Kyte

Followup  

July 23, 2004 - 5:14 pm UTC

and someone else has the same idea at the same time :)

no of working days

August 17, 2004 - 9:29 am UTC

Reviewer: Tanweer from USA

I want a output like this:

starting date ending date week No No of working days
01-apr-2004 04-apr-2004 1 3
05-apr-2004 09-apr-2004 2 5
12-apr-2004 17-apr-2004 3 6
19-apr-2004 23-apr-2004 4 5

-------

upto 31-mar-2005 (Consider it as one financial year)

1.My input will be the financial year date (from-01-apr-2004 and 31-mar-2005)
2. weekly off is sunday, second and 4th saturday

If any one help to slove the out put (in procedure/function)


Tom Kyte

Followup  

August 17, 2004 - 10:02 am UTC

don't understand the first row of data there. why 04-apr-2004

Translating a 7 digit binary string to Days of Week

August 17, 2004 - 11:27 am UTC

Reviewer: virgile from Paris , France

Hi,
Logan Palanisamy ask for a way to decode a BINARY STRING
and the next post (i don't know how a reader was able to submit a post for me i cannot) was setting up an example for a VARCHAR2(7) decoding. While it seems to be the same problematic (for decoding part), it is not the same for the storing part ( 7 BYTES vs 7 BITS and same information stored).

I just want to tell that, for binary strings another possibility came at hands : use of so call "BIT operators".
I this meaning i suggest everyone to read METALINK DOC "Boolean type and Bitwise Operators ".

Thanks,

Virgile

August 20, 2004 - 8:53 am UTC

Reviewer: virgile from Paris, France

Hi,

In addition to my recent post, i try manipulate RAW and find it very difficult.
So i provide an example using a CHAR(1) (capable of storing a MAP up to 8 bits).
Its easy to extend utilization to CHAR(N) to have 8 X N bits of possibilities
(but you have to take care of CHARSET of the database you are currently using).

drop table T1;
create table T1 (map varchar2(1 BYTE) );

REM i insert every possible value in table with an anonymous block
DECLARE
n number;
compteur number;
BEGIN
n := 0;
FOR compteur in 0 .. 255
LOOP
insert into t1 values (chr(compteur));
n := n+1;
END LOOP;
COMMIT;
END;

REM i create a function to lookup for a number representation of the character
REM for a given column
create or replace function my_funct (R IN VARCHAR) return varchar2
IS
my_value number;
BEGIN
select to_number(replace(substr(dump(R,10), 14,3 ),' ','')) into my_value from dual;
return my_value;
END;
/

col "number" for a3;
col "bin" for a8;
select my_funct(map) as "number",
to_bin(my_funct(map)) as "bin" from t1
where rownum <= 10;

It gives an output like this :

num bin
--- --------
0 0
1 1
2 10
3 11
4 100
5 101
6 110
7 111
8 1000

Now, we can use another function to have it decipher (a very simple one just to illustrate example) :

create or replace function decipher_this (R IN VARCHAR) return VARCHAR2
is
my_string varchar(100);
my_string2 varchar(100);
my_number number;
BEGIN
select lpad(to_bin(my_funct(R)),7,0) into my_string from dual;
select decode(substr(my_string, 1,1), 1, 'S', '-') ||
decode(substr(my_string, 2,1), 1, 'M', '-') ||
decode(substr(my_string, 3,1), 1, 'T', '-') ||
decode(substr(my_string, 4,1), 1, 'W', '-') ||
decode(substr(my_string, 5,1), 1, 'T', '-') ||
decode(substr(my_string, 6,1), 1, 'F', '-') ||
decode(substr(my_string, 7,1), 1, 'S', '-') into my_string2 from dual;
return my_string2;
END;
/

col "number" for a3;
col "bin" for a8;
col "decipher" for a7;
select my_funct(map) as "number",
to_bin(my_funct(map)) as "bin",
decipher_this (map) as "decipher" from t1
where rownum <= 10;

num bin deciphe
--- -------- -------
0 0 -------
1 1 ------S
2 10 -----F-
3 11 -----FS
4 100 ----T--
5 101 ----T-S
6 110 ----TF-
7 111 ----TFS
8 1000 ---W---
9 1001 ---W--S


Looks like we have what we were looking for, but in one BYTE instead of 7......

Virgile

add_business_days

November 04, 2004 - 12:10 pm UTC

Reviewer: sonali from waltham, ma

If I have a date and num_business_days and I want to add it and return it, how to do it ..
I have code as below but is slow...

CREATE OR REPLACE FUNCTION add_business_days(dtInitial IN DATE, inDays IN NUMBER, inWeekends IN NUMBER)
RETURN DATE IS
dtResult date;
inCount number;
inAddNum number;

BEGIN

dtResult := dtInitial;
if inWeekends = 10 then
dtResult := dtInitial + inDays;
else
if inDays >= 0 then
inCount := 0;
while inCount < inDays
loop
inAddNum := 1+ (CASE (to_number(to_char (dtResult+1, 'd'))) when 7 then 2 when 1 then 1 else 0 end);
dtResult := dtResult + inAddNum;
inCount := inCount + 1;
end loop;
else
inCount := inDays;
while inCount < 0
loop
inAddNum := 1+ (CASE (to_number(to_char (dtResult-1, 'd'))) when 7 then 1 when 1 then 2 else 0 end);
dtResult := dtResult - inAddNum;
inCount := inCount + 1;
end loop;
end if;

end if;

RETURN(dtResult);
END;
/
Thanks

Tom Kyte

Followup  

November 05, 2004 - 2:36 pm UTC

don't understand the logic -- didn't try to totally reverse engineer it -- but "inweekends"??  

if you only are skipping weekends - why not make it an "add weeks"

trunc(indays/5) -> gives weeks
mod(indays,5)   -> gives the last couple of days to add.

result + trunc(indays/5)*7 -- and then to that just need to add the rest of the week or mod(indays,5) more days....


ops$tkyte@ORA9IR2> select * from (
  2  select dt, r,
  3         add_business_days( dt, r, inweekends ) func,
  4         decode( inweekends, 10, dt+r,
  5                     dt+trunc(r/5)*7+mod(r,5)+sign(greatest(0,mod(r,5)+to_char(dt,'d')-6))*2)  new_dt
  6    from (select dates.* , bdays.*, mod(rownum,10)+1 inweekends
  7           from (select sysdate+rownum dt from all_users where rownum <= 7) dates,
  8                (select rownum r from all_objects where rownum <= 350) bdays
  9                  where to_char(dt,'DY') not in ('SAT','SUN')
 10             )
 11  )
 12   where new_dt <> func
 13  /



that decode should work unless 

ops$tkyte@ORA9IR2> select to_char(next_day(sysdate,'mon'),'d') from dual;
 
T
-
2

returns a different number -- then you need to play with the "sign(greatest...)


the logic:

dt+trunc(r/5)*7+mod(r,5)+sign(greatest(0,mod(r,5)+to_char(dt,'d')-6))*2

take DT
add to that trunc(r/5)*7 -- that adds N weeks to the dt.

Now, mod(r,5) will be 0,1,2,3 or 4
and to_char(dt,'d') will be 2, 3, 4, 5, 6

(I'm assuming SAT and SUN are NOT valid inputs to this, if they are -- you need to fix that as well)......

We can add 0,1,2,3,4 days to monday (2) with no implications

We can add 0,1,2,3 days to tuesday(3) -- but if we try to add 4 -- we are on saturday, must add 2 more days  (3+4 = 7)

We can add 0,1,2 days to wed(4) -- but if we try to add 3 or 4 -- we are on sat/sun, must add 2 more days (4+3 = 7, 4+4 = 8)

We can add 0,1, days to thurs (5) -- but if we try to add 2, 3 or 4 -- we either are on sat/sun or skipped the weekend to monday -- regardless - must add 2 more days (5+2 = 7, 5+3 = 8, 5+4 = 9)

And likewise for friday.

so that is what:

sign(greatest(0,mod(r,5)+to_char(dt,'d')-6))*2

is all about.  take mod(r,5) (the extra days to add) and add to that the day of the week (2,3,4,5,6) -- if that is greater than or equal to 7, we must add 2 more days.  So, we subtract 6 and take the greatest of that and 0 -- take the sign of that (returns -1 if negative, never negative, 0 if 0 and plus 1 if positive) and multiply that (either 0 or 1) by 2....


QED

Yes, there are at least 50 billion other ways to do it, that was the first that popped into my head.

yes, it is much faster than the plsql. 

Adding Buisness Days

November 29, 2004 - 8:33 am UTC

Reviewer: Shrikant Gavas from USA

Hi Tom

Thanks For all the help you are providing.

Is it possible to add specified number of working days to a particular date. For example take Thursday, '01-Jan-2004'. If I add 5 working days to this, it should return me '08-Jan-2004'. Please note that we can add upto 120 working days.

Thanks & Regards
Shrikant

Tom Kyte

Followup  

November 29, 2004 - 8:46 am UTC

this is a followup right after the previous one?  did you read the last followup before yours :)  ???


ops$tkyte@ORA9IR2> variable dt varchar2(20)
ops$tkyte@ORA9IR2> variable r number
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :dt := '01-jan-2004'; :r := 5;
 
PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select to_date(:dt,'dd-mon-yyyy')+trunc(:r/5)*7+mod(:r,5)+sign(greatest(0,mod(:r,5)+to_char(to_date(:dt,'dd-mon-yyyy'),'d')-6))*2  new_dt
  2    from dual
  3  /
 
NEW_DT
---------
08-JAN-04
 
ops$tkyte@ORA9IR2> exec :r := 120
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> /
 
NEW_DT
---------
17-JUN-04
 
 

Thanks Tom

November 29, 2004 - 9:07 am UTC

Reviewer: Shrikant

Tom, Can you please explain the logic behind this.

Tom Kyte

Followup  

November 29, 2004 - 2:55 pm UTC

"page up"

been there, did that....

Another SQL to Generate Business days between two dates ...

November 29, 2004 - 8:12 pm UTC

Reviewer: VKOUL from LACEY, WA USA

ACCEPT start_dt PROMPT "Enter Start Date : "
ACCEPT end_dt PROMPT "Enter End Date : "

SELECT SUM(days) "Total Working Days"
FROM (SELECT ((TO_DATE('&start_dt') + TRUNC((TO_DATE('&end_dt') - TO_DATE('&start_dt') + 1)/7)*7 - 1) - TO_DATE('&start_dt') + 1)*5/7 days
FROM DUAL
UNION ALL
SELECT CASE
WHEN TO_CHAR((TO_DATE('&start_dt') + TRUNC((TO_DATE('&end_dt') - TO_DATE('&start_dt') + 1)/7)*7 - 1)+ROWNUM, 'D')
NOT IN (1, 7)
THEN 1
ELSE 0
END
FROM (SELECT 'x'
FROM DUAL
GROUP BY CUBE (1,2,3)
)
WHERE (TO_DATE('&start_dt') + TRUNC((TO_DATE('&end_dt') - TO_DATE('&start_dt') + 1)/7)*7 - 1) + ROWNUM <= TO_DATE('&end_dt')
)
/

I haven't incorporated any holiday table logic, but played with it for some date ranges

Could you pls. check it out ?


counting business days

December 20, 2004 - 11:11 am UTC

Reviewer: Robin from Ski capital of the World, Vermont, USA

I see you've had lots of responses to this question. I'm using the following to count business days:

FUNCTION PU_weekday_count (v_begdate IN DATE, v_enddate IN DATE ) RETURN number
IS

currdate date := v_begdate; /* holds the next date */
theDay varchar2(10);/* day of the week for currdate */
countBusiness number := 0; /* counter for business days */

begin

/* start date must be earlier than end date */
if v_enddate - v_begdate <= 0 then
return (0);
end if;

loop
/* go to the next day */

/* finished if end_date is reached */
exit when currdate = v_enddate;

/* what day of the week is it? */
select to_char(currdate,'fmDay') into theDay from dual;

/* count it only if it is a weekday */
if theDay <> 'Saturday' and theDay <> 'Sunday' then
countBusiness := countBusiness + 1;
end if;

currdate := to_date(currdate+1);

end loop;

return (countBusiness);

EXCEPTION

WHEN OTHERS THEN
raise_application_error(-20001,'There was an error in edit_check_pkg.weekday_count.') ;
return 0;
END pu_weekday_count;

and the following to exclude holidays stored in a table:

FUNCTION PU_COUNT_HOLIDAYS(v_idno VARCHAR2, v_dstid VARCHAR2, v_date1 IN DATE, v_date2 IN DATE) RETURN NUMBER IS

p_dstid VARCHAR2(6) := v_dstid;

p_date1 DATE := v_date1;

p_date2 DATE := v_date2;

p_hol_count NUMBER := 0;

p_csr PKG_CURSOR_WEAK.v_CSR;

p_idno NUMBER := v_idno;

p_recordcount NUMBER;

p_admdate DATE;

p_strsql VARCHAR2(100);

TYPE holRec IS RECORD (hol1 DATE, hol2 DATE, hol3 DATE,hol4 DATE, hol5 DATE);

p_hol_rec holRec;

BEGIN


p_admdate := PKG_CONSTANT.C_admdate;

OPEN p_csr for SELECT hol1, hol2, hol3, hol4, hol5 FROM st_a_calc_adm_2 WHERE st_a_calc_adm_2.recordno = v_idno and st_a_calc_adm_2.ADMTDIST = v_dstid;

LOOP
FETCH p_CSR INTO p_hol_rec;
p_recordcount := p_csr%ROWCOUNT;
EXIT WHEN p_csr%NOTFOUND;
-- if the holiday falls within the range I'm calculating then count it so that we can subtract it from totdays.

IF NVL(p_hol_rec.hol1, p_admdate) <= p_date2 AND NVL(p_hol_rec.hol1, p_admdate)>= p_date1 THEN
p_hol_count := p_hol_count + 1;
END IF;

IF NVL(p_hol_rec.hol2, p_admdate) <= p_date2 AND NVL(p_hol_rec.hol2, p_admdate)>= p_date1 THEN
p_hol_count := p_hol_count + 1;
END IF;
IF NVL(p_hol_rec.hol3, p_admdate) <= p_date2 AND NVL(p_hol_rec.hol3, p_admdate)>= p_date1 THEN
p_hol_count := p_hol_count + 1;
END IF;
IF NVL(p_hol_rec.hol4, p_admdate) <= p_date2 AND NVL(p_hol_rec.hol4, p_admdate)>= p_date1 THEN
p_hol_count := p_hol_count + 1;
END IF;
IF NVL(p_hol_rec.hol5, p_admdate) <= p_date2 AND NVL(p_hol_rec.hol5, p_admdate)>= p_date1 THEN
p_hol_count := p_hol_count + 1;
end if;
end loop;

RETURN p_hol_count;

CLOSE p_csr;
end;

The calling procedure contains a cursor with approx. 100,000 records. The begin & end dates are different for each student. Basically I'm trying to count the days the child attended a particular school.

These procedures seem to bog down the database somewhat (when compared to other more complex updates/calculations) and I'm wondering if that's the expected behavior.

Tom Kyte

Followup  

December 20, 2004 - 1:46 pm UTC

did you try one of the faster approaches we've worked through over the years above?

Slower than ever...

December 21, 2004 - 11:31 am UTC

Reviewer: Robin

I changed my two function calls to the following (from above):

select count(*) into p_countdays
from ( select rownum rnum
from all_objects
where rownum <= to_date(p_enddate) - to_date(p_begdate) )
where to_char( to_date(p_begdate)+rnum, 'DY' )
not in ( 'SAT', 'SUN' )
and not exists
( select null from STU_CEN.vw_fsc_exclude_dates where holiday = trunc(to_date(p_begdate)+rnum) and orgid = v_orgid );

(I needed to modify the code given to include the orgid for each school because they all have different holidays.)

Basically what I'm doing now is looping through a cursor, checking a student record to see what his/her begin date and end date are at that particular school. I then pass those variables into the above code.

If anything, it seems to be running slower than ever. A cursor with 1500 student records took around 3 minutes with the revised code vs. 1 1/2 with the old code. It takes milliseconds to run other (seemingly more complex computations) so I'm very confused.

Thanks for any insight you can give me!

Tom Kyte

Followup  

December 21, 2004 - 1:26 pm UTC

you said it was bogging down?  milliseconds *doesn't fit any definition of bogging down i've heard od'


instead of all objects, 

create table my_rows (x primary key) organization index
as select rownum from all_objects;


and try to remove a "loop calling a function", just select this right out.


ops$tkyte@ORA9IR2> select object_name,
  2        ( select count(*)
  3            from my_rows
  4           where x <= AO.last_ddl_time - AO.created
  5             and to_char( AO.created+x, 'DY' ) not in ( 'SAT', 'SUN' )
  6        ) days
  7    from all_objects ao;
 

like that. 

Additional Info

December 21, 2004 - 1:05 pm UTC

Reviewer: Robin

When I try to view the SQL in the Enterprise Manager Console, I get the following error:

The explain plan cannot viewed because the cached plan for this cursor is malformed.

I wasn't getting this error before I changed the code.

Also, above the question was posted:


Why are we adding -1 to rownum, and then adding one below
where rownum <= to_date('&1') - to_date('&2')+1 ), and then subtracting -1 again below where to_char( to_date('&2')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' )


Followup:
I corrected that.


I don't find a corrected version posted.

thanks again!

Tom Kyte

Followup  

December 21, 2004 - 1:28 pm UTC

you don't see me adding -1 to rownum ;)

I fixed it.

Maybe I can clarify the problem.....

December 21, 2004 - 2:09 pm UTC

Reviewer: Robin

We have a staging table of student data which contains the student's enrollment begin and end dates. We have ANOTHER table of organizational (school) data containing the school's measurement begin and end dates and holidays (for this calculation). For each organization, we loop through the student records and calculate the number of business days in attendance for first 2 months (excluding holidays).

After the calculation in performed for EACH STUDENT the student record is updated in the staging table with the total attendance for that first 2 month period.

My dba and I don't see how we can accomplish this based on your code example. We can't bulk collect and update because each student can have different attendance dates.

Seems we need to use a cursor and pass in the student begin and end date in order to get the calc, then use the student identifier to go back and update the student record with the calc. However, this is the SLOWEST part of our app and we need to speed this up as much as possible. To clarify previous post, THIS ONE takes minutes to perform - other more complex calculations take MILLISECONDS. So what can we do to address this? Version 9.2.0.1, using RBO (no statistics), minimal indexes on tables.

Lastly, what exactly does that MALFORMED CURSOR message we got mean (see last post)?

Thanks much for your time!

Tom Kyte

Followup  

December 21, 2004 - 2:47 pm UTC

did you see the scalar subquery I used?

ops$tkyte@ORA9IR2> select object_name,
  2        ( select count(*)
  3            from my_rows
  4           where x <= AO.last_ddl_time - AO.created
  5             and to_char( AO.created+x, 'DY' ) not in ( 'SAT', 'SUN' )
  6        ) days
  7    from all_objects ao;


last_ddl_time = end_date
created = begin_date


 

Very interesting, but in your approach as the interval increases, so does the calculation time

December 21, 2004 - 2:39 pm UTC

Reviewer: Alexander from Chicago

Using a calendar table, we can equally fast calculate number of business days in a week and in a year

Settting up (stripped from tablespaces, storage clauses, indexes etc.):

CREATE TABLE CALENDAR(
BUSINESS_NUM NUMBER(5) NOT NULL,
NUM NUMBER(5) NOT NULL,
DAY DATE NOT NULL,
DAY_OF_WEEK NUMBER(1) NOT NULL,--0 - Sun,..., 6 - Sat
DAY_TYPE NUMBER(2) NOT NULL,
--- 0-work day, 1 weekend, 2 holiday
YEAR NUMBER(4) NOT NULL,
MONTH NUMBER(2) NOT NULL
);

INSERT INTO CALENDAR(BUSINESS_NUM, NUM, DAY, DAY_OF_WEEK, DAY_TYPE, YEAR, MONTH)
SELECT 0 AS BUSINESS_NUM,
N AS NUM,
(TO_DATE('09/01/2003', 'MM/DD/YYYY') + N) AS DAY,
MOD(N+1, 7) AS DAY_OF_WEEK,
CASE MOD(N+1, 7)
WHEN 0 THEN 1
WHEN 6 THEN 1
ELSE 0
END AS DAY_TYPE,
0 AS YEAR,
0 AS MONTH
FROM
(select (rownum-1) AS N from user_tAB_COLUMNS WHERE ROWNUM<1000) SEQ;
COMMIT;

--- UPDATE HERE HOLIDAY SCHEDULE FOR 2003 / 2004 ---------------
UPDATE CALENDAR SET DAY_TYPE=2 WHERE DAY IN (SELECT HOLIDAY FROM HOLIDAY_SCHEDULE);
COMMIT;

----- we perform the calculations only once, during set up
UPDATE CALENDAR
SET BUSINESS_NUM = (SELECT COUNT(*) FROM CALENDAR S1 WHERE S1.NUM < CALENDAR.NUM AND S1.DAY_TYPE=0),
YEAR = EXTRACT(YEAR FROM DAY),
MONTH = EXTRACT(MONTH FROM DAY);
COMMIT;

sample data from CALENDAR look like this:

SELECT BUSINESS_NUM BN, NUM, DAY, DAY_OF_WEEK DOW, DAY_TYPE
FROM CALENDAR WHERE DAY BETWEEN '24-NOV-2004' AND '1-DEC-2004'
BN NUM DAY DOW DAY_TYPE
------ ------ --------------------- --- --------
312 450 11/24/2004 3 0
313 451 11/25/2004 4 2
313 452 11/26/2004 5 2
313 453 11/27/2004 6 1
313 454 11/28/2004 0 1
313 455 11/29/2004 1 0
314 456 11/30/2004 2 0
315 457 12/1/2004 3 0
8 rows selected

Calculations for 1 week

SELECT
(SELECT BUSINESS_NUM FROM CALENDAR WHERE DAY = '1-DEC-2004')
-
(SELECT BUSINESS_NUM FROM CALENDAR WHERE DAY = '24-NOV-2004')
BUSINESS_DAYS_BETWEEN
FROM DUAL
BUSINESS_DAYS_BETWEEN
---------------------
3
1 row selected

i.e. there were 3 business days in the Thanksgiving week

Calculations for 1 year

SELECT
(SELECT BUSINESS_NUM FROM CALENDAR WHERE DAY = '31-DEC-2004')
-
(SELECT BUSINESS_NUM FROM CALENDAR WHERE DAY = '1-JAN-2004')
BUSINESS_DAYS_BETWEEN
FROM DUAL
BUSINESS_DAYS_BETWEEN
---------------------
251
1 row selected

What do you think?


Tom Kyte

Followup  

December 21, 2004 - 3:13 pm UTC

i agree - it seems a logical conclusion just looking at the approach.

Excluding holidays...

December 30, 2004 - 10:18 am UTC

Reviewer: Robin from Cold in Vermont

We're doing the following procedure (using your code from above -- somewhat modified )

We are collecting data on students in about 350 schools. (Schools have different holidays. ) Students may have different start and end dates within each school.

Our goal: Count how many days each student attended a school during the first 40 days a school was in session less holidays/weekends.

We have a few problems with the count. First our holiday information is saved horizontally in each record for each student -- original design -- won't be changeable per the powers that be). Currently we are taking the holiday list from the school profile table (also horizontal) and making it vertical using a view.

We then have to do the following to do the count:


PROCEDURE PU_STEP_19 is

p_bool BOOLEAN := FALSE;
p_strsql VARCHAR2(1000);
p_admdate DATE := STU_CEN.PKG_CONSTANT.C_ADMDATE ;

CURSOR p_csr IS
SELECT orgid
FROM stu_cen.li_org_app_passed
WHERE appl = 'P'
ORDER BY orgid;

p_org_rec p_csr%rowtype;

BEGIN

FOR p_org_rec IN p_csr LOOP

--First date is ADMEND date, second date is ADMBEGINDATE.

insert into STU_CEN.GT_ADM_DAY_COUNT (select recordno, ENRORGID, (select count(*) from STU_CEN.my_rows
where x <= st_a_calc_adm_2.admend - st_a_calc_adm_2.admbeg
and to_char( st_a_calc_adm_2.admbeg+x, 'DY' ) not in ( 'SAT', 'SUN' )
and not exists( select null from STU_CEN.vw_fsc_exclude_dates where holiday = trunc(st_a_calc_adm_2.admbeg+x) and orgid = p_org_rec.orgid )) ADMdays, admtdist,ADMFPSTAT, ADMBEG,GRADE from STU_CEN.st_a_calc_adm_2 where enrorgid = p_org_rec.orgid);


UPDATE STU_CEN.ST_A_CALC_ADM_2 SET TOTDAYS = (SELECT ADMDAYS FROM STU_CEN.GT_ADM_DAY_COUNT WHERE ST_A_CALC_ADM_2.RECORDNO = GT_ADM_DAY_COUNT.RECORDNO
and st_a_calc_adm_2.admtdist = gt_adm_day_count.admtdist
and st_a_calc_adm_2.admfpstat = gt_adm_day_count.admfpstat
and st_a_calc_adm_2.admbeg = gt_adm_day_count.admbeg
and st_a_calc_adm_2.grade = gt_adm_day_count.grade)
WHERE ENRORGID = p_org_rec.orgid;

END LOOP;

-- the admdays field is now calculated as totaldays less total holidays ????????????????????????????
commit;

END PU_STEP_19;

My question...is there a way to modify the above code (the day count) to exclude holidays based on columns within the source table (in our case ST_A_CALC_ADM_2) instead of from a table of days to exclude?

thanks, Robin



Tom Kyte

Followup  

December 30, 2004 - 11:37 am UTC

the things i would look at first would be questions like:

why am I doing this:

insert into STU_CEN.GT_ADM_DAY_COUNT (select recordno, ENRORGID,
(select count(*) from STU_CEN.my_rows
where x <= st_a_calc_adm_2.admend - st_a_calc_adm_2.admbeg
and to_char( st_a_calc_adm_2.admbeg+x, 'DY' ) not in ( 'SAT', 'SUN' )

and not exists( select null from STU_CEN.vw_fsc_exclude_dates where holiday =
trunc(st_a_calc_adm_2.admbeg+x) and orgid = p_org_rec.orgid )) ADMdays,
admtdist,ADMFPSTAT, ADMBEG,GRADE from STU_CEN.st_a_calc_adm_2 where enrorgid =
p_org_rec.orgid);

in a loop when

insert into STU_CEN.GT_ADM_DAY_COUNT
(select recordno,
ENRORGID,
(select count(*)
from STU_CEN.my_rows
where x <= st_a_calc_adm_2.admend - st_a_calc_adm_2.admbeg
and to_char( st_a_calc_adm_2.admbeg+x, 'DY' ) not in ( 'SAT', 'SUN' )
and not exists( select null
from STU_CEN.vw_fsc_exclude_dates
where holiday = trunc(st_a_calc_adm_2.admbeg+x)
and orgid = p_org_rec.orgid )) ADMdays,
admtdist,
ADMFPSTAT,
ADMBEG,
GRADE
from STU_CEN.st_a_calc_adm_2
where enrorgid in ( SELECT orgid
FROM stu_cen.li_org_app_passed
WHERE appl = 'P')
group by enrorgid;

(and the update is probably the same....)



but I don't have/see all of the columns here (the insert doesn't have them listed, so I don't know what is what...)


I tried doing as you suggested

January 07, 2005 - 9:45 am UTC

Reviewer: Robin from snowy vt

and removing the loop. However, the insert still takes over an hour to run. My question is this:

I have a student record that includes the begin, end and holidays for each as values horizontally in the row.

Ex:

StuID, BEGDATE, HOL1, HOL2, HOL3, HOL4, ENDDATE, ORG
1111, 9/1/04, 9/6/04, 9/7/04, 10/11/04, NULL, 11/1/04, P1
1112, 9/1/04, 9/6/04, 9/7/04, 10/11/04, NULL, 9/15/04, P1
1111, 8/31/04, 9/6/04, 9/7/04, 10/1/04, NULL, 11/1/04, P2
1112, 9/8/04, 9/6/04, 9/7/04, 10/1/04, NULL, 11/1/04, P2

I also have a table of orgs that gives the dates that the org began and the holidays for each org, again horizontally .

In the code you gave, the holiday information was stored vertically, so I created a view of all of the holidays from the organization table and then joined the student table to the vertical list.

My question is: since the holiday info is already stored horizontally in the student record is there any way to modify the code given to exclude holidays in that are in the record in order to avoid the join?

thanks, Robin


Tom Kyte

Followup  

January 07, 2005 - 9:49 am UTC

sure, just like we filter out SAT/SUN just add

and dt not in ( st_a_calc_adm_2.hol1, st_a_calc_adm_2.hol2, st_a_calc_adm_2.hol3, st_a_calc_adm_2.hol4 )

instead of the exists clause.

But what about a single day?

January 10, 2005 - 10:30 am UTC

Reviewer: Robin from Here

Code works perfectly for a range > 1 - thanks very much! But if your range is a single day (ex. start day is 11/1/2004 and end day is 11/1/2004) AND the next day falls within the exlusion subsets (e.g. if the day is a Friday and the next day is a Saturday, or the day is before a holiday) then the algorithm you provided returns 0. For example,

select count(*)
from my_rows
where x <= (to_date('11/1/2004') - to_date('11/1/2004'))+1
and to_char( to_date('11/1/2004')+x, 'DY' ) not in ( 'SAT', 'SUN' )
and to_date('11/1/2004')+x not in ( NVL(to_date('9/6/2004'), to_date('01/01/2005')), NVL(to_date('10/21/2004'), to_date('01/01/2005')),
NVL(to_date('10/22/2004'), to_date('01/01/2005')), NVL(to_date('11/2/2004'), to_date('01/01/2005')), NVL('', to_date('01/01/2005')));

returns 0 when it should return 1; Same thing occurs if you replace 11/1/2004 with 10/29/2004.

Other single dates work fine (e.g. if the date is a Thursday and the next day is not a holiday). How do we handle this situation without messing up the ranges that work?

Tom Kyte

Followup  

January 10, 2005 - 11:11 am UTC

I used

to_char( to_date('&2')+rnum-1, 'DY' )

are you missing a -1 in yours?

But you had an error in your code!

January 10, 2005 - 11:21 am UTC

Reviewer: Robin from Still Here

If you look back at the beginning of this thread, about 3 posts down there was this follow-up:

Need to Correct the Query October 08, 2001
Reviewer: Asif Momen from Riyadh

Hi Tom,

The above query is just wonderful. But, you need to correct it. Instead of
using "rownum-1", you need to select only "rownum". The reason is, for the first
record "rownum-1" will return 0 and the Where Clause
to_char(to_date('&2')+rnum-1, 'DY') not in ('SAT', 'SUN') will evaluate for a
day before the range specified.

Lets take your example of 01-feb-2000 and 29-feb-2000. The Where Calsuse will
execute for 31-jan-2000. In the above case as 31-jan-2000 is neither a sunday
nor a saturday, the output no. of records is correct, but will fail if you
consider 01-may-2000 and 31-my-2000. for that matter any date range starting
from monday.

Regards,

Asif Momen


Followup:
Yes, you are correct, thanks for pointing that out. I've corrected the code.

============================================================
Which I took to mean "don't use -1" - was I wrong?

Can you please post the corrected code again here? You said near the end of the thread that you had "corrected it" but I never found the actual corrected code?

Don't mean to appear dense - just need it crystal clear.

Thanks very much for your time!

Tom Kyte

Followup  

January 10, 2005 - 11:38 am UTC

nope, I "corrected the code", it had a bug -- so I did fix it.

you need DT+X-1

since X runs from 1 ... infinity. We nee to check

dt
dt+1
dt+2
.......



How to get the current half of the year

February 03, 2005 - 11:56 am UTC

Reviewer: anto

Hi Tom,

Is there any function like to_char(date_field,'h') to get the current half of the year.

example - If the date is on or before 30-Jun it should be '1st', other wise it should return '2nd'

thanks

Tom Kyte

Followup  

February 03, 2005 - 2:38 pm UTC

(case when to_date(df,'mm') <= 6 then 1 else 2 end)

February 03, 2005 - 12:17 pm UTC

Reviewer: anto

Of course I can check the month and then get the Half from that, but I was wondering whether there is some to_char format for that - could not see that in oracle documentation, although we have 'q' for quarter.

thanks

February 03, 2005 - 3:19 pm UTC

Reviewer: A reader

thanks

One way to handle floating holidays

June 09, 2005 - 5:52 pm UTC

Reviewer: Mark Hammingh from Seattle, WA USA

Hi Tom,

I have been looking for an "isHoliday" function that would magically take in an oracle date, and return a Y|N indicating if that date falls on an observed holiday. (observed meaning if holiday is on sunday the observed date is next monday, or if holiday is saturday the observed date is the prior friday). After looking around and not finding anything I came up with the below solution, I thought youre readers may find it usefull.

All the work is done in a function that converts a given
year,month,day,week number to an oracle date
examples:
first monday in march, 2005 ('2005','MAR','MON',1)
last tuesday in feb, 2005 ('2005','FEB','TUE',-1)
feb 25 2005 ('2005','FEB','25',NULL)

The holidays my company observes is held in a plsql table, the rest is pretty obvious.

Oh, and ther is one known problem - it does not handle Easter.

-Cheers,

declare
/*
Under current definitions, four are set by date:
New Year's Day January 1
Independence Day July 4
Veterans Day November 11
Christmas Day December 25

If any of the above fall on a Saturday, then Friday may be observed as a holiday by various institutions. Similarly, if one falls on a Sunday, then Monday may be observed as a holiday.
The other six are set by a day of the week and month:


Martin Luther King's Birthday Third Monday in January
Washington's Birthday Third Monday in February
Memorial Day Last Monday in May
Labor Day First Monday in September
Columbus Day Second Monday in October
Thanksgiving Fourth Thursday in November

For further information on other national observances, see
U.S. Code: Patriotic and National Observances
*/
type holiday_rec is record
( hname varchar2(55),
hmon varchar2(3),
hday varchar2(3),
hweekno number
);
type holidays_list_t is table of holiday_rec index by binary_integer;

holidays holidays_list_t;
holidayDT DATE;

--converts year,month,day,week number to a date
--examples:
-- first monday in march, 2005? ('2005','MAR','MON',1)
-- last tuesday in feb, 2005? ('2005','FEB','TUE',-1)
-- feb 25 2005? ('2005','FEB','25',NULL)
function ymdw_to_Date(
strYear in varchar2, --YYYY
strMonth in varchar2, --MON
strDay in varchar2, --[DY|DD] - depends on strWeek
numWeek in number := NULL--[-1|n|NULL]
-- NULL: not an "nth week in month" holiday - the strDay param is in format of DD
-- -1: holiday is in last week of month
-- N: holiday is in nth week of month
)
return date
is
bom date;
eom date;

currentWeek number := 0;
nthDay number := numWeek;
rdate date := null;--return: null for not a holiday

BEGIN
--check for set day (no week given)
if numWeek is null then
rdate := to_date(strYear || ' ' || strMonth || ' ' || strDay,'YYYY MON DD');
else
--its an "nth day in month" date

--loop from 1st to last day of month
bom := to_date(strYear || ' ' || strMonth,'YYYY MON');
eom := last_day(bom);

--if looking for last week (numWeek = -1) get the number of occurances of day in the month
if numWeek = -1 then
nthDay := 0;
while bom <= eom loop
if to_char(bom,'DY') = strDay then
nthDay := nthDay +1;
end if;
bom := bom + 1;
end loop;
end if;

bom := to_date(strYear || ' ' || strMonth,'YYYY MON'); --start at begin again
while bom <= eom loop
if to_char(bom,'DY') = strDay then
currentWeek := currentWeek +1;
end if;


if (currentWeek = nthDay) then
rdate := bom;
exit;
end if;


bom := bom + 1;--next day

end loop;

end if;

--dbms_output.put('the ' || nthDay || 'th ' || strDay || ' in ' || strMonth || ' ' || strYear || ' is ');
--dbms_output.put_line(to_char(rdate,'DAY DD-MON-YYYY'));
return rdate;

END ymdw_to_Date;

--Institutions may choose to observe holidays that fall on a weekend
--on monday if the holiday falls on sunday, or friday if the holiday falls on saturday.
--this functions returns the observed date.
FUNCTION getObservedDate(d in date) RETURN DATE
IS
BEGIN
if trim(to_char(d,'DAY')) = 'SUNDAY' then
return d +1;
elsif trim(to_char(d,'DAY')) = 'SATURDAY' then
return d -1;
ELSE
return d;
end if;

END getObservedDate;

FUNCTION isDateObservedHoliday(d in date) RETURN VARCHAR2
IS
r varchar2(1) := 'N';
holidayDate DATE;

BEGIN
--look at each known holiday in the year

for i in 1..holidays.count loop

holidayDate := ymdw_to_Date(to_char(sysdate,'YYYY'),holidays(i).hmon,holidays(i).hday,holidays(i).hweekno);

if getObservedDate(holidayDate) = d THEN
r := 'Y';
exit;
end if;


end loop;

return r;
END isDateObservedHoliday;

procedure addHoliday(
hName iN VARCHAR2,
hMon in varchar2,
hDay in varchar2,
hWeekNO in varchar2)
is
begin
holidays(holidays.count +1).hname := hName;
holidays(holidays.count).hmon := hMon;
holidays(holidays.count).hday := hDay;
holidays(holidays.count).hweekno := hWeekNO;
end;


BEGIN

--setup holidays list
addHoliday('NEW YEAR''S DAY','JAN',1,NULL);
addHoliday('CHRISTMAS DAY','DEC','25',NULL);
addHoliday('INDEPENDENCE DAY','JUL','4',NULL);
addHoliday('VALENTINE''S DAY','FEB','14',NULL);
addHoliday('ST. PATRICK''S DAY','MAR','17',NULL);
addHoliday('CINCO DE MAYO','MAY','5',NULL);
addHoliday('HALLOWEEN','OCT','31',NULL);
addHoliday('PRESIDENT''S DAY','FEB','MON',3);
addHoliday('LABOR DAY','SEP','MON',1);
addHoliday('MEMORIAL DAY','MAY','MON',-1);
addHoliday('MARTIN LUTHER KING DAY','JAN','MON',3);
addHoliday('COLUMBUS DAY','OCT','MON',2);
addHoliday('THANKSGIVING','NOV','THU',4);
addHoliday('DAY AFTER THANKSGIVING','NOV','FRI',4);

--Easter = ". calculate_easter($_GET["y"]);
--// Veteran's Day Observed - November 11th ?


--prints out a holiday schedule for current year.
dbms_output.put_line( to_char(sysdate,'YYYY') || ' holidays');
for i in 1..holidays.count loop
holidayDT := ymdw_to_Date(to_char(sysdate,'YYYY'),holidays(i).hmon,holidays(i).hday,holidays(i).hweekno);

dbms_output.put_line( to_char(getObservedDate(holidayDT),'DAY DD-MON-YYYY') || ' ' ||holidays(i).hname);
end loop;


--checks dates of current year for isHoliday.
holidayDT := trunc(sysdate,'YYYY');
while to_char(holidayDT,'YYYY') = to_char(sysdate,'YYYY') loop
dbms_output.put_line(to_char(holidayDT,'DAY DD-MON-YYYY') || ' ' || isDateObservedHoliday(holidayDT) );
holidayDT := holidayDT + 1;
end loop;

end;

Tom Kyte

Followup  

June 09, 2005 - 6:44 pm UTC

and the other problem is - it is US only :)

that is the problem with a general floating holidays - it is very locale specific (some of the ones in your list, I do not consider "holidays" myself)


but thanks for the idea!

All apologies

June 09, 2005 - 11:04 pm UTC

Reviewer: Mark Hammingh from Seattle, WA USA

Oops, Thought I'd point out the code I submitted has a bug in it.
The isDateObservedHoliday function only checks the current year. Replace the "to_char(sysdate,'YYYY')" with "to_char(d,'YYYY')" to fix it.

Sorry about that.

I would also like to expand on Tom's response.
This is not only local specific, but more to the point it's specific to the holidays you need to observe. Thats why you have to "tell" the script what the holidays are (the calls to addHoliday do that). I used it to populate a date dimension and it did the trick for me. Of course I only needed US non-secular holidays, which all can be expressed in the format "the [nth] [day of week] of [month]". I honestly havent got a clue if any holidays, US or not (other than Easter ) do not fit this mold.



<QUOTE>But -- as with all software -- before you use it make sure you

o Understand it
o Test it
o Validate it
</QUOTE>

</Disclaimer>






Tom Kyte

Followup  

June 09, 2005 - 11:05 pm UTC

;)

thanks

Can be done without using ALL_OBJECTS

June 17, 2005 - 7:36 am UTC

Reviewer: Justin Rowles from Southampton, UK

Hi Tom,

Here's a function that does inclusive business days (not Sat or Sun) between two dates with only a select against a table holding bank holidays. I'll leave your readers to figure out exactly what the trick is, but I believe it works perfectly in all cases. Don't put any Saturdays or Sundays into the bank holdays table though!

create table bank_holidays (
holiday date)
/

create function working_days(p_start_date date, p_end_date date) return number as
working_days number;
v_end_date date := trunc(p_end_date);
v_start_date date := trunc(p_start_date);
begin
select 5*trunc((v_end_date-v_start_date+1)/7) -- days in whole weeks
-count(*) -- minus bank holidays
-- base level of left over days
+ mod(v_end_date-v_start_date+1, 7)
-- minus number of weekend days hit
- decode(
-- if sunday then 1
to_char(v_start_date, 'DY'), 'SUN', 1,
-- otherwise
decode(
-- if day of week plus left over days = 8 then 1
to_number(to_char(v_start_date, 'D')) + mod(v_end_date-v_start_date+1, 7), 8, 1,
-- otherwise
decode(
-- if day of week plus left over days is nine or more then 2
sign(to_number(to_char(v_start_date, 'D')) + mod(v_end_date-v_start_date+1, 7)-9), -1, 0, 2
)
)
)
into working_days
from bank_holidays
where holiday between v_start_date and v_end_date;
return working_days;
end;

Cheers, keep up the fine work,
Justin.

how to subtract 100 years from a date

June 17, 2005 - 5:03 pm UTC

Reviewer: Usha G. from Fairfax, VA

We have a file we are loading in with sqlloader. It has the date format

'DD-MM-YY'

However YY can go back to the 1990s, so when we load a date of what we expect to be 1995, oracle interprets it as 2095.

1. Can we fix this with sqlloader?
2. if not how do we write the sql to fix this once the data is loaded? We hae dates from 1990-2005. So anything that is not 00-05, (which are interpreted as 2000-2005, we want to set back 100 years.

For example, '90' is interpreted as 2090, it should be 1990.

Tom Kyte

Followup  

June 17, 2005 - 5:09 pm UTC

No, you told us HOW TO interpret this.

if you use RR instead of YY, you would find it to treat (i cannot believe in the year 2005 people are still using two characters for what is obviously a four character field, it is as if we learned nothing 6 years ago) strings with a sliding window.

Today in 2005, the string 95 would be 1995, the string 05 would be 2005, the string 40 would be 2040, the string 60 would be 1960.


Use RR as your format (read about it in the SQL Reference manual, to_date section)

if you want everything between 2050 and 2099 to be 1950..1999

update t set dt = add_months(dt, -100*12 )
where dt >= to_date('01-jan-2050','dd-mon-yyyy');


and then get to fixing the serious bug in that flat file.

Any ideas how I can solve this puzzle??

June 24, 2005 - 10:41 am UTC

Reviewer: A reader

CREATE TABLE BETA
(
IN_DATE DATE NOT NULL,
OUT_DATE DATE
)



RIGHT NOW

IN_DATE OUT_DATE
3/4/2005 12:15:28 PM 3/7/2005 2:33:46 AM
3/3/2005 10:31:32 AM 3/3/2005 3:35:13 PM
3/3/2005 4:25:59 AM 3/3/2005 7:49:56 AM

THE WAY I WOULD LIKE IT TO BE FOR THE REPORT

IN_DATE OUT_DATE

3/3/2005 10:31:32 AM 3/3/2005 7:49:56 AM
3/4/2005 12:15:28 PM 3/3/2005 3:35:13 PM
3/3/2005 7:49:56 AM

Tom Kyte

Followup  

June 24, 2005 - 6:30 pm UTC

i suppose you would have to phrase in text what the way you like it represents. how you get from right now to what you want.


i didn't get it.

Function to Calculate Good Friday for any given year

July 03, 2005 - 3:50 pm UTC

Reviewer: Michael from Toronto, ON, CAN

Here is a function I created to calculate Good Friday.. hope it is useful for holidays..

create or replace function good_friday
(four_digit_year IN NUMBER DEFAULT to_char(sysdate,'YYYY'))

RETURN DATE is

easter_month number;
easter_date number;
century number;
golden_year number;
epact number;
days_to_full_moon number;
wkday_full_moon number;
days_full_moon number;
easter_sunday date;
good_friday_date date;

BEGIN

century := trunc(four_digit_year/100);
golden_year := mod(four_digit_year,19);
epact := MOD(TRUNC((century-TRUNC(century/4)-TRUNC((8*century+13)/25)+(19*golden_year)+15)),30);
days_full_moon := epact-TRUNC(epact/28)*(1-TRUNC(epact/28)*TRUNC(29/(epact+1))*TRUNC((21-golden_year)/11));
wkday_full_moon := MOD((four_digit_year+TRUNC(four_digit_year/4)+days_full_moon+2-TRUNC(four_digit_year/100)+TRUNC(TRUNC(four_digit_year/100)/4)),7);
days_to_full_moon := days_full_moon - wkday_full_moon;
easter_month := 3+TRUNC((days_to_full_moon+40)/44);
easter_date := days_to_full_moon+28-31*TRUNC(easter_month/4);
easter_sunday := to_date(to_char(easter_date,'00')||to_char(easter_month,'00')||to_char(four_digit_year,'0000'),'DD-MM-YYYY');
good_friday_date := easter_sunday - 2 ;

RETURN good_friday_date;

END;

1 begin
2 for i in 2005..2015 loop
3 dbms_output.put_line(i||' '||good_friday(i));
4 end loop;
5* end;
UTILS@orcl10>/
2005 25-MAR-05
2006 14-APR-06
2007 06-APR-07
2008 21-MAR-08
2009 10-APR-09
2010 02-APR-10
2011 22-APR-11
2012 06-APR-12
2013 29-MAR-13
2014 18-APR-14
2015 03-APR-15

Have a Good July 4th.

Calculate an enddate

November 02, 2005 - 2:48 pm UTC

Reviewer: Robin H. from Warm & Sunny VT

I have used your code to count the number of business days between two dates and posted problems with it above. Now I want to count out 40 business days (no weekends or holidays) and figure out the "end date". What I'm trying to do is:

Calculate the Average Daily Attendance for students. So we count the number of days a student physically attended the school vs. the number of days school was in session. (That's what I used the above code for).

The period we're interested in is the first 40 days of school based on the school year begin date + 40 days - (weekends + holidays).

I have created a procedure based on two cursors that take in a begin date and increment the date based on the day of the week and the holidays entered. I would prefer to use something like you've used here to make the calculations run faster, but not sure how to do that.

I'm sure others try to do this, but haven't found anything better than what I've done. I just hate using cursors or for loops to do what I'm sure could be done with a case statement or such. If mine is the best solution, then I'll stick with it, but do you have any ideas?

Tom Kyte

Followup  

November 03, 2005 - 6:35 am UTC

well, you know that 40 days will be at least 8 weeks (5 days/week).

If you added 56 days to the start date - you are "close" (just have holidays).

So, you could count the number of holidays that are not on SAT/SUN between start_date and start_date+56...

Say there were two.

Now you know the range must be at least:

start_date ... start_date+56+2

get the number of business days between those - if 40, done, else add the difference between 40 and the returned value and do it again....


That is the first thing that popped into my head.

Calculation working days, no holidays, no weekends

November 10, 2005 - 3:56 pm UTC

Reviewer: Sheila from St Louis, MO

I tried the function posted by Justin Rowles above, just changing the name of the table and column name and it worked like a charm. I tested many holiday scenarios and the results always matched my calculations. Short and concise, its the best solution I've seen. Thanks Justin!

When I add businessDays logic; query runs very slow. Please help

December 29, 2005 - 10:16 am UTC

Reviewer: Yogesh Chaudhari from New York, NY USA

Hi Tom,

I want to select rows where business_days < 21 days.

If I ran the following query it executes in 100ms

select b.caseid, t.tytycd, casemgr.businessDays(b.notice_to_mediate_date, sysdate) business_days, b.notice_to_mediate_date
from basicp b, typep t
where b.typep_seq# = t.seq# and t.tytycd in('407', '408') and b.bacsst = 'P' and b.bacost = 'A' and b.bastcg = 'A'

but when I add following where clause it takes 18secs.

casemgr.businessDays(b.notice_to_mediate_date, sysdate) < 21

Totals rows returned are 31. (100ms)
Rows returned after added additional 21 days clause: 3 rows. (18secs)

I tried with clause query like

with c as
(select b.caseid, t.tytycd, casemgr.businessDays(b.notice_to_mediate_date, sysdate) business_days, b.notice_to_mediate_date
from basicp b, typep t
where b.typep_seq# = t.seq# and t.tytycd in('407', '408') and b.bacsst = 'P' and b.bacost = 'A' and b.bastcg = 'A')
select *
from c
where business_days < 21

This query also takes 16 secs.

Then I created temp table to insert all rows & then filtered rows to check 21 days clause, total operation finishes less than 1 sec.

CREATE GLOBAL TEMPORARY TABLE TEMP_INITLETTER
(
CASEID VARCHAR2(13 BYTE),
CASE_TYPE VARCHAR2(3 BYTE),
BUS_DAYS NUMBER,
NOTICE_DATE DATE
)
ON COMMIT DELETE ROWS;

delete from temp_initLetter;

insert into temp_initLetter
select b.caseid, t.tytycd, casemgr.businessDays(b.notice_to_mediate_date, sysdate) business_days, b.notice_to_mediate_date
from basicp b, typep t
where b.typep_seq# = t.seq# and t.tytycd in('407', '408') and b.bacsst = 'P' and b.bacost = 'A' and b.bastcg = 'A';


select *
from temp_initLetter
where bus_days < 21;


I do not want to create temp table, also with large set of data query may to long time to finish.

Why pl/sql call in where clause is slowing this query?
How I can force sql to execute with clause query first before applying outer where clause.



Happy Holidays & Happy New Year 2006!

Thanks,
Yogesh Chaudhari


CREATE OR REPLACE function BusinessDays(stDate in date, enDate in date) return number is
businessDays number := 0;
cursor c is
select count(*) - 1 businessDays
from
(select trunc(stDate) + rownum-1 calDate
from basicp
where rownum <= trunc(enDate) - trunc(stDate)+1)
where to_char(calDate, 'DY') not in ('SUN', 'SAT')
and calDate not in (select hodate from holidays where record_status = 'A' and rerecd = 'AA');
begin
for r in c loop
businessDays := r.businessDays;
end loop;
return businessDays;
end BusinessDays;

Tom Kyte

Followup  

December 29, 2005 - 12:21 pm UTC

how about:

select *
from (
select caseid, tytycd, notice_to_mediate_date,
(select casemgr.businessDays(b.notice_to_mediate_date, sysdate)
from dual ) business_days
from ( select b.caseid,
t.tytycd,
b.notice_to_mediate_date,
rownum r
from basicp b,
typep t
where b.typep_seq# = t.seq#
and t.tytycd in('407', '408')
and b.bacsst = 'P'
and b.bacost = 'A'
and b.bastcg = 'A'
and b.notice_to_mediate_date >= sysdate-21
)
)
where business_days < 21

December 29, 2005 - 1:05 pm UTC

Reviewer: Yogesh Chaudhari from New York, NY

Excellent, it worked

January 03, 2006 - 2:30 am UTC

Reviewer: Ravi Kumar from Gurgaon, India

I need to calculate the delivery date for Project on the basis of a given Start Date and No. of Working Days required for that project.
I have a table to maintain holidays as well. What should be the best to calculate the Delivery Date. I need a PL/SQL Solution for this.

Can you please give me some idea.

Thanks & Regards
Ravi Kumar

Tom Kyte

Followup  

January 03, 2006 - 7:30 am UTC

hmm, this seems to be a page that describes how to get the number of business dates between two dates doesn't it? Did you find anything of use on this page?

January 03, 2006 - 11:03 pm UTC

Reviewer: Ravi Kumar from Gurgaon, India

Hi,

Thanks for your response, Yes this page is very useful, I can see the examples to get no.of buisness days between 2 dates,

But I am not getting it right When I change it in a way so that I can get the "delivery date" on the basis of "start date" and "no. of working days required".

Actualy, I am fine if I just need to exclude Weekends, But I am not getting it 100% right when I need to exclude holidays.

Can you please please show me how can we get that ?


Thanks & Regards
Ravi Kumar

Tom Kyte

Followup  

January 04, 2006 - 8:26 am UTC

no really, but only because you don't show us an example. You've changed something in some fashion and when using some data get the "wrong" answer.

The change you made - we don't know.
The fashion you made the change in - we don't know.
The data you are using - we don't know.
The "wrong" answer (which is obviously "right" given the code) - we don't know.

Test cases are

a) small
b) but 100% complete - we should be able to cut and paste them and run them
c) yet concise
d) but have everything
e) did I say "small"?
f) oh - they should be complete too.


see original answer for two examples of small, yet 100% complete examples.

January 04, 2006 - 12:54 am UTC

Reviewer: Ravi Kumar from Gurgaon, India

Just After posting my last question, Finally I am able to write the code to do this.


SQL> CREATE TABLE MY_HOLIDAYS
  2  (hdate DATE,
  3  description VARCHAR2(50));

Table created.

SQL> INSERT INTO MY_HOLIDAYS
  2  VALUES('02-JAN-2006','XXXXXXXX');

1 row created.

SQL> INSERT INTO MY_HOLIDAYS
  2  VALUES('06-JAN-2006','XXXXXXXX');

1 row created.

SQL> INSERT INTO MY_HOLIDAYS
  2  VALUES('09-JAN-2006','XXXXXXXX');

1 row created.

SQL> INSERT INTO MY_HOLIDAYS
  2  VALUES('10-JAN-2006','XXXXXXXX');

1 row created.


CREATE OR REPLACE FUNCTION Get_Del_Date (in_start DATE,
                                          in_days NUMBER)
RETURN DATE
IS
   l_no_of_days   NUMBER       := 0;
   l_dummy        VARCHAR2 (1);
   l_del_date     DATE         := in_start;
BEGIN
   LOOP
      BEGIN
         IF TO_CHAR (l_del_date, 'D') NOT IN (1, 7)
         THEN
            SELECT NULL
              INTO l_dummy
              FROM MY_HOLIDAYS
             WHERE hdate = l_del_date;
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_no_of_days := l_no_of_days + 1;
      END;
      EXIT WHEN l_no_of_days = in_days;
      l_del_date := l_del_date + 1;
   END LOOP;
   RETURN l_del_date;
END Get_Del_Date;


Can you please suggest something better/quicker to do this ?

Thanks & Regards
Ravi Kumar 

Tom Kyte

Followup  

January 04, 2006 - 8:49 am UTC

why doesn't the above approaches work for you?

Add_Bus_Days Recursive procedure

January 04, 2006 - 11:02 am UTC

Reviewer: Yogesh Chaudhari from New York, NY

FUNCTION Add_Bus_Days(p_date DATE, p_#days INTEGER) RETURN DATE IS
next_date DATE;
-- Local function to check weekend
FUNCTION Is_Bus_Day(p_date DATE) RETURN BOOLEAN IS
abbrname VARCHAR2(3);
dummy VARCHAR2(1);
CURSOR c (cdate DATE) IS
SELECT 'X'
FROM casemgr.holidays
WHERE record_status = 'A'
AND rerecd = 'AA'
AND Trunc(hodate) = Trunc(cdate);
BEGIN
SELECT TO_CHAR(p_date, 'DY') INTO abbrname FROM dual;
IF abbrname IN ('SAT', 'SUN') THEN
RETURN FALSE;
END IF;
OPEN c (p_date);
FETCH c INTO dummy;
IF c%FOUND THEN
CLOSE c;
RETURN FALSE;
END IF;
CLOSE c;
RETURN TRUE;
END Is_Bus_Day;

BEGIN
IF p_#days = 0 THEN
RETURN p_date;
END IF;
if p_#days > 0 then
IF p_#days = 1 THEN
next_date := p_date;
LOOP
next_date := next_date + 1;
EXIT WHEN Is_Bus_day(next_date) = TRUE;
END LOOP;
RETURN next_date;
ELSE
next_date := p_date;
LOOP
next_date := next_date + 1;
EXIT WHEN Is_Bus_day(next_date) = TRUE;
END LOOP;
RETURN Add_Bus_Days(next_date, p_#days-1);
END IF;
elsif p_#days < 0 then
IF p_#days = -1 THEN
next_date := p_date;
LOOP
next_date := next_date - 1;
EXIT WHEN Is_Bus_day(next_date) = TRUE;
END LOOP;
RETURN next_date;
ELSE
next_date := p_date;
LOOP
next_date := next_date - 1;
EXIT WHEN Is_Bus_day(next_date) = TRUE;
END LOOP;
RETURN Add_Bus_Days(next_date, p_#days + 1);
end if;
end if;
END Add_Bus_Days;

March 31, 2006 - 3:30 pm UTC

Reviewer: A reader

Tom,

I think there is something wrong with the logic here for finding the next business day, suggested by you somewhere in the middle of this page:(
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:185012348071#29205828398565

SQL> print :dt

DT
--------------------------------
26-mar-2006

SQL> print :r

         R
----------
         5

SQL> l
  1  select
  2  to_date(:dt, 'dd-mon-yyyy')+trunc(:r/5)*7+mod(:r,5)+
  3  sign(greatest(0,mod(:r,5)+to_char(to_date(:dt, 'dd-mon-yyyy'),'d')-6))*2 dt
  4* from dual
SQL> /

DT
---------
02-APR-06

Can you please help? 

Tom Kyte

Followup  

March 31, 2006 - 4:45 pm UTC

did I write that one?

point to where it is on this page, give something to ctl-f for.

additional info for above post..

March 31, 2006 - 3:33 pm UTC

Reviewer: A reader

I forgot to mention the issue is that the date given is Sunday (04/02/2006) (though the passed date also is sunday). Sorry.

March 31, 2006 - 4:55 pm UTC

Reviewer: A reader

here is the link again Tom, it is in the middle of this page:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:185012348071#29205828398565 <code>

I am basically trying to write a function (using sql) which can add/subtract n days to/from a business day and give the resulting business day (exclusing sat, sun and holidays from a holiday table).

Tom Kyte

Followup  

March 31, 2006 - 5:16 pm UTC

ahh, function as written assumed you were ON a business day (eg: does not make sense to add N business days to a day that is not a business day)

too slow !!:(

May 28, 2006 - 4:48 am UTC

Reviewer: oracleLeo from idnia

Hi,

I am trying to use this functionality in one of our apps. We have entity called "task" in our system and need to find how much time (working days) this task took before completing.

The queries return lots of other data related to the task and have 10+ tables. I am calling this fucntion in Select clasue, but it takes a too much of time at time 5-6 min. if i comment this piece of query with the working days calculation, it executes on 3-4 sec.

is there a better/faster way of calculating workign days

Tom Kyte

Followup  

May 28, 2006 - 9:21 am UTC

Since this takes but a small tiny fraction of a second, why do you thing "this is the root cause"

No example, no comment.

Search filters

July 26, 2006 - 11:20 am UTC

Reviewer: A reader

Given a DATE column, what is the best way to implement search filter predicates for commonly used use-cases like

a. Today (easy)
b. This week
c. Last week
d. This month
e. Last month
f. Last N months

where "week" is defined as starting on Monday and ending on Sunday

Thanks for any ideas

Tom Kyte

Followup  

July 26, 2006 - 11:55 am UTC

a) where dt >= trunc(sysdate) and dt < trunc(sysdate+1)

b) where dt >= next_day(trunc(sysdate),'sun')-6
and dt < next_day(trunc(sysdate),'sun')+1

c) see b, subtract 7

d) dt >= trunc(sysdate,'mm') and dt < add_months(trunc(sysdate,'mm'),1)

e) see d, use add months to shift back a month.

f) see e, generalize

Search filters

July 26, 2006 - 12:08 pm UTC

Reviewer: A reader

Thanks, is there a way to put these predicates in a WHERE clause of a query

I tried

WHERE 1=1
and CASE :Filter
when 'Today' then dt>=trunc(sysdate) and dt<trunc(sysdate+1)
...
END

that doesn't quite work.

Any ideas? Thanks

Tom Kyte

Followup  

July 26, 2006 - 1:31 pm UTC

where dt >= CASE when 'today' then trunc(sysdate) when ..... END
and dt < CASE ..... END




Search filters

July 26, 2006 - 12:18 pm UTC

Reviewer: A reader

I guess I can use the "pattern" that all predicates have a dt >= and a dt< to do something like

AND dt>= CASE :F
WHEN 'Today' THEN TRUNC(SYSDATE)
WHEN 'This week' THEN NEXT_DAY(TRUNC(SYSDATE),'sun')-6
WHEN 'Last week' THEN NEXT_DAY(TRUNC(SYSDATE),'sun')-6-7
...
END
AND dt < CASE :F
WHEN 'Today' THEN TRUNC(SYSDATE+1)
WHEN 'This week' THEN NEXT_DAY(TRUNC(SYSDATE),'sun')+1
WHEN 'Last week' THEN NEXT_DAY(TRUNC(SYSDATE),'sun')+1-7
...
END

Is that what you would do?

Tom Kyte

Followup  

July 26, 2006 - 1:32 pm UTC

it would work, yes.

July 27, 2006 - 4:58 pm UTC

Reviewer: A reader

Tom,

How can i rewrite this query without having to query same table repeatedly?

SELECT table2.col1,

(SELECT COUNT (*)
FROM table1
WHERE col1='A'
) AS A_count,
(SELECT COUNT (*)
FROM table1
WHERE col1='B'
) AS B_count
FROM table2;

Can I query table1 once and get different counts for different conditions?

Thanks in advance!

Tom Kyte

Followup  

July 27, 2006 - 5:47 pm UTC

ops$tkyte%ORA10GR2> select to_number( substr( data, 1, 10 ) ) cnt1,
  2         to_number( substr( data, 11 ) ) cnt2,
  3             dummy
  4    from (
  5  select (select to_char( count( case when job = 'CLERK' then 1 end ), 'fm0000000000' ) ||
  6                 count( case when job = 'ANALYST' then 1 end )
  7                    from scott.emp ) data,
  8                  dummy
  9    from dual
 10         )
 11  /

      CNT1       CNT2 D
---------- ---------- -
         4          2 X

1 row selected.

 

July 28, 2006 - 12:30 am UTC

Reviewer: Michel Cadot

Hi Tom,

Is there any reason you use concatenation/deconcatenation in the previous post instead of the simple

SQL> select dummy, cnt1, cnt2
  2  from (select count(case when job = 'CLERK' then 1 end) cnt1,
  3               count(case when job = 'ANALYST' then 1 end) cnt2
  4        from emp) data,
  5       dual
  6  /
D       CNT1       CNT2
- ---------- ----------
X          4          2

Michel 

Tom Kyte

Followup  

July 28, 2006 - 7:41 pm UTC

they wanted to return more than one column from a SCALAR subquery.

I was emulating their sql statement which was of the form:

select ....,
(select something from t2),
(select something_else from t2)
from t1;


I used scott.emp as t2 and dual as t1 for demonstration purposes.

even easier

July 28, 2006 - 7:12 am UTC

Reviewer: Matthias Rogel

select count(case when job = 'CLERK' then 1 end) cnt1,
count(case when job = 'ANALYST' then 1 end) cnt2
from emp

CNT1 CNT2
---------- ----------
4 2

?
why join with dual ?

Tom Kyte

Followup  

July 28, 2006 - 7:58 pm UTC

see above, I was emulating the problem as stated by the original poster. They had a query with two scalar subqueries and wanted to avoid having two scalar subqueries.

days as string in between two days given as string

December 28, 2006 - 3:08 am UTC

Reviewer: VijayS from India

Dear Tom,
good day to you as always, I am facing a small issue for which I am using pl/sql as of now, but will like to know if we can use sql for this. The input to query will be two days in string e.g. Saturday and Wednesday, the output required is all days between these two days including these days.

e.g input Saturday,Wednesday, output required Saturday,Sunday,Monday,Tuesday,Wednesday.


Thanks for your help on this.

Regards,
VijayS
Tom Kyte

Followup  

December 28, 2006 - 9:05 am UTC

ops$tkyte%ORA10GR2> variable d1 varchar2(20)
ops$tkyte%ORA10GR2> variable d2 varchar2(20)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :d1 := 'Saturday'; :d2 := 'Wednesday';

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
2 as
3 (
4 select d1, d2, d2-d1+1 days
5 from (
6 select next_day(sysdate,:d1) d1,
7 next_day(next_day(sysdate,:d1),:d2) d2
8 from dual
9 )
10 )
11 select to_char( d1+level-1, 'Day' ) day
12 from data
13 connect by level <= days
14 /

DAY
---------
Saturday
Sunday
Monday
Tuesday
Wednesday

ops$tkyte%ORA10GR2>

thanks for the help on this.

December 28, 2006 - 9:26 am UTC

Reviewer: VijayS from India

Dear Tom,
thanks for your time and help on this, can you please tell me the document from which I can get more information on usage of "with data as".

Thanks again for the help.

Regards,
VijayS
Tom Kyte

Followup  

December 28, 2006 - 9:51 am UTC

sql reference, subquery factoring is what it is called.

Here's how I handle it

December 28, 2006 - 2:41 pm UTC

Reviewer: Neil Kodner from Salt Lake City

I have a similar function which figures out the next business day-you could easily leverage this into a modified days_between function. I work in State Government and we observe nearly ALL holidays(yay). We also have to observe(close) Saturday holidays on Fridays and Sunday holidays on Mondays.

I was too lazy to use a table-driven approach so I declared the holidays as constants in my code(sorry Tom!!)

I also know that this is probably a lot of code, but I dont have to maintain the holidays each year. As we know, holiday dates are not always consistent(Thanksgiving for example).

  
  FUNCTION first_weekday(in_date IN date,in_day_of_week IN VARCHAR2)
  RETURN DATE
  IS
  BEGIN
    IF LTRIM(RTRIM(TO_CHAR(in_date,'DAY'))) = UPPER(in_day_of_week)
    THEN
      RETURN in_date;
    ELSE
      RETURN(NEXT_DAY(in_date,in_day_of_week));
    END IF;
  END;
  FUNCTION holiday_observed(in_holiday_dt IN DATE)
  RETURN DATE
  IS
  BEGIN
    IF TO_CHAR(in_holiday_dt,'D') = '7'
    THEN
      --use the prior friday
      RETURN TRUNC(in_holiday_dt) -1;
    ELSIF TO_CHAR(in_holiday_dt,'D') = '1'
    THEN
      --use the next monday
      RETURN TRUNC(in_holiday_dt) + 1;
    ELSE
      RETURN in_holiday_dt;
    END IF;
  END;
FUNCTION next_business_day(in_date IN DATE)
  RETURN DATE
  IS
    v_next_day DATE;

    --set up the holidays
    c_new_years_day  CONSTANT DATE := holiday_observed(TRUNC(in_date,'YYYY'));
    c_next_new_year  CONSTANT DATE := holiday_observed (TRUNC(ADD_MONTHS(in_date,12),'YYYY'));
    c_mlk_day        CONSTANT DATE := first_weekday (TRUNC(in_date,'YYYY'),'Monday') + 14;
    c_presidents_day CONSTANT DATE := first_weekday (ADD_MONTHS(TRUNC(in_date,'YYYY'),1),'MONDAY')+14;
    c_memorial_day   CONSTANT DATE := first_weekday (ADD_MONTHS(TRUNC(in_date,'YYYY'),5),'MONDAY')-7;
    c_july_4         CONSTANT DATE := holiday_observed (TO_DATE('04-JUL-'||TO_CHAR(in_date,'YYYY'),'DD-MON-YYYY'));
    c_pioneer_day    CONSTANT DATE := holiday_observed (TO_DATE('24-JUL-'||TO_CHAR(in_date,'YYYY'),'DD-MON-YYYY'));
    c_labor_day      CONSTANT DATE := first_weekday (ADD_MONTHS(TRUNC(in_date,'YYYY'),8),'Monday');
    c_veterans_day   CONSTANT DATE := holiday_observed (TO_DATE('11-NOV-'||TO_CHAR(in_date,'YYYY'),'DD-MON-YYYY'));
    c_thanksgiving   CONSTANT DATE := first_weekday (ADD_MONTHS(TRUNC(in_date,'YYYY'),10),'THURSDAY')+21;
    c_christmas      CONSTANT DATE := holiday_observed (TO_DATE('25-DEC-'||TO_CHAR(in_date,'YYYY'),'DD-MON-YYYY'));

  BEGIN
    IF LTRIM(RTRIM(TO_CHAR(in_date,'DAY'))) IN ('FRIDAY','SATURDAY','SUNDAY')
    THEN
      v_next_day := NEXT_DAY(in_date,'MONDAY');
    ELSE
      v_next_day := in_date + 1;
    END IF;
    --now, we have to check to see if v_next_day falls on a holiday

    IF v_next_day IN (c_new_years_day, c_next_new_year, c_mlk_day, c_presidents_day,
                      c_memorial_day,c_july_4, c_pioneer_day, c_labor_day,
                      c_veterans_day,c_thanksgiving, c_christmas)
    THEN
      v_next_day := next_business_day(v_next_day);
    END IF;

    RETURN TRUNC(v_next_day);

  END next_business_day;



In case you're wondering what Pioneer Day is, its a State holiday here in Utah that falls on 24-July.

Similar question

January 17, 2007 - 1:49 pm UTC

Reviewer: Sonali from NJ, USA

Tom,

I have a similar problem but with a slightly different requirement. I need to know the n th working day (date) before say sysdate. Could that be done in sql or would I need pl/sql for it.

Appreciate your help.
Regards,
Sonali

Nth business day before

January 17, 2007 - 2:51 pm UTC

Reviewer: Sonali from NJ, USA

As a further clarification to my question above....
By working day , I mean excluding weekend days. eg. the 4th working (business) day before today 1/17/07 would be 1/11/07.

Thanks,

hi

January 29, 2007 - 9:41 am UTC

Reviewer: A reader

thanks tom.

how to exclude the holidays

May 28, 2007 - 7:52 am UTC

Reviewer: yeshwant

how to find the number of working days between the start_day and end_day ,here we need to exclude the holidays and weekends(saturaday,sunday)
holidays include
Year Month Day Name
2007 1 1 New Year¿s Day
2007 1 2 New Year¿s Day
2007 2 18 Chinese New Year
2007 2 19 Chinese New Year
2007 2 20 Chinese New Year
2007 4 6 Good Friday
2007 5 1 Labour Day
2007 5 31 Vesak Day
2007 8 9 National Day
2007 10 13 Hari Raya Puasa
2007 11 8 Deepavali
2007 12 20 Hari Raya Hji
2007 12 25 Christmas Day

thankx in advance
Tom Kyte

Followup  

May 30, 2007 - 10:11 am UTC

did you read the original answer from the beginning?

.... A logical extension would be to add 'holidays' and other non-business days. We would use a table with dates to exclude. For example: ...

How to find out the number of working days between two dates

July 20, 2007 - 12:07 am UTC

Reviewer: Gaurav Sharma from India

Hi tom


my query is to find out the no of working days between two dates excluding sunday and holidays.........
please tell me in detail what to do and how to do.....waiting for your help
Tom Kyte

Followup  

July 20, 2007 - 8:15 am UTC

here are the details:

read this page from top to bottom.

all dates and times

July 20, 2007 - 10:02 am UTC

Reviewer: Gabriel from Montreal, Canada

Hello Tom,

Im doing a left outer join and I need to have a value if existing or 0 otherwise for every half an hour for the last month. Can you please give an ex of a query that would generate the date and time for every half an hour for the past 30 days so that I can use it in my outer join?

Thank you,
Tom Kyte

Followup  

July 20, 2007 - 4:20 pm UTC

ops$tkyte%ORA9IR2> with data
  2  as
  3  (select trunc(sysdate,'hh')-(level-1)*1/24/2 tim
  4     from dual
  5  connect by level <= 30 * 24 * 2
  6  )
  7  select * from data;

TIM
--------------------
20-jul-2007 16:00:00
20-jul-2007 15:30:00
20-jul-2007 15:00:00
20-jul-2007 14:30:00
...
20-jun-2007 18:30:00
20-jun-2007 18:00:00
20-jun-2007 17:30:00
20-jun-2007 17:00:00
20-jun-2007 16:30:00

1440 rows selected.


Thank you!!!

July 20, 2007 - 5:08 pm UTC

Reviewer: A reader


Getting Next Business Day

November 02, 2007 - 1:10 pm UTC

Reviewer: Alex from USA

Hi,

I need some help in getting next business day if date due falls on weekend or holiday.
What's given:
start date (which is any day, including weekends and holidays)
duration (number of calendar days to add)
holidays table that holds dederal holidays.
What's needed:
start date + duration = due date (next business day if due date weekend/holiday).
Ex. 10/2/07+2=10/4/07(business day)
10/4/07+3=10/7/07(should get 10/9/07(10/8/07 is holiday))
10/5/07+3=10/8/07(should get 10/9/0710/9/07(10/8/07 is holiday)
10/19/07+8=10/29...
Thank you

query

December 04, 2007 - 1:06 am UTC

Reviewer: esraa from iraq\baghdad

hi
i want ask about i want query of data insert in every end month

Built-in function for next business day?

January 02, 2008 - 6:02 pm UTC

Reviewer: Maverick

Tom, Was there a Builtin function to get next business day in Oracle 10g that eliminates Sat/Sun and 6 pre-defined holidays?
I was in a discussion with group of people regarding this and couple of them seem to used it..but didn't remember it exactly.. Is there any function like that?

Thanks,
Tom Kyte

Followup  

January 02, 2008 - 6:11 pm UTC

there is not - the problem is the concept of next business day varies by location, what things you believe in, whether you work for the government or not, and so on.

exclude weekends.

January 07, 2008 - 5:52 am UTC

Reviewer: Raja from India

Hi Tom,

I want the output as following:

Emp ID - Joining Date - number of das till today (excluding weekends)

select empid, doj, ???? from emp

I want the formula to be kept in ???? of above to get number of days employee joined and how many days he is here till today (sysdate). (I want exclude weekends and I am not interested in holidays). So, I just want weekends to be excluded.

Thanks again for all your help.. when I have any question on Oracle, first I come to your site then, I will go for other sites. But, usually I will never go beyond your site.. (I get all answers here itself)

Thanks
Raja
Tom Kyte

Followup  

January 07, 2008 - 11:04 am UTC

you can probably do this with some simple math.

sysdate-joining_date = number of days
divide by seven = number of weeks

Now, we need to cater for end points - but in general.... some simple math (hah, simple :)

I took the function from here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1527805947756

and "inlined it"

I used a scalar subquery to "verify" that it was returning what I expected.

Your column of interest is DAY1 - make sure today >= dt and it'll return the days between two dates minus weekends.

do not use the plsql function, it is for reference.
do not use the scalar subquery with connect by on dual, it is for reference

use a VIEW to hide the complexity of the calculation, but definitely INLINE it.

ops$tkyte%ORA10GR2> CREATE OR REPLACE
  2  FUNCTION weekends2( p_date1 IN DATE, p_date2 IN DATE )
  3  RETURN NUMBER
  4  AS
  5    l_monday varchar2(255)
  6             default to_char( to_date( '03052001', 'mmddyyyy' ), 'DAY' );
  7    l_date1 DATE DEFAULT LEAST(trunc(p_date1),trunc(p_date2));
  8    l_date2 DATE DEFAULT GREATEST(trunc(p_date1),trunc(p_date2));
  9    l_date2_monday DATE DEFAULT NEXT_DAY(l_date2,l_monday)-7;
 10    l_date1_monday DATE DEFAULT NEXT_DAY(l_date1,l_monday)-7;
 11    l_some_add NUMBER DEFAULT greatest((l_date2-l_date2_monday)-4,0);
 12    l_some_sub NUMBER DEFAULT greatest((l_date1-l_date1_monday)-5,0);
 13  BEGIN
 14      RETURN
 15         2*TRUNC((l_date2_monday-l_date1_monday)/7,0)+l_some_add-l_some_sub;
 16  END;
 17  /
Function created.

ops$tkyte%ORA10GR2> with days1
  2  as
  3  (select to_date( '01-jan-2008','dd-mon-yyyy')+level-1 dt from dual connect by level <= 31),
  4  days2
  5  as
  6  (select to_date('15-feb-2008','dd-mon-yyyy')+level-7 today from dual connect by level <= 8)
  7  select dt,
  8         today,
  9        (today-dt+1)
 10        - (2*TRUNC(( (next_day(today,'MON')-7) - (next_day(dt,'MON')-7) )/7,0)
 11           + ( greatest(today-(next_day(today,'MON')-7)-4, 0 ) )
 12           - ( greatest(dt-(next_day(dt,'MON')-7)-5, 0 ) )) days1,
 13        (select count(*) from dual where to_char(dt+level-1,'DY') not in ('SAT', 'SUN') connect by level <= (today-dt)+1) days2
 14    from days1, days2
 15   order by dt, today
 16  /

DT              TODAY                DAYS1      DAYS2
--------------- --------------- ---------- ----------
Tue 01 Jan 2008 Sat 09 Feb 2008         29         29
Tue 01 Jan 2008 Sun 10 Feb 2008         29         29
Tue 01 Jan 2008 Mon 11 Feb 2008         30         30
Tue 01 Jan 2008 Tue 12 Feb 2008         31         31
Tue 01 Jan 2008 Wed 13 Feb 2008         32         32
Tue 01 Jan 2008 Thu 14 Feb 2008         33         33
...
Thu 31 Jan 2008 Sat 09 Feb 2008          7          7
Thu 31 Jan 2008 Sun 10 Feb 2008          7          7
Thu 31 Jan 2008 Mon 11 Feb 2008          8          8
Thu 31 Jan 2008 Tue 12 Feb 2008          9          9
Thu 31 Jan 2008 Wed 13 Feb 2008         10         10
Thu 31 Jan 2008 Thu 14 Feb 2008         11         11
Thu 31 Jan 2008 Fri 15 Feb 2008         12         12
Thu 31 Jan 2008 Sat 16 Feb 2008         12         12

248 rows selected.



Thanks a lot Tom...

January 08, 2008 - 10:14 am UTC

Reviewer: Raja from India

Thanks a lot Tom...

business days between

March 04, 2009 - 6:42 am UTC

Reviewer: Alan from Birmingham, UK

A bit late perhaps but I've been using it for years - the '+ 1' allows the count to be inclusive, leave it out otherwise.

select length(
replace(
rpad(
substr('1111100',
to_number(to_char(:start_date,'d'))
),
:end_date - :start_date + 1,'1111100'),
'0')
)
from dual;

Optimized solution for SQL users

December 10, 2009 - 3:24 am UTC

Reviewer: Jay Khanpara from India

Here is my optimized solution for the SQL users:

CREATE function dbo.GetBusinessDays
(
@StartDate datetime,
@EndDate datetime
)
returns int
as
begin

declare @DaysBetween int
declare @BusinessDays int
declare @Cnt int
declare @Weekday int
declare @EvalDate datetime

set @DaysBetween = 0
set @BusinessDays = 0
set @Cnt=0

select @DaysBetween = datediff(Day,@StartDate,@endDate)

set @BusinessDays += (@DaysBetween/7)*5
set @Cnt = @Cnt + @DaysBetween - (@DaysBetween % 7)

while @Cnt < @DaysBetween + 1
begin

set @EvalDate = DATEADD(day, @Cnt, @StartDate)
set @Weekday = datepart(dw,@EvalDate)

if ((@Weekday <> 1) and (@Weekday <> 7))
set @BusinessDays = @BusinessDays + 1

set @Cnt = @Cnt + 1
end

return @BusinessDays
end
Tom Kyte

Followup  

December 10, 2009 - 2:54 pm UTC

hey, I resent that.

I *am* a SQL user. If you mean "microsoft sql server", that is not "sql".

Search Filters followup

February 16, 2010 - 10:14 am UTC

Reviewer: Stephan Uzzell

Hi Tom,

A question regarding your old (26 July 2006) answer on finding "this week" and "last week" for a given date:

SQL> var p_search_date varchar2(15)
SQL> exec :p_search_date := '16-FEB-2010'

PL/SQL procedure successfully completed.

SQL> select trunc( to_date( :p_search_date ) ) "Input"
  2       , next_day( trunc( to_date( :p_search_date ) ), 'sat' )    "Next Sat"
  3       , next_day( trunc( to_date( :p_search_date ) ), 'sat' ) -6 "Last Sun"
  4       , next_day( trunc( to_date( :p_search_date ) ), 'sat' ) +1 "Next Sun"
  5    from dual
  6  /

Input           Next Sat        Last Sun        Next Sun
--------------- --------------- --------------- ---------------
16-FEB-10       20-FEB-10       14-FEB-10       21-FEB-10


So far, so good. For today, I get my Sunday-->Saturday week, so I can filter on date being >= "Last Sun" and < "Next Sun".

Problem arises when :p_search_date is falls on the boundary:

SQL> exec :p_search_date := '20-FEB-2010'

PL/SQL procedure successfully completed.

SQL> select trunc( to_date( :p_search_date ) ) "Input"
  2       , next_day( trunc( to_date( :p_search_date ) ), 'sat' )    "Next Sat"
  3       , next_day( trunc( to_date( :p_search_date ) ), 'sat' ) -6 "Last Sun"
  4       , next_day( trunc( to_date( :p_search_date ) ), 'sat' ) +1 "Next Sun"
  5    from dual
  6  /

Input           Next Sat        Last Sun        Next Sun
--------------- --------------- --------------- ---------------
20-FEB-10       27-FEB-10       21-FEB-10       28-FEB-10


I want "This Week" to be 14-20 February, if :p_search_date is any day in there - including Saturday the 20th.

Is there any way to do that without explicitly coding an exception, a different set of rules for :p_search_date falling on Saturday?

Thanks, Tom!
Tom Kyte

Followup  

February 17, 2010 - 8:00 am UTC

just use date arithmetic and adjust as you see fit/need:

ops$tkyte%ORA10GR2> alter session set nls_date_format = 'Dy Mon DD YYYY';

Session altered.

ops$tkyte%ORA10GR2> var p_search_date varchar2(15)
ops$tkyte%ORA10GR2> exec :p_search_date := '16-FEB-2010'

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select dt
  2       , next_day( dt-1, 'sat' )    "Next Sat"
  3       , next_day( dt-1, 'sat' ) -6 "Last Sun"
  4       , next_day( dt-1, 'sat' ) +1 "Next Sun"
  5    from (select to_date( :p_search_date,'dd-mon-yyyy' )+level-1 dt
  6            from dual
  7                  connect by level <= 20 )
  8  /

DT              Next Sat        Last Sun        Next Sun
--------------- --------------- --------------- ---------------
Tue Feb 16 2010 Sat Feb 20 2010 Sun Feb 14 2010 Sun Feb 21 2010
Wed Feb 17 2010 Sat Feb 20 2010 Sun Feb 14 2010 Sun Feb 21 2010
Thu Feb 18 2010 Sat Feb 20 2010 Sun Feb 14 2010 Sun Feb 21 2010
Fri Feb 19 2010 Sat Feb 20 2010 Sun Feb 14 2010 Sun Feb 21 2010
Sat Feb 20 2010 Sat Feb 20 2010 Sun Feb 14 2010 Sun Feb 21 2010
Sun Feb 21 2010 Sat Feb 27 2010 Sun Feb 21 2010 Sun Feb 28 2010
Mon Feb 22 2010 Sat Feb 27 2010 Sun Feb 21 2010 Sun Feb 28 2010
Tue Feb 23 2010 Sat Feb 27 2010 Sun Feb 21 2010 Sun Feb 28 2010
Wed Feb 24 2010 Sat Feb 27 2010 Sun Feb 21 2010 Sun Feb 28 2010
Thu Feb 25 2010 Sat Feb 27 2010 Sun Feb 21 2010 Sun Feb 28 2010
Fri Feb 26 2010 Sat Feb 27 2010 Sun Feb 21 2010 Sun Feb 28 2010
Sat Feb 27 2010 Sat Feb 27 2010 Sun Feb 21 2010 Sun Feb 28 2010
Sun Feb 28 2010 Sat Mar 06 2010 Sun Feb 28 2010 Sun Mar 07 2010
Mon Mar 01 2010 Sat Mar 06 2010 Sun Feb 28 2010 Sun Mar 07 2010
Tue Mar 02 2010 Sat Mar 06 2010 Sun Feb 28 2010 Sun Mar 07 2010
Wed Mar 03 2010 Sat Mar 06 2010 Sun Feb 28 2010 Sun Mar 07 2010
Thu Mar 04 2010 Sat Mar 06 2010 Sun Feb 28 2010 Sun Mar 07 2010
Fri Mar 05 2010 Sat Mar 06 2010 Sun Feb 28 2010 Sun Mar 07 2010
Sat Mar 06 2010 Sat Mar 06 2010 Sun Feb 28 2010 Sun Mar 07 2010
Sun Mar 07 2010 Sat Mar 13 2010 Sun Mar 07 2010 Sun Mar 14 2010

20 rows selected.

Individual Dates from range

March 18, 2010 - 3:57 am UTC

Reviewer: Sheetal from Pune India

Hi Tom,

I have a particular implementation where we are storing leaves taken by employees,
but instead of exact dates we are using FromDate and TODate method.We are using 10gR2
Sunday is considered as weekly off.We need to run a report which gives
me total leaves taken by each employee per month for a calender year.
I have trouble since leave duration can exceed a month boundry,i can't extract
month and use group by on Fromdate.

Userid FromDate ToDate NoLeaves
U1 01-jan-2010 02-jan-2010 2
U1 08-jan-2010 11-jan-2010 3(10-jan-2010 is sunday weekly off)
U1 30-jan-2010 02-feb-2010 3(31-jan-2010 is sunday)
U2 11-jan-2010 12-jan-2010 2

U1 01-feb-2010 02-feb-2010 2
U1 16-feb-2010 18-feb-2010 3
U2 11-feb-2010 12-feb-2010 2

Output
Userid Month Count
U1 Jan 6 (2+3+1)
U1 Feb 5
U2 Jan 2
U2 Feb 2

Is there a way to implement is SQL.I am aware of the logic of getting
individual date from a range,but i am not able to plug that logic here.

select LEAVE_FROMDT + level -1
from dual
connect by level <= (LEAVE_TODT- LEAVE_FROMDT +1);

If at all we need to write a PL/SQL does oracle provided Package for calender function.

Thanks for your valuable time.

Sheetal;

Tom Kyte

Followup  

March 18, 2010 - 8:48 am UTC

no creates
no inserts
no look

Individual Dates from range

March 19, 2010 - 2:53 am UTC

Reviewer: Sheetal from Pune India

Hi Tom,
I apologize for the missing script.

CREATE TABLE leaves( Userid varchar2(10),FromDate Date,ToDate Date,Noleaves number);

INSERT INTO leaves
SELECT 'U1',to_date('01-jan-2010','dd-mon-yyyy'),to_date('02-jan-2010','dd-mon-yyyy'),2 from dual
union
SELECT 'U1',to_date('08-jan-2010','dd-mon-yyyy'),to_date('11-jan-2010','dd-mon-yyyy'),3 from dual
union
SELECT 'U1',to_date('30-jan-2010','dd-mon-yyyy'),to_date('02-feb-2010','dd-mon-yyyy'),3 from dual
union
SELECT 'U2',to_date('11-jan-2010','dd-mon-yyyy'),to_date('12-jan-2010','dd-mon-yyyy'),2 from dual
union
SELECT 'U1',to_date('16-feb-2010','dd-mon-yyyy'),to_date('18-feb-2010','dd-mon-yyyy'),3 from dual
union
SELECT 'U2',to_date('11-feb-2010','dd-mon-yyyy'),to_date('12-feb-2010','dd-mon-yyyy'),2 from dual
union
SELECT 'U3',to_date('30-mar-2010','dd-mon-yyyy'),to_date('03-may-2010','dd-mon-yyyy'),34 from dual;

SELECT * FROM leaves ORDER BY to_char(fromdate,'mm'),userid ;

Userid FromDate ToDate NoLeaves
U1 01-jan-2010 02-jan-2010 2
U1 08-jan-2010 11-jan-2010 3(10-jan-2010 is sunday weekly off)
U1 30-jan-2010 02-feb-2010 3(31-jan-2010 is sunday)
U2 11-jan-2010 12-jan-2010 2

U1 16-feb-2010 18-feb-2010 3
U2 11-feb-2010 12-feb-2010 2
U3 30-MAR-2010 03-May-2010 34 (02-may-2010 is sunday)
-------------------------------------------------------
Output of Report

Output
Userid Month Count
U1 Jan 6 (2+3+1)
U1 Feb 5 (2+3)
U2 Jan 2
U2 Feb 2
U3 Mar 2
U3 Apr 30
U3 May 2

Regards

Tom Kyte

Followup  

March 19, 2010 - 7:19 am UTC

now, go a step further and tell us the inputs to this query.

Meaning - are you planning on passing in a date range - a month range or are you wanting to query the table to find the minimum leave date, maximum leave date and generate that range?

SELECT * FROM leaves ORDER BY to_char(fromdate,'mm'),userid

very strange to order by 'mm' isn't it? assumes a year is constant and further lets the days in the month come out rather random...

Your output is wrong, there are not 30 days in April after you take sundays out.

anyway.. here is a solution:

ops$tkyte%ORA10GR2> variable start_month varchar2(20)
ops$tkyte%ORA10GR2> variable end_month varchar2(20)
ops$tkyte%ORA10GR2> exec :start_month := 'jan-2010'

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec :end_month := 'may-2010'

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> with data
  2  as
  3  (
  4  select *
  5    from (
  6  select to_date( :start_month, 'mon-yyyy')+level-1 dt
  7     from dual
  8  connect by level <= last_day(to_date( :end_month, 'mon-yyyy'))-to_date( :start_month, 'mon-yyyy')+1
  9          )
 10    where to_char( dt, 'd' ) <> to_char( to_date( '7-3-2010', 'dd-mm-yyyy' ), 'd' )
 11  )
 12  select userid, trunc(data.dt,'mm'), count(*)
 13    from data, leaves
 14   where data.dt between leaves.fromdate and leaves.todate
 15   group by userid, trunc(data.dt,'mm')
 16   order by userid, trunc(data.dt,'mm');

USERID     TRUNC(DAT   COUNT(*)
---------- --------- ----------
U1         01-JAN-10          6
U1         01-FEB-10          5
U2         01-JAN-10          2
U2         01-FEB-10          2
U3         01-MAR-10          2
U3         01-APR-10         26
U3         01-MAY-10          2

7 rows selected.

Individual Dates from range

March 20, 2010 - 5:49 am UTC

Reviewer: Sheetal from Pune India

Perfect solution,I got my answer.
Excuse me for not being able to present my problem clearly.
I am planning on passing on a date range
eg 01-jan-2010 (first day of month) and
31-dec-2010 (last day of month),we want to run report for a calendar year.

Just a generic query regarding Table Design. There are two options for storing data
1)One we are currently using
2)Eg if user 'U1' takes 3 days leave -
store it in 3 rows with exact dates,
instead of just one record with FromDate and FromDate.

Data Size - Total users in my system are 5615 .
If we take an average of two records per month per user
for one year i will have around 5615 *2 * 12 = 134760 rows.
This is case where I store one record for each leave application (Case 1: FromDate and FromDate ).
Other attributes for leave table are Type of leave,Leave Apply Date,
Approved Date,ApproverUserId,ApproverRemarks.

From your personal experience which one is a better approach,or is there a better way?
Thanks for helping me (Ignore if you find above design question to vague to consider)

Sheetal.
Tom Kyte

Followup  

March 20, 2010 - 9:32 am UTC

first approach will optimize inserts over retrieval.

second approach optimizes retrieval over inserts.

So, the question comes back to you - which do you need to optimize?


One approach will be able to answer the question "who was on leave on Monday, March 15th, 2010" rapidly, efficiently. The other will be able to answer it - but not nearly as fast, not nearly as efficiently.

So which do you choose? I still don't know because I don't know if you ever ASK that question or will ever ask that question. But you do :)


So, close your eyes, envision what you need to optimize most (it is probably retrieval, almost always it is retrieval).

You have a very small system - it probably doesn't matter one way or the other - unless you envision it growing very large at some time (which they typically do if they are successful). So, you might consider optimizing for retrieval.

And don't forget to DESIGN archiving and purging into your initial application release - from day one!

March 20, 2010 - 5:00 pm UTC

Reviewer: A reader


Why all the complex rownum and pl/sql fuzz?

March 25, 2010 - 10:05 am UTC

Reviewer: Niels Jessen from Denmark

Honestly, I do not understand the contents of the pl/sql solution. Being old and bitter and worried when I see long blog discussions I implemented a table based solution:

Advantages:

- it is simple and readable
- every business user can see it
- you can apply an Apex application to it
- you can access it using two natural joins - one for the 'from' date and one for the 'to' date
- it is easy to manipulate and document
- you can add other workday related stuff to it - like work hours as I did
- you can weigh selected day like 1.2 in stead of the default 1
- you can add time dependent stuff like when working hours go from one value to another
- you can create coutry specific versions of it


The sample code creates a test version based on a simple date dimension containg one row per date.

create table "test_workdays_dates"
as
select "dt_transaction_date" transaction_date
, trim(to_char("dt_transaction_date",'day')) week_day
, 0 workday_no
, 0 workhour_no
from mis_date_dimension
where dt_transaction_date >= add_months(trunc(sysdate,'year'),-12)
order by 1
;

declare
v_workday_no number := 0;
v_workhour_no number := 0;
cursor c_workdays is
select transaction_date,week_day,workday_no,workhour_no
from test_workdays_dates;

r_workdays c_workdays%rowtype;
begin

for r_workdays in c_workdays
loop

if lower(r_workdays.week_day) not in ('saturday','sunday')
then v_workday_no := v_workday_no + 1;
v_workhour_no := v_workhour_no + 7.4;
end if;

update test_workdays_dates
set workday_no = v_workday_no
,workhour_no = v_workhour_no
where transaction_date = r_workdays.transaction_date;
end loop;
end;

commit;
Tom Kyte

Followup  

March 26, 2010 - 1:43 pm UTC

have you heard of views? They were introduced quite a few releases ago....

Calculated instead of using a table

May 25, 2010 - 12:47 pm UTC

Reviewer: Bob Thiele from Fort Wayne, IN USA

--Getting the number of days is a simple subtraction
--Determining the week is a math function based on a given base date 1/1/1900 is a Monday and is fairly standard
--You can calculate the number of weekends by subtracting the two
--I assumed both dates would not be on a weekend. You can add a case statement to check if needed
--Get holidays from a table

select StartDate, EndDate,
trunc(EndDate) - Trunc(StartDate) as ddays,
cast(trunc(cast(StartDate - BaseDate as float)/7) as int) as weekstartdate,
cast(trunc(cast(EndDate - BaseDate as float)/7) as int) as weekenddate,
(select count(holdate) from resources.holiday where holdate between trunc(StartDate) and trunc(EndDate)) as holidays,
trunc(EndDate) - Trunc(StartDate) - (cast(trunc(cast(EndDate - BaseDate as float)/7) as int) - cast(trunc(cast(StartDate - BaseDate as float)/7) as int)) * 2 as weekdays
from(
select to_date('01/08/1900 23:10','MM/DD/YYYY HH24:MI') as StartDate,
to_date('01/15/1900 11:15','MM/DD/YYYY HH24:MI') as EndDate,
to_date('01/01/1900','MM/DD/YYYY') as BaseDate
from dual) t;

Bob Thiele's solution is WRONG

October 26, 2010 - 9:39 am UTC

Reviewer: StewS from Vermont, USA

I wanted something simple that didn't require creating a table, so I liked the idea of Bob's solution. Unfortunately, I also want the result to be right.

Testing that solution with startdate = 10/03/2010, enddate = 10/09/2010 returns 4 weekdays, when there are 5. That's without a holidays table.

The basic query that shows this is:

SELECT startdate,
       enddate,
       TRUNC(enddate) - TRUNC(startdate) AS ddays,
       CAST(TRUNC(CAST(startdate - basedate AS FLOAT) / 7) AS INT) AS weekstartdate,
       CAST(TRUNC(CAST(enddate - basedate AS FLOAT) / 7) AS INT) AS weekenddate,
       TRUNC(enddate) - TRUNC(startdate) -
       (CAST(TRUNC(CAST(enddate - basedate AS FLOAT) / 7) AS INT) -
        CAST(TRUNC(CAST(startdate - basedate AS FLOAT) / 7) AS INT)) * 2 AS weekdays
  FROM (SELECT TO_DATE('10/03/2010 00:01', 'MM/DD/YYYY HH24:MI') AS startdate,
               TO_DATE('10/09/2010 00:01', 'MM/DD/YYYY HH24:MI') AS enddate,
               TO_DATE('01/01/1900', 'MM/DD/YYYY') AS basedate
          FROM dual) t;


Rats.

DOH!

October 26, 2010 - 9:43 am UTC

Reviewer: StewS from Vermont, USA

Had I read Tom's solution more carefully, I'd have seen there's no table creation involved, unless you want to add a holiday schedule.

The solution doesn't get much simpler, provided you have a database with a fair number of objects or small date ranges!

working days

November 11, 2010 - 6:32 am UTC

Reviewer: A reader

Hi Tom,
Can you please advise a solution(SQL) - want to find working days between TWO dates (date1 and date2 ) .
It should not only exclude SAT and SUN but also the PUBLIC/BANK Holidays, I stored all Bank Holidays in a TABLE -TBL_PUBLIC_HOLIDAYS.


Tom Kyte

Followup  

November 11, 2010 - 2:47 pm UTC

hmmm,

seems like you might just have landed on a good page - did you read it yet? If you did, you might have saved yourself some keystrokes?

excluding holidays

January 02, 2011 - 4:27 am UTC

Reviewer: leo

Hi Tom thanks for wonderful codes from you.
I need your help again

create table no_study_days (ndate date);

insert into no_study_days values ('25-dec-2010');

create table absent (sid number,absdate date,reason varchar2(2));

insert into absent values (32,'24-dec-2010','A');

insert into absent values (32,'23-dec-2010','A');

insert into absent values (32,'22-dec-2010','A');

insert into absent values (248,'24-dec-2010','A');

insert into absent values (248,'23-dec-2010','A');

insert into absent values (363,'24-dec-2010','A');

insert into absent values (363,'23-dec-2010','A');

insert into absent values (363,'22-dec-2010','A');

select * from absent order by sid, absdate desc;

SID ABSDATE RE
---------- --------- --
32 24-DEC-10 A
32 23-DEC-10 A
32 22-DEC-10 A

248 24-DEC-10 A
248 23-DEC-10 A

363 24-DEC-10 A
363 23-DEC-10 A
363 22-DEC-10 A

8 rows selected.

i want to get those sid who have 3 continuous absentees from today.

Suppose today is '24-dec-2010' (change my system date)

so most recent three schools days are 22 dec, 23 dec and 24 dec

select sid,sum(cs) from (
select sid,sysdate,absdate,trunc(sysdate) - absdate,
case when trunc(sysdate) - absdate in (1,2,0) then 1 else 0 end cs from absent order by sid,absdate desc)
group by sid
having sum(cs) = 3
/

SID SUM(CS)
---------- ----------
32 3
363 3

But the problem begins with Sundays and NO_STUDY_DAYS table

insert into absent values (248,'27-dec-2010','A');

select * from absent order by sid, absdate desc;

SID ABSDATE RE
---------- --------- --
32 24-DEC-10 A
32 23-DEC-10 A
32 22-DEC-10 A

248 27-DEC-10 A
248 24-DEC-10 A
248 23-DEC-10 A

363 24-DEC-10 A
363 23-DEC-10 A
363 22-DEC-10 A

9 rows selected.

and suppose today's date is 27-dec-2010. (change my system date again)

so most recent three schools days are 23 dec, 24 dec and 27 dec

(becuase 25 is off due to public holiday present in NO_STUDY_DAYS table and 26 dec is SUNDAY itself)

the above query results

*no rows selected*

but I was expecting 248 only (32 and 363 will be considered as present students if they are not in absent table on '27-dec-2010')

so expected result set for above data is

SID SUM(CS)
---------- ----------
248 3

So how to achieve these results

Thanks a lot

Holiday Table from Vendor

April 22, 2011 - 2:23 pm UTC

Reviewer: Jeff from MPLS, MN

This is a great solution for solving a common problem. Thanks Tom.

I am tasked with reporting against a vendor supplied Holiday table that stores information like this:

desc holiday_schedule
Name Null Type
------------------------------ -------- ------------------
MONTH NOT NULL NUMBER(10)
YEAR NOT NULL NUMBER(10)
DAY_1 VARCHAR2(1 BYTE)
DAY_2 VARCHAR2(1 BYTE)
DAY_3 VARCHAR2(1 BYTE)
DAY_4 VARCHAR2(1 BYTE)
DAY_5 VARCHAR2(1 BYTE)
DAY_6 VARCHAR2(1 BYTE)
DAY_7 VARCHAR2(1 BYTE)
DAY_8 VARCHAR2(1 BYTE)
DAY_9 VARCHAR2(1 BYTE)
DAY_10 VARCHAR2(1 BYTE)
DAY_11 VARCHAR2(1 BYTE)
DAY_12 VARCHAR2(1 BYTE)
DAY_13 VARCHAR2(1 BYTE)
DAY_14 VARCHAR2(1 BYTE)
DAY_15 VARCHAR2(1 BYTE)
DAY_16 VARCHAR2(1 BYTE)
DAY_17 VARCHAR2(1 BYTE)
DAY_18 VARCHAR2(1 BYTE)
DAY_19 VARCHAR2(1 BYTE)
DAY_20 VARCHAR2(1 BYTE)
DAY_21 VARCHAR2(1 BYTE)
DAY_22 VARCHAR2(1 BYTE)
DAY_23 VARCHAR2(1 BYTE)
DAY_24 VARCHAR2(1 BYTE)
DAY_25 VARCHAR2(1 BYTE)
DAY_26 VARCHAR2(1 BYTE)
DAY_27 VARCHAR2(1 BYTE)
DAY_28 VARCHAR2(1 BYTE)
DAY_29 VARCHAR2(1 BYTE)
DAY_30 VARCHAR2(1 BYTE)
DAY_31 VARCHAR2(1 BYTE)

So if May 30, 2011 were a holiday, there would be a record in the table of MONTH=5, YEAR=2011, and DAY_30='T'. The rest of the DAY_ fields would be 'F', since no other holidays fall in the month of May for us.

Is it possible to modify your exclude clause on holidays to handle data like that?

Thanks.
Tom Kyte

Followup  

April 25, 2011 - 11:00 am UTC

ops$tkyte%ORA11GR2> insert into t ( month, year, day_30 ) values ( 5, 2011, 'T' );

1 row created.

ops$tkyte%ORA11GR2> insert into t ( month, year, day_29 ) values ( 6, 2011, 'T' );

1 row created.

ops$tkyte%ORA11GR2> insert into t ( month, year, day_30 ) values ( 6, 2011, 'T' );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select to_date( to_char(year,'fm0000')||to_char(month,'fm00')||to_char(r,'fm00'), 'yyyymmdd' )
  2    from t,
  3         (select level r from dual connect by level <= 31)
  4   where decode( r, 1, day_1,
  5                    2, day_2,
  6                    3, day_3,
  7                    4, day_4,
  8                    5, day_5,
  9                    6, day_6,
 10                    7, day_7,
 11                    8, day_8,
 12                    9, day_9,
 13                    10, day_10,
 14                    12, day_12,
 15                    13, day_13,
 16                    14, day_14,
 17                    15, day_15,
 18                    16, day_16,
 19                    17, day_17,
 20                    18, day_18,
 21                    19, day_19,
 22                    20, day_20,
 23                    22, day_22,
 24                    23, day_23,
 25                    24, day_24,
 26                    25, day_25,
 27                    26, day_26,
 28                    27, day_27,
 29                    28, day_28,
 30                    29, day_29,
 31                    30, day_30,
 32                    31, day_31 ) = 'T';

TO_DATE(T
---------
29-JUN-11
30-MAY-11
30-JUN-11




You can fix their horrible design "anti-pattern" and make it look like a normal table, yes.

Holiday Table from Vendor

April 25, 2011 - 11:54 am UTC

Reviewer: Jeff from MPLS, MN

Wow, you are freakin AWESOME!

So to integrate this into your original query, I would do something like this?


select count(*) from ( select rownum rnum from all_objects where rownum <= to_date('&1') - to_date('&2') +1 )
where to_char( to_date('&2')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' )
and not exists (
select null
from holiday_schedule,
(select level r from dual connect by level <= 31)
where decode( r, 1, day_1,
2, day_2,
3, day_3,
4, day_4,
5, day_5,
6, day_6,
7, day_7,
8, day_8,
9, day_9,
10, day_10,
12, day_12,
13, day_13,
14, day_14,
15, day_15,
16, day_16,
17, day_17,
18, day_18,
19, day_19,
20, day_20,
22, day_22,
23, day_23,
24, day_24,
25, day_25,
26, day_26,
27, day_27,
28, day_28,
29, day_29,
30, day_30,
31, day_31 ) = 'T'
and to_date( to_char(year,'fm0000')||to_char(month,'fm00')||to_char(r,'fm00'), 'yyyymmdd' ) = trunc(to_date('&2')+rnum-1))

It seems to work, I just wanted to check with you.

Thanks again!
Tom Kyte

Followup  

April 25, 2011 - 1:29 pm UTC

You could make a view to hide the complexity - and include the MONTH and YEAR columns

then you would query:

not exists ( select Null from your_view
where year = to_number( to_char( to_date('&2')+rnum-1, 'yyyy' ) )
and month = to_number( to_char( to_date('&2')+rnum-1, 'mm' ) )
and the_date = trunc(to_date('&2')+rnum-1) );


(the_date is a column you added in the view that is the TO_DATE of the stuff from the base table)

that would let us use an index on year and month if it exists, in any case, it would cut down on the work performed - we wouldn't have to create all possible holidays, just the one in the month of interest

Modified for PeopleSoft pay calendar

May 05, 2011 - 3:06 pm UTC

Reviewer: Tom H. from OKC, OK

SELECT (((pay_end_dt - pay_begin_dt) - (round((pay_end_dt - pay_begin_dt)/7))*2))+1
FROM sysadm.ps_pay_calendar WHERE pay_end_dt = To_Date('28-FEB-2011','dd-mon-yyyy')
rem will compute number of work days including holdidays, in case shown 28-Feb-2011 is in pay_calendar record.

obtain the date after X working days

June 02, 2011 - 2:31 pm UTC

Reviewer: Pedro

Hi Tom

A question, if I want to obtain the date after X working days from today how do we do that?

For example, today is 02/JUN/2011, I want the date after 9 working days which is 15/JUN/2011, how can we do this in SQL?

Thanks

June 27, 2011 - 4:52 pm UTC

Reviewer: Joao Camilo from Rio de Janeiro, Brazil

Pedro,

Try

SELECT MAX(dates) as desired_date
FROM
(SELECT initial_date + LEVEL AS dates
FROM DUAL
WHERE TO_CHAR( initial_date + LEVEL,'D') NOT IN ('1','7')
CONNECT BY LEVEL <= working_days + 2*CEIL((working_days/7) + 1)
)
;

Change "workink_days" and "initial_date" with values that you want to use.

June 27, 2011 - 5:10 pm UTC

Reviewer: Joao Camilo from Rio de Janeiro, Brazil

Sorry Pedro,

The last query needed some adjusts.

SELECT dates as desired_date
FROM
(SELECT initial_date + LEVEL AS dates, rownum line
FROM DUAL
WHERE TO_CHAR( initial_date + LEVEL,'D') NOT IN ('1','7')
CONNECT BY LEVEL <= working_days + 2*CEIL((working_days/7) + 2)
)
where line = working_days
;

Change "workink_days" and "initial_date" with values that you want to use.
Now I think that it returns the right answer in all cases.

Week Ranges

July 01, 2011 - 1:11 am UTC

Reviewer: Rizwan from INDIA

Hi Tom,
In my table one Msgtype varchar,Subdate date,port Varchar
are there i wnat to display count of each row in Msgtype where port should be group by.I will give startdate and enddate dynamically then
count(msgtype)
where Subdate between ---- and ----
here in between i wnt some week ranges where week should start from saturday.
Example: startdate:1-07-2011 Enddate:31-07-2011
then I want count of ranges from 2-07-2011 - 8-07-2011,
09-07-2011 - 15-07-2011, 16-07-2011 - 22-07-2011, 23-07-2011 - 29-07-2011.
like below 4 ranges
Msgtype range1 range2 range3 range4
abc 5 3 0 9
def 6 2 5 6
geh 7 1 9 8

Thanks in advance

Tom Kyte

Followup  

July 01, 2011 - 9:19 am UTC

no create table
no inserts
no look

i have no idea if what you want to do can be done, all I know is that I have no example to work with.

Day calculation without subquery

November 19, 2012 - 7:25 am UTC

Reviewer: Max from Munich, GER

Hello,

in order to use the following query in my own subquery (i.e.: iterating over pickup and delivery dates of a logistic application, to calculate the days of delivery process):

SELECT COUNT (*)
  FROM (SELECT ROWNUM rnum
          FROM all_objects
         WHERE ROWNUM <= TO_DATE ('&1') - TO_DATE ('&2') + 1)
 WHERE TO_CHAR (TO_DATE ('&2') + rnum - 1, 'DY') NOT IN ('SAT', 'SUN');


I have to do it with a single query:

SELECT SUM (CASE
               WHEN TO_CHAR (TO_DATE ('&2') + ROWNUM - 1, 'DY') NOT IN ('SAT', 'SUN') THEN 1
               ELSE 0
            END)
  FROM all_objects
 WHERE ROWNUM <= TO_DATE ('&2') - TO_DATE ('&1') + 1;


So I can do the following:

SELECT pickup_date
     , delivery_date
     , (SELECT SUM (CASE
                       WHEN TO_CHAR (delivery_date + ROWNUM - 1, 'DY') NOT IN ('SAT', 'SUN') THEN 1
                       ELSE 0
                    END)
          FROM all_objects
         WHERE ROWNUM <= delivery_date - pickup_date + 1)
          days_of_delivery
  FROM t;


Regards,
Max

Simple algorithm

April 18, 2013 - 7:01 pm UTC

Reviewer: Paulo Terça from Lisbon, Portugal

Hi.
I came accross this problem today and found this post. I did my own implementation and thought of sharing it here.
I hope it may be useful to someone.
Best regards,
Paulo Terça
Lisbon, Portugal

/* This should be used to register holidays */
CREATE TABLE HOLIDAYS( HOLIDAY DATE);

/* This returns the number of holidays that fall in weekdays */
CREATE OR REPLACE FUNCTION GET_NUM_HOLIDAYS( p_start_date DATE, p_end_date DATE) RETURN NUMBER
AS
v_num NUMBER;
BEGIN
SELECT count(1)
INTO v_num
FROM HOLIDAYS
WHERE HOLIDAY BETWEEN p_start_date AND p_end_date
AND to_char(HOLIDAY,'D') BETWEEN '2' AND '6';
RETURN v_num;
END GET_NUM_HOLIDAYS;
/

/* This returns the number of business days between two dates */
CREATE OR REPLACE FUNCTION DAYS_BETWEEN_DATES( p_start_date DATE, p_end_date DATE) RETURN NUMBER
AS
v_interval NUMBER(38);
v_weekenddays INTEGER;
v_modweek PLS_INTEGER;
v_start_day PLS_INTEGER;
TYPE T_WEEKTAB IS VARRAY(7) OF PLS_INTEGER;
TYPE T_MODTAB IS VARRAY(7) OF T_WEEKTAB;
G_MODWEEKTAB T_MODTAB := T_MODTAB( -- S M T W T F S
/* Mod 0 */ T_WEEKTAB( 0, 0, 0, 0, 0, 0, 0)
/* Mod 1 */ ,T_WEEKTAB( 1, 0, 0, 0, 0, 0, 1)
/* Mod 2 */ ,T_WEEKTAB( 1, 0, 0, 0, 0, 1, 2)
/* Mod 3 */ ,T_WEEKTAB( 1, 0, 0, 0, 1, 2, 2)
/* Mod 4 */ ,T_WEEKTAB( 1, 0, 0, 1, 2, 2, 2)
/* Mod 5 */ ,T_WEEKTAB( 1, 0, 1, 2, 2, 2, 2)
/* Mod 6 */ ,T_WEEKTAB( 1, 1, 2, 2, 2, 2, 2)
);
BEGIN
v_interval := p_end_date - p_start_date + 1; -- calculate total number of days between dates, including start and end dates
v_weekenddays := floor(v_interval / 7) * 2; -- calculate aproximate number of weekend days between the two dates
v_modweek := mod( v_interval, 7); -- calculate how many days remain after computing the total number of full weeks
v_start_day := to_number(to_char( p_start_date, 'D')); -- get day of week of start date
v_interval := v_interval - v_weekenddays - G_MODWEEKTAB(v_modweek + 1)(v_start_day); -- subtract weekend days, correcting with G_MODWEEKTAB table
v_interval := v_interval - GET_NUM_HOLIDAYS( p_start_date, p_end_date); -- subtract holidays
RETURN v_interval;
END DAYS_BETWEEN_DATES;
/

org.hibernate.exception.sqlgrammarexception

May 11, 2013 - 12:19 pm UTC

Reviewer: A reader


Query to get previous two business days.

October 03, 2013 - 7:52 am UTC

Reviewer: Rajeshwaran

Tom,

We got an requirement to get two previous business days for a given date ( the given date will always a business day and never be a Week ends (sat,sun) ). I got the solution framed like this, do you have any smarter way of handling this ?

rajesh@ORA11GR2> exec :dt := '03-oct-2013';

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>
rajesh@ORA11GR2> select x
  2  from (
  3  select to_date(:dt,'dd-mon-yyyy') - level +1 x
  4  from dual
  5  where to_char( to_date(:dt,'dd-mon-yyyy') - level +1 ,'dy') not in ('sat','sun')
  6  connect by level <= 5
  7  order by x desc
  8       )
  9  where rownum <=3 ;

X
-----------------------
03-OCT-2013 12:00:00 AM
02-OCT-2013 12:00:00 AM
01-OCT-2013 12:00:00 AM

3 rows selected.

rajesh@ORA11GR2> exec :dt := '30-sep-2013';

PL/SQL procedure successfully completed.

rajesh@ORA11GR2> /

X
-----------------------
30-SEP-2013 12:00:00 AM
27-SEP-2013 12:00:00 AM
26-SEP-2013 12:00:00 AM

3 rows selected.

rajesh@ORA11GR2> 

i need to check if i miss something

May 24, 2015 - 3:23 pm UTC

Reviewer: khamis al-mughrabi from kuwait

Hi TOM,

I do not have problems here, but you are the expert and I like to share. could you advice on below function thanks.

create or replace function fn_GetBusDays
(
v_DATEFROM DATE,
v_DATETO DATE
)
RETURN NUMBER
AS
v_iDays INTEGER := 0;
v_iWeeks INTEGER := 0;
v_iBusDays INTEGER := 0;
v_iRem INTEGER := 0;
v_iHoly INTEGER := 0;
v_DATEFROMTEMP DATE := v_DATEFROM;
BEGIN
v_iDays := v_DATETO - v_DATEFROM + 1;
v_iWeeks := v_iDays / 7;
v_iBusDays := v_iWeeks * 5;
v_iRem := MOD(v_iDays, 7);

--// Query Holydays from table
v_iHoly := 0;

--// Loop Only with the remining and execlude the week-ends.
loop

exit when v_iRem <= 0;

if to_char( to_date(v_DATEFROMTEMP), 'DY' ) not in ( 'SAT', 'SUN' ) then
v_iBusDays := v_iBusDays + 1;
end if;

v_DATEFROMTEMP := v_DATEFROMTEMP + 1;
v_iRem := v_iRem - 1;

end loop;

--// fn_GetHoldyBusDays to not recounte weekends again
SELECT SUM(CASE WHEN ( HolyBeginDate BETWEEN v_DATEFROM AND v_DATETO ) AND v_DATETO > HolyEndDate
THEN fn_GetHoldyBusDays(HolyBeginDate, HolyEndDate)
WHEN ( HolyBeginDate BETWEEN v_DATEFROM AND v_DATETO ) AND v_DATETO <= HolyEndDate
THEN fn_GetHoldyBusDays(HolyBeginDate, v_DATETO)
WHEN ( HolyEndDate BETWEEN v_DATEFROM AND v_DATETO )
THEN fn_GetHoldyBusDays(v_DATEFROM, HolyEndDate)
ELSE 0 END)
INTO v_iHoly
FROM TBL_HR_HOLYDAY
WHERE ( HolyBeginDate BETWEEN v_DATEFROM AND v_DATETO )
OR ( HolyEndDate BETWEEN v_DATEFROM AND v_DATETO );

IF v_iHoly > 0 then
v_iBusDays := v_iBusDays - v_iHoly;
end if;

RETURN v_iBusDays;

END;

The function kills response time on large records number

October 27, 2017 - 4:21 pm UTC

Reviewer: Alex

This function 20-25 times faster - depends on hardware and dates' difference
create or replace function num_business_days_my(p_beg_dt date, p_end_dt date) return number
is
l_work_days binary_integer;
begin
with dif as (
select case when nvl(p_end_dt - p_beg_dt, 0) > 0 then floor(p_end_dt - p_beg_dt) + 1 end days
from dual
)
select days - count(case when to_char(p_beg_dt + level,'DY') in ('SAT','SUN') then 1 end) work_days
into l_work_days
from dif connect by level <= days;
return l_work_days;
end num_business_days_my;
/

Connor McDonald

Followup  

October 28, 2017 - 2:52 am UTC

SQL> select num_business_days_my(date '2000-01-01', date '2000-01-05') hmmm from dual;

      HMMM
----------
         4


That date range is:

Sat
Sun
Mon
Tue
Wed


java code

May 10, 2018 - 8:29 pm UTC

Reviewer: vinodreddy from India

sir please wirt in java code

How about this ?

May 11, 2018 - 7:03 pm UTC

Reviewer: BC from MT, MI

Simple SQL solution that can easily be included in a function

To exclude business holidays, I created a table containing the 2018 Business Holidays that we have and populated it as follow

dba@12Cr2> select *
2 from business_holidays;

HOLIDAY_NAME HOLIDAY
-------------------------------------------------- --------------------
New Years Day 01-Jan-2018 00:00:00
Martin Luther King, Jr. Day 15-Jan-2018 00:00:00
Presidents Day Monday 19-Feb-2018 00:00:00
Memorial Day 28-May-2018 00:00:00
Independence Day 04-Jul-2018 00:00:00
Labor Day 03-Sep-2018 00:00:00
Veterans Day 12-Nov-2018 00:00:00
Thanksgiving 22-Nov-2018 00:00:00
Christmas 25-Dec-2018 00:00:00

9 rows selected.

Yeah we have 9 holidays ... :)

Now If I want to find the Business Days for 2018, excluding weekends and the 9 days above, I run

dba@12Cr2> select sum( business_day ) business_days
2 from (
3 select start_date + ( level - 1 ) current_date,
4 to_char( start_date + ( level - 1 ), 'Day D' ) current_day,
5 level,
6 case
7 when to_char( start_date + ( level - 1 ), 'DY' ) in( 'SAT', 'SUN' ) then 0
8 else 1
9 end business_day
10 from ( select to_date( '&start_date', 'mm/dd/yyyy' ) start_date,
11 to_date( '&end_date', 'mm/dd/yyyy' ) end_date,
12 to_date( '&end_date', 'mm/dd/yyyy' ) - to_date( '&start_date', 'mm/dd/yyyy' ) days_count
13 from dual )
14 where 1 = 1
15 connect by level <= days_count + 1
16 )
17 where 1 = 1
18 and not exists( select 'x'
19 from business_holidays
20 where holiday = current_date );

Enter value for start_date: 01/01/2018
old 10: from ( select to_date( '&start_date', 'mm/dd/yyyy' ) start_date,
new 10: from ( select to_date( '01/01/2018', 'mm/dd/yyyy' ) start_date,
Enter value for end_date: 12/31/2018
old 11: to_date( '&end_date', 'mm/dd/yyyy' ) end_date,
new 11: to_date( '12/31/2018', 'mm/dd/yyyy' ) end_date,
Enter value for end_date: 12/31/2018
Enter value for start_date: 01/01/2018
old 12: to_date( '&end_date', 'mm/dd/yyyy' ) - to_date( '&start_date', 'mm/dd/yyyy' ) days_count
new 12: to_date( '12/31/2018', 'mm/dd/yyyy' ) - to_date( '01/01/2018', 'mm/dd/yyyy' ) days_count

BUSINESS_DAYS
-------------
261

And to verify that this works for Connors example above

dba@12Cr2> /
Enter value for start_date: 01/01/2000
old 10: from ( select to_date( '&start_date', 'mm/dd/yyyy' ) start_date,
new 10: from ( select to_date( '01/01/2000', 'mm/dd/yyyy' ) start_date,
Enter value for end_date: 01/05/2000
old 11: to_date( '&end_date', 'mm/dd/yyyy' ) end_date,
new 11: to_date( '01/05/2000', 'mm/dd/yyyy' ) end_date,
Enter value for end_date: 01/05/2000
Enter value for start_date: 01/01/2000
old 12: to_date( '&end_date', 'mm/dd/yyyy' ) - to_date( '&start_date', 'mm/dd/yyyy' ) days_count
new 12: to_date( '01/05/2000', 'mm/dd/yyyy' ) - to_date( '01/01/2000', 'mm/dd/yyyy' ) days_count

BUSINESS_DAYS
-------------
3



Connor McDonald

Followup  

May 12, 2018 - 1:28 am UTC

nice input

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.