Solved

date difference excluding Holidays

Posted on 2010-11-20
22
1,181 Views
Last Modified: 2012-06-22
Hi All Experts,

I have a problem.I have two dates in my datasets.First is an Activity Start date and second is activity end date.There is a field like activity description.So i have to prefix text "Exceeds 10 business days" before activity text where activity is completed in more than 10 days.

So i was using below query:

Update Activity_table set activity_text = 'Exceeds 10 business days' || activity_text
where activity_end_date - activity_start_date > 10;

now the problem is that i need to count only business days means i have to exclude the weekends and all US holidays when counting the difference between two dates.

For excluding USA holidays,what should be the approach like will i need to make another table having the dates of holidays.Plesae give a detailed answer because i am not aware how to proceed on this problem.

Regards,
Oracle rookie








0
Comment
Question by:oracle_rookie
  • 9
  • 6
  • 5
  • +1
22 Comments
 
LVL 5

Expert Comment

by:Zopilote
ID: 34180793
you need to create a function and a table with the US holidays.
check here:
http://knoworacle.wordpress.com/2009/05/13/oracle-plsql-%E2%80%93-calculating-weekdays-between-two-dates/
0
 
LVL 3

Expert Comment

by:mpaladugu
ID: 34180855
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34181417
"For excluding USA holidays,what should be the approach like will i need to make another table having the dates of holidays.Plesae give a detailed answer because i am not aware how to proceed on this problem.  " ----> YES, you are right.

something like a small holiday table "holiday_tab" given below...

create table holiday_tab
( holiday_date date
  holiday_remarks varchar2(200));

create function holiday_count ( a date, b date ) return number is
ret_var number;
begin
select count(1) into ret_var from holiday_tab where trunc(holiday_date) between trunc(a) and trunc(b);
return ret_var;
end;
/

so your query will be like :

Update Activity_table set activity_text = 'Exceeds 10 business days' || activity_text
where activity_end_date - activity_start_date > 10 + holiday_count(activity_start_date, activity_end_date) ;

Test this out and any modifications to this can be done easily.

Thanks
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 470 total points
ID: 34181467
your holiday table should have all holidays including non working days ( SAT & SUN ) if your comany works 5 days a week. If your company works 6 days a week, then this table should have all holidays and non working day ( SUN ).

create table holiday_tab
( holiday_date date,
  holiday_remarks varchar2(200));

This table can be populated by the below given INSERT for all ( SAT & SUN ) for any given year. I have put for example for 2010 year. Just replace 2010 with any year and run the insert. Additionally you need to insert all the holidays which fall on weekdays into this table manually.


insert into holiday_tab
select d , 'weekends - non working days'
from (
select to_date('01-JAN-'||'2010') + level - 1 d from dual
connect by level <= 366  )
where to_char(d,'DY') in ('SAT','SUN')
and d <= '31-DEC-' || '2010' ;

commit;
0
 

Author Comment

by:oracle_rookie
ID: 34188037
Hi Nav kum v,

Thanks a lot for a great answer and explanation.I will try this and will let you know in case i face any problem.

Regards,
Oracle Rookie
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34188463
Fine. Will await your feedback.
0
 

Author Comment

by:oracle_rookie
ID: 34193323
Hi ,

When i tried method given by you on two small table then it worked fine.I made one small table having five holidays and one small table having 20 records then it worked fine.But in my real case there are 5000000 records and there are 2000 records in the holiday table for last ten years and when i ran the query oracle got hung and after that even a simple query didn't run.Then DBA person told me that i exceeded temp memory so oracle could not commit the query.

Please suggest what should i do in this case and how much time query will take to run for so many records??

Thanks,
Oracle Rookie
0
 
LVL 3

Expert Comment

by:mpaladugu
ID: 34193426
Try to use a PL/SQL procedure, and implement bulk processing,
Pl/sql has features like Bulk collect and limit rows, by using this u can fetch limited number of rows every iteration and then write an update statement inside for-all loop, By using these techniques you can update
for example 5000 rows every iteration, this techniques will be light memory.

read this article, you can get a better idea
http://www.orafaq.com/node/1399
0
 
LVL 3

Expert Comment

by:mpaladugu
ID: 34193440
Can you send your Query.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34193445
@oracle_rookie,

What is the query which got hung ? Can you give as it is which is not working for you ?
0
 
LVL 5

Expert Comment

by:Zopilote
ID: 34193549
you need to post your query, so we can help you.
regards,
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:oracle_rookie
ID: 34193617
Hi nav_kum_v,

I have tried two methods which are as follows.This time i am writing this from my home so may be some difference in the query which i actually used but i guess this is same which i used.

first i tried to do in one step by using the function given by nav_kum_v and when it was taking too much time then i tried to do in two steps but neither worked:

method 1:

Update Activity_table set activity_text = 'Exceeds 10 business days' || activity_text
where to_date(activity_end_date,'YYYYMMDD') - to_date(activity_start_date,'YYYYMMDD') -
holiday_count(to_date(activity_start_date,'YYYYMMDD'), to_date(activity_end_date,'YYYYMMDD')) > 10  ;


method 2:

update activity_summary set activity_days = to_date(activity_end_date,'YYYYMMDD') -
to_date(activity_start_date,'YYYYMMDD') - (select count(*) from holiday_tab a, activity_summar b
where  a.holiday_date between to_date(b.activity_end_date,'YYYYMMDD') and to_date(b.activity_start_date,'YYYYMMDD'));

Update Activity_table set activity_text = 'Exceeds 10 business days' || activity_text
where activity_days > 10;

Regards,
Oracle rookie
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34193709
1) How many records are there in your table ?
2) What is the size of your table ? query dba_segments to check that.
3) what indexes are there on the tables ?

