Missing/overlapping time intervals
Summary
This operator takes a Durations table (see Types of Tables) as input table and compares its intervals 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.
Example
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 
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:0006: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 notoverlapped 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 notoverlapped 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. 
Settings
This operator computes overlaps between a Durations table (see 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.
Columns of input table
Parameters
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.
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 nonoverlap 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 nonoverlap 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. 
Missing intervals (view per row)
Data node  Settings  Result  Remarks 

Examples/E04  "Result From" and "Result To" fields indicate (parts of) the interval from columns E and F (interval table) not overlapped by the interval from columns B and C (input table). 
Missing intervals (all)
Data node  Settings  Result  Remarks 

Examples/E05  "Result From" and "Result To" fields indicate (parts of) the interval from columns E and F (interval table) not overlapped by any input interval. Data is shown for each day from the earliest day (07.02.2017) to the latest day (09.02.2017) of the input table. 
Times not included in intervals
Data node  Settings  Result  Remarks 

Examples/E06  "Result From" and "Result To" fields indicate (parts of ) the interval from columns B and C (input table) not overlapped by any interval from the interval table. 
Classification of times
Data node  Settings  Result  Remarks 

Examples/E07  "Result From" and "Result To" fields indicate (parts of) either the interval from columns B and C (input table) or columns E and F (interval table), depending on the classification of the interval (see "Classification of interval relations" above). 
Definition of holidays
Data node  Settings  Result  Remarks 

Examples/E08  This data node is the same as E07 except that a node defining holidays is included in the settings (node Intervals/I02). The result is the same except that row 1 of E07 is missing in E08 as this interval is not defined for holidays and 07.02.2017 is defined as holiday in node I02. 
Interval over midnight
Here, the interval node Intervals/I03 is used. It contains only the following interval.
Depending on the value of parameter "Interval 23:0006:00 means", the overlappings are computed in different ways.
Data node  Settings  Result  Remarks 

Examples/E09  The interval of the interval table only counts once and only overlaps with input intervals that start on the first day are considered. Note that in row 4, the time between 04:00 and 06:00 is not considered an overlap!  
Examples/E10  Here, the interval of the interval table is split into two intervals, one ending and one starting at midnight, both on the same day. Note that in row 6, the overlap between 04:00 and 06:00 is considered. 
Troubleshooting
Problem  Frequent Cause  Solutions 

... 
 ... 
Related topics
 ...