9 Replies Latest reply on May 26, 2014 2:04 PM by Frank Kulash

    PL/SQL function to calculate non-working days

    Tiny Penguin

      Hello,

       

      I have the following pl/sql function that generates a count of working days between two dates selected (i.e. to exclude weekends). However, I also need it to exclude specific holidays - Christmas day etc. Dates of these holidays are stored in a table in our database (11GR2) called 'HOLIDAYS'.

       

      How can I integrate holiday dates held in the HOLIDAYS table into the following query to exclude these dates as well? I know how to write a separate pl/sql funtion to get a count of holidays between two dates using SELECT...INTO but I can't work out how to bring them together into one query.

       

      Could anyone show me how with the pl/sql / dates below please?

       

      Sample HOLIDAYS table below.

       

      Thanks!

       

      TP

       

      create or replace
      function WORKING_DAYS (pi_start_date in date, pi_end_date in date) return integer
      
      is
      
      v_start_date date :=pi_start_date;
      v_end_date date:=pi_end_date;
      v_count integer:=0;
      
      begin
      
      while v_start_date <= v_end_date
            loop
                              if to_char(v_start_date,'D') not in ('6','7')                    
                              then
                              v_count := v_count+1;                       
                              end if;
                             
                      v_start_date:=v_start_date+1;
                     
            end loop;
           
      return v_count;
      
      end;
      
      

       

      (select '10-Apr-2013' as NWD from dual union all
      select '06-May-2013' from dual union all
      select '27-May-2013' from dual union all
      select '26-Aug-2013' from dual union all
      select '26-Dec-2013' from dual union all
      select '25-Dec-2013' from dual union all
      select '01-Jan-2014' from dual union all
      select '18-Apr-2014' from dual union all
      select '21-Apr-2014' from dual union all
      select '05-May-2014' from dual union all
      select '26-May-2014' from dual union all
      select '25-Aug-2014' from dual union all
      select '25-Dec-2014' from dual union all
      select '26-Dec-2014' from dual) HOLIDAYS
      
        • 2. Re: PL/SQL function to calculate non-working days
          BrunoVroman

          Hello,

           

          first I really dislike the way you count working days one by one. I would compute a smarter function based on the following:

          For every 7 consecutive days you have 5 working days and 2 weekend days

          So if you want to count the number of days between D1 and D2, the number of "full weeks" * 5 is a first component. And the rest depends of the modulo( D2 - D1, 7) and of the starting day (Mon, Tue, ..., Sun?)

          I leave you this homework ;-)

           

          Then the additional holidays: I assume that you have a table HOLIDAY( the_date, some_text )

          You just have to count how many days are in the interval (D1, D2) -I assume that there are no "hour min sec" in all those dates- and are NOT a Saturday or a Sunday (because those days have already been subtracted) and subtract this from the previous count.

           

          SELECT COUNT(*)

             FROM holidays

             WHERE the_date BETWEEN d1 AND d2

               AND SUBSTR( TO_CHAR( the_date, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH' ), 1 ) != 'S'

           

          Best regards,

           

          Bruno Vroman.

          • 3. Re: PL/SQL function to calculate non-working days
            BrunoVroman

            Hello,

             

            I had given a function doing what you ask in August 2012 but I've just realized that the document in MOSC was "damaged" (all spaces have been replaced by "Â ") and I can't edit the document. So I've posted it again: Number of working dates between two dates

             

            HTH,

             

            Bruno.

            • 4. Re: PL/SQL function to calculate non-working days
              Frank Kulash

              Hi,

               

              Instead of making a table that has one row for every holiday, create a table that has one row for every day, whether it's a holiday or not.

              Include columns for

              • Date
              • Day type (maybe 'H'=Holiday, 'W'=Weekend, 'B'=Business day)
              • Cumulative business days since the earliest date in the table

              You may want other columns as well.

               

              To find how many business days are between 2 dates, join 2 copies of this table, and subtract the cumulative business days.

               

              You might create the table, and fill in some of the columns, like this:

               

              CREATE TABLE calendar
              (   dt      DATE          PRIMARY KEY
                           CONSTRAINT  cal_dt_ck
                                                   CHECK  (dt = TRUNC (dt))
              ,   day_type    VARCHAR2 (1)  NOT NULL
                        CONSTRAINT  cal_day_type_ck
                                                   CHECK  (day_type IN ('B', 'H', 'W'))
              ,   bus_day_cnt    NUMBER (5)    NOT NULL
              ,   fiscal_yr      NUMBER (4)    NOT NULL
              ,   holiday_name   VARCHAR2 (20)
              );

               

              PROMPT =====  Populate calendar table (except for holidays  =====
              --      bus_day_cnt depends on holidays, so it will be populated later

               

              INSERT INTO  calendar (dt, day_type, bus_day_cnt, fiscal_yr)
              WITH  got_dt  AS
              (
                  SELECT  DATE '2011-01-01' + LEVEL - 1   AS dt
                  FROM    dual
                  CONNECT BY  LEVEL <= DATE '2041-01-01'  -- This date is NOT included, but days before are
                                     - DATE '2011-01-01'
              )
              SELECT  dt
              ,       CASE
                          WHEN  TO_CHAR (dt, 'DY')  IN ('SAT', 'SUN')
                          THEN  'W'
                          ELSE  'B'
                      END                            AS day_type
              ,       0                              AS bus_day_type
              ,       EXTRACT (YEAR FROM dt) + CASE
                                                   WHEN  TO_CHAR (dt, 'MM') >= '07'
                                                   THEN  1
                                                   ELSE  0
                                               END   AS fiscal_yr
              FROM    got_dt
              ;

               

              --  Reality checks

              SELECT  COUNT (*)
              FROM    calendar;

               

               

              SELECT    *
              FROM      calendar
              WHERE     dt  BETWEEN  TRUNC (SYSDATE)
                            AND      SYSDATE + 7
              ORDER BY  dt;

              You can then update the table for the annual holidays.  Below are just 2 examples; you'll need to repeat this for all the holidays you observe.

              PROMPT  =====  Populate Memorial Day  =====
              --  Last Monday in May

               

              UPDATE  calendar
              SET     day_type     = 'H'
              ,       holiday_name = 'Memorial Day'
              WHERE   TO_CHAR (dt, 'fmMONTH DAY') = 'MAY MONDAY'
              AND     EXTRACT (DAY FROM dt)       >= 25
              ;

               

              SELECT    *
              FROM      calendar
              WHERE     holiday_name = 'Memorial Day'
              ORDER BY  dt;

               


              PROMPT  =====  Populate Independence Day  =====
              --  July 4 (or closest non-weekend day)

               

              UPDATE  calendar
              SET     day_type     = 'H'
              ,       holiday_name = 'Independence Day'
              WHERE   day_type  = 'B'
              AND     (   TO_CHAR (dt, 'fmMONTH DD')       = 'JULY 4'
                      OR  TO_CHAR (dt, 'fmMONTH DD DAY')  IN ( 'JULY 3 FRIDAY'
                                                             , 'JULY 5 MONDAY'
                                                             )
                      )
              ;

               

              SELECT    *
              FROM      calendar
              WHERE     holiday_name  = 'Independence Day'
              ORDER BY  dt;


              PROMPT  =====  Populate the other holidays  =====

              -- ...

              Note that this is not the most efficient way to populate this table.  Efficiency is not important here.  You will populate this table perhaps once every 5 or 10 years, and even the inefficient code shown here runs in just a few seconds,  Accuracy and clarity are much more important than speed.

               

              Also, because this will be run so infrequently, I haven't been very cautious about things like CHECK constraints or NLS dependency.  (You might not want to bother with even the CHECK constraints I did post.)   Be very careful when modifying this table, and if you do make any changes, test them very thoroughly,

               

              After all of the holidays are populated, populate the bus_day_cnt column like this:

               

               

              PROMPT  =====  Populate bus_day_cnt  =====

              MERGE  INTO  calendar  dst
              USING  (
                         SELECT  dt
                         ,       COUNT(CASE WHEN day_type = 'B' THEN 1 END)
                                     OVER (ORDER BY  dt)  AS bus_day_cnt
                         FROM    calendar
                     )               src
              ON     ( dst.dt           = src.dt )
              WHEN MATCHED THEN UPDATE
              SET      dst.bus_day_cnt  = src.bus_day_cnt
              ;


              -- Do some checks to verify the results, e.g.

               

              SELECT   *
              FROM      calendar
              WHERE     dt  BETWEEN TRUNC (SYSDATE)
                            AND     SYSDATE + 50
              ORDER BY  dt
              ;

              If you ever change holidays, make all the changes in the day_type column, and then run the MERGE statement above to re-compute bus_day_cnt.

              You can define "holidays" very loosely.  For example, say your business was closed for a week last winter because of weather.  You could mark those individual dates as day_type='H', and re-compute bus_day_cnt.  Say you worked a couple of Saturdays this spring to help make up for that; you could mark those individual dates as day_type='B'.

               

              Here's how you might use this calendar table in a query.

              Say you have a table like this:

               

              CREATE TABLE   sample_data    AS
                  SELECT  DATE '2014-05-23' AS order_dt, DATE '2014-05-26' AS ship_dt  FROM dual  UNION ALL
                  SELECT  DATE '2014-05-23',             DATE '2014-05-27'             FROM dual  UNION ALL
                  SELECT  DATE '2015-05-01',             DATE '2015-08-01'             FROM dual
              ;

              To find how many business days elapsed between the order_dt and ship_dt, you can do this:

               

              SELECT    d.order_dt
              ,         d.ship_dt
              ,         s.bus_day_cnt - o.bus_day_cnt  AS days_to_ship
              FROM      sample_data  d
              JOIN      calendar     o  ON  o.dt  = d.order_dt
              JOIN      calendar     s  ON  s.dt  = d.ship_dt
              ORDER BY  d.order_dt
              ,         d.ship_dt
              ;

              Output:

               

               

              ORDER_DT    SHIP_DT     DAYS_TO_SHIP

              ----------- ----------- ------------

              23-May-2014 26-May-2014            0

              23-May-2014 27-May-2014            1

              01-May-2015 01-Aug-2015           63

              • 5. Re: PL/SQL function to calculate non-working days
                theoa

                I would keep it simple.

                Suppose you have a holiday table as defined below, then use a query like this:

                 

                with holidays as

                ( select to_date('20140101','yyyymmdd') as holiday, 'Newyear''s Day' as description from dual

                  union all

                  select to_date('20141225','yyyymmdd') as holiday, 'Christmas Day' as description from dual

                  union all

                  select to_date('20141226','yyyymmdd') as holiday, 'Boxing Day' as description from dual

                )

                select count(*) from

                (

                select the_day

                from ( select :p_start_date + level - 1 as the_day

                       from   dual

                       connect by :p_start_date + level - 1 <= :p_end_date

                     )

                where to_char(the_day, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') not in ('SAT', 'SUN')

                minus

                select holiday from holidays

                )

                1 person found this helpful
                • 6. Re: PL/SQL function to calculate non-working days
                  Tiny Penguin

                  Morning All,

                   

                  Wow, thanks for all your replies, some really interesting approaches here that I didn't think of.

                   

                  I totally appreciate  your suggestions looking to keep away from PL/SQL - yes, they likely definitely more efficient but I:

                   

                  1) enjoy the opportunity to try and learn a bit of PL/SQL and

                  2) Am getting more into the habit of writing modular PL/SQL that I can deploy to lots of different reports (write once, use many times kind of approach). We're now producing so many reports that it's becoming time-consuming to manage all the independently written queries/datasets in circulation.

                   

                  Frank, brill and well-explained suggestion as always. I initially considered this approach (building just a table of weekends and bank holidays for exclusion) but favoured PL/SQL for the reasons above (mainly reason 1).

                   

                  Theoa, I didn't think of using Minus at all! I've used CONNECT BY syntax to calculate weekdays like that before too, a really convenient way of calculating it with pure SQL. I've found it slows down a bit when applied over large ranges though.

                   

                  Hoek, Thanks, the Bible of SQL! Is there a question Tom hasn't answered?? If only the bible was formatted a little nicer....

                   

                  Bruno, yes, appreciate that loops will never be the most efficient way of calculating data out - I think that's the old Excel VBA part of my mind doing the thinking. Thanks for the example, but unfortunately I can't access it - the link takes me to a page that says 'Access Restricted'?

                   

                  I also updated my initial function with your count(*) suggestion below...though haven't got around to looking at your homework solution yet! Makes sense to calculate based on a single range rather than iteratively though. I think I was having a daft moment yesterday... the iterative function below seems to work well, doesn't seem too inefficient when used with several thousand records. Happy for further criticism if anyone would care to offer another PL/SQL approach or if I've missed anything obvious in the below.

                   

                  Thanks all, really good of you to help.

                   

                  create or replace
                  function WORKING_DAYS (pi_start_date in date, pi_end_date in date) return integer
                  
                  is
                  
                  v_start_date date :=pi_start_date;
                  v_end_date date:=pi_end_date;
                  v_count_hd integer:=0;
                  v_count_wd integer:=0;
                  
                  begin
                  
                  select count(nwd.nwd_date) into v_count_hd from holidays nwd
                  where nwd.nwd_date between trunc(v_start_date) and trunc(v_end_date)
                  and to_char(nwd_date,'D') not in ('6','7');
                  
                  
                  while trunc(v_start_date) <= trunc(v_end_date)
                      
                        loop
                                          if to_char(v_start_date,'D') not in ('6','7')                    
                                              then
                                              v_count_wd := v_count_wd+1;
                                          end if;
                                            
                        v_start_date:=v_start_date+1;
                                 
                        end loop;
                     
                  return
                  v_count_wd - v_count_hd;
                  
                  end;
                  
                  • 7. Re: PL/SQL function to calculate non-working days
                    theoa

                    You are right, the sql solution with CONNECT BY slows down indeed for large data sets.

                    Counting all weekdays for a thousand years takes about 1.2 seconds on my database.

                    • 8. Re: PL/SQL function to calculate non-working days
                      BrunoVroman

                      Hello,

                       

                      the link is on MOSC, not OTN... Here is a copy:


                      This function computes the number of days between two dates, skipping weekends as well as public holidays (if asked and if public holidays are stored in a table)

                      I give an example of "public_holiday" table with sample data, but the users have to make sure that their table contains the relevant data (all public holidays within the maximum range of use of the function)

                       

                      CREATE TABLE public_holiday( calendar_day DATE, text VARCHAR2(30) );

                       

                      CREATE OR REPLACE  FUNCTION nb_days ( p_date_from IN DATE

                                                          , p_date_to   IN DATE DEFAULT TRUNC( sysdate )

                                                          , p_public_holidays IN CHAR DEFAULT 'Y'

                                                          ) RETURN NUMBER

                      AUTHID DEFINER

                      AS

                        /*********************************************************************/

                        /* Author     : Bruno Vroman                                         */

                        /* Creation   : 23-AUG-2012                                          */

                        /* Last update: 23-AUG-2012                                          */

                        /* Object: to compute number of days between 2 dates, excluding      */

                        /*         Saturday/Sunday, as well as "public holidays" if the      */

                        /*         argument 'p_public_holidays' = 'Y'                        */

                        /* Assumption: p_date_from <= p_date_to                              */

                        /*            "hour min sec" component ignored (we simply count days)*/

                        /* First step:                                                       */

                        /*   count the calendar days between the 2 days                      */

                        /*   removing 2 days for each "full week" and 0 or 1 extra day       */

                        /*   depending of a "complex" condition mixing day of week of first  */

                        /*   day and number of days remaining when full weeks are removed    */

                        /*   (set up once for all, for example if there are 3 days and the   */

                        /*   first day is a Thursday, there is 1 "Sat/Sun" to subtract)      */

                        /* Second step: if 'p_public_holidays' = 'Y': within the remaining   */

                        /*   days, do not count the public holidays.                         */

                        /*  public holidays are defined in a table "public_holiday"          */

                        /*  remark: there might be public holidays defined on Sat/Sun.       */

                        /*********************************************************************/

                        l_result NUMBER;

                        l_from  DATE;

                        l_to    DATE;

                        l_case  CHAR(4);

                      BEGIN

                        l_from := TRUNC( p_date_from );

                        l_to := TRUNC( p_date_to );

                        l_case := TO_CHAR( l_from, 'Dy', 'NLS_DATE_LANGUAGE=English' ) ||

                                  TO_CHAR( MOD( l_to - l_from + 1, 7 ) );

                        l_result := l_to - l_from + 1

                                    - TRUNC( ( l_to - l_from + 1 ) / 7 ) * 2

                                    - CASE

                                        WHEN l_case IN ( 'Mon6', 'Tue5', 'Wed4', 'Thu3', 'Fri2'

                                                       , 'Sat1', 'Sun1', 'Sun2', 'Sun3', 'Sun4'

                                                       , 'Sun5', 'Sun6'

                                                       )

                                          THEN 1

                                        WHEN l_case IN ( 'Tue6', 'Wed5', 'Wed6', 'Thu4', 'Thu5'

                                                       , 'Thu6', 'Fri3', 'Fri4', 'Fri5', 'Fri6'

                                                       , 'Sat2', 'Sat3', 'Sat4', 'Sat5', 'Sat6'

                                                       )

                                          THEN 2

                                        ELSE 0

                                      END

                                    ;

                        IF UPPER ( p_public_holidays ) = 'Y'

                        THEN

                          SELECT l_result - COUNT(*)

                            INTO l_result

                            FROM public_holiday p

                            WHERE p.calendar_day >= l_from

                              AND p.calendar_day <= l_to

                              AND SUBSTR( TO_CHAR( p.calendar_day

                                                 , 'Dy'

                                                 , 'NLS_DATE_LANGUAGE=English'

                                                 )

                                         , 1, 1 ) != 'S'

                          ;

                        END IF;

                        RETURN l_result;

                      EXCEPTION

                        WHEN OTHERS

                        THEN

                          DBMS_OUTPUT.PUT_LINE( 'fct nb_days( ' ||

                                                TO_CHAR( p_date_from, 'DD-MON-YYYY' ) || ', ' ||

                                                TO_CHAR( p_date_to, 'DD-MON-YYYY' ) || ', ' ||

                                                p_public_holidays  || ' ) : ' || SQLERRM

                                              );

                          RAISE;

                      END nb_days;

                      /

                       

                      REM

                      REM example:

                      REM A) populate "public_holidays"

                      REM

                      TRUNCATE TABLE public_holiday;

                      INSERT INTO public_holiday VALUES ( DATE '2012-01-01', 'New Year (a Sunday)' );

                      INSERT INTO public_holiday VALUES ( DATE '2012-01-03', 'for the example' );

                      REM (insert other days...)

                      COMMIT;

                       

                      REM B) call the function for a few couples of dates

                      REM   nb1: remove only Sat/Sun

                      REM   nb2: remove also public holidays

                      REM

                      ALTER SESSION SET nls_date_format='Dy DD-MON-YY';

                      WITH some_dates AS

                      ( SELECT DATE '2011-12-29' d FROM dual

                        UNION ALL SELECT DATE '2012-01-08' FROM dual

                        UNION ALL SELECT DATE '2012-01-10' FROM dual

                      )

                      SELECT d1.d "FROM"

                           , d2.d "TO"

                           , nb_days( d1.d, d2.d, 'N' ) nb1

                           , nb_days( d1.d, d2.d, 'Y' ) nb2

                        FROM some_dates d1

                           , some_dates d2

                        WHERE d1.d <= d2.d

                        ORDER BY nb1, nb2, d1.d

                      ;

                      FROM          TO             NB1  NB2

                      ------------- ------------- ---- ----

                      Sun 08-JAN-12 Sun 08-JAN-12    0    0

                      Thu 29-DEC-11 Thu 29-DEC-11    1    1

                      Tue 10-JAN-12 Tue 10-JAN-12    1    1

                      Sun 08-JAN-12 Tue 10-JAN-12    2    2

                      Thu 29-DEC-11 Sun 08-JAN-12    7    6

                      Thu 29-DEC-11 Tue 10-JAN-12    9    8

                       

                      Hope this might be useful, but note that this code has not been thoroughly tested, so check and test before trusting it (in case of any issue, please post a comment)

                       

                      Bruno Vroman.

                       

                      Best regards,

                       

                      Bruno

                      • 9. Re: PL/SQL function to calculate non-working days
                        Frank Kulash

                        Hi,

                         

                        Sometimes the convenience of calling a PL/SQL function justifies the inefficiency.  When that's the case, I would use a function like this:

                         

                        CREATE OR REPLACE FUNCTION  business_days

                        (   dt_1  IN  DATE

                        ,   dt_2  IN  DATE  DEFAULT TRUNC (SYSDATE)

                        )

                        RETURN  PLS_INTEGER

                        IS

                            return_val  PLS_INTEGER;

                        BEGIN

                            SELECT  c2.bus_day_cnt - c1.bus_day_cnt

                            INTO    return_val

                            FROM    calendar  c1

                            JOIN    calendar  c2  ON   c1.dt  = dt_1

                                                  AND  c2.dt  = dt_2;

                         

                         

                            RETURN  return_val;

                        END   business_days;

                        /

                        or, if you prefer:

                        CREATE OR REPLACE FUNCTION  business_days

                        (   dt_1  IN  DATE

                        ,   dt_2  IN  DATE  DEFAULT TRUNC (SYSDATE)

                        )

                        RETURN  PLS_INTEGER

                        IS

                            n_1  PLS_INTEGER;

                            n_2  PLS_INTEGER;

                        BEGIN

                            SELECT  bus_day_cnt

                            INTO    n_1

                            FROM    calendar 

                            WHERE   dt  = dt_1;

                         

                         

                            SELECT  bus_day_cnt

                            INTO    n_2

                            FROM    calendar 

                            WHERE   dt  = dt_2;

                         

                         

                            RETURN  n_2 - n_1;

                        END   business_days;

                        /

                         

                        where calendar is the table I described earlier.  You only have to fetch 2 rows from a table; no loops are needed.

                        1 person found this helpful