每年开始如何自动将序列值重置为1 [英] How do I automattically reset a sequence value to 1 in every year starts

问题描述

嗨


Doid我是通过计算数据库中的记录数来获取的.
我的要求是，如果是新的一年，则计数必须重新从1开始
每年开始如何自动将序列值重置为1

范例(2011)

2011scl001-D0
2011scl002-DO
2011scl003_Do

(2012)

2012scl001-D0
2012scl002-DO
2012scl003_Do

谢谢

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

推荐答案

请参阅此表并根据您的表结构进行修改

See this and modify as per your table stucture

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

希望这会有所帮助，如果是，则接受并投票回答，否则返回您的查询
--Rahul D.

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

如果您有三个单独的数据，可以说您有
1. CurrentDate
2. CustID
3. CurrentMonth

然后，

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

Then,

DoId = getDoId(YearofCurrentDate, CustId, CurrentMonth);

调用getDoId方法

Call the getDoId Method

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

这篇关于每年开始如何自动将序列值重置为1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！