How To Set Continue Ref Number

Question

Hello

Im using MS SQL Table In Table when ever new record insert it should each Record RefNo For Example

Here ABC Is Our Code Next 001 It should go incremental data whenever record insert and 23/24 finianical year should chagge automtically every year on 01-Apr

ABC-001-23/24
ABC-002-23/24
ABC-003-23/24

ABC-004-23/24

So on it has to go in sequence.

Thanking You

Answer 1

Try something like this in SQL:

declare @OurCode varchar(max) = 'ABC'

declare @max_code int = coalesce((select max(substring(left(RefNo, len(@OurCode)+1+3), len(@OurCode)+2, 1000)) from OurTable), 0)

declare @year int = year(getdate()) % 100
declare @fin_year varchar(max)

if month(getdate()) >= 4 
	set @fin_year = format(@year, '00') + '/' + format(@year + 1, '00')
else
	set @fin_year = format(@year - 1, '00') + '/' + format(@year, '00')

declare @next_RefNo varchar(max) = @OurCode + '-' + format(@max_code + 1, '000') + '-' + @fin_year

Use the @next_RefNo to insert the new record. You can make some stored procedures. However, make sure that this code is not used incorrectly by multiple tasks in parallel. Otherwise, find other approaches.

Answer 2

@Viorel Thanks or your code if we use old ms sql version how would be the query after remove format tag.

Answer 3

let us assume when ever i inster new record it has to go sequence wise

Here ABC Is Our Code Next 001 It should go incremental data whenever record insert and 23/24 finianical year should chagge automtically every year on 01-Apr ABC-001-23/24
ABC-002-23/24
ABC-003-23/24 ABC-004-23/24 So on it has to go in sequence.