How to create voucher numbers in SQL?

This is a short tutorial on creating voucher numbers in SQL Server. I took most of the help from this so article but made my own changes. This can be very handy script if you are working in a financial software where you need to generate voucher numbers or sequence numbers per day.

Voucher numbers are typical numbers sequences based on department from start to end of financial year.Typical examples can be SAL/001, CST/001 etc.

The following code creates a function (needs to be created first) which is then used by the table. Every time a new entry is made in the table, the function is called which generates a voucher number. In this case, the voucher number is simply a sequence number which is generated on daily basis. For example, on a new day, first voucher is always starts with 1 and then goes.

-- step 1 create function
create FUNCTION dbo.GetVoucherNumber(
	@id int,
	@department varchar(20),
    @VoucherDate DATE
) RETURNS VARCHAR(17)
AS
BEGIN

	declare @vno int

	if(@VoucherDate is null)
		return cast('VoucharDate is empty.' as int);


	select @vno = max(temp.vourcher_no) from test as Temp where department = @department 
	and convert(date,createdate) between convert(date, getdate()) and  convert(date, getdate())  and id < @id 

	if(isnull(@vno,0) = 0)
		return 1;
	else
		return @vno + 1;

	return cast('Vouncher Num is incorrect.' as int);
END


-- step 2: Create table
Create Table Test(
id int not null primary key identity,
salesdate date,
createdate date default getdate(),
department varchar(20),
vourcher_no  AS (dbo.GetVoucherNumber(id,department,createdate)) 
)



-- step 3 Test the code
insert into Test(salesdate,department) Values(dateadd(day,1,getdate()),'IT')
insert into Test(salesdate,department) Values(dateadd(day,1,getdate()),'IT')
insert into Test(salesdate,department) Values(dateadd(day,1,getdate()),'Accounts')

select * from test
Advertisements
This entry was posted in Tutorials/Tips and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s