How to Generate Date wise Sales Order Number in SQL Server?

Step 1:- Create the table

Create table TestSalesRecord

(

       SalesOrderID int identity,

SalesOrderNumber nvarchar(25) not null,

       InsertDate datetime default(getdate())

)

Step 2:- Create the view for get current date

create view view_CurrentDateWithHyphen

AS

SELECT CONVERT(varchar(10), GETDATE(), 105) AS CurrentDate

create view view_CurrentDateWithoutHyphen

AS

SELECT REPLACE(CONVERT(varchar(10), GETDATE(), 105), '-', '') AS CurrentDate

Step 3:- Create the function to Generate the Sales Order Number date wise.

create function GenerateSalesOrderNumber()

returns varchar(50)

as

begin

       declare @CurrentDateWithHyphen varchar(50)

       declare @SalesOrderNumber varchar(50)

       declare @CurrentDateWithoutHyphen varchar(50)

       declare @SalesOrderNumberOutput varchar(50)

       set @CurrentDateWithHyphen = (

                                  Select CurrentDate

                                  from view_CurrentDateWithHyphen

       )

       set @SalesOrderNumber = (

                                  select max(right(SalesOrderNumber,4))

                                  from TestSalesRecord

                                  where CONVERT(varchar(10), InsertDate, 105)=@CurrentDateWithHyphen

       )

       set @CurrentDateWithoutHyphen = (

                               select CurrentDate

                                  from view_CurrentDateWithoutHyphen

       )

       if  @CurrentDateWithHyphen not in (select CONVERT(varchar(10), InsertDate, 105) from TestSalesRecord)

       begin

              set @SalesOrderNumberOutput = '' +@CurrentDateWithoutHyphen+'' +''+ right(10000+1,4)

       end

       else

       begin

              set @SalesOrderNumber = @SalesOrderNumber+1

              if @SalesOrderNumber<>''

              begin

                     set @SalesOrderNumberOutput = '' +@CurrentDateWithoutHyphen+'' +''+ right(10000+@SalesOrderNumber,4)

              end

              if @SalesOrderNumber = ''

              begin

                     set @SalesOrderNumberOutput = '' +@CurrentDateWithoutHyphen+'' +''+ right(10000+1,4)

              end

       end

              return @SalesOrderNumberOutput

end

Step 4:- Insert the value in the TestSalesRecord table

insert into TestSalesRecord(

SalesOrderNumber

)

select dbo.GenerateSalesOrderNumber()

go 5