How to find Missing numbers between the range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to find Missing numbers between the range

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-31 : 07:39:58

An employee table contains 35000 employes information with Empid, Empname.
EmpID Ranges between 1401 and 36400. The Employee Id's are also not in sequence
i.e The id's will be like this 1401,1432,1603.....36400(doesn't follow any order. But all employee id'sa re in this range).
The EmpId is niether primary key nor identity column.
The whole aim is need to find out the missing numbers in range 1400 to 36400.
Not only this range..i want more customize this. What ever the range i given to query, it has to get the missing id's.

How can i write a query for this..

Marshy
Starting Member

2 Posts

Posted - 2009-08-31 : 08:30:46

Try this:

IF OBJECT_ID('TEMPDB..#TALLY') IS NOT NULL
DROP TABLE #TALLY

CREATE TABLE #TALLY ( ID INT )

DECLARE @counter int

SET @COUNTER = 1
WHILE @COUNTER <= 35000
BEGIN
INSERT INTO #TALLY ( ID )
VALUES ( @COUNTER )

SET @COUNTER = @COUNTER + 1
END

SELECT *
FROM #TALLY

SELECT A.*
FROM #TALLY A
LEFT JOIN tbl B ON A.ID = B.EmpID
WHERE B.EmpID IS NULL
AND A.ID BETWEEN 1400 AND 36400
ORDER BY A.ID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 08:34:28

Search for Find gaps+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources