Checking consistance in a list of numbers 4

[darronb]

Hi there

I am a bit of a novice in this but hope you can help.

I am use SQL 2005 and have a table with 4000 lines in it. One of the columns should be a number count from 1 to 4000.

I would like to know how I could check with some sort of SQL script that there are no gaps within it, and if there are where the gaps are.

I know I could do a count but there are probably duplicates in there that I am not worried about.

Hope you can help.

 

[RiverGuy]

DECLARE @MissingValues TABLE (MissingValue INT)

DECLARE @i INT
SELECT @i = 1
WHILE @i <= 4000
BEGIN
	IF NOT EXISTS (SELECT 1 FROM SomeTable WHERE SomeColumn = @i)
	BEGIN
		INSERT INTO @MissingValues SELECT @i
	END
	SELECT @i = @i + 1
END

SELECT * FROM @MissingValues

 

[bborissov]

SELECT Table1.Range
FROM
(select b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range
from       (select 0 b1  union select 1    b1)  t1
cross join (select 0 b2  union select 2    b2)  t2
cross join (select 0 b3  union select 4    b3)  t3
cross join (select 0 b4  union select 8    b4)  t4
cross join (select 0 b5  union select 16   b5)  t5
cross join (select 0 b6  union select 32   b6)  t6
cross join (select 0 b7  union select 64   b7)  t7
cross join (select 0 b8  union select 128  b8)  t8
cross join (select 0 b9  union select 256  b9)  t9
cross join (select 0 b10 union select 512  b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= 4000) Table1
LEFT JOIN YourTable ON Table1.Range = YourTable.NumberColumn
WHERE YourTable.NumberColumn IS NULL

NOT TESTED!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RyanEK]

declare @test table (id int)
insert into @test values(1)
insert into @test values(2)
insert into @test values(4)
insert into @test values(5)
insert into @test values(6)
insert into @test values(8)

select id + 1 as missingid
from @test t1
where (
 select top 1 id from 
 @test t2
 where t2.id > t1.id
 order by id
) <> t1.id + 1

 

[snitin78]

Awesome, Boris! Hats off to you!!