Missing records

I am trying to find missing Checks from Check Table (I know there are no
tables).
Here is a sample table (oops did it again) :):
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME =
'TestTable')
DROP table TestTable
CREATE TABLE [dbo].[TestTable] (
[SeqNumber] [int] NULL ,
[AName] [varchar] (30) NULL
) ON [PRIMARY]
GO
Insert TestTable Values (1,'Joe')
Insert TestTable Values (2,'Mary')
Insert TestTable Values (3,'Frank')
Insert TestTable Values (5,'Larry')
Insert TestTable Values (6,'Jerry')
Insert TestTable Values (7,'Greg')
Insert TestTable Values (10,'Ron')
Insert TestTable Values (11,'Pat')
Insert TestTable Values (12,'Beth')
Insert TestTable Values (13,'Lynn')
Insert TestTable Values (20,'Sharon')
go
Is there an easy way to do a select and come back with the missing sequences
(in this case - 4,8,9,14,15,16,17,18,19).
Thanks,
TomUse a table consisting of all the possible check numbers:
SELECT N.num
FROM numbers AS N
LEFT JOIN testtable AS T
ON N.num = T.seqnumber
WHERE T.seqnumber IS NULL
AND N.num BETWEEN 1 AND 20 ;
Or, if you prefer to see ranges of numbers:
SELECT T1.seqnumber+1 AS missing_from,
MIN(T2.seqnumber)-1 AS missing_to
FROM testtable AS T1
JOIN testtable AS T2
ON T1.seqnumber < T2.seqnumber
GROUP BY T1.seqnumber
HAVING T1.seqnumber < MIN(T2.seqnumber)-1 ;
David Portas
SQL Server MVP
--