Generating unique serial numbers for batches in pharma industry - SQL SERVER 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 30-Oct-19 19:38pm

Prathap Gangireddy

Updated 4-Nov-19 2:24am

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RickZeeland · Answer 1 · 2019-10-30T22:30:00

Solution 1

Take a look at Sequences: An Essential Guide to SQL Server Sequence By Practical Examples[^]
And also: CREATE SEQUENCE (Transact-SQL) - SQL Server | Microsoft Docs[^]

If you have an older SQL Server version, take a look at: Custom Auto-Generated Sequences with SQL Server - SQLTeam.com[^]

Posted 30-Oct-19 22:30pm

RickZeeland

Updated 30-Oct-19 22:39pm

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

Maciej Los · Answer 2 · 2019-10-30T22:52:00

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

SQL

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[^]

MadMyche · Answer 3 · 2019-11-04T02:24:00

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.

Generating unique serial numbers for batches in pharma industry - SQL SERVER 2008

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0