Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

ADD_WORKDAYS

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

Output

Table1

Column1 : DATE

Calendar_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'

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

Output

TFACS

IDENT : STRING

JAHR : INT

MON01 : STRING

MON02 : STRING

MON03 : STRING

MON04 : STRING

MON05 : STRING

MON06 : STRING

MON07 : STRING

MON08 : STRING

MON09 : STRING

MON10 : STRING

MON11 : STRING

MON12 : STRING

'BAY'

1974

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111100'

'111110011110000111100111110011'

'0110011111001111100111010011111'

'000111100111010001111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111100111110111110011111001'

'1111001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

Table1

Date : DATE

Integer : INT

Thu Dec 26 1974 00:00:00.000

null

null

1

null

null

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

Output

TFACS

IDENT : STRING

JAHR : INT

MON01 : STRING

MON02 : STRING

MON03 : STRING

MON04 : STRING

MON05 : STRING

MON06 : STRING

MON07 : STRING

MON08 : STRING

MON09 : STRING

MON10 : STRING

MON11 : STRING

MON12 : STRING

'BAY'

2018

'0111100111110011111001111100111'

'1100111110011111001111100111'

'1100111110011111001111100111100'

'001111001111100111110011111001'

'0111001110100111110001111001110'

'100111110011111001111100111110'

'0111110011111001111100111110011'

'1110011111001101100111110011111'

'001111100111110011111001111100'

'1101100111110011111001111100111'

'010011111001111100111110011111'

'0011111001111100111110010011001'

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

Output

Table1

Date : DATE

Wed Dec 24 1399 00:00:00.000

Fri Dec 31 9999 23:59:59.999

Result

Column1 : DATE

Column2 : DATE

null

null

null

null

Note

ADD_WORKDAYS: Invalid calendar. Returning NULL instead.

See also: