Finding gaps in ranges...how?

  • 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
  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

  • thanks very much guys, it worked famously!
     
    Regards
  • I used checksum(whatever product) for the same purpose.

    Quand on parle du loup, on en voit la queue

  • 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

  • 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

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

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