I need to create a plsql function where we need to determine end date for a task. For this, input will be only start date & number of hours allocated for that task. Based on this input we need to exclude Business hours & need to include only Business hours. and in the end we need to achieve the end date after the hour addition to the start date.
Example : INPUT: 03-OCT-2013 12:00:00 PM /
Hours Allocated 30 Hrs.
Business Hours - 11 AM to 9 PM.
So time starts from or Startdate is Friday 03-OCT-2013 12:00:00 PM.
So If I want to calculate the end-date by adding 30 hours to it, it should come WednesDay 09-OCT-2013 12:00:00 PM because I excluded Weekends & considered only business hours that is 11 am to 9 pm.
I am not able to get any such guidance in Internet as most of the docs are having start & end date as input.
Thanks in advance !!
I'll give you a steer as to how I'd tackle it.
You have a business day that's 10 hours long, and a 5-day business week. You're given a number of hours to add. So you can calculate three values from that:
weeks := TRUNC(hours/50); -- number of whole weeks
days := TRUNC(MOD(hours,50)/10); -- number of days in the addition to the above
hours := MOD(hours,10); -- Number of hours in addition to the above
First, check that your start date/time falls within a business day. If it doesn't, change it to be the beginning of the next business day.
Then, add (weeks * 7).
Then add days. If the result puts you in or over the weekend, you'll need to add extra days to compensate.
Then add hours/24. If the result puts you outside the business day, you'll need to add extra hours to compensate.
That should get you started.
Thanks for your valuable input. But so far somehow I am not able to implement your first point. how to decide if the start day is not a weekend or holiday. Do I need to create a seperate table for the holidays.?
Can you please help me with a sample code ?
Thanks in advance !
You said in your original question that you'd be doing the calculation without holidays.
If you need to include provision for holidays in your calculation then yes, you'll need a table to store when the holidays are - there's no magic Oracle function that can tell whether a given day is a holiday at your place of work. You'll also have to add extra steps to ensure that the start day isn't a holiday, and to add days to compensate for any holidays that come up in the task period.
As Christ said, there's no nuilt-in Oracle function to tell whether a given DATE is a holiday or not, partially because there's so much local variation in holidays. You can write a function like that (see http://forums.oracle.com/forums/message.jspa?messageID=3351081 ), but creating a table will be simpler and more efficient.
I suggest creating a row for every date, whether it's a work day or not; that way, you can have variations in the schedule (e.g., schedule changes and
CREATE TABLE work_calendar
( dt DATE PRIMARY KEY
CHECK (dt = TRUNC (dt))
, day_type VARCHAR2 (8) NOT NULL
CHECK ( day_type IN ( 'HOLIDAY'
, 'WORK DAY'
, start_time DATE
, end_time DATE
, work_hours NUMBER
, work_hours_since_1970 -- or some point earlier than you'll ever need
, CONSTRAINT work_calendar_start_time
CHECK ( TRUNC (start_time) = dt)
, CONSTRAINT work_calendar_end_time
CHECK ( TRUNC (end_time) = dt)
This will let you do a lot of the calculations you need without a function.