Q: Tricky question...

Hi all gurus out there ;)
I have a cutstomer table, now i want to find a span of customerid's in this
table.
Lets say the customerid's that are taken are:
1-10, 11-13, 15, 18, 20-33 and so on.
Now i want to create a query, that find the non used customerid's and place
them in a table, also specifying the last number to check for, lets say
35...
The result from the query, should be...
14, 16, 17, 19, 34, 35
Is it possible to do?
Any hints, tips, code snippets would be greatly appreciated.
Regards
Martincreate a table with just numbers from 1 to your max customerid
Do a left join against this table and you are done
http://sqlservercode.blogspot.com/
"Visual Systems AB (Martin Arvidsson)" wrote:

> Hi all gurus out there ;)
> I have a cutstomer table, now i want to find a span of customerid's in thi
s
> table.
> Lets say the customerid's that are taken are:
> 1-10, 11-13, 15, 18, 20-33 and so on.
> Now i want to create a query, that find the non used customerid's and plac
e
> them in a table, also specifying the last number to check for, lets say
> 35...
> The result from the query, should be...
> 14, 16, 17, 19, 34, 35
> Is it possible to do?
> Any hints, tips, code snippets would be greatly appreciated.
> Regards
> Martin
>
>|||Declare @.cid as int
Declare @.max as int
set @.cid = 1-- start here
set @.max = 35-- end here
while (@.cid <= @.max)
begin
if not exists (select customerid from yourcustomerstable where
customerid = @.cid)
begin
insert into sometable (customierid) values(@.cid)
end
end|||Hi there
If you want to use unique number for each customerid Then you can use our
own number like this
Declare @.cid int
SELECT @.cid = Max(customerId) + 1 From Table1
Thanks
________________________________________
___________________
"Visual Systems AB (Martin Arvidsson)" wrote:

> Hi all gurus out there ;)
> I have a cutstomer table, now i want to find a span of customerid's in thi
s
> table.
> Lets say the customerid's that are taken are:
> 1-10, 11-13, 15, 18, 20-33 and so on.
> Now i want to create a query, that find the non used customerid's and plac
e
> them in a table, also specifying the last number to check for, lets say
> 35...
> The result from the query, should be...
> 14, 16, 17, 19, 34, 35
> Is it possible to do?
> Any hints, tips, code snippets would be greatly appreciated.
> Regards
> Martin
>
>|||Oops forgot one peice:
Declare @.cid as int
Declare @.max as int
set @.cid = 1-- start here
set @.max = 35-- end here
while (@.cid <= @.max)
begin
if not exists (select customerid from yourcustomerstable where
customerid = @.cid)
begin
insert into sometable (customierid) values(@.cid)
end
set @.cid = @.cid +1
end