Finding available customer numbers

Hi All,
How do I find unused customer numbers in a table?
Thanks
Mac
Hi,
With this information it is difficult to give a solution. A tip will be,
Check the trasnaction table against the customer master table.
If you have not dealed with that customer , you wont have any transaction in
TRAN table.
With that you can identify the list of customers, query will be some thing
like:-
select cust_num,cust_name from cust_master where cust_num not in (select
cust_num from cust_transaction)
Thanks
Hari
MCDBA
"Mac" <jmcgrath@.planesunited.com> wrote in message
news:46ABDDA1-B5FC-4F33-8655-E99A7802D794@.microsoft.com...
> Hi All,
> How do I find unused customer numbers in a table?
> Thanks
> Mac
|||I apoligize, I did not give enough info. The problem is actually simpler than that. In my customer master all customers are asigned a unique number from 10000- 30000. I am looking for available numbers (not in the table) in this range.
Thanks,
Mac
|||This will give you all the gaps :
SELECT col + 1
FROM tbl
WHERE NOT EXISTS( SELECT * FROM tbl t1
WHERE t1.col = tbl.col + 1 )
AND IDNo < ( SELECT MAX( col ) FROM tbl );
To get the smallest one:
SELECT MIN( col ) + 1
FROM tbl
WHERE NOT EXISTS( SELECT * FROM tbl t1
WHERE t1.col = tbl.col + 1 ) ;
Anith
|||Thanks very much Anith!