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'.
Column1
WORKDAYS_BETWEEN ( "Table1"."Calendar_ID" , "Table1"."Column1" , "Table1"."Column2" )
Table1
| Column1 : DATE | Column2 : DATE | Calendar_ID : STRING |
|---|---|---|
Sat Apr 15 2017 00:00:00.000 | Sun Apr 16 2017 00:00:00.000 | 'Calendar ID that does not exist' |
Sun Apr 16 2017 00:00:00.000 | Mon Apr 17 2017 00:00:00.000 | 'Calendar ID that does not exist' |
Mon Apr 17 2017 00:00:00.000 | Tue Apr 18 2017 00:00:00.000 | 'Calendar ID that does not exist' |
Result
| Column1 : INT |
|---|
1 |
1 |
1 |
WORKDAYS_BETWEEN: Invalid calendar. Returning number of days between the dates instead.