4 Replies Latest reply on Oct 3, 2013 1:31 PM by Frank Kulash

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

    Subhadeep Roy

      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.

       

      Please help!

       

      Thanks in advance !!

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

          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.

          1 person found this helpful
          • 2. Re: How to Calculate End-Date for a given Startdate in business hours without holidays
            Subhadeep Roy

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

            Can you please help me with a sample code ?

             

            Thanks in advance !

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

              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
                Frank Kulash

                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.