Click here to Skip to main content
14,391,651 members
Rate this:
Please Sign up or sign in to vote.
In the front end when the client enters a quantity of 100/200 of a particular medicine, need to generate unique serial numbers for the batches which have validations of what to include/exclude and save 100/200 rows with all the details of batches along with a unique serial number.

For example: if I'm ordering a quantity of 100 and press on "Generate serial numbers" button and the validation is like need to include 'ABCDEFGHIJKL2589' (just an example can vary what to include).

Variables here are the Quantity and Alphanumerics to be included to generate serial numbers. (which are dynamic)

Serial number max length is 20.

Few more examples
There is a form in the front end to select the Alphabets and Numbers to be included in the serial number generation.It can either be UpperCase or LowerCase but cant be both and numbers from 0-9. No special characters.

If i need 10 Qty( batches) then using the selected letters and numbers i will generate 10 different serial numbers(in this example i might have selected 'A,B,C,D,1,2,3' in front end).
A,AB,AC,AD,AD1,AB23 etc

If i need If i need 20 Qty( batches) then using the selected letters and numbers i will generate 20 different serial numbers.(in this example i might have selected 'G,H,A,B,5,0,3' in front end).
GHA,BA5,GHAB503 etc


What I have tried:

trying few solutions using
newid() 
but m not getting how to generate unique serial number using the selected Alphanumerics and which has to be unique over all the batches going forward.
Posted
Updated 4-Nov-19 3:24am
v3
Rate this:
Please Sign up or sign in to vote.

Solution 1

   
v4
Comments
Prathap Gangireddy 31-Oct-19 4:33am
   
m using Sql server 2008 and i think sequence is not available in this version.
Prathap Gangireddy 31-Oct-19 4:41am
   
m using sql server 2008,sequences
Rate this:
Please Sign up or sign in to vote.

Solution 2

If you would like to generate number of unique serial numbers, the best way to achieve that is to use NEWID[^] and CTE[^]. See:
DECLARE @RowsCount INT = 100

;WITH CTE AS
(
	--initial part
	SELECT 1 AS RowNo, NEWID() AS CustomSeq
	--recursive part
	UNION ALL
	SELECT RowNo +1 AS RowNo, NEWID() AS CustomSeq
	FROM CTE
	WHERE RowNo<@RowsCount 
)
SELECT RowNo, CustomSeq
FROM CTE
OPTION (MAXRECURSION 0)


For further details, please see:
MAXRECURSION Sql Server | SqlHints.com[^]
Common Table Expressions (Introduction to CTE's) - Essential SQL[^]
Mastering Common Table Expression or CTE in SQL Server[^]
   
v2
Comments
Prathap Gangireddy 31-Oct-19 4:55am
   
Here we are sending a combination of Alphabets and Digits (alphanumeric) parameter to the database from which we need to generate unique serial number. Like 'ABCDEFGHIJKL2589' or 'ABCD' or '124568' 'ABC123'. Any such combination as parameter will be send and used to generate serial number.
Rate this:
Please Sign up or sign in to vote.

Solution 3

Posting on this thread instead of the repost where I started this before knowing this was a repost

I would strongly suggest that you look at some type of intelligent serial number; similar to an automobiles VIN (Vehicle Identification Number), or look at what is currently being used by others in the industry.

The way I would implement would contain (but not limited to) DrugID (type, strength), batch number, and then a sequential count.
These separate elements would be stored in different tables within your system, and the final serial number would be a string concatenation of the elements.
The only thing that should possibly be up the "operator" would be to enter the batch number, and that would be subject to its own logic to be sequential in nature and have the manufacturing date encoded into it.

There seems to be too much end-user decisions the way you have it lined out which seem to be translated into just one numbering system. It really needs to be broken down in my opinion.
Business rules should be created on each element separately; an each element should have its own application logic to implement, unrelated to the other elements.
The final serial number should have its own business rule, which can follow the rules you have outlined for character type and length.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100