Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

WEEKDAY_CALENDAR

Applies to: CELONIS 4.7

Description

WEEKDAY_CALENDAR produces a new weekday calendar using the given configuration.

A weekday calendar specifies on which weekdays (e.g., 'Monday') work is done. Additionally, the weekday calendar can be used to specify shifts (e.g., '06:30-17:00') for each weekday.

The weekday calendar is used as input for functions like REMAP_TIMESTAMPS. In these contexts the calendar allows to restrict the accounted DATEs which are used for the date based calculations. By this, only valid weekdays (and their respective shifts) as configured in the weekday calendar will be considered.

Syntax

We can create a new weekday calendar by using either of the two syntaxes shown below. The first syntax creates a single weekday calendar from different day and day time range, whereas the second syntax creates multiple weekday calendars by making use of a table that has at least 4 columns named IDENT, WEEKDAY, SHIFT_BEGIN, and SHIFT_END. More details about the table and the required columns can be found below.

WEEKDAY_CALENDAR ( day [ day_time ] ... )

  • day: Restricts the weekday. One of MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY or SUNDAY

  • day_time: Restricts the time of the day in the format HH:MM-HH:MM. If no time is specified, a full day (00:00-24:00) is assumed.

WEEKDAY_CALENDAR ( calendar_table )

  • calendar_table: Table with four columns.

    • IDENT: STRING column specifying the calendar specification ID.

    • WEEKDAY: Restricts the weekday. One of MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY or SUNDAY

    • SHIFT_BEGIN: INTEGER column specifying begin of shift in milliseconds after midnight.

    • SHIFT_END: INTEGER column specifying end of shift in milliseconds after midnight.

Please note:

  • If the begin and end of the shift are equal, an empty shift is specified and will thus be handled as if the given weekday was not set at all.

  • The shift 00:00-24:00 specifies a shift lasting the entire day (i.e., 24 hours).

  • Multiple shifts during one weekday are currently not supported.

  • If a weekday is specified more than once, only the last specification is respected (i.e., previous specifications are overwritten).

Tips

Please note, before (Since: CELONIS 4.7) simply writing 'MONDAY TUESDAY 08:00-16:30' (i.e., without the 'WEEKDAY_CALENDAR' keyword) produced a weekday calendar as well (for valid weekdays Monday and Tuesday from 8:00 to 16:30). When combining this calendar specification with the time unit 'DAYS', the specified shifts are ignored for all computations. This behaviour is inconsistent with regards to the other time units. Nevertheless, if this behaviour is desired (e.g., to compute the number of valid weekdays on which work was done) you can use the weekday calendar specification without the 'WEEKDAY_CALENDAR' keyword and the time unit 'DAYS'. Since (Since: CELONIS 4.7) we recommend (if applicable) to use the WORKDAY_CALENDAR for such computations. In the future, the weekday calendar specification without the 'WEEKDAY_CALENDAR' keyword will not be supported in new calendar related features such as calendar intersection (see INTERSECT) for example. [ DEPRECATED SINCE 4.7 Use the weekday calendar specification with the 'WEEKDAY_CALENDAR' keyword ].

Examples

[1] For a constant timestamp, calculate the number of days since 1970-01-01 by using the REMAP_TIMESTAMPS function in conjunction with a weekday calendar.

Query

Column1

REMAP_TIMESTAMPS (
    TO_TIMESTAMP ( '01.12.1969 06:30.30' , 'DD.MM.YYYY HH:mm.SS' ) ,
    DAYS ,
    WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY )
)

Input

Output

Result

Column1 : INT

-22

[2] Calculate the number of working days between two dates using the REMAP_TIMESTAMPS function. It is assumed that working days are from Monday to Friday (with 24h a day).

Query

Column1

REMAP_TIMESTAMPS (
    "Table1"."DateColumn2" ,
    DAYS ,
    WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY )
)
-
REMAP_TIMESTAMPS (
    "Table1"."DateColumn1" ,
    DAYS ,
    WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY )
)

Input

Output

Table1

DateColumn1 : DATE

DateColumn2: DATE

Fri Feb 01 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Sat Feb 02 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Sun Feb 03 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Mon Feb 04 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Tue Feb 05 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Wed Feb 06 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Thu Feb 07 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Fri Feb 08 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Sat Feb 09 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Sun Feb 10 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Mon Feb 11 2019 00:00:00.000

