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...

Re: Missing Serial No in a Sequence 80 80

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.

Re: Missing Serial No in a Sequence 80 80

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.

Re: Missing Serial No in a Sequence 80 80

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

Re: Missing Serial No in a Sequence 80 80

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
Re: Missing Serial No in a Sequence 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.