Q

Finding gaps in column of sequential numbers

How do I write a routine that will find gaps in a column of sequential numbers? For example: the employee table has a column named deptno (0001, 0002, 0005, etc,), and I want to find all the gaps.

The script below should help you with this. The performance of this is relative to the number of rows within your employee table.

For the purpose of this example I will create a fake employees table. In real life you would replace this with your existing one.

 create table dbo.EmployeesExample ( EmpID char(4) PRIMARY KEY, FirstName varchar(50), LastName varchar(50) ) insert dbo.EmployeesExample VALUES('0001','Laura','Jones') insert dbo.EmployeesExample VALUES('0003','Stephanie','Smith') insert dbo.EmployeesExample VALUES('0017','William','Black') insert dbo.EmployeesExample VALUES('0020','Linda','White')

Now this is the code that finds the missing values.

 create table #MissingIDS ( EmpId char(4) PRIMARY KEY ) declare @maxid int, @currentid int --Find the current maximum employee id SELECT @maxid=max(cast(empid as int)) FROM dbo.EmployeesExample select @currentid=1 --Loop around sequentially populating the temporary table with every --possible employee id while @currentid<@maxid begin insert #MissingIDS select right('0000'+cast(@currentid as varchar(4)),4) select @currentid=@currentid+1 end --delete the existing employee ids from the temporary table --by joining to the Employees table delete #MissingIDS from #MissingIDS m inner join dbo.EmployeesExample e on m.empid=e.empid --And what you have left is the missing ids select * from #MissingIDS drop table #MissingIDS
A slight variation on this checks to see if the empid exists before inserting into the temporary table, thereby eliminating the need for the delete.

Now this is the code that finds the missing values.

 create table #MissingIDS ( EmpId char(4) PRIMARY KEY ) declare @maxid int, @currentid int --Find the current maximum employee id SELECT @maxid=max(cast(empid as int)) FROM dbo.EmployeesExample select @currentid=1 --Loop around sequentially populating the temporary table with every --possible employee id while @currentid<@maxid begin if not exists(select empid from dbo.EmployeesExample where EmpID=right('0000'+cast(@currentid as varchar(4)),4)) insert #MissingIDS select right('0000'+cast(@currentid as varchar(4)),4) select @currentid=@currentid+1 end --And what you have left is the missing ids select * from #MissingIDS drop table #MissingIDS

 

For More Information

This was first published in November 2003

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close