Fri Feb 01 2019 00:00:00.000

Tue Feb 12 2019 00:00:00.000

Result

Column1 : INT

0

1

1

1

2

3

4

5

6

6

6

7

[3] Example for using a weekday calendar with shifts as input to the REMAP_TIMESTAMPS function. The 1970-01-01 is a Thursday.

Query

Column1

REMAP_TIMESTAMPS (
    TO_TIMESTAMP ( '01.01.1970 04:00' , 'DD.MM.YYYY HH:mm' ) ,
    HOURS ,
    WEEKDAY_CALENDAR ( THURSDAY FRIDAY 00:00 - 12:59 )
)

Input

Output

Result

Column1 : INT

4

[4] For a constant timestamp, calculate the number of hours since 1970-01-01 by using a weekday calendar with specified shifts (again, using the REMAP_TIMESTAMPS function).

Query

Column1

REMAP_TIMESTAMPS (
    TO_TIMESTAMP ( '29.12.1969 06:30.30' , 'DD.MM.YYYY HH:mm.SS' ) ,
    HOURS ,
    WEEKDAY_CALENDAR ( MONDAY 8:00 - 13:00 WEDNESDAY 8:00 - 13:00 )
)

Input

Output

Result

Column1 : INT

-10

[5] If a weekday is specified more than once, only the last specification is respected (i.e., previous specifications are overwritten).

Query

Column1

REMAP_TIMESTAMPS (
    TO_TIMESTAMP ( '01.01.1970 01:00.30' , 'DD.MM.YYYY HH:mm.SS' ) ,
    SECONDS ,
    WEEKDAY_CALENDAR ( THURSDAY 00:00 - 24:00 THURSDAY 00:00 - 00:30 )
)

Input

Output

Result

Column1 : INT

1800

[6] The following example shows the behavior of the weekday calendar without the 'WEEKDAY_CALENDAR' token. The first of January 1970 is a Thursday. The 7th of January is a Wednesday. Due to the defined shifts REMAP_TIMESTAMPS takes the following times into account (when not using the time unit 'DAYS').

1970-01-04 - 0 hours

1970-01-05 - 8 hours

1970-01-06 - 8 hours

1970-01-07 - 7.5 hours

That results to 27 hours and 30 minutes. Depending on the timestamps the result is rounded down.

HOURS -> 27

Due to the different behaviour when using the time unit 'DAYS' with this calendar specification, REMAP_TIMESTAMPS takes the following times into account:

1970-01-01 - 24 hours

1970-01-02 - 0 hours

1970-01-03 - 0 hours

1970-01-04 - 0 hours

1970-01-05 - 24 hours

1970-01-06 - 24 hours

1970-01-07 - 15.5 hours

That results to 87 hours and 30 minutes. Depending on the timestamps the result is rounded down.

DAYS -> 3

Query

Column1

REMAP_TIMESTAMPS (
    "T"."C" ,
    HOURS ,
    MONDAY 08:00 - 16:00 TUESDAY 08:00 - 16:00 WEDNESDAY 08:00 - 16:00 THURSDAY 08:00 - 12:00
)

Column2

REMAP_TIMESTAMPS (
    "T"."C" ,
    DAYS ,
    MONDAY 08:00 - 16:00 TUESDAY 08:00 - 16:00 WEDNESDAY 08:00 - 16:00 THURSDAY 08:00 - 12:00
)

Input

Output

T

C : DATE

Wed Jan 07 1970 15:30:00.000

Result

Column1 : INT

Column2 : INT

27

3

[7] WEEKDAY_CALENDAR (calendar_table) syntax. This is used in REMAP_TIMESTAMPS along with a Calendar IDs column. Weekday Calendar is created for different identifiers present in the Calendar IDs column.

Query

Column1

REMAP_TIMESTAMPS (
    "Table2"."Timestamp" ,
    HOURS ,
    WEEKDAY_CALENDAR ( "Table1" ) ,
    "Table2"."CalendarID"
)

Input

Output

Table1

IDENT : STRING

IDENT : STRING

SHIFT_BEGIN : INT

SHIFT_END : INT

'THURSDAY'

0

46740000

'FRIDAY'

0

46740000

Table2

CalendarID : STRING

Timestamp : DATE

'DE'

Wed Jan 07 1970 15:30:00.000

'DE'

Wed Jan 07 1970 16:30:00.000

Result

Column1 : INT

25

25