August 12, 2004 at 9:06 am
We have a system that records various incidents. For one reason or another, when these incidents are pulled in from a seperate system not all of them are always moved over, therefore we end up with gaps in our incident numbers.
Can anyone suggest a nice way of listing the missing ones?
for example, we have incidents:
1001, 1002, 1003, 1005, 1006, 1010
How would i write a query to spot that 1004, 1007, 1008 and 1009 are missing?
Thanks in advance
Growing old is mandatory, growing up is optional
August 12, 2004 at 9:32 am
How about this?
select a.idno + 1
from tablename a
left outer join tablename b on (b.idno = a.idno + 1)
where b.idno is null
Kevin Williams
August 12, 2004 at 9:38 am
Thankyou very much, works a treat.
Growing old is mandatory, growing up is optional
August 12, 2004 at 9:54 am
I recreated the problem and the query suggested does NOT work for all numbers...See following...
create table incidents (incident_id int not null,
incident_detail varchar(100) null)
insert incidents
values(1001, 'incident 1')
insert incidents
values(1002, 'incident 2')
insert incidents
values(1003, 'incident 3')
insert incidents
values(1005, 'incident 5')
insert incidents
values(1006, 'incident 6')
insert incidents
values(1010, 'incident 10')
select * from incidents
incident_id incident_detail
----------- ----------------------
1001 incident 1
1002 incident 2
1003 incident 3
1005 incident 5
1006 incident 6
1010 incident 10
(6 row(s) affected)
--------------the suggested solution yields only 3 rows-----
--------------it misses row 8 and row 9
select a.incident_id + 1
from incidents a
left outer join incidents b on
(b.incident_id = a.incident_id + 1)
where b.incident_id is null
------------------------------------------------------
1004
1007
1011
(3 rows only)
--------------
A more complete solution follows by creating a temp table:
declare @someid int, @max_id int, @min_id int
select @max_id= max(incident_id),@min_id = min(incident_id) from incidents
create table #tmp ( someid int not null)
set @someid =@max_id-----max number of your incidents table
while @someid >= @min_id ---min number of your incidents table
begin
insert #tmp
values (@someid)
select @someid=@someid -1
end
-----------------------------------
select someid
from #tmp t
where not exists (select incident_id from incidents i where t.someid=i.incident_id)
order by 1 asc
someid
-----------
1004
1007
1008
1009
(4 row(s) affected)
------------------------------------------------------
August 12, 2004 at 10:01 am
Again, thanks very much, this has now given me a heart attack to see just how many are missing!! At least i can now use this list to copy them all over.
Growing old is mandatory, growing up is optional
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply