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

This is close but this is the result set.

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]

I assume this will need to be done in an INSTEAD OF trigger but wanted to see how it would be done before tackling the conversion.

Thanks

John Fuhrman

 

[sparkbyte]

OK found the answer...


remove the From @Test in the insert statement.

Thanks

John Fuhrman

 

[davejam]

Now that it works, i would review the following if there is ever a possible chance of having more than 9 entries in a given year!!

Cast(Max(dbo.fnGetNumbers(ID))+1 As VarChar(1))

does this work when it is the first record for a given year, may have to have some kind of isnull(x,0) handling in there!!

Just a thought

daveJam

it works on my machine, so technically i win!