January 22, 2005 at 2:13 pm
January 22, 2005 at 3:22 pm
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
January 22, 2005 at 10:14 pm
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
January 23, 2005 at 11:33 am
January 24, 2005 at 8:32 am
I used checksum(whatever product) for the same purpose.
Quand on parle du loup, on en voit la queue
January 24, 2005 at 10:10 am
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
January 24, 2005 at 1:18 pm
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