Finding gaps in ranges...how?

Question

Post reply

Finding gaps in ranges...how?

John Mason

SSC Rookie

Points: 48
More actions
January 22, 2005 at 2:13 pm

#60685

Hi,

I have the necessary SQL to create the following unique product ID's:

SL000001
SL000002
SL000003
..
..
..
etc

Now, this is all well and good but if the product is deleted then I can effectively have gaps or 'free' products ID available to be assigned to new products.

What I want to do is know if I have any gaps within my products IDS and then assign one of them before I do my unique generation (which is simply based on the highest number after the SL part).

Any help would be great!

Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Several ways to do this. One is to create a new calculated field in your Products table:

NumericID = (convert(int,right([productid],6)))

and then it's just a straightforward query to find the next available:

select min(p1.numericid) + 1 as NextID

from products p1

where (p1.numericid + 1) not in (select p2.numericid from products p2)

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Benny Lopez Ten Centuries Points: 1171 More actions · Answer 2

It seems that the question was to obtain the product key in the first gap. hence, we need a function that returns such value. I took Phil's answer and re-wrote it as a function (two functions actually)

somewhere the client may say: select NextID().

Create function dbo.NextID()

returns varchar(9)

as

Begin

declare @SLS varchar(9)

select @SLS='SLS'+REPLACE(STR(min(dbo.nbr(p1.rid)) + 1, 6, 0),CHAR(32),CHAR(48))

from dbo.mytable p1

where (dbo.nbr(p1.rid) + 1) not in

(select dbo.nbr(p2.rid)

from dbo.mytable p2)

Return @SLS

End

-------------------------------------

Create function dbo.nbr (@slsid varchar(9))

returns int

as

Begin

declare @NumericId int

set @NumericId = (convert(int,right(@slsid,6)))

Return (@NumericID)

End

Ben

John Mason SSC Rookie Points: 48 More actions · Answer 3

thanks very much guys, it worked famously!

Regards

PBirch SSCrazy Eights Points: 8493 More actions · Answer 4

I used checksum(whatever product) for the same purpose.

Quand on parle du loup, on en voit la queue

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 5

G'day

As shown, the technical ability to do what you requested is not particularly difficult. However, with 25+ years of logistics software development behind me, I have to ask: Are you sure you want to do this? You are describing one of the classic nightmare's of a supply system - two different products that have the same product number. For example, trying to do year-over-year analysis of production rates, usage rates, etc becomes a good deal harder if you now have to track the specific point in time that one product ended and the new product started. There are multiple ways to handle the situation, and almost any of them are better than sharing the same product number. The basic question becomes "What are you trying to achieve by reusing the product numbers?" followed quickly by "Is this particular solution really worth the headaches it will bring?"

Just my two cents. Your mileage may vary. Use only under adult supervision. etc.

Wayne

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 6

I agree with the nightmare scenario. Unless you think you are going to run out of numbers, is there a reason to reuse Product IDs?

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3