sasansamani (11/28/2012) Sean Lange (11/28/2012)
Our department wants to automatically generate permit numbers for our customers when they apply for a license.They want something similar to a key generator. Yes SQL Server is my only resource at this moment.
I can help but you have to help me understand the scope of what you are trying to do. Is there a certain format you need this to be in? Is there any reason these permit numbers can't be numeric?
This is the format they want 00000-V/C 0000- AT- YR
They want to be able distinguish if it’s a vehicle or container. They want to be able distinguish if it’s a temporary or permanent. It can’t be a numeric permit number.
I am going to take a shot in the dark as to what you want. It seems that most your "key" is made up of values already in the table. This is where you could use a computed column. It also seems from earlier that you have some poor decisions in your table structure. It seems like you have material and classification as a single field. This is what is known as attribute splitting. Each column should contain one and only one value. Don't make things harder for yourself. Also you want to store the year. I would not do this as an int column. Instead make it datetime and then you can return only the year portion if you want.
Here is my take on how you could do this. You may have to
IF OBJECT_ID('TempDB..#Haulers','U') IS NOT NULL
drop table #Haulers
create table #Haulers
HaulerID int identity primary key,
DateCreated datetime not null default getdate(),
PermitNumber as right(replicate('0', 5) + cast(HaulerID as varchar(5)),5) + '-' + Asset + Material + Classification + cast(year(DateCreated) as char(4)) PERSISTED
select 'A', 'M', 'C', GETDATE() union all
select 'T', 'U', 'X', GETDATE()
select * from #Haulers
Please notice the PERSISTED keyword at the end of the column definition. This is important so you can add an index to this column. You can read more about persisted columns here. http://msdn.microsoft.com/en-us/library/ms191250%28v=sql.105%29.aspx
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's splitter
.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)