Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
macca
Posting Yak Master
146 Posts |
 Posted - 2005-05-24 : 09:59:54
|
| I am using the following code to increment a number by 1:SELECT RIGHT(YEAR(GETDATE()), 4) AS Year, MAX(num) + 1 AS NextFileNumFROM numsWHERE (numyear = RIGHT(YEAR(GETDATE()), 4))The problem I am having is that this increments from 0 to 10 but once the num value is at 10 it will not increment anymore, it keeps returning 10 instead of incrementing up to 11.numyear and num are columns that contain values in a table called nums.Anyone any ideas?Thanks. |
|
|
Sully
Starting Member
30 Posts |
Posted - 2005-05-24 : 13:09:39
|
| macca, the code you've posted does not show how you are updating the table. Can you give us more info.Stuck in neutral in a pedal to the metal world,Sully |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-05-24 : 13:10:53
|
| If you want to have an auto-incrementing value for FileNum, why not make FileNum an identity column?Sounds like the max(num) for your criteria is 9 so you'll always get 10 as the next value. That's not to mention bigger issues you'll have with contention when many people are trying to generate new FileNums.>>numyear and num are columns that contain values in a table called numsThank you for that helpfull explanation, but the DDL and DML would server our purposes better :)Be One with the OptimizerTG |
|
|
|
|
|
|
Incrementing numbers