Search

Top 60 Oracle Blogs

Recent comments

Counting business days between 2 dates

One of the most commonly hit questions on AskTom is how to count the number of work days (Mon => Fri) between a start and end date range.

This is not a particular tough problem to solve since we have easy access to the day of the week via TO_CHAR, thus simply cycling between the start and end date looking for weekdays gives us the answer


select count(*) 
from ( select rownum rnum
       from dual
       connect by level <= greatest(:start_date,:end_date) - least(:start_date,:end_date)+1 
      )
where to_char( least(:start_date,:end_date)+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' );

That solution is fine for the requirement where you have a single pair of inputs. But what if you have a table with thousands or millions of rows, each with a start/end date pair. I don’t want to be cycling through synthesized rows (even if that cycling is just on the DUAL pseudo-table) because that won’t scale, or at best will burn a hole in one of my server CPU cores! Smile

Rather than cycling through the days between a start and end pair, we can apply some arithmetic rules:

  • total number of complete weeks (days difference / 7) between the dates gives multiple of 5 work day,
  • but you need to adjust that if the start date falls later in the week than the end date,
  • if the start date falls on a  saturday/sunday, then adjust a day or two accordingly,
  • if the end date falls on saturday/sunday, then adjust a day or two accordingly

After some tinkering, I came up with the following expression:

(I also added greatest/least wrappers so that it didn’t matter if the start date was later than the end date)


   (trunc(abs(:start_date-:end_date)/7) + 
     case when to_number(to_char(least(:start_date,:end_date),'D')) > to_number(to_char(greatest(:start_date,:end_date),'D')) then 1 else 0 end 
   ) * 5 +
   to_number(to_char(greatest(:start_date,:end_date),'D')) - to_number(to_char(least(:start_date,:end_date),'D')) + 
   case when to_number(to_char(least(:start_date,:end_date),'D')) in (1,7) then 0 else 1 end +
   case when to_number(to_char(least(:start_date,:end_date),'D')) = 7 then 1 else 0 end + 
   case when to_number(to_char(greatest(:start_date,:end_date),'D')) = 7 then -1 else 0 end 

Now that this an expression, it can trivially be added as a virtual column to an existing table if needed.


SQL> create table t ( sd date, ed date);

Table created.

SQL>
SQL> insert into t
  2  select date '2020-01-01'-20+rownum*2, date '2020-01-01'-10+rownum
  3  from dual
  4  connect by level <= 30;

30 rows created.

SQL>
SQL> select * from t;

SD        ED
--------- ---------
14-DEC-19 23-DEC-19
16-DEC-19 24-DEC-19
18-DEC-19 25-DEC-19
20-DEC-19 26-DEC-19
22-DEC-19 27-DEC-19
24-DEC-19 28-DEC-19
26-DEC-19 29-DEC-19
28-DEC-19 30-DEC-19
30-DEC-19 31-DEC-19
01-JAN-20 01-JAN-20
03-JAN-20 02-JAN-20
05-JAN-20 03-JAN-20
07-JAN-20 04-JAN-20
09-JAN-20 05-JAN-20
11-JAN-20 06-JAN-20
13-JAN-20 07-JAN-20
15-JAN-20 08-JAN-20
17-JAN-20 09-JAN-20
19-JAN-20 10-JAN-20
21-JAN-20 11-JAN-20
23-JAN-20 12-JAN-20
25-JAN-20 13-JAN-20
27-JAN-20 14-JAN-20
29-JAN-20 15-JAN-20
31-JAN-20 16-JAN-20
02-FEB-20 17-JAN-20
04-FEB-20 18-JAN-20
06-FEB-20 19-JAN-20
08-FEB-20 20-JAN-20
10-FEB-20 21-JAN-20

30 rows selected.

SQL>
SQL> alter table t add business_days number generated always as (
  2     (trunc(abs(sd-ed)/7) +
  3       case when to_number(to_char(least(sd,ed),'D')) > to_number(to_char(greatest(sd,ed),'D')) then 1 else 0 end
  4     ) * 5 +
  5     to_number(to_char(greatest(sd,ed),'D')) - to_number(to_char(least(sd,ed),'D')) +
  6     case when to_number(to_char(least(sd,ed),'D')) in (1,7) then 0 else 1 end +
  7     case when to_number(to_char(least(sd,ed),'D')) = 7 then 1 else 0 end +
  8     case when to_number(to_char(greatest(sd,ed),'D')) = 7 then -1 else 0 end
  9  );

Table altered.

SQL>
SQL> select * from t;

SD        ED        BUSINESS_DAYS
--------- --------- -------------
14-DEC-19 23-DEC-19             6
16-DEC-19 24-DEC-19             7
18-DEC-19 25-DEC-19             6
20-DEC-19 26-DEC-19             5
22-DEC-19 27-DEC-19             5
24-DEC-19 28-DEC-19             4
26-DEC-19 29-DEC-19             2
28-DEC-19 30-DEC-19             1
30-DEC-19 31-DEC-19             2
01-JAN-20 01-JAN-20             1
03-JAN-20 02-JAN-20             2
05-JAN-20 03-JAN-20             1
07-JAN-20 04-JAN-20             2
09-JAN-20 05-JAN-20             4
11-JAN-20 06-JAN-20             5
13-JAN-20 07-JAN-20             5
15-JAN-20 08-JAN-20             6
17-JAN-20 09-JAN-20             7
19-JAN-20 10-JAN-20             6
21-JAN-20 11-JAN-20             7
23-JAN-20 12-JAN-20             9
25-JAN-20 13-JAN-20            10
27-JAN-20 14-JAN-20            10
29-JAN-20 15-JAN-20            11
31-JAN-20 16-JAN-20            12
02-FEB-20 17-JAN-20            11
04-FEB-20 18-JAN-20            12
06-FEB-20 19-JAN-20            14
08-FEB-20 20-JAN-20            15
10-FEB-20 21-JAN-20            15

30 rows selected.

Note: Day of Week calculations from TO_CHAR can vary due to NLS settings, so you might need to alter this expression slightly if you don’t live in Australia Smile