MS SQL Server - Missing Values

Author

Missing Values

Frinton

2006-02-17, 7:23 am

Hi,

Can anyone help me with a query?

Let say I have a table called tblTest with one int field called NumOfCall.
NumOfCall hold numbers in sequential order but some are missing. for
example the table has 100 records 1 - 105 and 4, 19, 32, 46, 86 are missing.
I need a query that will tell me what values are missing.

Thanks

Fred

David Portas

2006-02-17, 9:23 am

Frinton wrote:
> Hi,
>
> Can anyone help me with a query?
>
> Let say I have a table called tblTest with one int field called NumOfCall.
> NumOfCall hold numbers in sequential order but some are missing. for
> example the table has 100 records 1 - 105 and 4, 19, 32, 46, 86 are missing.
> I need a query that will tell me what values are missing.
>
> Thanks
>
> Fred

Use a table (Numbers) that contains all the potential numbers you want
to look for:

SELECT num
FROM Numbers AS N
WHERE NOT EXISTS
(SELECT *
FROM tblTest
WHERE numofcall = N.num)
AND N.num BETWEEN 1 AND 100 ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Anith Sen

2006-02-17, 1:23 pm

Another option is to use:

SELECT col + 1
FROM tbl
WHERE col < ( SELECT MAX( col ) FROM tbl )
AND NOT EXISTS( SELECT *
FROM tbl t1
WHERE t1.col = tbl.col + 1 ) ;

If you have series of missing numbers do:

SELECT start + 1, end - 1
FROM ( SELECT t1.col, MIN( t2.col )
FROM tbl t1
INNER JOIN tbl t2
WHERE t1.col < t2.col
GROUP BY t1.col ) D ( start, end )
WHERE start < end - 1 ;

--
Anith

Sponsored Links