Jump to content
We've recently updated our Privacy Statement, available here. ×

Comparing time ranges from 2 different tables

Wim Broens

Recommended Posts


I am trying to find a solution for following situation.

I have 2 tables:

A: Is showing a number of tasks that have a start and stop time and a type of task. It can be UP or DOWN

B: Is showing another list of task that only have start and stop time.

What I want to achieve is adding an extra column in table A that indicates how much time (minutes) overlaps a DOWN task overlaps with a task in table B

Following drawing can explain it a little bit more:




I am starting to learn Rscripting.

If somebody have a solution or can at least push me in the right directory, I am happy guy



Link to comment
Share on other sites


this works if the second table does not contain rows that overlap in time. If so, you will have to modify the code slightly, and also best to output a separate table, as the new 'Overlap' column would contain more rows than the original table, if the same task overlaps with more than one loss, and you would want to specify which lossnr it overlaps with.

It is based on the logic of a previous community answer:


Comments are within the code. Basically the code enumerates both tables into sequences of minutes, then merges them, counts the minutes that appear in both tables, and outputs the result as a new column, Overlap, to the Tasks table.

Make sure that the columns sent into the data function for the first table do not contain Overlap, when already defined, otherwise it causes a cyclic dependency.

It depends on the library data.table being installed. You can either install it with the TERR Tools interface, or uncomment the piece of code, or if you are using TSSS have it installed on the server.

I am uploading the code as well in case you cannot load the dxp.


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...