Calculate business days in Oracle SQL(no functions or procedure)
Asked Answered
P

12

13

I am trying to calculate business days between two dates in Oracle select. I got to the point when my calculation gives most results correct for given dates (I compare it with NETWORKDAYS in excel) but sometimes it varies from 2 days to -2 days - and I don't know why...

Here's my code:

SELECT
((to_char(CompleteDate,'J') - to_char(InstallDate,'J'))+1) - (((to_char(CompleteDate,'WW')+ (52 * ((to_char(CompleteDate,'YYYY') - to_char(InstallDate,'YYYY'))))) - to_char(InstallDate,'WW'))*2) as BusinessDays
FROM TABLE

Thanks!

Prosser answered 15/2, 2013 at 15:51 Comment(1)
Add your solution as an answer so you could "accept" it (it's better for this site to have questions with accepted answers)Resplendent
P
35

The solution, finally:

SELECT OrderNumber, InstallDate, CompleteDate,
  (TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 - 
  ((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
  (CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
  (CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) as BusinessDays
FROM Orders
ORDER BY OrderNumber;

Thanks for all your responses !

Prosser answered 17/2, 2013 at 15:5 Comment(3)
I think you're taking a good approach, trying to use simple SQL functions instead of a function or a date table. But the results are not always accurate. For example, if you use the dates 2012-02-15 and 2012-02-18, a Friday and Monday, the result is 3.Every
Well actually it does work correctly for all dates :) You just used wrong dates :) 2012-02-15 is Wednesday, and 2012-02-18 is Saturday. I guess you wanted to calculate same days but in 2013 - it gives 2 days (again correct!).Prosser
I had to add in two extra cases for this to work for me. Note: according to my oracle environment, a week starts on a monday. Not sure if this is universal to oracle or not.Phelgon
L
9

I took into account all the different approaches discussed above and came up with a simple query that gives us the number of working days in each month of the year between two dates:

WITH test_data AS ( SELECT TO_DATE('01-JAN-14') AS start_date, TO_DATE('31-DEC-14') AS end_date
FROM dual ), all_dates AS (
SELECT td.start_date, td.end_date, td.start_date + LEVEL-1 as week_day FROM test_data td CONNECT BY td.start_date + LEVEL-1 <= td.end_date) SELECT TO_CHAR(week_day, 'MON'), COUNT(*)
FROM all_dates WHERE to_char(week_day, 'dy', 'nls_date_language=AMERICAN') NOT IN ('sun' , 'sat') GROUP BY TO_CHAR(week_day, 'MON');

Please feel free to modify the query as needed.

Longhorn answered 14/11, 2013 at 16:24 Comment(1)
Thank you very much for this query. I modified it to reduce the number of businessdays by our company holidays and am now able to use this calculation alone.Shively
R
2

Try this:

with holidays as 
(
select d from (
select minDate + level -1 d
 from (select min(submitDate) minDate, max (completeDate) maxDate
 from t)
 connect by level <= maxDate - mindate + 1) 
 where to_char(d, 'dy', 'nls_date_language=AMERICAN') not in ('sun' , 'sat')
)
select t.OrderNo, t.submitDate, t.completeDate, count(*) businessDays
from t join holidays h on h.d between t.submitDate and t.completeDate
group by t.OrderNo, t.submitDate, t.completeDate
order by orderno

Here is a sqlfiddle demo

Resplendent answered 17/2, 2013 at 6:20 Comment(0)
S
1

I changed my example to more readable and to return count of bus. days between. I do not know why you need 'J'- Julian format. All it takes is start/Install and end/Complete dates. You will get correct number of days between 2 dates using this. Replace my dates with yours, add NLS if needed...:

 SELECT Count(*) BusDaysBtwn
  FROM
  (
  SELECT TO_DATE('2013-02-18', 'YYYY-MM-DD') + LEVEL-1 InstallDate  -- MON or any other day 
       , TO_DATE('2013-02-25', 'YYYY-MM-DD') CompleteDate           -- MON or any other day
       , TO_CHAR(TO_DATE('2013-02-18', 'YYYY-MM-DD') + LEVEL-1, 'DY') InstallDay   -- day of week
    FROM dual 
  CONNECT BY LEVEL <= (TO_DATE('2013-02-25', 'YYYY-MM-DD') - TO_DATE('2013-02-18', 'YYYY-MM-DD')) -- end_date - start_date 
   )
   WHERE InstallDay NOT IN ('SAT', 'SUN')
  /

  SQL> 5
Shallot answered 15/2, 2013 at 16:17 Comment(8)
Thanks - I was able to calculate it with my methodProsser
@Prosser - share it with us.Shallot
It is shared as an answer to my original postProsser
@Prosser - I tried using your method and got 2 in return... Maybe I'm not understanding it or smth... Here's the query-please copy-paste if interested: SELECT TRUNC((CompleteDate),'J') CompleteDate , TRUNC((InstallDate),'J') InstallDate , ((((TRUNC((CompleteDate),'D'))-(TRUNC((InstallDate),'D')))/7)*2) "wks?" FROM ( SELECT TO_DATE('2013-02-16', 'YYYY-MM-DD') InstallDate , TO_DATE('2013-02-23', 'YYYY-MM-DD') CompleteDate FROM dual ) /Shallot
I don't know what you're trying to calculate with your query :) If I paste your dates in my query I get 5 days SELECT (TO_CHAR(TO_DATE('2013-02-23','YYYY-MM-DD'),'J') - TO_CHAR(TO_DATE('2013-02-16','YYYY-MM-DD'),'J'))+1 - ((((TRUNC(TO_DATE('2013-02-23','YYYY-MM-DD'),'D'))-(TRUNC(TO_DATE('2013-02-16','YYYY-MM-DD'),'D')))/7)*2) - (CASE WHEN TO_CHAR(TO_DATE('2013-02-16','YYYY-MM-DD'),'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -(CASE WHEN TO_CHAR(TO_DATE('2013-02-23','YYYY-MM-DD'),'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) as BusinessDAys FROM DUALProsser
@Prosser - I cocopied yours and got 6. It is OK as long as it works for you. I'd simplify queries. Thank you.Shallot
If your week starts with Sunday you may be getting different results. I set the nls territory for Poland. I'm pretty new with Oracle so I'm open for any suggestions to simplify:)Prosser
@Prosser - I simplified my example. It does not matter when your week starts/ends in my example. Copy/paste to see results.Shallot
F
1

I see that marked final solution is not correct always. Suppose, InstallDate is 1st of the month (if falls on Saturday) and CompleteDate is 16th of the month (if falls on Sunday)

In that case, actual Business Days is 10 but the marked query result will give the answer as 12. So, we have to treat this type of cases too, which I used

(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SAT' AND TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SUN' THEN 2 ELSE 0 END

line to handle it.

SELECT OrderNumber, InstallDate, CompleteDate,
(TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 - 
((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
(CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) -
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SAT' AND TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SUN' THEN 2 ELSE 0 END)as BusinessDays
FROM Orders
ORDER BY OrderNumber;
Fatma answered 21/4, 2015 at 17:10 Comment(1)
As a late update: This is incorrect, your extra line breaks the code. The final solution does correctly handle the situation given, because of the behaviour of TRUNC(<date>,'D') as rounding to the first day of the working week (although this may depend on what your database considers "the first day of the week").Taejon
S
0

The accepted solution is quite close but seems wrong in some cases (e.g., 2/1/2015 through 2-28/2015 or 5/1/2015 through 5/31/2015). Here's a refined version...

  end_date-begin_date+1 /* total days */
  - TRUNC(2*(end_date-begin_date+1)/7) /* weekend days in whole weeks */
  - (CASE
      WHEN TO_CHAR(begin_date,'D') = 1 AND REMAINDER(end_date-begin_date+1,7) > 0 THEN 1
      WHEN TO_CHAR(begin_date,'D') = 8 - REMAINDER(end_date-begin_date+1,7) THEN 1
      WHEN TO_CHAR(begin_date,'D') > 8 - REMAINDER(end_date-begin_date+1,7) THEN 2
      ELSE 0
    END) /* weekend days in partial week */
  AS business_days

The part that handles the multiples of 7 (whole weeks) is good. But, when considering the partial week portion, it depends on both the day-of-week offset and the number of days in the partial portion, according to the following matrix...

   654321
1N 111111
2M 100000
3T 210000
4W 221000
5R 222100
6F 222210
7S 222221
Switch answered 23/3, 2015 at 13:24 Comment(0)
I
0

To just remove sundays and saturdays you can use this

SELECT Base_DateDiff
     - (floor((Base_DateDiff + 0 + Start_WeekDay) / 7))
     - (floor((Base_DateDiff + 1 + Start_WeekDay) / 7))
FROM   (SELECT 1 + TRUNC(InstallDate) - TRUNC(InstallDate, 'IW') Start_WeekDay
             , CompleteDate - InstallDate + 1 Base_DateDiff
        FROM TABLE) a

Base_DateDiff counts the number of days between the two dates
(floor((Base_DateDiff + 0 + Start_WeekDay) / 7)) counts the number of sundays
(floor((Base_DateDiff + 1 + Start_WeekDay) / 7)) counts the number of saturdays

1 + TRUNC(InstallDate) - TRUNC(InstallDate, 'IW') get 1 for mondays to 7 for sunday

Issus answered 22/2, 2017 at 17:13 Comment(0)
E
0

This query can be used to go backward N days from the given date (business days only)

For example, go backward 15 days from 2017-05-17:

select date_point, closest_saturday - (15 - offset + floor((15 - offset) / 6) * 2) from(
   select date_point,
          closest_saturday,
          (case
             when weekday_num > 1 then
              weekday_num - 2
             else
              0
           end) offset
    from (
           select  to_date('2017-05-17', 'yyyy-mm-dd') date_point,
                   to_date('2017-05-17', 'yyyy-mm-dd') - to_char(to_date('2017-05-17', 'yyyy-mm-dd'), 'D') closest_saturday,
                   to_char(to_date('2017-05-17', 'yyyy-mm-dd'), 'D') weekday_num
           from dual
          ))

Some brief explanation: suppose we want to go backward N days from a given date - Find the closest Saturday that is less than or equal to the given date. - From the closest Saturday, go back ward (N - offset) days. offset is the number of business days between the closest Saturday and the given date (excluding the given date).

*To go back M days from a Saturday (business days only), use this formula DateOfMonthOfTheSaturday - [M + Floor(M / 6) * 2]

Eparch answered 17/5, 2017 at 20:23 Comment(0)
A
-1

Here is a function that is fast and flexible. You can count any weekday in a date range.

CREATE OR REPLACE FUNCTION wfportal.cx_count_specific_weekdays( p_week_days   VARCHAR2 DEFAULT 'MON,TUE,WED,THU,FRI'
                                                              , p_start_date  DATE
                                                              , p_end_date    DATE)
RETURN NUMBER 
IS

 /***************************************************************************************************************
  *
  * FUNCTION DESCRIPTION:
  *
  *   This function calculates the total required week days in a date range.
  *
  * PARAMETERS:
  *
  *   p_week_days   VARCHAR2  The week days that need to be counted, comma seperated e.g. MON,TUE,WED,THU,FRU,SAT,SUN 
  *   p_start_date  DATE      The start date
  *   p_end_date    DATE      The end date
  *
  * CHANGE history
  *
  * No.  Date         Changed by       Change Description
  * ---- -----------  -------------    -------------------------------------------------------------------------
  *    0 07-May-2013  yourname         Created
  *
  ***************************************************************************************************************/

   v_date_end_first_date_range    DATE;
   v_date_start_last_date_range   DATE;
   v_total_days_in_the_weeks      NUMBER;
   v_total_days_first_date_range  NUMBER;
   v_total_days_last_date_range   NUMBER;
   v_output                       NUMBER;

   v_error_text                   CX_ERROR_CODES.ERROR_MESSAGE%TYPE;

   --Count the required days in a specific date ranges by using a list of all the weekdays in that range.
   CURSOR c_total_days ( v_start_date DATE
                       , v_end_date   DATE ) IS
     SELECT COUNT(*) total_days
     FROM ( SELECT ( v_start_date + level - 1) days
            FROM dual
            CONNECT BY LEVEL <= ( v_end_date - v_start_date ) + 1
           )
     WHERE INSTR( ',' || p_week_days || ',', ',' || TO_CHAR( days, 'DY', 'NLS_DATE_LANGUAGE=english') || ',', 1 ) > 0
   ;

   --Calculate the first and last date range by retrieving the first Sunday after the start date and the last Monday before the end date. 
   --Calculate the total amount of weeks in between and multiply that with the total required days.
   CURSOR c_calculate_new_dates ( v_start_date DATE
                                , v_end_date   DATE ) IS
     SELECT date_end_first_date_range
     ,      date_start_last_date_range
     ,      ( 
              (
                ( date_start_last_date_range - ( date_end_first_date_range + 1 ) )
              ) / 7 
            ) * total_required_days   total_days_in_the_weeks  --The total amount of required days 
     FROM ( SELECT v_start_date + DECODE( TO_CHAR( v_start_date, 'DY', 'NLS_DATE_LANGUAGE=english')
                                        , 'MON', 6
                                        , 'TUE', 5
                                        , 'WED', 4
                                        , 'THU', 3
                                        , 'FRI', 2
                                        , 'SAT', 1
                                        , 'SUN', 0
                                        , 0 )   date_end_first_date_range
            ,      v_end_date - DECODE( TO_CHAR( v_end_date, 'DY', 'NLS_DATE_LANGUAGE=english')
                                      , 'MON', 0
                                      , 'TUE', 1
                                      , 'WED', 2
                                      , 'THU', 3
                                      , 'FRI', 4
                                      , 'SAT', 5
                                      , 'SUN', 6
                                      , 0 )  date_start_last_date_range
            ,      REGEXP_COUNT( p_week_days, ',' ) + 1  total_required_days  --Count the commas + 1 to get the total required weekdays
            FROM dual 
     )
     ;

BEGIN

  --Verify that the start date is before the end date
  IF p_start_date < p_end_date THEN

    --Get the new calculated days.
    OPEN c_calculate_new_dates( p_start_date, p_end_date );

      FETCH c_calculate_new_dates INTO  v_date_end_first_date_range
                                      , v_date_start_last_date_range
                                      , v_total_days_in_the_weeks;

    CLOSE c_calculate_new_dates;

    --Calculate the days in the first date range
    OPEN c_total_days( p_start_date, v_date_end_first_date_range );
      FETCH c_total_days INTO v_total_days_first_date_range;
    CLOSE c_total_days;

    --Calculate the days in the last date range
    OPEN c_total_days( v_date_start_last_date_range, p_end_date );
      FETCH c_total_days INTO v_total_days_last_date_range;
    CLOSE c_total_days;

    --Sum the total required days
    v_output := v_total_days_first_date_range + v_total_days_last_date_range + v_total_days_in_the_weeks;

  ELSE

     v_output := 0;

  END IF;

  RETURN v_output;

  EXCEPTION

    WHEN OTHERS
    THEN

    RETURN NULL;

END cx_count_specific_weekdays;
/
Apprehensive answered 7/5, 2013 at 7:52 Comment(1)
The asker requested no functions or procedures. This is likely why your answer was down-voted.Romans
B
-1

Here you go...

  1. First check how many days you got in the holiday table, excluding weekend days.
  2. Get business days (MON to FRI) between the 2 dates and after that subtract the holiday days.

    create or replace
    FUNCTION calculate_business_days (p_start_date IN DATE, p_end_date IN DATE)
            RETURN NUMBER IS
            v_holidays     NUMBER;
            v_start_date   DATE   := TRUNC (p_start_date);
            v_end_date     DATE   := TRUNC (p_end_date);
            BEGIN
            IF v_end_date >= v_start_date
            THEN
                    SELECT COUNT (*)
                    INTO v_holidays
                    FROM holidays
                    WHERE day BETWEEN v_start_date AND v_end_date
                    AND day NOT IN (
                            SELECT hol.day 
                            FROM holidays hol 
                            WHERE MOD(TO_CHAR(hol.day, 'J'), 7) + 1 IN (6, 7)
                    );
    
            RETURN   GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0)
                 +   (  (  NEXT_DAY (v_end_date, 'MON')
                         - NEXT_DAY (v_start_date, 'MON')
                        )
                      / 7
                     )
                   * 5
                 - GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0)
                 - v_holidays;
            ELSE
                    RETURN NULL;
            END IF;
    END calculate_business_days;
    

After that you can test it out, like:

    select 
            calculate_business_days('21-AUG-2013','28-AUG-2013') as business_days 
    from dual;
Boom answered 21/8, 2013 at 14:55 Comment(1)
The asker requested no functions or procedures. This is likely why your answer was down-voted.Romans
M
-1

There is another easier way, using connect by and dual...

with t as (select to_date('30-sep-2013') end_date, trunc(sysdate) start_date from dual)select count(1) from dual, t where to_char(t.start_date  + level, 'D') not in (1,7) connect by t.start_date + level <= t.end_date;

with connect by you get all the dates from start_date till the end_date. Then you can exclude the dates you don't need and count only the needed.

Musicale answered 12/9, 2013 at 15:25 Comment(0)
T
-1

This would return business days:

(CompleteDate-InstallDate)-2*FLOOR((CompleteDate-InstallDate)/7)-
  DECODE(SIGN(TO_CHAR(CompleteDate,'D')-
    TO_CHAR(InstallDate,'D')),-1,2,0)+DECODE(TO_CHAR(CompleteDate,'D'),7,1,0)-
    DECODE(TO_CHAR(InstallDate,'D'),7,1,0) as BusinessDays,
Tallie answered 5/3, 2018 at 17:26 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.