CODEWITHSUNDEEP
sql
Matt Porterfield
Matt Porterfield

Reputation: 123

How to find missing records in one table

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.

Upvotes: 1

Views: 9186

Answers (1)

void
void

Reputation: 7890

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

Upvotes: 1

Related Questions