Skip to main content

Celonis Product Documentation

IN_CALENDAR
Description

IN_CALENDAR checks whether a given date is within a calendar.

Supported input column types: timestamp_column -> DATE

Output column type: INT

If the timestamp is outside of the scope of the given workday or factory calendar(s), NULL is returned. For a 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.

Syntax
  IN_CALENDAR ( timestamp_column, calendar_specification [, calendar_id_column])

  • timestamp_column: timestamp

  • 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.

NULL handling

If any parameter is NULL, the result is NULL as well.

Exceptions

The IN_CALENDAR 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.

Examples

[1]

A simple IN_CALENDAR example using a weekday calendar. The first of January 2018 is a Monday.

Query

Column1 

         IN_CALENDAR ( "TimestampTable"."Timestamps" , 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

Mon Jan 01 2018 10:00:00.000

Fri Jan 05 2018 08:59:59.999

Fri Jan 05 2018 09:00:00.000

Fri Jan 05 2018 16:59:59.999

Fri Jan 05 2018 17:00:00.000

Sun Jan 07 2018 14:00:00.000

Result

Column1 : int

1

0

1

1

0

0

[2]

A simple IN_CALENDAR example using a workday calendar. The first of January 2017 is a Sunday.

Query

Column1 

         IN_CALENDAR ( "TimestampTable"."Date" , 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'

2016

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

2017

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

2018

'0111100111110011111001111100111'

'1100111110011111001111100111'

'1100111110011111001111100111100'

'001111001111100111110011111001'

'0111001110100111110001111001110'

'100111110011111001111100111110'

'0111110011111001111100111110011'

'1110011111001101100111110011111'

'001111100111110011111001111100'

'1101100111110011111001111100111'

'010011111001111100111110011111'

'0011111001111100111110010011001'

TimestampTable

Date : date

Sun Jan 01 2017 10:00:00.000

Wed Jan 03 2018 10:00:00.000

Sat Jan 01 2022 10:00:00.000

Result

Column1 : int

0

1

null

[3]

IN_CALENDAR can also be used with an intersection of different calendars. Only the timestamps that are covered by both calendars are taken into account.

Query

Column1 

         IN_CALENDAR ( "TimestampTable"."Timestamps" , INTERSECT ( WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 WEDNESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 ) , FACTORY_CALENDAR ( "FactoryTable"."START" , "FactoryTable"."END" ) ) )

Input

Output

FactoryTable

START : date

END : date

Tue Jan 02 2018 09:00:00.000

Tue Jan 02 2018 12:00:00.000

Tue Jan 02 2018 13:00:00.000

Tue Jan 02 2018 16:00:00.000

Wed Jan 03 2018 09:00:00.000

Wed Jan 03 2018 12:00:00.000

Wed Jan 03 2018 13:00:00.000

Wed Jan 03 2018 16:00:00.000

Fri Jan 05 2018 09:00:00.000

Fri Jan 05 2018 12:00:00.000

Fri Jan 05 2018 13:00:00.000

Fri Jan 05 2018 16:00:00.000

TimestampTable

Timestamps : date

Tue Jan 02 2018 11:00:00.000

Fri Jan 05 2018 16:10:00.000

Wed Jan 03 2018 12:05:00.000

Wed Jan 03 2018 11:00:00.000

Tue Jan 03 2017 11:00:00.000

Fri Jan 04 2019 11:00:00.000

Result

Column1 : int

1

0

0

1

null

null

[4]

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

Query

Column1 

         IN_CALENDAR ( "TimestampTable"."Timestamps" , 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'

Wed Jan 03 2018 08:00:00.000

Wed Jan 03 2018 17:00:00.000

'DE'

Fri Jan 05 2018 08:00:00.000

Fri Jan 05 2018 17:00:00.000

'US'

Sat Jan 06 2018 14:00:00.000

Sat Jan 06 2018 23:00:00.000

'US'

Sun Jan 07 2018 14:00:00.000

Sun Jan 07 2018 23:00:00.000

'US'

Mon Jan 08 2018 14:00:00.000

Mon Jan 08 2018 23:00:00.000

TimestampTable

IDENT : string

Timestamps : date

'DE'

Sat Jan 06 2018 16:00:00.000

'US'

Sat Jan 06 2018 16:00:00.000

Result

Column1 : int

0

1