Missing/overlapping time intervals 2.0

Summary

This operator compares time intervals of its input table to a set of intervals defined in another data node (interval node, see below). The interval node specifies valid intervals for each day of the week.

Its result table compares these two tables in different ways, depending on the settings. It can further allocate intervals of the input table to intervals of the interval node according to identifiers.

Configuration

Input settings of existing table

Parameter	Value	Opt.	Description	Example
Identifier	System.Object	opt.	One or more columns that contain identifiers of the data rows. If identifiers are defined here, they have to match the identifiers in the interval table (see parameter "Identified interval node" in the table below).The result table then allocates the intervals of the input table according to the intervals in the interval node with the same identifier(s).	-
Column from	System.DateTime	-	Date/Time column containing the start time and date of an interval.	-
Column to	System.DateTime	-	Date/Time column containing the end time and date of an interval.	-

Settings

Parameter	Value	Opt.	Description	Example
Result	System.String Overlapping intervals (first) Overlapping intervals (all) Missing intervals (view per row) Missing intervals (all) Times not included in intervals Classification of times	-	Information to be displayed in the result table. See below for a description of each of the options.	-
Time intervals data node	System.Int32	-	Reference to the interval node, see description above.	-
Identified interval node	System.String	opt.	One or more columns containing identifiers in the interval node. They have to match the identifiers defined in the specification of the relevant columns of the input table (see table above).	-
Interval Time From column	System.String	-	Date/Time column containing the start time and date of the intervals in the interval node.	-
Interval Time To column	System.String	-	Date/Time column containing the end time and date of the intervals in the interval node.	-
Weekdays column	System.String	-	Seven boolean columns of the interval node defining the validity of intevers for weekdays (see description of interval node above), in the order Mo, Tue, Wed, Thu, Fri, Sat, Sun. Optionally one more column added to the end defining validity of the interval on a holiday.	-
Same From and To time means	System.String 24 hours 0 hours (ignore interval)	-	Intervals that contain the same time in their "From" and "To" column can be interpreted in different ways. 24 hours: The interval spans to the following day. 0 hours: The interval is ignored.	-
Holidays data node	System.Int32	opt.	Reference to a data node containing a list of holidays.	-
Holiday column	System.String	opt.	Date column of the holiday node that contains the holidays.	-
Interval 23:00-06:00 means	System.String 00:00–06:00 and 23:00-24:00 same day 23:00-06:00 following day	-	The interval table may contain data rows that show an earlier time in their "To" column than in their "From" column. They can be interpreted in different ways. 00:00–06:00 and 23:00–24:00 same day: The interval is split into two intervals, one at the beginning, and the other at the end of the same day. 23:00–06:00 following day: The interval spans to the following day. (This distinction is important when intervals are only valid on some weekdays. If they are valid on all weekdays, the result will be the same.)	-
Generate blank row if nothing has been found	System.Boolean	-	If checked, a table containing a single row is returned instead of an empty table. This row contains NULL in numerical columns and date columns and the text defined in parameter "Text fields content for blank line" in text columns.	-
Text fields content for blank line	System.String	opt.	The text to be inserted in text columns if parameter "Generate blank row if nothing has been found" is checked.	-

Examples

