Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Generated Sequences with SQL Server Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 11:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 12,004, Visits: 11,035
sasansamani (11/28/2012)
Sean Lange (11/28/2012)
sasansamani (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.


Honestly I am trying to help but you just aren't giving me any details. What is that format? Keep in mind I can't see your screen and I have no knowledge of your tables or your project.


_______________________________________________________________

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)
Post #1390028
Posted Wednesday, November 28, 2012 12:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 12,004, Visits: 11,035
sasansamani (11/28/2012)
Sean Lange (11/28/2012)
sasansamani (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,
Asset char(1),
Material char(1),
Classification char(1),
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
)

insert #Haulers
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)
Post #1390034
Posted Wednesday, November 28, 2012 12:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 11:52 AM
Points: 28, Visits: 50
thank you very much. I realized using SQL managment studio to generate these permit numbers is a lame idea. They can just use Microsoft Access form to generate these alpha numeric permits. They pick the value and then it generates the alpha numeric permits.

Thank you everyone.
Post #1390075
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse