How do I automattically reset a sequence value to 1 in every year starts

Question

2.00/5 (1 vote)

See more:

SQL-Server-2005

, +

Hi

Doid I am getting by counting number of records in database.
My requirement is if it is a new year the count must start again from 1
How do I automattically reset a sequence value to 1 in every year starts

Example(2011)

2011scl001-D0
2011scl002-DO
2011scl003_Do

(2012)

2012scl001-D0
2012scl002-DO
2012scl003_Do

Thanks

Posted 11-Feb-12 20:39pm

Lancy.net

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Answer 1 · 2012-02-11T21:19:00

See this and modify as per your table stucture

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--DELETE FROM Scl_info 
--SELECT * FROM Scl_info 
--EXEC [sp_SaveSclinfo] '2012scl002','GHI'
CREATE PROCEDURE [dbo].[sp_SaveSclinfo]
 (
	@SclId NVARCHAR(50) ,
	@SclName NVARCHAR(50) 
 )
 AS 
 BEGIN
  DECLARE @Scl_Id NVARCHAR(50)
  DECLARE @SclNumber Bigint	
  
  If EXISTS (SELECT * FROM Scl_info WHERE SclId=@SclId)
   Begin
       Update Scl_info
        SET 					
			SclName=@SclName 
       WHERE SclId=@SclId
   End
  Else
   Begin
		SELECT @SclNumber =(COALESCE(MAX(SclNumber),0) +1) FROM Scl_info WHERE SclYear=Year(GetUtcDate())

		SELECT @SclId=CAST(Year(GetUtcDate()) AS NVARCHAR(50)) + 'scl00' +  CAST(@SclNumber AS NVARCHAR(50)) + '-D0' 

		INSERT INTO Scl_info SELECT @SclId,@SclNumber,Year(GetUtcDate()),@SclName
   End		
END

Hope this helps if yes then accept and vote the answer otherwise revert back with your queries
--Rahul D.

Aniket Yadav · Answer 2 · 2012-02-12T00:54:00

If you have three separate data lets say You have
1. CurrentDate
2. CustID
3. CurrentMonth

Then,

C#

DoId = getDoId(YearofCurrentDate, CustId, CurrentMonth);

Call the getDoId Method

C#

private string getDoId(String YYYY, String C_Id, String Month)
{
    String ID = "";
    ID = YYYY + C_Id + ("00") + Month;
    return ID;
}

So Passing the correct year, custid, month will give you the desired output

Hope this will help you

How do I automattically reset a sequence value to 1 in every year starts

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0