November 28, 2012 at 9:33 am
Does SQL Server Express support all programmability features?
Is a sequence a function or DDL trigger?
I am using this as a reference
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server
Objective:
Create a Waste Hauler Unique Number. For instance, 00000-V/C 0000- AT- YR
November 28, 2012 at 9:34 am
I guess I need to know where I need to start from.
November 28, 2012 at 9:49 am
sasansamani (11/28/2012)
Does SQL Server Express support all programmability features?
What do you mean by this?
Is a sequence a function or DDL trigger?
You posted this in the 2005 section so I assume you are using 2005? SQL 2012 introduced the sequence. It is an object but not a function or a ddl trigger.
I am using this as a reference
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server
Objective:
Create a Waste Hauler Unique Number. For instance, 00000-V/C 0000- AT- YR
I am guessing that you want to create some sort of incrementing complex value? This is almost always a bad idea because it it a nightmare to get right. Concurrency is a real issue with a roll your own type of solution. There is lots of pain and little to gain. It would be far easier to use an identity as one column and the rest of the "key" in a second column.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 10:03 am
thank you for your reply.
Can I use the Identity Property in SQL express 2008?
November 28, 2012 at 10:09 am
yes, identity is supported in all versions of SQL Server.
November 28, 2012 at 10:24 am
Let me explain what exactly we are trying to do.
Waste Hauler Unique Number Example: 00000-V/C 0000- AT- YR
Requirements:
Hauler # = 00000 (permanent)- there may be 1000 hauler companies
Asset = V / C (vehicle or container + permanent #)
Material/Classification = AT (landfill / temporary)- this may be different
(Material: A- Landfill B-Recycling C-Compost)(Classification: T- Temporary P- Permanent)
YR = Year (2013)
So the best way to implement this is to use the Identity Property? Is that correct?
I just need to know a logical way to implement this.
November 28, 2012 at 10:33 am
sasansamani (11/28/2012)
Let me explain what exactly we are trying to do.Waste Hauler Unique Number Example: 00000-V/C 0000- AT- YR
Requirements:
Hauler # = 00000 (permanent)- there may be 1000 hauler companies
Asset = V / C (vehicle or container + permanent #)
Material/Classification = AT (landfill / temporary)- this may be different
(Material: A- Landfill B-Recycling C-Compost)(Classification: T- Temporary P- Permanent)
YR = Year (2013)
So the best way to implement this is to use the Identity Property? Is that correct?
I just need to know a logical way to implement this.
Not sure exactly what you are trying to do from this. Are you wanting a single column to have all this information?
If I were going to generate a table to hold this data it would look something like this.
create table #Haulers
(
HaulerID int identity primary key,
Asset char(1),
Classification char(2),
Material char(1),
DateCreated datetime not null default getdate()
)
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 10:48 am
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.
November 28, 2012 at 11:25 am
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?
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 11:37 am
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.
November 28, 2012 at 11:52 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 12:00 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 12:47 pm
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.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply