Generate Complex Next Numbers with SQL Server

Generate Next Numbers with SQL Server

Introduction

Generating next numbers in SQLServer should not be a problem. But problems arise when a customer asks for different types of next numbers that you cannot generate directly from SQL Server. This brief article describes how you would tackle this problem in different scenarios.

Number Generating Requirements

I was on a team that developed an Invoicing System for a major photo developer in Sri Lanka. The system has Orders, Invoices, and Receipts, among other features. When we carried out a system study, we found that their number generating is quite different from that of other clients, for which we have implemented. Normally, others would be happy if it is a general increment that is unique. In SQL Server, there is a property of Increment that we were using happily for some times. But, for this particular customer, things were not easy.

For all numbers, there should be a prefix attached to it. The prefix is .O., .I., and .R. for Orders, Invoices, and Receipts respectively. The invoice number should be reset every month and the receipt number reset every year. The invoice number consists of year and month; the receipt number must contain the year for its number.

The order number sequence depends on the channel wise. This client has several channels. Channel 'A' order number has a different sequence than that of channel 'B'.

There are some other source documents, such as delivery orders and advance slips; their sequence number method has not been implemented at the time of the discussion. But they said it will be one of the above methods.

These are their main requirements to the number generating. Things are not easy as it looks.

Implementation

Because the full implementation is not complete, we had to design in advance. We adopted the following methods of doing it.

The main steps are storing the last number, reading the last number, and updating it.

Storing the Last Number

Following are the file structures that are used to keep the last number.

A Main Seq_no table was introduced to keep the sequence.

CREATE TABLE [dbo].[SEQ_NO] (
[TYPE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Prefix] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastNumber] [int] NULL ,
[Length] [smallint] NULL ,
[Year] [bit] NULL ,
[Month] [bit] NULL ,
[LastYear] [smallint] NULL ,
[LastMonth] [smallint] NULL ,
[TableName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Sample Data for the SEQ_NO

Type Prefix Last Number Length Year Month Last Year Last Month Different Table TableName
INV I 984 4 1 1 2004 2    
ORD   0 8 1 0 2004   1 SEQ_CHAN
RCT R 125 10 0 0        

Table for keeping channel wise sequence numbers:

CREATE TABLE [dbo].[SEQ_CHAN] (
[Code] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastNumber] [int] NULL
) ON [PRIMARY]
GO
Sample Data for the SEQ_CHAN
Code LastNumber
A 9534
B 835

Reading and Updating the Last Number

I have used a single stored procedure for reading and updating the last number. This is due to the fact that in a multi user environment, there can be a case where two users get same the number for the same record type. To avoid that at the point of generating the next number, the number will be updated to the tables.

The stored procedure (sp_Get_Next_Number) has three parameters. The first parameter is the type i.e. INV or ORD or RCT. The second parameter is tje date. Normally, the date is the current system date. However, in the cases of generating the previous document, the date has to be parameterized, keeping the system date as the default. The last parameter is an optional parameter for the second table code.

/* Purpose :Get Last Number
Author : PPG Dinesh Asanka
Create Date : 2002-01-23


Version Date Modification
1.01 2002-02-28 Change to Get Last Number from Channel wise table

*/
CREATE PROCEDURE [dbo].[sp_Get_Next_Number]
@TypeID as varchar(5),@TranDate as varchar(20) = "",
                      @secondType as varchar(5) = ""
AS
DECLARE @Prefix VARCHAR(1),
@LastNumber int,
@Length smallint,
@Year bit,
@Month bit,
@LastYear smallint,
@LastMonth smallint,
@DiffrentTable bit,
@TableName varchar(10),
@TrnYear varchar(2),
@TrnMonth varchar(2),
@char_Next_Number as varchar(25),
@SQL as varchar(120),
@charLastNumber as varchar(15)

If @TranDate = ""
Select @TranDate = GetDate()

if exists (Select * From SEQ_NO Where TYPE = @TypeID)
begin
Select @prefix = Prefix,
@LastNumber = LastNumber,
@Length = Length,
@Year = [Year],
@Month = [Month],
@LastYear = [LastYear],
@LastMonth = [LastMonth],
@DiffrentTable = [DiffrentTable],
@TableName = [TableName]
From SEQ_NO Where TYPE = @TypeID

