Finding spaces (missing records) in database records using SQL

Question

I have a table with entries for each consecutive hour. Every hour has some meaning. I want the T-SQL query to retrieve the missing records (missing hours, spaces). So, for DDL below, I should get a record for the missing hour 04/01/2010 02:00 (assuming the date range is between the first and last record). Using SQL Server 2005. Prefer a set-based query.

DDL: CREATE TABLE [Readings]( [StartDate] [datetime] NOT NULL, [SomeValue] [int] NOT NULL ) INSERT INTO [Readings]([StartDate], [SomeValue]) SELECT '20100401 00:00:00.000', 2 UNION ALL SELECT '20100401 01:00:00.000', 3 UNION ALL SELECT '20100401 03:00:00.000', 45 

Answer 1

Assuming all entries are accurate hours:

 WITH q(s, e) AS ( SELECT MIN(StartDate), MAX(StartDate) FROM Readings UNION ALL SELECT DATEADD(hour, 1, s), e FROM q WHERE s < e ) SELECT * FROM q WHERE s NOT IN ( SELECT StartDate FROM Readings ) OPTION (MAXRECURSION 0) 

Answer 2

The only way I could solve this problem is to create a table with all the dates you expect to have, and then connect to the table you want to check for spaces. You can create a function that takes 2 dates, returns a table with all hourly dates between the two dates, so you do not need to create a new table for every time you want to find all the spaces for a certain period.

This is a dial-based solution when you have a table with all the dates in it. I don’t think there is a way to do this without creating a table with dates, and I’m sure you cannot do it based on a set.