Skip to main content

Detect Overlapping Intervals

Let’s see how Query Xtractor can help you solve the typical SQL problem of detecting overlapping datetime intervals. We have a table of projects employees are working on for a period of time, between a start and end date. It is indeed very hard to figure out from the results grid how large each period of time is and how they compare to each other.

overlapping-1

Overlapping Projects per Employee

A query that may solve our problem is presented below. This shows all pairs of projects that overlap, for every employee working on the projects. It’s still a bit hard to properly interpret the results, but looking at the two queries we may see that:

  • For CLARK, only project 1 does not overlap with any of his other projects (7, 4, 10 and 13).
  • KING has overlapping projects 5, 2, 8. Projects 14 and 11 do not overlap.
  • MILLER has two separate pairs of overlapping projects: 6 with 3, and 12 with 9. His project 15 is separate.

overlapping-4

Visual Interval Effect

Clone your initial first query and adapt it quickly as below. What you do is create a StartDate .. EndDate interval, on which you may apply an Interval Bar visual effect! This may work as well with any other pairs of dates or numbers, and the ” .. ” separator is customizable (look for the Interval Bar Separator option).

overlapping-3

The new image speaks for itself. You may instantly see all we concluded before through a query:

  • how project 1 is the only one of CLARK not overlapping.
  • how KING worked at the end, for shorter period of times, on non-overlapping projects 14 and 11.
  • how MILLER’s project 15 is separate from his other two pair.

However, this simple image tells you way more. It shows you how large each period is and how in time they relate to each other.

Conclusion

Don’t stop at the simple rectangular display of your data. Explore drill-down on results, client-side summaries and highlights, and format. And, last but not least, take advantage of powerful visual effects, with one simple combo selection. They are not just fancy, they may provide powerful additional information about your data.

Leave a Reply

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close