Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7 

Description

Based on a TFACS style calendar, this function adds working days to a given date.

The ADD_WORKDAYS function adds a given number of workdays to a given date. The result is a new date which has as many workdays between the given date as specified in the input. The ADD_WORKDAYS function retrieves its information regarding valid workdays from the passed WORKDAY_CALENDAR which in turn is based on a TFACS style calendar table in the data model. If the computation leaves the scope of the calendar, NULL will be returned.

Syntax

The recommended way of using the ADD_WORKDAYS function is the following (Since:  CELONIS 4.7): 

ADD_WORKDAYS ( workday_calendar_config, date, number_of_days )
  • workday_calendar_config: WORKDAY_CALENDAR to use.
  • date: base date.
  • number_of_days: number of working days which are added to the base date.

Deprecated Behavior

As an alternative to the recommended syntax, the ADD_WORKDAYS function can be called as follows: [ DEPRECATED SINCE 4.7 Use ADD_WORKDAYS with an inline workday calendar configuration instead ]. 

ADD_WORKDAYS ( <calendar_id>, <date>, <number_of_days> [, INVALID_TO_NULL] )
  • calendar_id: Defines which calendar entries out of the TFACS table are used.
  • date: The base date.
  • number_of_days: The number of working days which are added to the base date.
  • INVALID_TO_NULL: This flag prevents the fallback to ADD_DAYS.
In this case, the calendar table needs to have the same layout as for WORKDAYS_BETWEEN. Note that the TFACS table and workday calendars were set during data load. If the calendar ID does not exist in the calendar table, or if the scope of the calendar would have been left by the computation (i.e., adding given days to the date would have a result which is not covered by the calendar), ADD_WORKDAYS behaves like the ADD_DAYS function and adds the specified number of days to the date. The result of ADD_DAYS, however, may still be in the scope of the calendar. When working with the deprecated version of this function, using the INVALID_TO_NULL flag will prevent the fallback to ADD_DAYS, and NULL will be returned instead. This is the recommended behavior, since the fallback may lead to unexpected/unintuitive results.

Null handling

If any parameter is NULL, the result is NULL

Exceptions

The ADD_WORKDAYS operator does not work if there are any gaps in the years covered by the respective WORKDAY_CALENDAR. An error will be returned if gaps are found in the workday calendar configuration.

Example


[1] Here ADD_WORKDAYS is used without an existing work calendar. The function therefore adds the specified number of days.

Query
Column1
ADD_WORKDAYS ( "Table1"."Calendar_ID" , "Table1"."Column1" , 1 )
Input
Table1
Column1 : DATECalendar_ID : STRING
Sat Apr 15 2017 00:00:00.000
'Non-existing Calendar ID'
Sun Apr 16 2017 00:00:00.000
'Non-existing Calendar ID'
Mon Apr 17 2017 00:00:00.000
'Non-existing Calendar ID'
Output
Result
Column1 : DATE
Sun Apr 16 2017 00:00:00.000
Mon Apr 17 2017 00:00:00.000
Tue Apr 18 2017 00:00:00.000



[2] Here ADD_WORKDAYS is used with a workday calendar based on a TFACS table for the year 1974. Some inputs are null and thus null is returned in the result.

Query
Column1
ADD_WORKDAYS ( WORKDAY_CALENDAR ( "TFACS" , 'BAY' ) , "Table1"."Date" , "Table1"."Integer" )
Input
TFACS
IDENT : STRINGJAHR : INTMON01 : STRINGMON02 : STRINGMON03 : STRINGMON04 : STRINGMON05 : STRINGMON06 : STRINGMON07 : STRINGMON08 : STRINGMON09 : STRINGMON10 : STRINGMON11 : STRINGMON12 : STRING
'BAY'
1974
'0111001111100111110011111001111''1001111100111110011111001111''1001111100111110011111001111100''111110011110000111100111110011''0110011111001111100111010011111''000111100111010001111001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111100111110111110011111001''1111001111100111110011111001111''000111110011111001101100111110''0111110011111001111100110010011'
Table1
Date : DATEInteger : INT
Thu Dec 26 1974 00:00:00.000
null
null
1
null
null
Output
Result
Column1 : DATE
null
null
null



[3] Here ADD_WORKDAYS is used with a workday calendar based on a TFACS table for the year 2018. We use constants as input and add one workday to the 29th of March 2018. As the 30th of March is a public holiday in Bavaria Germany, the 31st is on the weekend, the 1st of April 2018 is also on the weekend and the 2nd of April is yet again a public holiday, we get 3rd of April 2018 as result.

Query
Column1
ADD_WORKDAYS ( WORKDAY_CALENDAR ( "TFACS" , 'BAY' ) ,
    TO_TIMESTAMP ( '29.03.2018' , 'DD.MM.YYYY' ) ,
    1
)
Input
TFACS
IDENT : STRINGJAHR : INTMON01 : STRINGMON02 : STRINGMON03 : STRINGMON04 : STRINGMON05 : STRINGMON06 : STRINGMON07 : STRINGMON08 : STRINGMON09 : STRINGMON10 : STRINGMON11 : STRINGMON12 : STRING
'BAY'
2018
'0111100111110011111001111100111''1100111110011111001111100111''1100111110011111001111100111100''001111001111100111110011111001''0111001110100111110001111001110''100111110011111001111100111110''0111110011111001111100111110011''1110011111001101100111110011111''001111100111110011111001111100''1101100111110011111001111100111''010011111001111100111110011111''0011111001111100111110010011001'
Output
Result
Column1 : DATE
Tue Apr 03 2018 00:00:00.000



[4] Results outside the supported DATE range.

Query
Column1
ADD_WORKDAYS ( 'Non-existing Calendar ID' , "Table1"."Date" , - 1 , INVALID_TO_NULL )
Column2
ADD_WORKDAYS ( 'Non-existing Calendar ID' , "Table1"."Date" , + 1 , INVALID_TO_NULL )
Input
Table1
Date : DATE
Wed Dec 24 1399 00:00:00.000
Fri Dec 31 9999 23:59:59.999
Output
Result
Column1 : DATEColumn2 : DATE
null
null
null
null

ADD_WORKDAYS: Invalid calendar. Returning NULL instead.


See also:

  • No labels