How to check for overlaps in dates?

KNIME Analytics Platform
1

Hello guys,
I have a problem to solve: I have a file with payment transactions, start and end dates for each and transaction type. I need to check that there is no overlap in payments between type A and type B.
How do you suggest I achieve this? I am thinking of filtering type A into one table and type B into another and then compare the two but how? Say type A transaction is for Jan-Dec, while type B is for March of the same year. It is an overlap. Suggestions?

2

I would suggest transforming the dates into numbers
2019-02-01 => 20190201

and then do the split you have suggested and use a SQL database (H2 or SQLite) with an INNER JOIN with two ON conditions

ON t1.start_date >= t2.start_date
AND t1.end_date <= t2.end_date

So the ones that do match would be overlaps while the other ones would not.

3

As a generic approach

  1. Use Create Date&Time Range to create common time space.
  2. Use Rule Engine to create time segments corresponding to time frames.
  3. Use Joiner to join by dates time segments.
4

I ended up filtering out the list into 2 lists, then Joined the tables and ran date compare rules. Ended up easier than I thought (I was thinking of loops).

2 Likes
5

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.