4) I am bit confused with those 2 methods which you had tried because i see 2 tables there one is "activity_summary" and other other is "activity_table". So what is the table which needs to be updated ?

Just call your update statement in a loop with batch commit and then should avoid temp tablespace & rollback segment issues. Give it a try. Probably you can test with a batch size of 10000 records for each commit.

Thanks
0
 

Author Comment

by:oracle_rookie
ID: 34193784
Sorry for that.There is only one table activity_summar.I wrongly written that activity_table.I have just two tables activity_summary and holiday_tab.In the holiday_tab table i have inserted all the weekends and holidays.

Number of records in activity_summary table is 5000000.

Please let me know how i should write syntax for batch commit.I am not aware of it and also i have to write many insert command also in this table which needs to be done in the batch commit process so please let me know how i should do this and please write a sample syntax.

I will be grateful to you.


Oracle rookie
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 470 total points
ID: 34193818
try this. I did not test this. so let me know if this does not work or requires any change. Thanks,

declare
batch_s number := 10000;
x number;

begin

loop

update activity_summary set activity_days = to_date(activity_end_date,'YYYYMMDD') - to_date(activity_start_date,'YYYYMMDD') -
holiday_count(to_date(activity_start_date,'YYYYMMDD'), to_date(activity_end_date,'YYYYMMDD'))
where activity_days is null
and rownum <= batch_s;

x := sql%rowcount;

Update Activity_table
set activity_text = 'Exceeds 10 business days' || activity_text
where activity_days > 10;

commit;

exit when x < batch_s;

end loop;

end;
/
0
 

Author Comment

by:oracle_rookie
ID: 34202766
Hi,

Query ran successfully but it took around four hours to update the whole data set.So i just had one question that when i will put this query in my complete package which was taking already time of 2 hours,will this query have any effect on the performance or it will not affect.It will just take much time otherwise there will be no more effect on the overall performance.

I am new to oracle that's why i am asking basic questions.This is my last comment and after getting response to this comment i will close this question and will give point.

I have one more query that i am writing sql loader script to upload the daily records in the database using shell scripting job scheduling.I had to maintain the 4 yr data always in the database.So i am thinking of using the below query in my package.will it work just let me know:

Truncate table activity_summary where max(activity_start_date) - min(activity_start_date) > 30000;

Should i use truncate or delete???



activity start date is a numeric field which has YYYYMMDD format so i guess 4 yrs will be equivalent to 40000.for example 20100101 - 20070101 = 30000


Regards,
Oracle rookie






0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34202822
<<<Query ran successfully but it took around four hours to update the whole data set.So i just had one question that when i will put this query in my complete package which was taking already time of 2 hours,will this query have any effect on the performance or it will not affect.It will just take much time otherwise there will be no more effect on the overall performance.

I am new to oracle that's why i am asking basic questions.This is my last comment and after getting response to this comment i will close this question and will give point. >>>>> ---> I would say TEST this for yourself as it is difficult to say exactly.

TRUNCATE table does not support where clause and hence the whole table will get truncated. I mean all the records will get deleted from the table with the TRUNCATE command.

You need to use DELETE command only. Is your table a partitioned table by date range wise ( probably year wise ) ? then it would easy to drop just that partition.

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34202836
activity start date is a numeric field which has YYYYMMDD format so i guess 4 yrs will be equivalent to 40000.for example 20100101 - 20070101 = 30000 --> Not sure why it is. You should have it as DATE so that DATE arithmetic becomes easier.

to delete the data which is before 4 years, you can use the below delete query :

DELETE from activity_summary where to_date(activity_start_date,'YYYYMMDD') < add_months(trunc(sysdate),-48);

Again probably you should try to loop through and do it in batches.

Thanks
 
0
 

Author Comment

by:oracle_rookie
ID: 34206644
Hi nav_kum_v,

You are a genius.I am impressed.

this query is working fine but is there any way by which i can make sure that database has 4 yrs data all the time.because tracking with sysdate doesn't ensure that there will be four years data always.For example if someone runs script on 31st december 2010 but the data available is till 25th Dec 2010 then it will delete any records having date before 31st December 2007 but ideally it should delete any records having date before 25th December 2007.
DELETE from activity_summary where to_date(activity_start_date,'YYYYMMDD') < add_months(trunc(sysdate),-48);
but when i try the below syntax:

DELETE from activity_summary where to_date(activity_start_date,'YYYYMMDD') < add_months(trunc(max(to_date(activity_start_date,'YYYYMMDD'))),-48);
it gives following error:
SQL Error: ORA-00934: group function is not allowed here
00934. 00000 -  "group function is not allowed here"
Is there any way to do this using the max date available in data.


Regards,
Oracle Rookie

0
 
LVL 3

Assisted Solution

by:mpaladugu
mpaladugu earned 30 total points
ID: 34206752
try this

DELETE from activity_summary where to_date(activity_start_date,'YYYYMMDD') < (select add_months(trunc(max(to_date(activity_start_date,'YYYYMMDD'))),-48) from activity_summary);
0
 
LVL 3

Expert Comment

by:mpaladugu
ID: 34206795
and u can remove trunc() function from the above query, you are not actually truncating anything.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 470 total points
ID: 34209743
@oracle_rookie

try this:  This is the same as given already. The TRUNC (..) was used to ensure that time portion does not come into picture during the data arithmetic logic which we are trying to use if activity_start_date has time portion as well in it.

If activity_start_date does not have time, then TRUNC is not required.

DELETE from activity_summary where to_date(activity_start_date,'YYYYMMDD') < (select add_months(trunc(max(to_date(activity_start_date,'YYYYMMDD'))),-48) from activity_summary);

Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This is about my first experience with programming Arduino.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now