-1, +1, -1 why?
Sam, October 07, 2001 - 9:36 pm UTC
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.
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
Asif Momen, October 08, 2001 - 2:54 am UTC
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
October 08, 2001 - 8:07 am UTC
Yes, you are correct, thanks for pointing that out. I've corrected the code.
A reader, October 08, 2001 - 12:04 pm UTC
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' )
October 08, 2001 - 1:37 pm UTC
I corrected that.
Will this work, one of our customer says that this is fastest one
Sonali Kelkar, June 04, 2002 - 1:45 pm UTC
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;
/
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
Vladimir, December 09, 2002 - 4:41 pm UTC
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.
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
Randy Schneider, April 23, 2003 - 12:53 pm UTC
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.
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 ?
A Reader, April 28, 2003 - 5:39 am UTC
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
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
A Reader, April 28, 2003 - 5:48 am UTC
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 ?
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...
A Reader, April 28, 2003 - 6:22 am UTC
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
A reader, June 04, 2003 - 2:08 am UTC
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) ) ;
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
debjit, June 04, 2003 - 8:26 am UTC
Can u tell how to select all days between two date which are not business dates in a single sql
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?
Kashif, June 04, 2003 - 2:09 pm UTC
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
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.
A reader, July 09, 2003 - 7:05 pm UTC
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
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.
A reader, July 10, 2003 - 12:54 am UTC
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
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
Vj, July 10, 2003 - 7:39 am UTC
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
A reader, July 15, 2003 - 7:58 pm UTC
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.
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
A reader, July 16, 2003 - 4:26 am UTC
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.
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?
A reader, July 16, 2003 - 4:37 am UTC
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.
July 16, 2003 - 9:34 am UTC
seems SIMPLE enought to me
"subtract one"
(oh wait, i already said that before.....)
Genius
Mike, August 19, 2003 - 10:55 am UTC
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....
Nick, September 08, 2003 - 10:34 am UTC
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.
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
Andrew Rye, October 10, 2003 - 6:45 pm UTC
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 */
vj, November 18, 2003 - 3:05 am UTC
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
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
sonali, March 19, 2004 - 8:33 am UTC
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
Logan Palanisamy, July 23, 2004 - 3:46 pm UTC
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.
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
A reader, July 23, 2004 - 4:15 pm UTC
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
July 23, 2004 - 5:14 pm UTC
and someone else has the same idea at the same time :)
no of working days
Tanweer, August 17, 2004 - 9:29 am UTC
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)
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
virgile, August 17, 2004 - 11:27 am UTC
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
virgile, August 20, 2004 - 8:53 am UTC
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
sonali, November 04, 2004 - 12:10 pm UTC
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
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
Shrikant Gavas, November 29, 2004 - 8:33 am UTC
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
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
Shrikant, November 29, 2004 - 9:07 am UTC
Tom, Can you please explain the logic behind this.
November 29, 2004 - 2:55 pm UTC
"page up"
been there, did that....
Another SQL to Generate Business days between two dates ...
VKOUL, November 29, 2004 - 8:12 pm UTC
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
Robin, December 20, 2004 - 11:11 am UTC
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.
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...
Robin, December 21, 2004 - 11:31 am UTC
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!
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
Robin, December 21, 2004 - 1:05 pm UTC
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!
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.....
Robin, December 21, 2004 - 2:09 pm UTC
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!
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
Alexander, December 21, 2004 - 2:39 pm UTC
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?
December 21, 2004 - 3:13 pm UTC
i agree - it seems a logical conclusion just looking at the approach.
Excluding holidays...
Robin, December 30, 2004 - 10:18 am UTC
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
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
Robin, January 07, 2005 - 9:45 am UTC
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
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?
Robin, January 10, 2005 - 10:30 am UTC
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?
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!
Robin, January 10, 2005 - 11:21 am UTC
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!
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
anto, February 03, 2005 - 11:56 am UTC
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
February 03, 2005 - 2:38 pm UTC
(case when to_date(df,'mm') <= 6 then 1 else 2 end)
anto, February 03, 2005 - 12:17 pm UTC
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
A reader, February 03, 2005 - 3:19 pm UTC
thanks
One way to handle floating holidays
Mark Hammingh, June 09, 2005 - 5:52 pm UTC
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;
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
Mark Hammingh, June 09, 2005 - 11:04 pm UTC
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>
June 09, 2005 - 11:05 pm UTC
;)
thanks
Can be done without using ALL_OBJECTS
Justin Rowles, June 17, 2005 - 7:36 am UTC
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
Usha G., June 17, 2005 - 5:03 pm UTC
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.
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??
A reader, June 24, 2005 - 10:41 am UTC
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
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
Michael, July 03, 2005 - 3:50 pm UTC
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
Robin H., November 02, 2005 - 2:48 pm UTC
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?
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
Sheila, November 10, 2005 - 3:56 pm UTC
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
Yogesh Chaudhari, December 29, 2005 - 10:16 am UTC
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;
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
Yogesh Chaudhari, December 29, 2005 - 1:05 pm UTC
Excellent, it worked
Ravi Kumar, January 03, 2006 - 2:30 am UTC
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
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?
Ravi Kumar, January 03, 2006 - 11:03 pm UTC
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
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.
Ravi Kumar, January 04, 2006 - 12:54 am UTC
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
January 04, 2006 - 8:49 am UTC
why doesn't the above approaches work for you?
Add_Bus_Days Recursive procedure
Yogesh Chaudhari, January 04, 2006 - 11:02 am UTC
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;
A reader, March 31, 2006 - 3:30 pm UTC
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?
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..
A reader, March 31, 2006 - 3:33 pm UTC
I forgot to mention the issue is that the date given is Sunday (04/02/2006) (though the passed date also is sunday). Sorry.
A reader, March 31, 2006 - 4:55 pm UTC
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 !!:(
oracleLeo, May 28, 2006 - 4:48 am UTC
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
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
A reader, July 26, 2006 - 11:20 am UTC
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
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
A reader, July 26, 2006 - 12:08 pm UTC
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
July 26, 2006 - 1:31 pm UTC
where dt >= CASE when 'today' then trunc(sysdate) when ..... END
and dt < CASE ..... END
Search filters
A reader, July 26, 2006 - 12:18 pm UTC
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?
July 26, 2006 - 1:32 pm UTC
it would work, yes.
A reader, July 27, 2006 - 4:58 pm UTC
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!
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.
Michel Cadot, July 28, 2006 - 12:30 am UTC
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
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
Matthias Rogel, July 28, 2006 - 7:12 am UTC
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 ?
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
VijayS, December 28, 2006 - 3:08 am UTC
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
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.
VijayS, December 28, 2006 - 9:26 am UTC
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
December 28, 2006 - 9:51 am UTC
sql reference, subquery factoring is what it is called.
Here's how I handle it
Neil Kodner, December 28, 2006 - 2:41 pm UTC
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
Sonali, January 17, 2007 - 1:49 pm UTC
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
Sonali, January 17, 2007 - 2:51 pm UTC
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
A reader, January 29, 2007 - 9:41 am UTC
thanks tom.
how to exclude the holidays
yeshwant, May 28, 2007 - 7:52 am UTC
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
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
Gaurav Sharma, July 20, 2007 - 12:07 am UTC
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
July 20, 2007 - 8:15 am UTC
here are the details:
read this page from top to bottom.
all dates and times
Gabriel, July 20, 2007 - 10:02 am UTC
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,
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!!!
A reader, July 20, 2007 - 5:08 pm UTC
Getting Next Business Day
Alex, November 02, 2007 - 1:10 pm UTC
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
esraa, December 04, 2007 - 1:06 am UTC
hi
i want ask about i want query of data insert in every end month
Built-in function for next business day?
Maverick, January 02, 2008 - 6:02 pm UTC
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,
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.
Raja, January 07, 2008 - 5:52 am UTC
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
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...
Raja, January 08, 2008 - 10:14 am UTC
Thanks a lot Tom...
business days between
Alan, March 04, 2009 - 6:42 am UTC
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
Jay Khanpara, December 10, 2009 - 3:24 am UTC
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
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
Stephan Uzzell, February 16, 2010 - 10:14 am UTC
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!
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
Sheetal, March 18, 2010 - 3:57 am UTC
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;
March 18, 2010 - 8:48 am UTC
no creates
no inserts
no look
Individual Dates from range
Sheetal, March 19, 2010 - 2:53 am UTC
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
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'),useridvery 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
Sheetal, March 20, 2010 - 5:49 am UTC
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.
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!
A reader, March 20, 2010 - 5:00 pm UTC
Why all the complex rownum and pl/sql fuzz?
Niels Jessen, March 25, 2010 - 10:05 am UTC
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;
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
Bob Thiele, May 25, 2010 - 12:47 pm UTC
--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
StewS, October 26, 2010 - 9:39 am UTC
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!
StewS, October 26, 2010 - 9:43 am UTC
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
A reader, November 11, 2010 - 6:32 am UTC
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.
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
leo, January 02, 2011 - 4:27 am UTC
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
Jeff, April 22, 2011 - 2:23 pm UTC
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.
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
Jeff, April 25, 2011 - 11:54 am UTC
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!
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
Tom H., May 05, 2011 - 3:06 pm UTC
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
Pedro, June 02, 2011 - 2:31 pm UTC
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
Joao Camilo, June 27, 2011 - 4:52 pm UTC
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.
Joao Camilo, June 27, 2011 - 5:10 pm UTC
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
Rizwan, July 01, 2011 - 1:11 am UTC
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
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
Max, November 19, 2012 - 7:25 am UTC
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
Paulo Terça, April 18, 2013 - 7:01 pm UTC
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
A reader, May 11, 2013 - 12:19 pm UTC
Query to get previous two business days.
Rajeshwaran, October 03, 2013 - 7:52 am UTC
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
khamis al-mughrabi, May 24, 2015 - 3:23 pm UTC
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
Alex, October 27, 2017 - 4:21 pm UTC
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;
/
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
vinodreddy, May 10, 2018 - 8:29 pm UTC
sir please wirt in java code
May 11, 2018 - 5:50 am UTC
How about this ?
BC, May 11, 2018 - 7:03 pm UTC
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
May 12, 2018 - 1:28 am UTC
nice input