Solved

Create Autonumber

Posted on 2015-01-22
3
80 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
  • 2
3 Comments
 
LVL 29

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 29

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now