Cart

Detect Overlapping Intervals

Published by Cristian Scutaru on

Let’s see how Visual 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.

Table of Contents

Overlapping Projects per Employee

overlapping-1

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.

ImportantData Xtractor includes all the functionality of Visual Xtractor, but you can also design the SQL queries with our builders, instead of typing them.

As a combination of Visual, Query and Model Xtractor, in Data Xtractor you may also create diagrams with Entity-Relationship models for your database.

Download Data Xtractor

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.

Categories: Data Visualization

Cristian Scutaru

I designed and implemented the Data Xtractor suite, with Model Xtractor, Query Xtractor, and Visual Xtractor as separate modules. I am a software architect and developer with over 30 years professional experience. I’ve been working with relational databases for almost three decades and I was constantly unhappy with the relative limitation of those tools used to connect directly to a platform, and instantly extract and display data in flexible ways.

Related Posts

Data Visualization

History of Charts: Who Invented This Chart Type?

Find out who created the Stream Graph or the Chord diagram, the Sparklines or the Bullet Graph, the Pie chart and the Bar chart. And why exactly we also know the Polar Area chart as Nightingale Rose. This is a list of data scientists and other influencers in data visualization, credited as inventors of a particular type of chart.

Data Visualization

Alternatives to Pie Charts, with FREE Cheat Sheet Infographic

If you are wondering about alternatives to Pie charts you may use, here is a detailed reference, with a short checklist for every transition to an equivalent or similar chart type. At the end, we share a cheat sheet infographic you may use for your projects. We limited this post to a typical single-series Pie chart with a small acceptable number of slices, having all values already sorted in descending order. Other specific Pie chart types, with just two slices, or with multiple side-by-side or nested pies, may be considered separately in another post.