if (@DiffrentTable = 1)
Select @LastNumber = LastNumber From SEQ_CHAN Where CODE = @secondType

Select @LastNumber = @LastNumber + 1
-- To Fill 0 for prefix
Select @charLastNumber = RTRIM(REPLICATE("0",@length - len(@LastNumber))
                         + cast(@LastNumber as varchar(10)))

Select @TrnYear = RIGHT(DATEPART(yyyy, @TranDate) ,2)
Select @TrnMonth = Month( @TranDate)
If Len(@TrnMonth) = 1 Select @TrnMonth = '0' + @TrnMonth

if @Year = 1 AND @Month = 1 -- Next Number is change for Every Month
if @LastMonth = Month(@TranDate) AND @LastYear
              = RIGHT(DATEPART(yy, @TranDate) ,2)
Select @char_Next_Number = @TrnYear + @TrnMonth + @charLastNumber
else
begin
Select @char_Next_Number = @TrnYear + @TrnMonth
                         + REPLICATE ("0" ,@length -1) + '1'
Select @LastNumber = 1
Select @LastMonth = @TrnMonth
Select @LastYear = @TrnYear
end
else
if @Year = 1 -- Next Number is changing for Every year
if @LastYear = RIGHT(DATEPART(yy, @TranDate) ,2)
Select @char_Next_Number = @TrnYear + @charLastNumber
else
begin
Select @LastYear = @LastYear
Select @LastNumber = 1
Select @char_Next_Number = @TrnYear + REPLICATE ("0" ,@length -1) + '1'
end
else
Select @char_Next_Number = @charLastNumber

Select RTRIM(LTRIM(ISNULL(@prefix,"") + @char_Next_Number))

-- Update Tables for next Numbers
if (@DiffrentTable = 1)
begin

Select @sql = "Update " + @Tablename + " Set LastNumber ="
+ Cast(@LastNumber as varchar(10)) +
" Where CODE ='" + @secondType + "'"
execute (@sql)
end
Update SEQ_NO Set LastNumber = @LastNumber,
LastYear = @LastYear,
LastMonth = @LastMonth
Where TYPE = @TypeID
end
else
Select "Invalid Type"

GO

Examples

The following results will be returned for the above data set:

sp_Get_Next_Number 'INV'        = I04020985
sp_Get_Next_Number 'RCT'        = R0000000126
sp_Get_Next_Number 'ORD','','A' = 0400009535

Limitations

Problems arise when the user tries to make ad hoc changes for the existing numbering system. Therefore, we specifically advised them not to do ad hoc changes to the numbering method.

Conclusion

Until now, we have not found any problems with regard to the above numbering system. Do you have any other way of doing these things? I warmly welcome any suggestions and improvements to the above numbering system.

Dinesh Asanka



Comments

  • this is a nonworking code....

    Posted by X-Expert on 07/05/2004 07:38pm

    The submitted sqlcode is not working, it has newer been either. I guess it's better to clarify why I think so. 1. To many datatypes 2. Mixed up variables, makes it difficult to know what it's doing. 3. When reading the introduction and requirements, I feel like I'm asked to do their job. 4. Declaring 'nonexistens features' that have to be done. 5. The implementation has to be done 'in advance'. To Codeguru Editor: This is not worth to be an article named 'Generate complex numbers.....', it should be named 'Request for help generating complex numbers...'. The problem is intricate and not an easy task. But it's would be great if the article clarified it's aim in real. Anyway, it will work with a tablestructure like this: [TYPE] [sPrefix] [iSeqNumber] [iLength] [sSeqReset] [sChannel] [dLastReset] [iTotalNumber] and a related table to store used sequence to support the other datevariable than currentdate. Best regards

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • It's no secret what keeps CIOs up at night. Mobile, cloud, data, security, and social have become the "five imperatives," the drivers of business progress, innovation, and competitive differentiation. Business leaders around the world want to hear how other companies are succeeding. How are they applying the latest technologies? How did they get started? What outcomes are they achieving? Read this online magazine for success stories from organizations like the NBA, Pfizer, and San Jose State University as they …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds