Comparing Date Ranges from two Tables

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 entries 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, 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.

Best Regards,

 

Michael

 

Hi Michael,

this is not an easy one, but def, we can do this :-). Could you make me an example workflow and tell me which are the rows are the ones supposed to be in the output?

I would use the node "Binner Dictionary" for this. It can help you detect if a time column is between two other times. alternatively you can make a cross join on both tables and use a rule-based row filter to filter the non-matching ones?

Sorry, but I need an example to see which of my ideas would work best.

Cheers, Iris

Double posting it seems - see https://tech.knime.org/forum/knime-general/comparing-date-ranges-from-two-tables

I'll repost, too.... :-)

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