Situation	We want to know how our work time records (input table) overlap with our core working hours (interval node). We have two groups of employees, office staff and production workers. The table below is our input table containing the work time records (data node A01 in project file below). It indicates the employee group in column A and the actual working intervals in columns B and C. Column D is not required, it is added for a better understanding of later examples. The next table is our interval table containing the core working hours (data node I01 in project file below). Note: The columns "From" ("Von") and "To" ("Bis") only specify times, not dates. Their creation date is added per default because the Date/Time format requires a date. Hence, in this table, the dates "06.04.2017" are ignored, only the times are relevant. The columns C to J indicate on which days of the week the intervals are valid. Column K indicates for which employee group the intervals are valid.
Settings	We set the columns A of the input table and K of the interval table as identifiers to allocate the data rows to the employee groups (operation in data node C01). We choose "Overlapping intervals (first)" for the parameter "Result" (German "Überlappende Intervalle" for "Ergebnis). Further, we specify the interval node "I01 Intervals" with its columns "From" ("Spalte Intervallzeit von"), "To" ("Spalte Intervallzeit bis") , and the columns indicating the validities per weekday. ("Spalten Wochentage").
Result	The result table contains one row for each overlap between an interval of the input table with an interval of the interval table with matching identifiers (data node C01). All fields of these rows of both tables are repeated. Additionally, the two columns "Result From" and "Result To" are added to indicate the overlap.
Project File	Confluence missing%2foverlapping op.gzip

Interval node and holiday node

The interval node is a different data node in the same project. It defines time intervals with a validity on specific days of the week and for holidays. The time intervals of the input table are compared to the intervals valid on the respective day.

The interval node must contain the following columns:

Name convention	Description	Type	Optional/required	Remark
From	Start time of the interval	Time	Required	Only the time is specified and relevant, but the creation date is added automatically. The date is ignored for all functionality of this operator.
To	End time of the interval	Time	Required	Only the time is specified and relevant, but the creation date is added automatically. The date is ignored for all functionality of this operator.
Mo	Validity of interval on Mondays	Boolean	Required
Tue	Validity of interval on Tuesdays	Boolean	Required
Wed	Validity of interval on Wednesdays	Boolean	Required
Thu	Validity of interval on Thursdays	Boolean	Required
Fri	Validity of interval on Fridays	Boolean	Required
Sat	Validity of interval on Saturdays	Boolean	Required
Sun	Validity of interval on Sundays	Boolean	Required
Holiday	Validity of interval on holidays	Boolean	Optional	Holidays are specified in a separate data node.

The order of the Boolean columns is compulsory.

The time in the "From" column can be later than or equal to the time in the "To" column. This way, intervals spanning over two days can be defined. In these cases, different interpretations are possible. The options can be chosen by setting the parameters "Same From and To time means" and "Interval 23:00-06:00 means", respectively.

Further, the interval table may contain one or more columns of identifiers. They can be used to distinguish, e.g., working times for different groups of employees.

Holidays are listed explicitly in a different data node. This data node's result table must contain a Date column. The data node and the date column in the data node are specified in the operator's parameters "Holidays data node" and "Holiday column", respectively.

Classification of interval relations

An interval specified in the input table can have one or more of the following relations to an interval specified in the interval table.

English name

German name

Description

Example

Overlapping intervals

Überlappende Intervalle

The input interval overlaps with an interval from the interval table

Indicates the overlap.

Interval 17:00 to 19:00 from the input table overlaps with the interval 14:00 to 18:00 from the interval table. The overlap is from 17:00 to 18:00.

Missing intervals

Fehlende Intervalle

The input interval overlaps only with parts of an interval from the interval table or it does not overlap at all with an interval of the interval table.

Indicates (part of) the not-overlapped interval from the interval table.

In row 1, the input interval 10:00 to 13:00 does not overlap the interval 05:00 to 09:00 from the interval table. The whole interval 05:00 to 09:00 is missed by the input interval.

In row 5, the input interval 04:00 to 08:00 overlaps only parts of interval 05:00 to 09:00 from the interval table. It misses 08:00 to 09:00

Times not included in intervals

Zeiten kommen in Intervallen nicht vor

The input interval overlaps only with parts of an interval from the interval table or it does not overlap at all with an interval of the interval table.

Indicates (part of) the not-overlapped interval from the input table.

In row 3, the interval 4:00 to 5:00, which is part of the input interval 4:00 to 8:00 does not overlap with any interval from the interval table.

Want to learn more?

This operator computes overlaps between a Durations table (see TIS 6.0 Types of Tables) and a set of intervals defined in the interval node (see above). The settings define the (parts of the) input table, the (parts of the) interval table and the holiday table, and the columns and calculations in the result table.

Result options and result table

Independently of the result option, the result table contains

all columns of the input table in their order,
all columns of the interval table in their order,
one DateTime column "Result From", and
one DateTime column " Result To"

in this order.

The value of the parameter "Result" decides which rows of the input table are contained in the result table, with which rows of the interval table they are combined, and the information shown in the "Result From" and "Result To" columns.

Overlapping (first)

These option computes the intervals and parts of intervals where data from the input table and data from the interval table overlap.

The result table contains one row for each overlap between the input intervals and the intervals of the interval table.

It contains more than one row for one input interval, if this interval overlaps with more than one interval of the interval table.
However, if an input interval overlaps with more than one interval of the interval table and these intervals of the interval tables have the same "From" time and the same "To" time, then only the overlap with the first interval, with respect to the ordering of the rows in the interval table, is contained in the result table.

The result table copies all fields of the respective row in the input table and all fields of the row in the interval table that contains the interval it overlaps with. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the overlap.

See also "Overlapping intervals" in the classification of interval relations above. Table below shows result table of data node C02 of project file.

Overlapping (all)

These option computes the intervals and parts of intervals where data from the input table and data from the interval table overlap.

The result table contains one row for each overlap between the input intervals and the intervals of the interval table.

It contains more than one row for one input interval, if this interval overlaps with more than one interval of the interval table, regardless of the double intervals having the same "From" and "To" times (this is the difference to the option Overlapping (first)).

The result table copies all fields of the respective row in the input table and all fields of the row in the interval table that contains the interval it overlaps with. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the overlap.

See also "Overlapping intervals" in the classification of interval relations above. Table below shows result table of data node C03 of project file. Note row 2 of this table, which is not included in the table computed with option "Overlapping (first)" above.

Missing intervals (view by row)

This option computes the intervals and parts of intervals of the interval table not overlapped by intervals of the input table. It considers for each entry in the input table all intervals valid for that day.

The result table contains one row for each input interval and interval of the interval table valid for the respective day and not at all or not completely overlapped by the input interval. It hence contains more than one row for one input interval if there is more than one interval in the interval table valid on the day which the input interval does not overlap.

The result table copies all fields of the respective rows of both the input table and the interval table. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the (part of the) interval that is not overlapped by the input data on the respective day.

See also "Missing intervals" in the classification of interval relations above. Table below shows result table of data node C04 of project file.

Missing intervals (all)

This option computes intervals and parts of intervals of the interval table not overlapped by intervals of the input table, like option "Missing intervals (view by row). The difference is that it considers for each day between and including the earliest and the latest day of the input table each interval valid on that day.

The result table contains one row for each interval of the interval table for each day for which it is valid between the earliest and the latest day of the input table where it is not completely covered by an interval of the input table.

The result table fills all fields of the input table with "-" and copies those of the interval table. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the (part of the) interval that is not overlapped by the input interval on the respective day.

See also "Missing intervals" in the classification of interval relations above. Table below shows result table of data node C05 of project file.

Times not included in intervals

This option computes intervals of the input table not overlapped by intervals of the interval table (the inverse of option "Missing intervals (view by row)").

The result table contains one row for each input interval that is not fully overlapped by an interval of the interval table. It hence contains at most one row for each row of the input table. It copies the rows of the input table and fills all fields of the interval table with "-". The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the (part of the) input interval not overlapped by an interval of the interval table.

See also "Times not included in intervals" in the classification of interval relations above. Table below shows result table of data node C06 of project file.

Classification of times

This option considers each row of the input intervals and classifies its interval according to the classifications described above ("Classification of interval relations").

The result table contains one row for each input interval and its classification(s). Hence, if an input interval falls into more than one classification, then the result table contains more than one row for this interval. The "Result From" and "Result To" columns contain the start and the end time of different intervals depending on its classification:

Overlapping intervals (Überlappende Intervalle): Start time and end time of the overlap inside the interval of the interval table.
Missing intervals (Fehlende Intervalle): Start time and end time of the non-overlap inside the interval of the interval table.
Times not included in intervals (Zeiten kommen in Intervallen nicht vor): Start time and end time of the non-overlap inside the input interval.

Table below shows result table of data node C07 of project file.

Examples

Introduction

The following examples refer to the following interval table, holiday table, and input table, unless indicated otherwise. The examples are contained in this project and can be imported into TIS. Confluence missing%2foverlapping op.gzip

Interval table (data node "Interval tables/I01 Intervals", dates blanked out for better readability as they are not relevant)

Holiday table (data node "Interval tables/I02 Holidays")

Input table (data node "Raw data/A02 simple with missing days"

Overlap (first)

Data node	Settings	Result	Remarks
Examples/E02			"Result From" and "Result To" fields indicate the overlap between the columns B and C (input interval) and columns E and F (interval from interval table).

Overlap (all)

Data node

Settings

Result

Remarks

Examples/E03

"Result From" and "Result To" fields indicate the overlap between the columns B and C (input interval) and columns E and F (interval from interval table).

Rows 1 and 2 contain an interval from the interval table with the same "From" and "To" times, both valid on Wednesday. Other than for option "Overlap (first)", here they are both included in the result table.