Q: Tricky question...

22 Sep 2005 5:12 PM

Visual Systems AB (Martin Arvidsson)

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

Martin

22 Sep 2005 5:18 PM

SQL

create 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/

Show quote

"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 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
>
> Martin
>
>
>

22 Sep 2005 5:30 PM

jroozee@gmail.com

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

22 Sep 2005 5:31 PM

jroozee@gmail.com

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

22 Sep 2005 5:29 PM

Akbar khan is a Senior Database develope

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
___________________________________________________________
Show quote

"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 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
>
> Martin
>
>
>