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.

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.