Finding gaps (missing records) in database records using SQL
NickName:Tony_Henrich Ask DateTime:2010-04-23T01:50:36

Finding gaps (missing records) in database records using SQL

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

Copyright Notice:Content Author:「Tony_Henrich」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/2693171/finding-gaps-missing-records-in-database-records-using-sql

Answers
Quassnoi 2010-04-22T18:00:00

Assuming that all records are exact hours:\n\nWITH q(s, e) AS\n (\n SELECT MIN(StartDate), MAX(StartDate)\n FROM Readings\n UNION ALL\n SELECT DATEADD(hour, 1, s), e\n FROM q\n WHERE s < e\n )\nSELECT *\nFROM q\nWHERE s NOT IN\n (\n SELECT StartDate\n FROM Readings\n )\nOPTION (MAXRECURSION 0)\n",


More about “Finding gaps (missing records) in database records using SQL” related questions

Finding gaps (missing records) in database records using SQL

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

Show Detail

Finding gaps in records

I have a Oracle table that have placard numbers, timestamps and direction, either IN or OUT. Sometimes, the readers fail, and there are gaps, For the same placard number I should have: Placard

Show Detail

Moving records from SQL Server 2014 to 2005 database

I have 917419 records in a table in a SQL Server 2014 database. I want to move theses records into a SQL Server 2005 database. I have generated script into a file using "generate and publish wiza...

Show Detail

Missing records in SQL server tables

I have a database in place with a client that seems to lose data overnight. They enter records and exit the system, and then claim to not be able to find them again the next day. The ID numbers in...

Show Detail

SQL Finding records with one value

I'm a newbie on SQL hence my limitation on coding it. This question is similar to this previous questions: SQL Finding Duplicate Values of Rows Where Another Field has a Value and Oracle/SQL - Find...

Show Detail

Insert missing records

I have a sql server 2008 r2 table that contains thousands of records which have been updated by a separate program. Due to an oversight there are a series of missing records. I need to insert the

Show Detail

database records missing randomly in mysql

I am using joomla as CMS I have a system of creating users from the back end and give those login information to the users to log in our site we are following this system as we only serve a selected

Show Detail

Java code for comparing two mongodb databases and finding the missing records in them

Java code for comparing two mongodb databases and finding the missing records in them please share the code Java code for comparing two mongodb databases and finding the missing records in them

Show Detail

Some records missing while insert into DB2/400 database using C#

We have web site developed using C# and hosted on IIS, which inserts records into a IBM DB2/400 database. The connection is made using 'iSeries Access ODBC Driver'. The records are inserted with i...

Show Detail

Script to delete records from SQL Server database on Azure using webjobs

I want to run the following command daily on my Microsoft SQL Server database on free Azure portal account, to delete records older than 7 days from now. USE [myMSSqlDatabaseName] GO DELETE FROM ...

Show Detail