Missing Values

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
FredFrinton 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 missin
g.
> 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
--|||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