How to find missing records in one table

Question

I have one table with many records in the table. Each time a record is entered the date the record was entered is also saved in the table.

I need a query that will find all the missing records in the table.

So if I have this in my table:

ID          Date          Location
1           4/1/2015      bld1
2           4/2/2015      bld1
3           4/4/2015      bld1

I want to run a query like

Select Date, Location
FROM [table]
WHERE (Date Between '4/1/2015' and '4/4/2015') and (Location = bld1)
WHERE Date not in (Select Date, Location FROM [table])

and the results should be:

 4/3/2015   bld1

Thank You.

I mean which database system you are using? SQL Server, Oracle, PostgreSQL, MySQL,....!!??? — void, Apr 22 '15 at 01:22
You're going to need to populate another table (can be a #localtemptable or a @tablevariable if you like) with all the possible dates you want to allow. SQL doesn't automatically know which ones you want that aren't already there - that would include infinite rows. More details here: http://stackoverflow.com/questions/6125504/how-can-i-generate-a-temporary-table-filled-with-dates-in-sql-server-2000 — CactusCake, Apr 22 '15 at 01:33

void · Answer 1 · 2015-04-22T01:42:46.637

I guess you are using SQL Server, below is a solution for it, the method is usable for almost all of RDBMSs.

To find the gaps you need to have a sequence of dates between the min and max dates in your table, you can generate them with a recursive cte, finally left join the cte to your table and take the record where the values of the table is null:

Schema:

create table ttbl(ID int,[Date] date,Location varchar(10));
insert into ttbl values
(1,'4-1-2015','bld1'),
(2,'4-2-2015','bld1'),
(3,'4-4-2015','bld1');

Query:

declare @startDate date=(select min(date) from ttbl)
declare @endDate date=(select max(date) from ttbl)
--this generates the required sequence of dates between min and max dates in the table:
;with dts as 
(
     select @startDate dt
     union all
     select dateadd(dd, 1, d.dt) 
     from dts d
     where dateadd(dd, 1, d.dt) <= @endDate
   )
--this finds the gaps:
select d.dt,
       (select top 1 Location from ttbl) 
from dts d 
left join ttbl t on d.dt=t.date
where t.id is null

Output:

2015-04-03  bld1

How to find missing records in one table

1 Answers1