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
Table1
DateColumn1 : DATEDateColumn2 : 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
Output
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-01 - 4 hours
1970-01-02 - 0 hours
1970-01-03 - 0 hours
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
T
C : DATE
Wed Jan 07 1970 15:30:00.000
Output
Result
Column1 : INTColumn2 : 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
Table1
IDENT : STRINGWEEKDAY : STRINGSHIFT_BEGIN : INTSHIFT_END : INT
'DE''THURSDAY'
0
46740000
'DE''FRIDAY'
0
46740000
Table2
CalendarID : STRINGTimestamp : DATE
'DE'
Wed Jan 07 1970 15:30:00.000
'DE'
Wed Jan 07 1970 16:30:00.000
Output
Result
Column1 : INT
25
25


  • No labels