Missing Serial No in a Sequence

[garyrichard]

I am having a table with nvarchar feild in which values are entered like
1
4
8
25
I want a sql query to find the missing sequence of number in the above series...

Thanks in advance...

[seslie]

Hello garyrichard,

Well, first of, you need to post what you've been able to do. That said..are you entering these values manually? I should think there'll be a predefined query that generates them.
If it's already an hard coded table and you just need to find the missing sequence, then you can also write a code for that but first, you need to show your attempt.

[adam_k]

Create a table (or with a cartesian it can be done in a query) with the series of numbers from your smaller one to the greater one and left or right join it to your table. You'll get the missing ones when you add for criteria where your column is null.

[garyrichard]

thanks for your replies...
i am having a table name KHEWAT..
it has a feild KHEWAT_NO whose data type is VARCHAR.
this table has value from 1501 to 1986 in which there are some missing no..which i have to find out...
for that purpose i am looking for an sql query...

Thanks in advance

[adam_k]

I don't see any effort, but I'll give you the answer as I'm too bored and there are not many threads open for my taste.

select seq.id from KHEWAT right join 
    (select 1501 + convert(int,(a0.id + a1.id + a2.id)) as id 
     from 
     (select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union
      select 6 union select 7 union select 8 union select 9) a0,  
     (select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union  select 90) a1, 
     (select 0 id union select 100 union select 200 union select 300 union select 400) a2
      ) seq
on KHEWAT_NO = seq.id 
where seq.id is null 
and seq.id between 1501 and 1986

[Shivraj_1]

SELECT (t1.log_id + 1) AS gap_starts_at,
(SELECT MIN(t3.log_id) -1 FROM logtable t3 WHERE t3.log_id > t1.log_id) AS gap_ends_at
FROM logtable t1
WHERE NOT EXISTS (SELECT t2.log_id FROM logtable t2 WHERE t2.log_id = t1.log_id + 1)
HAVING gap_ends_at IS NOT NULL