Incrementing ID puzzle

[sparkbyte]

I have a DB one of our departments is using. (Access 2003)

What they have been doing is at the end of each contract, is to recreate the primary table and archive the old data so that the ID feild counter starts over.

I am thinking of moving this to SQL2005, but the incrementing field is bothering me.

The scenerio is that the contract date is from Dec 1 - Nov 30.

so rows added on dec 1, 2010 would start with ID 1 increasing by 1 until Nov 30 starting over Dec 1 again.

Bogus Table to show what I am trying to do.

PK ID CSR Date
1 1 John 12/01/2009
2 2 Don 01/01/2010
3 3 John 05/21/2010
4 4 Don 11/22/2010
5 1 John 12/05/2011
6 2 Don 05/11/2011

Declare @Test Table 
(
		PK Int,
		ID VarChar(4),
		SalesName VarChar(12),
		DateEntered DateTime
)
Insert Into @Test Values(1,'1','Mike','01/12/2009')
Insert Into @Test Values(2,'2','John','02/13/2009')
Insert Into @Test Values(3,'1','John','01/12/2010')
Insert Into @Test Values(4,'2','John','02/12/2010')
Insert Into @Test Values(5,'3','Mike','03/12/2010')
Insert Into @Test Values(6,'4','John','04/12/2010')
;

	Select Cast(Max(dbo.fnGetNumbers(ID))+1 As VarChar(1)) As ID 
	from @Test 
	where DateEntered Between DATEADD(yy, DATEDIFF(yy,0,getdate()), -365) -- 1st Prior Last Year
			and dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0)) -- Last Day Prior Year

Declare @Next Int
Insert Into @Test
	Select 
		7,
			(Select Cast(Max(dbo.fnGetNumbers(ID))+1 As VarChar(1)) 
				from @Test 
				where DateEntered >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) As ID,
		'John', '5/11/2010'
	from @Test

Select * from @Test
[\code]

This is close but this is the result set.

[code]
PK          ID   SalesName    DateEntered
----------- ---- ------------ -----------------------
1           1    Mike         2009-01-12 00:00:00.000
2           2    John         2009-02-13 00:00:00.000
3           1    John         2010-01-12 00:00:00.000
4           2    John         2010-02-12 00:00:00.000
5           3    Mike         2010-03-12 00:00:00.000
6           4    John         2010-04-12 00:00:00.000
7           5    John         2010-05-11 00:00:00.000
7           5    John         2010-05-11 00:00:00.000
7           5    John         2010-05-11 00:00:00.000
7           5    John         2010-05-11 00:00:00.000
7           5    John         2010-05-11 00:00:00.000
7           5    John         2010-05-11 00:00:00.000

Thanks

John Fuhrman

 

[sparkbyte]

Please delete...Duplicate post

Thanks

John Fuhrman