Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

WORKDAY_CALENDAR

Applies to: CELONIS 4.7

Description

WORKDAY_CALENDAR produces a new workday calendar (i.e., SAP TFACS-style calendar or sometimes called SAP Factory Calendar) using the TFACS table passed to it.

A workday calendar is based on the SAP TFACS table (sometimes called SAP Factory calendar) and specifies on which full days work is done (e.g., by default assumes 24 hours of working time). However, a workday calendar used in conjunction with a WEEKDAY_CALENDAR as input to INTERSECT makes it possible to restrict the workday calendar to the shifts as configured in the respective weekday calendar. The workday calendar gets its data on working days (and non-working days such as weekends and public holidays) from a given TFACS table. Also, the workday calendar only considers the years which have a corresponding entry in the TFACS table. Years which are not covered by the TFACS table are ignored (i.e., treated as invalid workdays) in all workday calendar based computations.

The workday calendar is used as input for operators like REMAP_TIMESTAMPS or ADD_WORKDAYS. In these contexts the calendar allows to restrict the accounted DATEs which are used for the date based calculations. By this, only valid workdays as configured in the workday calendar will be considered.

Syntax

We can create a new workday calendar by using either of the two syntaxes shown below. The first syntax creates a single workday calendar using a TFACS table and a TFACS entry identifier, whereas the second syntax creates multiple workday calendars based on IDENT column of TFACS table.

WORKDAY_CALENDAR ( TFACS_table, TFACS_entry_identifier )

  • TFACS_table: The name of the TFACS table in the data model. Must have at least the following attributes

    • IDENT: STRING column containing the TFACS entry identifier (e.g., to refer to a location-specific calendar).

    • JAHR: INT column containing the respective years covered by the TFACS table entries.

    • MON[01-12]: STRING columns containing the respective bit encoded days for each month (1: working day, 0: non-working day).

    For further information regarding the TFACS table, please consult the internet search engine of your choice.

  • TFACS_entry_identifier: The TFACS table entry ID for which to create the workday calendar.

WORKDAY_CALENDAR ( TFACS_table )

  • TFACS_table: Same as described previously.

Examples

[1] Here we use REMAP_TIMESTAMPS with a workday calendar based on a TFACS table for the years 1907, 1974, 2018 and 2019. We want to calculate the number of passed workdays since 1970-01-01 until the specified dates (here null and 1974-02-01). The first date is 'null' which always maps to null. The second input is the first of February in the year 1974. From 1970-01-01 until this date, there have been 22 valid workdays. This is because all the years between 1970 and 1974 are not covered by the TFACS table (and thus assumed to be invalid workdays). Only the days between 1974-01-01 and 1974-02-01 are used for the calculation. Within this date range, there are 22 valid workdays in Bavaria Germany.

Query

Column1

