WORKDAYS_BETWEEN
Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7
Description
This function calculates working days between two dates based on a TFACS style calendar table.
The calendar table can be configured in the Data Model Editor and applies to all calls of WORKDAYS_BETWEEN in the Data Model.
The calendar table has to have the following layout:
Calendar ID: Identifier of the working calendar
Year: Year of the calendar
Month 1: Working days of January bit encoded. 1 is a working day. 0 is a day off. Example of January 2019 with the weekends off: 1111001111100111110011111001111
Month 2: Working days of February.
...
Month 12: Working days of December
WORKDAYS_BETWEEN ( calendar_id, start, end )
calendar_id: the ID defines which calendar out of the calendar table is used.
If the calendar ID does not exist in the calendar table, WORKDAYS_BETWEEN behaves like the DAYS_BETWEEN function and returns the number of days between the specified dates.
To calculate the workdays between two dates without a calendar table, you might want to use the WEEKDAY_CALENDAR in combination with REMAP_TIMESTAMPS.
Note
Please note that for the same two dates and the same WORKDAY_CALENDAR specification, there is a difference in using WORKDAYS_BETWEEN and subtracting two REMAP_TIMESTAMPS calls for these dates. WORKDAYS_BETWEEN always ignores the time of the day (e.g., 1970-01-01T23:59:59:999 is treated as 1970-01-01T00:00:00:000) while REMAP_TIMESTAMPS does not.
NULL handling
If any parameter is NULL, the result is NULL as well.
Example
For example, assume 1970-01-01 and 1970-01-02 are both valid workdays: WORKDAYS_BETWEEN([workday_calendar_id], 1970-01-01 23:59, 1970-01-02 00:30) would return '1' workday while REMAP_TIMESTAMPS(1970-01-02 00:30, MINUTES, [workday_calendar_specification]) - REMAP_TIMESTAMPS(1970-01-01 23:59, MINUTES, [workday_calendar_specification]) would return only '31' minutes. In this specific example, REMAP_TIMESTAMPS with the time unit DAYS would have also returned '1' day (as WORKDAYS_BETWEEN) because the first REMAP_TIMESTAMPS call would have returned '1' and the second call would have returned '0'.
[1] Here WORKDAYS_BETWEEN is used without an existing working calendar. Therefore it returns the number of days between the specified dates.
|