Counting working days in the Oracle database without a calendarPublished on: Author: Stefan van Glabbeek Category: Oracle
In the Oracle database, you can count the number of days between two dates by subtracting the dates from each other. See the following query:
- SELECT TO_DATE (:period_to, 'yyyy-MM-dd') - TO_DATE ( :period_from, 'yyyy-MM-dd') FROM DUAL;
Oracle returns the number of days in between those dates. However, sometimes, and especially in HCM, we are interested in the number of working days. For instance, if we want to calculate the absence percentage of an employee.
In order to do this, you normally use a calendar. Or to be more precise, a table that contains a calendar. You have a table that contains for example the days of the current year, starting with 2019-01-01 and ending with 2019-12-31. There are a few more columns, like which day it is (Monday, Tuesday, etc.) and the week number.
Using this table, we can count the days between two dates that are not Saturday and Sunday.
A disadvantage of this strategy, which is commonly used at different companies, is that the table needs to be maintained. The rows of a year are inserted by a program, which runs on a yearly basis. If the program didn’t run or if we want to go far into the future or in the past, we might have a problem. In my case, I tried to do this in the Oracle HCM cloud but I didn’t find a table that was suitable.
An option would be to do what such a program would have done but in a PL/SQL package procedure: putting the resulting calendar in a collection and using that. This option is not available if you work in the SAAS cloud however, because you only have read permission to the database via BI-publisher.
I searched online and found the solution on an Indian forum. Instead of making use of a table filled with a calendar, you can also make use of the table ALL_OBJECTS, which is always there. It’s limited by the amount of objects in the database but for normal use it will probably be enough. As simple as this idea might seem once you know of it, the possibility had simply never occurred to me before.
It inspired me to write the following basic version of a query to calculate the working days between two dates:
- WITH count_days AS
- (SELECT ROWNUM rnum
- FROM all_objects
- WHERE ROWNUM <= ( TO_DATE (:period_to, 'yyyy-MM-dd') - TO_DATE ( :period_from, 'yyyy-MM-dd') + 1)
- SELECT COUNT (1)
- FROM count_days cds
- WHERE TO_CHAR ( TO_DATE ( :period_from, 'yyyy-MM-dd') + cds.rnum - 1, 'DY') NOT IN ('SAT', 'SUN');
* Note: in this case, I needed to include both a starting date and an end date in the calculation of the absence percentage, because you are still absent on the end date of your absence.
In this example, the ‘DY’ format of the date is the 3-letter abbreviation of the day in English. However, this is not always the same in every database, and probably depends of the taste of the Oracle DBA. In Fusion Cloud’s database, it is the day-number: ‘6’ instead of ‘SAT’ and ‘7’ instead of ‘SUN’.
Of course, you also want to know which days are official holidays. In Fusion, I made a subquery combining the following tables:
Here I assumed that the working location determines which holiday schedule applies to an employee. If you combine those tables, you can determine the holidays and exclude them in the same way as the weekends.