REMAP_TIMESTAMPS ( Table1.Column1 , 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'

1904

'0001101100111110011111001111100'

'11111001111100111110011111001'

'1111001111100111110011111001111'

'000011110011111001111100111110'

'0111110011101001111100011110011'

'101001111100111110011111001111'

'1001111100111110011111001111100'

'1111100111110001111001111100111'

'110011111001111100111110011111'

'0011111001111100111110011111001'

'011100111110011111001111100111'

'1100111110011111001111100011110'

'BAY'

1907

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111000'

'011110011111001111100111110011'

'0110011101001111100011110011101'

'001111100111110011111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1111001111100111110011111001111'

'000111110011111001111100111110'

'0111110011111001111100110010011'

'BAY'

1974

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111100'

'111110011110000111100111110011'

'0110011111001111100111010011111'

'000111100111010001111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111100111110111110011111001'

'1111001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

'BAY'

2018

'0111100111110011111001111100111'

'1100111110011111001111100111'

'1100111110011111001111100111100'

'001111001111100111110011111001'

'0111001110100111110001111001110'

'100111110011111001111100111110'

'0111110011111001111100111110011'

'1110011111001101100111110011111'

'001111100111110011111001111100'

'1101100111110011111001111100111'

'010011111001111100111110011111'

'0011111001111100111110010011001'

'BAY'

2019

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111000111110011111001111100'

'111110011111001111000011110011'

'0110011111001111100111110011101'

'001111100011110011101001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1101001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

Table1

Column1 : DATE

null

Fri Feb 01 1974 00:00:00.000

Result

Column1 : INT

null

22

Note

The provided workday calendar contains one or more gaps between its start and end year. Please note, that all the missing years (i.e., the gaps) are ignored for calendar calculations. Affected IDENT value: BAY.

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

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 years 1907, 1974, 2018 and 2019. Some inputs are not within the scope of the TFACS configuration and thus mapped to null.

Query

Column1

ADD_WORKDAYS ( WORKDAY_CALENDAR ( "TFACS" , 'BAY' ) , "Table1"."DATES" , "Table1"."NUMBER_OF_DAYS" )

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'

1904

'0001101100111110011111001111100'

'11111001111100111110011111001'

'1111001111100111110011111001111'

'000011110011111001111100111110'

'0111110011101001111100011110011'

'101001111100111110011111001111'

'1001111100111110011111001111100'

'1111100111110001111001111100111'

'110011111001111100111110011111'

'0011111001111100111110011111001'

'011100111110011111001111100111'

'1100111110011111001111100011110'

'BAY'

1905

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1906

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1907

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111000'

'011110011111001111100111110011'

'0110011101001111100011110011101'

'001111100111110011111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1111001111100111110011111001111'

'000111110011111001111100111110'

'0111110011111001111100110010011'

'BAY'

1908

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1909

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1910

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1911

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1912

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1913

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1914

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1915

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1916

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1917

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1918

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1919

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1920

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1921

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1922

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1923

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1924

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1925

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1926

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1927

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1928

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1929

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1930

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1931

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1932

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1933

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1934

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1935

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1936

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1937

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1938

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1939

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1940

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1941

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1942

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1943

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1944

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1945

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1946

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1947

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1948

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1949

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1950

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1951

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1952

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1953

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1954

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1955

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1956

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1957

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1958

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1959

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1960

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1961

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1962

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1963

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1964

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1965

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1966

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1967

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1968

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1969

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1970

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1971

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1972

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1973

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1974

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111100'

'111110011110000111100111110011'

'0110011111001111100111010011111'

'000111100111010001111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111100111110111110011111001'

'1111001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

'BAY'

1975

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1976

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1977

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1978

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1979

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1980

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1981

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1982

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1983

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1984

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1985

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1986

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1987

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1988

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1989

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1990

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1991

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1992

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1993

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1994

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1995

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1996

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1997

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1998

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

1999

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

2000

'0000000000000000000000000000000'

'00000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

2001

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

2002

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'BAY'

2003

'0000000000000000000000000000000'

'0000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

'000000000000000000000000000000'

'0000000000000000000000000000000'

Table1

DATES : DATE

NUMBER_OF_DAYS : INT

Tue Dec 01 1903 00:00:00.000

-1

Sat Dec 01 1906 00:00:00.000

19

Wed Jan 01 2020 00:00:00.000

-1

Wed Jan 01 2020 00:00:00.000

19

Fri Jan 01 1904 00:00:00.000

-10

Tue Jan 01 1907 00:00:00.000

10

Mon Dec 31 2018 00:00:00.000

-10

Mon Dec 31 2018 00:00:00.000

10

Thu Jan 01 2015 00:00:00.000

10

Wed Dec 31 1975 00:00:00.000

-10

Result

Column1 : DATE

null

Mon Jan 28 1907 00:00:00.000

Tue Dec 31 2019 00:00:00.000

null

null

Tue Jan 15 1907 00:00:00.000

Thu Dec 13 2018 00:00:00.000

Tue Jan 15 2019 00:00:00.000

Mon Jan 15 2018 00:00:00.000

Mon Dec 16 1974 00:00:00.000

Note

ADD_WORKDAYS: Adding '-10' workdays to the given start date ('1904-01-01T00:00:00') leads to a date not covered by the configured scope of the workday calendar. Returning NULL instead.

Note

ADD_WORKDAYS: The start date ('1903-12-01T00:00:00') passed to ADD_WORKDAYS is before the smallest year ('1904') configured in the workday calendar. Therefore, it is not possible to add '-1' workdays to the start date as these days are not covered by the workday calendar. Returning NULL instead.

Note

ADD_WORKDAYS: The start date ('2020-01-01T00:00:00') passed to ADD_WORKDAYS is after the highest year ('2019') configured in the workday calendar. Therefore, it is not possible to add '19' workdays to the start date as these days are not covered by the workday calendar. Returning NULL instead.

[4] 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

[5] WORKDAY_CALENDAR(TFACS_table) syntax. This is used in REMAP_TIMESTAMPS along with a Calendar IDs column. Workday Calendar is created for different identifiers present in the Calendar IDs column.

Query

Column1

REMAP_TIMESTAMPS ( "Table1"."Column1" , DAYS , WORKDAY_CALENDAR ( "TFACS" ) , "Table1"."Ident" )

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'

1904

'0001101100111110011111001111100'

'11111001111100111110011111001'

'1111001111100111110011111001111'

'000011110011111001111100111110'

'0111110011101001111100011110011'

'101001111100111110011111001111'

'1001111100111110011111001111100'

'1111100111110001111001111100111'

'110011111001111100111110011111'

'0011111001111100111110011111001'

'011100111110011111001111100111'

'1100111110011111001111100011110'

'BAY'

1907

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111000'

'011110011111001111100111110011'

'0110011101001111100011110011101'

'001111100111110011111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1111001111100111110011111001111'

'000111110011111001111100111110'

'0111110011111001111100110010011'

'BAY'

1974

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111100'

'111110011110000111100111110011'

'0110011111001111100111010011111'

'000111100111010001111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111100111110111110011111001'

'1111001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

'BAY'

2018

'0111100111110011111001111100111'

'1100111110011111001111100111'

'1100111110011111001111100111100'

'001111001111100111110011111001'

'0111001110100111110001111001110'

'100111110011111001111100111110'

'0111110011111001111100111110011'

'1110011111001101100111110011111'

'001111100111110011111001111100'

'1101100111110011111001111100111'

'010011111001111100111110011111'

'0011111001111100111110010011001'

'BAY'

2019

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111000111110011111001111100'

'111110011111001111000011110011'

'0110011111001111100111110011101'

'001111100011110011101001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1101001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

Table1

Column1 : DATE

Ident : STRING

null

'BAY'

Fri Feb 01 1974 00:00:00.000

'BAY'

Result

Column1 : INT

null

22

Note

The provided workday calendar contains one or more gaps between its start and end year. Please note, that all the missing years (i.e., the gaps) are ignored for calendar calculations. Affected IDENT value: BAY

[6] Calendar id column and the timestamp column should have a join relation for the operator to work.

Query

Query

TABLE ( REMAP_TIMESTAMPS ( "Table1"."Column1" , DAYS , WORKDAY_CALENDAR ( "TFACS" ) , "TFACS"."Ident" ) )

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'

1904

'0001101100111110011111001111100'

'11111001111100111110011111001'

'1111001111100111110011111001111'

'000011110011111001111100111110'

'0111110011101001111100011110011'

'101001111100111110011111001111'

'1001111100111110011111001111100'

'1111100111110001111001111100111'

'110011111001111100111110011111'

'0011111001111100111110011111001'

'011100111110011111001111100111'

'1100111110011111001111100011110'

'DE'

1907

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111000'

'011110011111001111100111110011'

'0110011101001111100011110011101'

'001111100111110011111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1111001111100111110011111001111'

'000111110011111001111100111110'

'0111110011111001111100110010011'

'JPN'

1974

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111100'

'111110011110000111100111110011'

'0110011111001111100111010011111'

'000111100111010001111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111100111110111110011111001'

'1111001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

'BAY'

2018

'0111100111110011111001111100111'

'1100111110011111001111100111'

'1100111110011111001111100111100'

'001111001111100111110011111001'

'0111001110100111110001111001110'

'100111110011111001111100111110'

'0111110011111001111100111110011'

'1110011111001101100111110011111'

'001111100111110011111001111100'

'1101100111110011111001111100111'

'010011111001111100111110011111'

'0011111001111100111110010011001'

'DE'

2019

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111000111110011111001111100'

'111110011111001111000011110011'

'0110011111001111100111110011101'

'001111100011110011101001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1101001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

'JPN'

1904

'0001101100111110011111001111100'

'11111001111100111110011111001'

'1111001111100111110011111001111'

'000011110011111001111100111110'

'0111110011101001111100011110011'

'101001111100111110011111001111'

'1001111100111110011111001111100'

'1111100111110001111001111100111'

'110011111001111100111110011111'

'0011111001111100111110011111001'

'011100111110011111001111100111'

'1100111110011111001111100011110'

'BAY'

1907

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111000'

'011110011111001111100111110011'

'0110011101001111100011110011101'

'001111100111110011111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1111001111100111110011111001111'

'000111110011111001111100111110'

'0111110011111001111100110010011'

'DE'

1974

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111100'

'111110011110000111100111110011'

'0110011111001111100111010011111'

'000111100111010001111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111100111110111110011111001'

'1111001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

'JPN'

2018

'0111100111110011111001111100111'

'1100111110011111001111100111'

'1100111110011111001111100111100'

'001111001111100111110011111001'

'0111001110100111110001111001110'

'100111110011111001111100111110'

'0111110011111001111100111110011'

'1110011111001101100111110011111'

'001111100111110011111001111100'

'1101100111110011111001111100111'

'010011111001111100111110011111'

'0011111001111100111110010011001'

'BAY'

2019

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111000111110011111001111100'

'111110011111001111000011110011'

'0110011111001111100111110011101'

'001111100011110011101001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1101001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

'DE'

1904

'0001101100111110011111001111100'

'11111001111100111110011111001'

'1111001111100111110011111001111'

'000011110011111001111100111110'

'0111110011101001111100011110011'

'101001111100111110011111001111'

'1001111100111110011111001111100'

'1111100111110001111001111100111'

'110011111001111100111110011111'

'0011111001111100111110011111001'

'011100111110011111001111100111'

'1100111110011111001111100011110'

'JPN'

1907

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111000'

'011110011111001111100111110011'

'0110011101001111100011110011101'

'001111100111110011111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1111001111100111110011111001111'

'000111110011111001111100111110'

'0111110011111001111100110010011'

'BAY'

1974

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111100111110011111001111100'

'111110011110000111100111110011'

'0110011111001111100111010011111'

'000111100111010001111001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111100111110111110011111001'

'1111001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

'DE'

2018

'0111100111110011111001111100111'

'1100111110011111001111100111'

'1100111110011111001111100111100'

'001111001111100111110011111001'

'0111001110100111110001111001110'

'100111110011111001111100111110'

'0111110011111001111100111110011'

'1110011111001101100111110011111'

'001111100111110011111001111100'

'1101100111110011111001111100111'

'010011111001111100111110011111'

'0011111001111100111110010011001'

'JPN'

2019

'0111001111100111110011111001111'

'1001111100111110011111001111'

'1001111000111110011111001111100'

'111110011111001111000011110011'

'0110011111001111100111110011101'

'001111100011110011101001111100'

'1111100111110011111001111100111'

'1100111110011101001111100111110'

'011111001111100111110011111001'

'1101001111100111110011111001111'

'000111110011111001101100111110'

'0111110011111001111100110010011'

Table1

Column1 : DATE

Ident : STRING

null

'BAY'

Fri Feb 01 1974 00:00:00.000

'BAY'

Fri Feb 01 1974 00:00:00.000

'DE'

Fri Feb 01 1974 00:00:00.000

'JPN'

Warning

PQLException: To use the calendar ID column ["TFACS"."IDENT"], it must have a join relationship to the table containing the timestamp column ["Table1"].

See also: