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 |
|
typo
Starting Member
14 Posts |
 Posted - 2011-08-26 : 14:11:25
|
| I'm using SQL Server 2005. And I'm trying to create a custom sequence for an equipment_id. Format ABC-YEAR-0001 ex: ABC-2011-0001, ABC-2011-0002, ABC-2011-0003 . The client wants to reset the last 4 digits at the start of each new year. Ex: ABC-2012-0001How can I accomplish this in a proc or function?Thanks |
|
|
gwilson67
Starting Member
42 Posts |
Posted - 2011-08-26 : 15:18:32
|
| Try the following:select field1, 'ABC-+convert(varchar(25),datepart(yyyy,getdate())) + '-' + convert(varchar(25), row_number() over (order by (select 1))) from tableNameGreghttp://www.freewebstore.org/tsqlcoderepositoryPowerful tool for SQL Server development |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-26 : 15:34:43
|
| >>custom sequence for an equipment_idIf this will be a Key used as a primary key and/or for referential integrity then (In my opinion) a custom sequence would be mistake. For a surrogate key go with identity or (nonclustered guid), or sequencial guid.If the client needs this "smart key" as some sort of display thing - and you can't talk them out of it - then just make it an attribute of the table with a unique constraint. Can I assume that once a value is associated with a given piece of equipment it needs to stick permanently, right? So an on demand solution based on row_number() is out of the question? Generating sequential keys manually is always risky with regards to concurrency. You're bound to get collisions unless your db is in single user mode. If I haven't talked you out of it yet post back we can come up with a hack. Can I also assume that you'll need the solution to be able to handle multi-row inserts?Be One with the OptimizerTG |
|
|
|
gwilson67
Starting Member
42 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-26 : 17:06:47
|
quote:
Originally posted by gwilson67
reset the last 4 digits -- use :DBCC CHECKIDENT (yourtableName, reseed, @NewStartSeedValue)Greghttp://www.freewebstore.org/tsqlcoderepositoryPowerful tool for SQL Server development
That would work if you kept a seperate identity column for the sequence portion of the code and it would solve the concurrency problem. However if they want a separate sequence for each prefix then it wouldn't work. ie ABC-2011-0001 and DEF-2011-0001.Be One with the OptimizerTG |
|
|
|
typo
Starting Member
14 Posts |
Posted - 2011-08-28 : 10:28:41
|
| It is more of a display thing. Sort of like a barcode to be attached to equipment. The first 3 letters (ABC) will never change. It will not be used as a primary key. It looks like this might do the trick.select field1, 'ABC-+convert(varchar(25),datepart(yyyy,getdate())) + '-' + convert(varchar(25), row_number() over (order by (select 1))) from tableNameBut how do I get the last 4 digits to reset automatically back to 0001 at the start of each new year? That's where I'm stuck.Thanks. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-28 : 22:48:48
|
| Then you could simply add a PARTITION BY in you over clause:'ABC-+convert(varchar(25),datepart(yyyy,getdate())) + '-' + convert(varchar(25), row_number() over (partition by datepart(yyyy,getdate()) order by equipmentid)) I changed the order by so that your results would be more consistent one run to the next. However, as your data changes you may get a different sequence value for the same equipment_id. That would not be good if you are using this value as a reference to a specific row.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Custom sequence logic troubles?