Missing Date Ranges- the Sequel

Alex Kozak returns with another Date puzzle. A readers question gives Alex the inspiration to see if is possible to list unused date ranges in one Select statement.

Some time ago, I published the article “Find Missing Date Ranges in SQL”: . In that article, I showed how to find missing dates, using different SQL techniques.

A few months later, I received a message from a Simple-Talk reader, who thought he had a similar problem, and asked me for help. Here is the reader’s problem:

‘There is a table, where two of the columns are DateFrom and DateTo. Both columns contain date and time values. How does one find the missing date ranges or, in other words, all the date ranges that are not covered by any of the entries in the table’.

At first glance, the reader’s problem and the one on my article look similar; they both involve finding missing, or unused, date ranges. However, they are not the same. In the article, a hypothetical application registers the moments (time) of events and stores them in a database (table). In the reader’s problem, some imaginary application records the beginning and the end of the processes, representing them as continuous date/time ranges.

The second task may become more complicated, if the processes can overlap each other in time.

The following diagram illustrates the reader’s problem (see Fig.1):

Fig.1 The reader’s problem

As shown on the diagram, there is only one missing date range: from Jan 24 to Jan 31.

Without the process that started on Jan 01 and completed on Jan 22, shown in green in the diagram, there would be three missing date ranges:

  1. Jan 04 – Jan 04;
  2. Jan 11 – Jan 19;
  3. Jan 24 – Jan 31;

Notice, that a special case, when the process started, but did not finish yet, can be treated as a process with the end time equal to the current time.

For example, if the process started on June 10 and still running on June 14 (when you execute the query), you can consider that process as a process that started on June 10 and finished on June 14. In other words, dates in the range from June 10 to June 14 are covered (exist).

Solution with Auxiliary Table

Before we explore the fist solution, let us create and load sample table (see Listing1):

Listing1. Create and load sample table

Fig.2 The Test data

Then, using an auxiliary table you can easily find missing dates (see Listing2):

Listing2. Find missing dates, using an auxiliary table

The same approach can be used, if you need to find missing hours, minutes or seconds. For example, for missing minutes, you need to make a small change in DATEADD() function in common table expression (see Listing3):

Listing3. Solution for missing minutes with auxiliary table

However, the solutions, as shown in  Listing2 and Listing3, are not the answers for the reader’s problem. This will  require finding the missing, or unused, date ranges, but not missing, or unused,  dates.

One possible way to solve that problem is to transform already found missing dates into missing date ranges.

In order to test that solution, you will need to:

  1. Create and load an auxiliary table dateSequence as shown in the Listing2
  2. Run following query (see Listing 4)

Listing4. Find missing date ranges, using an auxiliary table

There is, however, at least one way to solve the reader’s problem in one query and without an auxiliary table.

The ‘Missing Date Ranges’ Puzzle

Try to find the solution for the reader’s problem. Consider it as a puzzle with the following requirements:

  • You have a sample table dateRanges with two date/time columns dateFrom and dateTo (see Listing1)
  • You need to find the missing date ranges (that were not used in any rows), where the format of result and the result should be the same as in the Listing4.
  • The solution should be one-query solution without an auxiliary table.
  • It should work in SQL Server 2005 and in SQL Server 2000.

The best solution will be awarded with a $50 Amazon voucher.

I will post my solution as comments in a couple of weeks.