Solved

Create Autonumber

Posted on 2015-01-22
3
85 Views
Last Modified: 2015-02-05
Hi,
I have already created Autonumber 0001 until 9999 function

My question is automated number limit is until 9999 , after that need to start from 0001

when customer enter expired date, if date have expired no need to start 0001 , need to use expired date MIN auto number alreday created.

for example after 9999 auto number , customer enter expired date 01/23/2015 , when next customer need to search any
expired date available before create auto number , if expired date in the 0010 is expired then customer automatically assigning to 0010.

See below code i wrote any idea appriciate
DECLARE @BarcodeNumber nvarchar(100) 
DECLARE @LastBarcodeNumber INT

DECLARE @CheckAvaible int
DECLARE @BarCode bigint
DECLARE @Num1 bigint
DECLARE @BarcodeNum2 NVARCHAR(20)
DECLARE @BarcodeNum3 NVARCHAR(20)
DECLARE @ComplteBarcode nvarchar(100)
DECLARE @NumberofRecords INT
DECLARE @Results INT
select  @LastBarcodeNumber=Count(Num1) from NewBarcodes where Num1='9999'
select @CheckAvaible=Count(Num1) from NewBarcodes where Expired_BarCode is null 

SELECT @NumberofRecords=Count(Num1) from NewBarcodes
SET @Results=0

PRINT @CheckAvaible

IF (@LastBarcodeNumber>=1 and @CheckAvaible=0)
BEGIN
SET @Results=1
SET @Num1=1
SET @BarcodeNum2=right('00' +'1', 2)
set @ComplteBarcode=CONVERT(nvarchar(20),@Num1)
--SET @BarcodeNumber=right(CONVERT(nvarchar(20), @ComplteBarcode)+'0000000000000' , 12)
SET @BarcodeNumber=right('00000' +CONVERT(nvarchar(20), @ComplteBarcode), 5)+CONVERT(nvarchar(20),@BarcodeNum2)+'00000'
END


IF(@NumberofRecords=0)
BEGIN
SET @Results=1
SET @Num1=1
SET @BarcodeNum2=right('00' +'1', 2)
set @ComplteBarcode=CONVERT(nvarchar(20),@Num1)
--SET @BarcodeNumber=right(CONVERT(nvarchar(20), @ComplteBarcode)+'0000000000000' , 12)
SET @BarcodeNumber=right('00000' +CONVERT(nvarchar(20), @ComplteBarcode), 5)+CONVERT(nvarchar(20),@BarcodeNum2)+'00000'
END

DECLARE @NotInCluded INT
SET @CheckAvaible=0
select @CheckAvaible=Count(Num1) from NewBarcodes where Expired_BarCode is Not null AND Num1 Not in('9999') 
IF(@CheckAvaible>=1)
BEGIN
SET @Results=1
select  @Num1=MIN(Num1) from NewBarcodes where Num1 Not in('9999') and Expired_BarCode is Not null
SET @BarcodeNum2=right('00' +CONVERT(nvarchar(20),dbo.BarcodeAddResults(@Num1)),2)
SET @ComplteBarcode=CONVERT(nvarchar(20),@Num1)
SET @BarcodeNumber=right('00000' +CONVERT(nvarchar(20), @ComplteBarcode), 5)+CONVERT(nvarchar(20),@BarcodeNum2)+'00000'
END


IF(@Results=0)
BEGIN
	SELECT  @Num1=MAX(Num1) from NewBarcodes  where  Num1 Not in('9999') 
	SET @Num1=@Num1+1
	SET @BarcodeNum2=right('00' +CONVERT(nvarchar(20),dbo.BarcodeAddResults(@Num1)),2)
	SET @ComplteBarcode=CONVERT(nvarchar(20),@Num1)
	SET @BarcodeNumber=right('00000' +CONVERT(nvarchar(20), @ComplteBarcode), 5)+CONVERT(nvarchar(20),@BarcodeNum2)+'00000'

END

PRINT  'Max Records Number'+@BarcodeNumber +'Check Available  '+CONVERT(nvarchar(20),@CheckAvaible)






DECLARE @CheckAlreadyCreated int

select  @CheckAlreadyCreated=Count(*) from NewBarcodes where Created_By=@Username and Expiry is NULL and Expired_BarCode is null

IF(@CheckAlreadyCreated=0)
BEGIN
INSERT INTO NewBarcodes(Barcode_Number,Created_By,Num1,BarcodeNum2) Values(@BarcodeNumber,@Username,@Num1,@BarcodeNum2)
END

IF(@CheckAlreadyCreated=1)
BEGIN
select  @BarcodeNumber=Barcode_Number from NewBarcodes where Created_By=@Username and Expiry is NULL and Expired_BarCode is null
END


UPDATE NewBarcodes
SET Expired_BarCode=NULL 
WHERE Barcode_Number=@BarcodeNumber

Open in new window

0
Comment
Question by:ukerandi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 500 total points
ID: 40565689
I'm sorry, I don't understand the question.  Would you restate the question please?

I'm assuming your 'autonumber' is the barcode in the code segment?

Are you attempting to find an available unused 'autonumber'?  (I.e. a 'gap' in the values which you can then fill?)  I know there are a few possible methods to find the gaps...

I'm also assuming there is a strong reason to limit the 'autonumber' to four digits?  (And if limited to four digits, since it doesn't appear to really be treated as a number... is there a restraint preventing the use of non-numeric values in the four digits?)

If there isn't a length restraint on the autonumber, is there a reason not to use an Identity or Sequence?
0
 
LVL 10

Author Comment

by:ukerandi
ID: 40576454
I'm assuming your 'autonumber' is the barcode in the code segment? NO


Are you attempting to find an available unused 'autonumber'?
YES
But after Date Expired then user can use that expired slot
For example ID  8115 Expired date 27/01/2015
user automatically assigned to 8115
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 40578148
If the autonumber between 0000 and 9999 is not the barcode values, then I've gotten lost in your code.  First, if the code works for you, I wouldn't knock it ... but on the idea of giving ideas, to perhaps help in the future:

I'd start with readability.
There are variables for @BarCode, and @Num1 which are numbers (bigint), while @BarcodeNumber, @BarCodeNum2, @BarcodeNum3 which one would expect to be numbers based on their names are variable length character strings.  @ComplteBarcode is also a string.

While you probably have a really good idea what each section of the code is doing, I'd comment it, so that you'll still know when you have to revise it in five years... or in case someone else has to thread through it.

Second, I'd try to be real clear what you are attempting to accomplish, in this case, why your 'autonumber' is limited to four digits.  Assuming this is a solid requirement.  If this were a new implementation, I'd be tempted to break the task into a few pieces:  First, I'd repopulate the entire table with all the possible values from 0000 to 9999, and in one field I'd have a flag (either a tinyint or binary value) of 'Available', and initially, I'd flag every entry as available.  A periodic (daily?) automated task would scan the table and mark expired slots as available.  Finally, when you need a value, I'd just do a "select top 1 from <table> where available = <available value>", and when you assign the value, set it's expiration date and availability flag.

I assume value 9999 has some special meaning... but I don't know what that is... and I don't know if the value is suppose to be unique?

Most of the rest of what I see in the code is a lot of converting from ints to strings, etc... I assume to build a full barcode string value.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question