"Jami" <jami.khan@yahoo.com> wrote in message
news:ed4EAkfEHHA.4132@TK2MSFTNGP04.phx.gbl...
>
> Hi!
> i am maintaining a distributed database. data comes in shape of batches.
> i want to determine which no is missing.
> sample data and table steucture are like this
>
> Main_table
> s-no batch_no region_id
> 1 1 AA1
> 2 1 AA1
> 3 1 AA1
> 4 1 AA1
> 17 4 AA1
> 18 4 AA1
> 19 4 AA1
> 23 5 AA2
> 35 8 AA2
> .
> .
> .
>
>
> i have made a table with sequnce no's to compare the batch no sequence.
>
> and i m using following query
>
> select distinct b.region_name,batch_no from main_talbe a join Regions b
> on a.Region_id = b.region_id
> where a.batch_no in (select seq_no from list)
>
>
> this query gives me complete list of batches centers by each region. but
> i want break in the sequnce batches not sent by the regions. if i use
> 'not in' it returns me nothing as batch no is in the list as list is
> quite long 1..1000. how can i change my query to get the desired result.
>
> thanx
> Jami
>
> *** Sent via Developersdex http://www.developersdex.com ***

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:uf6FwtgEHHA.2356@TK2MSFTNGP03.phx.gbl...
> Jami
> CREATE TABLE X (C INT NOT NULL PRIMARY KEY)
> insert X select 1 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 7 union all
> select 10
>
>
> SELECT
> c+n FROM x, (
> SELECT 0 n UNION ALL SELECT 1
> ) T
> GROUP BY c+n
> HAVING MIN(n) = 1 and c+n < (select max(c) from x)
>
>
> "Jami" <jami.khan@yahoo.com> wrote in message
> news:ed4EAkfEHHA.4132@TK2MSFTNGP04.phx.gbl...
>>
>> Hi!
>> i am maintaining a distributed database. data comes in shape of batches.
>> i want to determine which no is missing.
>> sample data and table steucture are like this
>>
>> Main_table
>> s-no batch_no region_id
>> 1 1 AA1
>> 2 1 AA1
>> 3 1 AA1
>> 4 1 AA1
>> 17 4 AA1
>> 18 4 AA1
>> 19 4 AA1
>> 23 5 AA2
>> 35 8 AA2
>> .
>> .
>> .
>>
>>
>> i have made a table with sequnce no's to compare the batch no sequence.
>>
>> and i m using following query
>>
>> select distinct b.region_name,batch_no from main_talbe a join Regions b
>> on a.Region_id = b.region_id
>> where a.batch_no in (select seq_no from list)
>>
>>
>> this query gives me complete list of batches centers by each region. but
>> i want break in the sequnce batches not sent by the regions. if i use
>> 'not in' it returns me nothing as batch no is in the list as list is
>> quite long 1..1000. how can i change my query to get the desired result.
>>
>> thanx
>> Jami
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>
>

"Jami" <jami.khan@yahoo.com> wrote in message
news:ed4EAkfEHHA.4132@TK2MSFTNGP04.phx.gbl...
>
> Hi!
> i am maintaining a distributed database. data comes in shape of batches.
> i want to determine which no is missing.
> sample data and table steucture are like this
>
> Main_table
> s-no batch_no region_id
> 1 1 AA1
> 2 1 AA1
> 3 1 AA1
> 4 1 AA1
> 17 4 AA1
> 18 4 AA1
> 19 4 AA1
> 23 5 AA2
> 35 8 AA2
> .
> .
> .
>
>
> i have made a table with sequnce no's to compare the batch no sequence.
>
> and i m using following query
>
> select distinct b.region_name,batch_no from main_talbe a join Regions b
> on a.Region_id = b.region_id
> where a.batch_no in (select seq_no from list)
>
>
> this query gives me complete list of batches centers by each region. but
> i want break in the sequnce batches not sent by the regions. if i use
> 'not in' it returns me nothing as batch no is in the list as list is
> quite long 1..1000. how can i change my query to get the desired result.
>
> thanx
> Jami
>
> *** Sent via Developersdex http://www.developersdex.com ***