自动生成编号有关问题

自动生成编号问题
我有一个编号模板为：ADS/WT-2012-05XX (XX代表如01,02,03 之前的个数+1)(2012是插入当前年)

假如我插入的时间在4.21到5.20这段时间则显示为ADS/WT-2012-05XX  
假如我插入的时间在5.21到6.20这时间则显示为ADS/WT-2012-06XX  

这个要怎么写

[解决办法]

SQL code

--下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。--得到新编号的函数CREATE FUNCTION f_NextBH()RETURNS char(8)ASBEGIN    RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))ENDGO--在表中应用函数CREATE TABLE tb(BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),col int)--插入资料BEGIN TRAN    INSERT tb(col) VALUES(1)    INSERT tb(col) VALUES(2)    INSERT tb(col) VALUES(3)    DELETE tb WHERE col=3    INSERT tb(col) VALUES(4)    INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)COMMIT TRAN--显示结果SELECT * FROM tb/*--结果BH         col ---------------- ----------- BH000001  1BH000002  2BH000003  4BH000004  14--*/
[解决办法]
SQL code
if OBJECT_ID('fun_test')is not nulldrop function fun_testgocreate function fun_test(@id int,@date datetime)returns varcharas beginreturn 'ADS/WT-'+case when day(@date)>=20           then right('00'+ltrim(month(@date)+1),2)+right('00'+ltrim(@id),2)          else right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) endendgo
[解决办法]
SQL code
---->>>TravyLee生成测试数据if OBJECT_ID('fun_test')is not nulldrop function fun_testgocreate function fun_test(@id int,@date datetime)returns varchar(20)as beginreturn 'ADS/WT-'+case when day(@date)>=20           then right('00'+ltrim(month(@date)+1),2)+right('00'+ltrim(@id),2)          else right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) endendgoif OBJECT_ID('test') is not nulldrop table testgocreate table test(id int identity,dates date)goinsert test(dates)select '2012-05-19' union allselect '2012-05-20' union allselect '2012-05-21' union allselect '2012-05-22'select dbo.Fun_test(id,dates) as BH,dates from test/*BH    dates----------------------------------ADS/WT-0501    2012-05-19ADS/WT-0602    2012-05-20ADS/WT-0603    2012-05-21ADS/WT-0604    2012-05-22*/
[解决办法]
SQL code
---->>>TravyLee生成测试数据if OBJECT_ID('fun_test')is not nulldrop function fun_testgocreate function fun_test(@id int,@date datetime)returns varchar(20)as beginreturn 'ADS/WT-'+case when day(@date)>=20           then ltrim(year(dateadd(mm,1,@date)))+'-'+right('00'+ltrim(month(dateadd(mm,1,@date))),2)+right('00'+ltrim(@id),2)          else ltrim(year(@date))+'-'+right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) endendgoif OBJECT_ID('test') is not nulldrop table testgocreate table test(id int identity,dates date)goinsert test(dates)select '2012-05-19' union allselect '2012-05-20' union allselect '2012-05-21' union allselect '2012-05-22' union allselect '2012-12-20' union allselect '2012-12-21' select dbo.Fun_test(id,dates) as BH,dates from test/*BH    datesADS/WT-2012-0501    2012-05-19ADS/WT-2012-0602    2012-05-20ADS/WT-2012-0603    2012-05-21ADS/WT-2012-0604    2012-05-22ADS/WT-2013-0105    2012-12-20ADS/WT-2013-0106    2012-12-21*/