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