create table testTable (i int) 
go

insert testTable 
    select 1
    union select 2
    union select 3
    union select 4
    union select 6
    union select 7
    union select 10
    union select 11
    union select 15
    union select 21
go

select  'there is a gap between ' 
+       convert(varchar, l.i) 
+       ' and ' 
+       (select convert(varchar, min(i)) from testTable where i > l.i)
from    testTable l
left    join (select i from testTable ) r
        on l.i + 1 = r.i 
where   r.i IS NULL
and     l.i < (select max(i) from testTable)
order   by l.i

select  'there is a gap between ' 
+       convert(varchar, l.i) 
+       ' and ' 
+       (select convert(varchar, min(i)) from testTable where i > l.i)
from    testTable l
where   not exists (
        select 47
        from testTable r
        where l.i + 1 = r.i )
and     l.i < (select max(i) from testTable)
order   by l.i

go
drop table testTable
go

quote:

---

OK, here's an alternate and shorter very quick way to spot gaps:


SELECT "Gap between " & x.Value & " and " & Max(y.value) AS Message
FROM Vals AS y, Vals AS x
WHERE y.Value < x.[value]
GROUP BY x.Value
HAVING Max(y.Value) <> x.value - 1


If you are using dates, just edit the "x.value - 1" as needed. For example, DateAdd(Day,-1,x.value).

I think this takes care of all cases ..... Have fun!

---

create table vals(i int primary key, d varchar(10))

GO
declare @i int;
set @i=0
set nocount on
while @i < 14000
	begin
	if @i % 424 <> 0 insert into vals(i, d) values(@i, 'dummy data')
	set @i = @i+1
	end
set nocount off
GO

-- my "new" way:

select v1,v2 from
(
SELECT x.i v1, (select max(y.i) from vals y where y.i < x.i) as v2
from vals x
) a
where v1 <> v2+1

-- NR's method:

SELECT x.i, y.i
FROM Vals y, Vals x 
WHERE y.i = (select max(z.i) from Vals z where z.i < x.i)
and y.i <> x.i - 1

GO

drop table vals