Comparing Date Ranges from two Tables

KNIME Analytics Platform
1

Hello,

I have two tables. The tables both show: the Wellname, the Well ID, Start DateTime, End DateTime, and a comment. Both tables can have multiple rows for each well.

Essentially, I am trying to see if the date ranges from Table Number 1 are included in the date ranges in Table Number 2.  If the date range in Table Number 1 does not fall in the date range of Table number 2 (at least partially), kick this row out to a report.

I have been scratching my head on this problem for a while now. Please let me know if you can help.

If it makes it easier, I can create a "Source" column in both tables and then concatenate them into one individual table. If this was only one well, I know that I could use the binner dictionary node.

However, the issue is that there are multiple wells and the date ranges from table number 1 may fall in multiple date ranges in table number two.

 

Regards,

 

Michael

 

2

Michael,

To get started I would make sure to create unique RowIDs on both tables and only keep these IDs and the date columns. Then cross-join both tables and start a series of time-diff computations:

  • TD_1start_1end_1length
  • TD_2start_2end_2length
  • TD_1start_2start
  • TD_1start_2end
  • TD_1end_2start
  • TD_1end_2end

Based on these time difference columns you should have all the information necessary to determine which periods overlap or don't overlap, or to which absolute or relative extent they overlap.

HTH.

-E