The stored procedure below will increment the value(SubmissionNumber) starting with value 1 for every formcode.
If a new formcode is found, it sets the submitnumber to 1 and starts incrementing by one for the subsequent requests.
If there is one already, it increments by 1.
The following stored procedure implements auto increment for every formcode. But I want the SubmitNumber to start from 1 for every form code in every year
ALTER PROC [dbo].[usp_SubmissionHeaderInsert]
(
@FormId uniqueidentifier,
@FormCode varchar(10),
@FormTitle varchar(200),
@User varchar(50),
@Url varchar(255) = NULL,
@Host varchar(50),
@RemoteHost varchar(50) = NULL,
@UserAgent varchar(255) = NULL,
@Referrer varchar(255) = NULL,
@SubmissionId uniqueidentifier out,
@SubmitSequence varchar(30) out
) AS
BEGIN
DECLARE @SubmitNumber int
SELECT @SubmitNumber = ISNULL(MAX(SubmitNumber),0) + 1
FROM [dbo].[SubmissionHeader]
WHERE FormCode = @FormCode
DECLARE @InsertedHeader TABLE (SubmissionId uniqueidentifier, FormCode varchar(10), SubmitNumber bigint)
INSERT INTO [dbo].[SubmissionHeader]
([FormId],[FormCode] ,[SubmitNumber] ,[FormTitle] ,[User], [Url] ,[Host], [RemoteHost] ,[UserAgent] ,[Referrer],[calendaryear])
OUTPUT inserted.SubmissionId, inserted.FormCode, inserted.SubmitNumber
INTO @InsertedHeader (SubmissionId, FormCode, SubmitNumber)
SELECT
@FormId,
@FormCode,
@SubmitNumber,
@FormTitle,
@User,
@Url,
@Host,
@RemoteHost,
@UserAgent,
@Referrer
year(getdate())
SELECT
@SubmissionId = SubmissionId,
@SubmitSequence = CONVERT(varchar(30),[FormCode] + '-' + CONVERT(varchar(30), SubmitNumber))
FROM @InsertedHeader
The table is as below:
FormCode SubmissionNumber CalendarYear
ABC 1 2016
DEF 1 2016
GHI 1 2016
ABC 2 2016
DEF 2 2016
ABC 3 2016
DEF 3 2016
ABC 4 2016
DEF 4 2016
GHI 2 2016
In the year 2017, the SubmissionNumber should be reset to 1 for each form code in 2017 and should increment for every
further insert. That is, the table should be as below altogether
FormCode SubmissionNumber CalendarYear
ABC 1 2016
DEF 1 2016
GHI 1 2016
ABC 2 2016
DEF 2 2016
ABC 3 2016
DEF 3 2016
ABC 4 2016
DEF 4 2016
GHI 2 2016
ABC 1 2017
DEF 1 2017
GHI 1 2017
ABC 2 2017
DEF 2 2017
ABC 3 2017
DEF 3 2017
ABC 4 2017
DEF 4 2017
GHI 2 2017