This discussion is archived
4 Replies Latest reply: Oct 3, 2013 6:31 AM by Frank Kulash

How to Calculate End-Date for a given Startdate in business hours without holidays

Currently Being Moderated

Hello Experts,

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.

• 1. Re: How to Calculate End-Date for a given Startdate in business hours without holidays
Currently Being Moderated

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 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.

• 2. Re: How to Calculate End-Date for a given Startdate in business hours without holidays
Currently Being Moderated

Hi Chris,

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.?

• 3. Re: How to Calculate End-Date for a given Startdate in business hours without holidays
Currently Being Moderated

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.

• 4. Re: How to Calculate End-Date for a given Startdate in business hours without holidays
Currently Being Moderated

Hi,

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

partial holidays).

CREATE TABLE  work_calendar

(   dt  DATE       PRIMARY KEY

CONSTRAINT  work_calendar_dt

CHECK (dt = TRUNC (dt))

,   day_type       VARCHAR2 (8)  NOT NULL

CONSTRAINT  work_calendar_day_type

CHECK ( day_type IN ( 'HOLIDAY'

, 'WEEKEND'

, 'WORK DAY'

)

)

,   start_time     DATE

,   end_time       DATE

,   work_hours     NUMBER

,   work_hours_since_1970     -- or some point earlier than you'll ever need

NUMBER

,   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.

Legend

• Correct Answers - 10 points