Generate date wise user complaint reference number in sql or Generate date wise order number in sql server

Step 1:- Create table

CREATE TABLE tblUserComplaint

(

       RowId int identity,

       ReferenceId varchar(50),

       UserName varchar(50),

       InsDtTime datetime constraint default_InsDtTime default(getdate())

)

Step 2:- Create funtion

CREATE FUNCTION fnGenerateUserComplaintRefId()

RETURNS VARCHAR(100)

AS

BEGIN

       DECLARE @Alias VARCHAR(10) = 'REF'

       DECLARE @CurrentDate VARCHAR(10)

       DECLARE @SrNo VARCHAR(10)

       DECLARE @ReferenceId VARCHAR(100)

       SET @CurrentDate = (SELECT FORMAT( GETDATE(), 'yyyyMMdd', 'en-US' ))

       SET @SrNo = (

                        SELECT MAX(RIGHT(ReferenceId,4))

                        FROM tblUserComplaint

                        WHERE CONVERT(VARCHAR(10), InsDtTime, 112)=@CurrentDate

                   )

       IF @CurrentDate NOT IN (SELECT DISTINCT CONVERT(VARCHAR(10), InsDtTime, 112) FROM tblUserComplaint)

       BEGIN

              SET @ReferenceId = (SELECT CONCAT(@Alias,@CurrentDate,RIGHT(10000+1,4)))

       END

       ELSE

       BEGIN

              SET @SrNo = @SrNo+1

        IF @SrNo<>''

        BEGIN

                SET @ReferenceId = (SELECT CONCAT(@Alias,@CurrentDate,RIGHT(10000+@SrNo,4)))

        END

        IF @SrNo = ''

        BEGIN

                SET @ReferenceId = (SELECT CONCAT(@Alias,@CurrentDate,RIGHT(10000+1,4)))

        END

       END

       RETURN @ReferenceId

END

Step 3:- Create insert procedure

CREATE PROC spUserComplaintIns

@UserName varchar(50)

AS

BEGIN

       INSERT INTO tblUserComplaint

       (

              ReferenceId,

              UserName

       )

       VALUES

       (

              (SELECT dbo.fnGenerateUserComplaintRefId()),

              @UserName

       )

END

Step 4:- Insert record in tblUserComplaint table

EXEC spUserComplaintIns 'Aadarsh Shrivastav'

SELECT * FROM tblUserComplaint

EXEC spUserComplaintIns 'Aadarshini Goel'

SELECT * FROM tblUserComplaint

EXEC spUserComplaintIns 'Bajrang Goswami'

SELECT * FROM tblUserComplaint

EXEC spUserComplaintIns 'Bulbul Desai'

SELECT * FROM tblUserComplaint

EXEC spUserComplaintIns 'Chetan Bhagat'

SELECT * FROM tblUserComplaint

EXEC spUserComplaintIns 'Chaya Deol'

SELECT * FROM tblUserComplaint

Note:-

You can insert record in table using query.

So, your query look like below

INSERT INTO tblUserComplaint

       (ReferenceId,UserName)

VALUES

       ((SELECT dbo.fnGenerateUserComplaintRefId()),'Aadarsh Shrivastav')

Demo is created using Microsoft Sql Server 2014