Finding gaps (missing records) in database records using SQL
Posted
by Tony_Henrich
on Stack Overflow
See other posts from Stack Overflow
or by Tony_Henrich
Published on 2010-04-22T17:50:36Z
Indexed on
2010/04/22
17:53 UTC
Read the original article
Hit count: 293
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
© Stack Overflow or respective owner
