Finding gaps (missing records) in database records using SQL

Question

I have a table with records for every consecutive hour. Each hour has some value. I want a T-SQL query to retrieve the missing records (missing hours, the gaps). So for the DDL below, I should get a record for missing hour 04/01/2010 02:00 AM (assuming 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

Stickseed

Answer 1

Assuming that all records are exact 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)

Eda

I cant up or down vote this becuase Im looking at this sql going .. gaaaaahhahahaaaa – Derringdo

That's a sweet query. I never realized WITH could be used recursively. – Deanedeaner

This is pretty slick. I still do a ton of 2000 work, so I haven't fully grokked what can be done with recursive CTEs. – Agenda

Answer 2

The only way I could see to solve this would be to create a table with all the dates you expect to have, and then perform a join to the table you want to check for gaps. You could create a function which takes 2 dates returns a table with all the hourly dates between those two dates so you don't have to create a new table for each time you want to find all the gaps in a given period.

This is a set based solution once you have the table with all the dates in it. I don't think there is a way to do this without first generating a table with the dates, and I'm pretty sure you can't do this in a set based way.

Haberdasher