Skip to main content

Celonis Product Documentation

ADD_WORKDAYS
Description

The ADD_WORKDAYS function adds a given number of workdays to a given date. If the start date is not a workday, ADD_WORKDAYS returns the same result as if the start date was the next workday instead (even when 0 days are added).

This function only modifies the date of the given timestamps and does not change the time of day.

The ADD_WORKDAYS function retrieves its information regarding valid workdays from the passed Calendar configuration. For the weekday and factory calendars any day with a (non-empty) shift is considered a workday.

If the computation leaves the scope of the calendar, NULL is returned. This is also the case when the start date lies outside of the scope of the calendar. For the factory calendar the scope contains the complete year containing the first defined shift (so also all days before the first shift in that year), the complete year containing the last defined shift and all years in between.

ADD_WORKDAYS follows the same logic as the ADD_WORKDAYS function in SAP HANA.

Syntax

The recommended way of using the ADD_WORKDAYS function is the following:

  ADD_WORKDAYS ( date, number_of_days, calendar_specification [, calendar_id_column])
 
  • date: Start date

  • number_of_days: Number of workdays which are added to the start date (can be negative)

  • calendar_specification: One of WEEKDAY_CALENDAR, FACTORY_CALENDAR, WORKDAY_CALENDAR, or INTERSECT

  • calendar_id_column: Column to create a mapping between the respective activities and their used calendar specification. This is mandatory when using multiple calendar specifications. For more details, please take a look at the respective documentation of the DateTime Calendar.

Legacy Behavior

There is also a legacy way of using the ADD_WORKDAYS function. Here it is only possible to specify a workday calendar.

  ADD_WORKDAYS ( workday_calendar_config, date, number_of_days )
 
  • workday_calendar_config: WORKDAY_CALENDAR to use. A TFACS entry identifier is required.

  • date: Base date.

  • number_of_days: Number of working days which are added to the base date.

Deprecated Behavior

The ADD_WORKDAYS function can also be called as follows: [ DEPRECATED | Use the newest version of ADD_WORKDAYS 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. In this case, a warning is issued.

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.

Differences Between the Newest and the Legacy Versions

If the start is not a workday and a positive amount of days is added, the newest version will return the first workday which comes after the result of the legacy versions. Additionally, if the start date is out of bounds of the given calendar, but the first date after/before (when adding/subtracting days) is inside the bounds of the given calendar, the newest version will return NULL, while the legacy versions will return a result.

Example

[1]

In this first test scenario, workdays are added and subtracted using a workday calendar. In the first two examples the weekend on the sixth and seventh is skipped. In the last two examples the start is not a workday.

Query

Column1

         ADD_WORKDAYS ( "TimestampTable"."Timestamps" , "TimestampTable"."Days" , WORKDAY_CALENDAR ( "TFACS" , 'BAY' ) )
        

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'

TimestampTable

Timestamps : date

Days : int

Tue Jan 02 2018 01:00:00.000

4

Mon Jan 08 2018 02:00:00.000

-4

Mon Jan 01 2018 03:00:00.000

1

Sat Jan 06 2018 04:00:00.000

-1

Result

Column1 : date

Mon Jan 08 2018 01:00:00.000

Tue Jan 02 2018 02:00:00.000

Wed Jan 03 2018 03:00:00.000

Fri Jan 05 2018 04:00:00.000

[2]

If the start is not a workday, adding 0 days returns the next workday.

Query

Column1

         ADD_WORKDAYS ( "TimestampTable"."Timestamps" , "TimestampTable"."Days" , WORKDAY_CALENDAR ( "TFACS" , 'BAY' ) )
        

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'

TimestampTable

Timestamps : date

Days : int

Mon Jan 01 2018 01:00:00.000

0

Tue Jan 02 2018 02:00:00.000

0

Result

Column1 : date

Tue Jan 02 2018 01:00:00.000

Tue Jan 02 2018 02:00:00.000

[3]

The function also works with a weekday calendar. In the first example the third (Wednesday) is skipped. In the second example both the seventh (Sunday) and the third are skipped.

Query

Column1

         ADD_WORKDAYS ( "TimestampTable"."Timestamps" , "TimestampTable"."Days" , WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00 SATURDAY 09:00 - 17:00 ) )
        

