3
Contributors
4
Replies
6
Views
5 Years
Discussion Span
Last Post by adam_k
0

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.

0

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.

0

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

0

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

Edited by adam_k

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.