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 |
|
osmansays
Starting Member
6 Posts |
 Posted - 2008-04-30 : 12:24:48
|
| I have this table where there is tow primary keys ,one for Year and the other for DocNumber.In this table the DocNumber takes a number based on each year,for example in year 2008 the DocNumber should start counting from 1 and increment by one for every record,when the year changes to 2009 the DocNumber should start again from 1 until and seed by one again.Those columns are PKs because there must be one doc Number for each year and they can not be null .Is there any simple way to approach this in Sql Server 2005 ,or do I have to work on trigger to do so...???Any ideas will be really appreciated.Eng.Osman Hamadhttp://arabic.aspose.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-30 : 12:29:30
|
| [code]SELECT ROW_NUMBER() OVER(PARTITION BY Year ORDER BY Year) AS DocNumber,*FROM YourTable[/code] |
 |
|
|
osmansays
Starting Member
6 Posts |
Posted - 2008-04-30 : 12:40:54
|
| thanks visakh16 ,could you please let me know how to set this value in DocNumber field ,can I use a trigger to perform this even if the DocNumber is PK .Eng.Osman Hamad |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-30 : 12:44:46
|
| Nope. Use it in Insert statement itself. |
|
|
|
osmansays
Starting Member
6 Posts |
Posted - 2008-04-30 : 12:58:36
|
| Thanks again visakh16 ,you really saved me... :)Eng.Osman Hamad |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-30 : 13:00:37
|
quote:
Originally posted by osmansays
Thanks again visakh16 ,you really saved me... :)Eng.Osman Hamad
You're welcome  |
|
|
|
|
|
|
Identity PK column based on other PK...please help