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.

Why BETWEEN is error prone

We do not use BETWEEN for now, and chances are we’ll never expose it as a direct operator in our query builder. Sure, there are pros and cons on using x BETWEEN a AND b rather then x >= a AND x <= b, and that’s one reason why we have this article. However, assuming our tool tries to avoids as much as possible error prone SQL constructs and the “element of surprise”, it seems like the bad won over the good. (more…)

Learn about ALL and SOME/ANY

Query Xtractor is mainly a productivity tool. But it can be also used to learn SQL. One particularly great way to do it is to turn ON one of the Always Emulate options. We have this for grouping sets, crosstabs, Intersect/Except, All/Some/Any. With the setting OFF, we use the native support when available, and we emulate features when there is no other way. The obvious advantage in having ALL/SOME/ANY expressions emulated as well for let’s say SQL Server – where they are recognized and implemented by the vendor – is to better understand what they actually do through equivalent constructs. (more…)

Intersect and Except

Not all database platforms support INTERSECT and EXCEPT (MINUS in Oracle), and MySQL is one of them. Let’s try to solve two simple related problems:

  • We want department numbers that appear in both Departments and Employees tables. In other words, we’re looking for all departments with at least one employee. This is a great candidate for INTERSECT, because we want numbers that exist in both tables.
  • We also want all department numbers that do not appear in Employees. In other words, we’re looking for departments with no employees. This is a great candidate for EXCEPT, because we want numbers that exist in only one table.

(more…)

Fastest way to build vendor-independent queries

Prepare to be amazed. Really.

What do you know about grouping sets? Not much?…

Let’s see how hard it is to build this query first. From the Databases tab, on the left, I right-click on the emp table and select Add to New Query. I keep checked only the DEPTNO, JOB and SAL shape items. I show only the Field, Lookup, Group, Sort and Format builder bars. I click on Page Size to turn paging off. (more…)