Input

Output

TimestampTable

Timestamps : date

Days : int

Mon Jan 01 2018 01:00:00.000

4

Mon Jan 08 2018 02:00:00.000

-4

Result

Column1 : date

Sat Jan 06 2018 01:00:00.000

Tue Jan 02 2018 02:00:00.000

[4]

Additionally, a factory calendar or an arbitrary intersection of the different calendars can be used. In the following scenario, the first example skips over the third (Wednesday), because it does not appear in the weekday calendar and the fifth (Friday), because it does not appear in the factory calendar. The second example skips over the seventh (Sunday) because even though the day appears in both calendars, the shifts on the day do not overlap and so it is not contained in the intersection.

Query

Column1

         ADD_WORKDAYS ( "TimestampTable"."Timestamps" , "TimestampTable"."Days" , INTERSECT ( WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00 SATURDAY 09:00 - 17:00 SUNDAY 13:00 - 15:00 ) , FACTORY_CALENDAR ( "FactoryTable"."START" , "FactoryTable"."END" ) ) )
        

Input

Output

FactoryTable

START : date

END : date

Tue Jan 02 2018 08:00:00.000

Tue Jan 02 2018 17:00:00.000

Wed Jan 03 2018 08:00:00.000

Wed Jan 03 2018 17:00:00.000

Thu Jan 04 2018 08:00:00.000

Thu Jan 04 2018 17:00:00.000

Sat Jan 06 2018 08:00:00.000

Sat Jan 06 2018 17:00:00.000

Sun Jan 07 2018 08:00:00.000

Sun Jan 07 2018 12:00:00.000

Mon Jan 08 2018 08:00:00.000

Mon Jan 08 2018 17:00:00.000

Tue Jan 09 2018 08:00:00.000

Tue Jan 09 2018 17:00:00.000

TimestampTable

Timestamps : date

Days : int

Mon Jan 01 2018 00:00:00.000

2

Tue Jan 09 2018 00:00:00.000

-2

Result

Column1 : date

Sat Jan 06 2018 00:00:00.000

Sat Jan 06 2018 00:00:00.000

[5]

It is also possible to use different calendars for different rows by specifying an additional calendar id column.

Query

Column1

         ADD_WORKDAYS ( "TimestampTable"."Timestamps" , "TimestampTable"."Days" , FACTORY_CALENDAR ( "FactoryTable" ) , "TimestampTable"."Ident" )
        

Input

Output

FactoryTable

IDENT : string

START : date

END : date

'DE'

Tue Jan 02 2018 08:00:00.000

Tue Jan 02 2018 17:00:00.000

'DE'

Thu Jan 04 2018 08:00:00.000

Thu Jan 04 2018 17:00:00.000

'DE'

Sat Jan 06 2018 08:00:00.000

Sat Jan 06 2018 17:00:00.000

'US'

Wed Jan 03 2018 08:00:00.000

Wed Jan 03 2018 17:00:00.000

'US'

Sun Jan 07 2018 08:00:00.000

Sun Jan 07 2018 12:00:00.000

'US'

Mon Jan 08 2018 08:00:00.000

Mon Jan 08 2018 17:00:00.000

TimestampTable

IDENT : string

Timestamps : date

Days : int

'DE'

Tue Jan 02 2018 00:00:00.000

2

'US'

Wed Jan 03 2018 00:00:00.000

2

Result

Column1 : date

Sat Jan 06 2018 00:00:00.000

Mon Jan 08 2018 00:00:00.000

[6]

In this example which makes use of the legacy version, 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

See also: