Creating Sequence Number DDMMYY-00001 where DDMMYY is today’s date

The defined logic for generating sequence number field which is 12 digits is

E.g. 281113-00001

Where 281113 - Today’s date and

00001 – Unique no which will auto increment.

We require a table which will hold a single row with one filed today_date which will hold the current date and current_sequence_no which will hold the current sequence number.

create table Tab_VKM_SequenceManagement

(id int identity(1,1),

todays_date date,

current_sequence_no int)

insert into Tab_VKM_SequenceManagement(todays_date,current_sequence_no)

values(GETDATE(),1)

select * from Tab_VKM_SequenceManagement

Let’s create a procedure which will first check the date stored in Tab_VKM_SequenceManagement with the current date, if it is current date then it will update the table current_sequence count to +1 and generate the sequence number.

If the date in the table is not current date then it will update the date filed to current date and current_sequence to 1 and generate the required sequence number.

alter procedure  Proc_VKM_GetNextSequence

@return_sequence varchar(15) OUTPUT

as  

    declare @date date

    declare @sequence_no int

   

    declare @day varchar(4)

    declare @month varchar(4)

    declare @year varchar(4)

begin       

    select @date = todays_date, @sequence_no = current_sequence_no

    from Tab_VKM_SequenceManagement where id = 1     

  

    if @date = cast (getdate() as date)

    begin

           

            update Tab_VKM_SequenceManagement

            set current_sequence_no = current_sequence_no +1

            where id =1

           

            set @day =

            right('00' + cast( datepart(DD,@date) as varchar),2)

           

            set @month =

            right('00' + cast( datepart(MM,getdate()) as varchar),2)

           

            set @year =

            cast(Right(datepart(YY,getdate()),2) as varchar)

                       

            set @return_sequence =

            @day + @month + @year + '-' +

            right(('00000' + cast((@sequence_no +1) as varchar)),5)    

    end

    else if @date != cast (getdate() as date)

    begin  

            update Tab_VKM_SequenceManagement

           

            set todays_date = GETDATE(), current_sequence_no =1

           

            select @date = todays_date, @sequence_no = current_sequence_no

            from Tab_VKM_SequenceManagement where id = 1

           

            set @day =

            right('00' + cast( datepart(DD,@date) as varchar),2)

           

            set @month =

            right('00' + cast( datepart(MM,getdate()) as varchar),2)

           

            set @year =

            cast(Right(datepart(YY,getdate()),2) as varchar)

           

            set @return_sequence = @day + @month + @year + '-' +

            right(('00000' + cast((@sequence_no) as varchar)),5)

    end            

end

Script to call the procedure.

declare @next_sequence varchar(15)

exec Proc_VKM_GetNextSequence @next_sequence OUT

select @next_sequence

When the code shows the happiness, ego bored into it.

Post Reference: Vikram Aristocratic Elfin Share