| Author |
Topic |
|
CSDevMan
Starting Member
5 Posts |
 Posted - 2008-01-04 : 09:17:00
|
Hello Everyone, I am reposting this in a little different format as the original post did not return any suggestions.Here I am fresh out of school and working on my first demo project to show potential employers and I am running into a snag with the database. So I am turning to you experts for some help, Thanks by the way as I have learned a lot by reading the articles and posts on this site!  Here is the relevant background:I am building an ASP.Net in C# web app. with a SQL DB (I am working with SQL Server 2005 Management Studio Express) the App. will be an online printing ordering system which I am basing off a system we use in my current job. I would actually like to get this all completed and leave as a gift (the current system is outdated) for my current employer before I head off to my new career!!Here is the layout of my test Orders table:[ID_Num] [int] IDENTITY(1,1) NOT NULL,[Date] [smalldatetime] NOT NULL,[Quantity] [int] NOT NULL,[Ord_Num] [char](15) -- to be created via ???I have the Identity column and everything working (Thanks to this site!) except the Ord_Num column. This is where I am struggling, on the creation of a new record I want to combine the ID_Num and the Date to create the actual Order_Num. So the finished order number will look like this “123107000001”.What is the best way to accomplish this?I read in an article on this site that it could be done via a trigger or computed column but I ran into trouble trying to do what I described above as it was a little different from the article.Please provide code sample and some reasoning behind the method so I can learn and understand this a little better. Thanks in advance for the help!!Thanks, Kurt |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-01-04 : 10:09:10
|
| Kurt,Let's get the "rules" right first.It looks like you want to have this ID_Num consists of three parts:1) date as MMDDYY, 2) ID_Num part (1 as your sample data shows), and 3)zeros (00000) in between to fill up the whole length.Some of experts on this site may argue why there is a need for such a key on your table. But if that is what you want based on above rules, I will try some thing for you. |
 |
|
|
CSDevMan
Starting Member
5 Posts |
Posted - 2008-01-04 : 11:16:33
|
| Thanks Hommer for your response and your willingness to help it is very much appreciated!I know it is not the ideal way but the staff using this get a lot of phone calls to check on orders. By having the order number like this (with the date in it) they can tell based on their turn around time if the order is almost complete without the need to go look up the number every time the phone rings. Thanks for clarifying. This should actually just be two parts the 1) the Date the order is placed 2) the ID_Num Identity field which should be formatted with the leading zeros i.e. 000001. Not sure how to do that, I assume making the seed 000001? So 1 & 2 combined to create the Ord_Num.For the record, I am open for any suggestions too! Pro’s or Con’s I am not above constructive criticism just eager to learn all I can!!Thanks again!Thanks, Kurt |
|
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2008-01-04 : 12:38:14
|
| create a function which accepts date and ID_num as input and returns concatenated carchar fields of date (as MMDDYY) and the ID_Num (padding zeros on the left) and change the DDL from [Ord_Num] [char](15)to[Ord_Num] AS ([dbo].[your_new_function]([Date], [Ord_Num]))here's the SQL to formate your date and ID_num-------------------------------------------------------------select right(cast(datepart(mm,getdate()) + 100 as varchar),2) + right(cast(datepart(dd,getdate()) + 100 as varchar),2) + right(cast(datepart(yy,getdate()) as varchar),2)-------------------------------------------------------------SELECT REPLICATE('0', (6 - LEN(CAST(1 as VARCHAR)))) + CAST(1 as VARCHAR)------------------------------------------------------------- |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-01-04 : 12:46:14
|
| From what you said is *sounds* like the date will change over time so this might a candidate for a computed column (you can look computed columns up in BOL (books online)). |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-01-04 : 12:47:10
|
| Ok,ID_Num as INT will not give you a way to store the leading zeros. That has to be done in a character type field, unless I am missing some obvious trick.Also, Ord_Num as Char(15) may run out space depending how ID_Num goes. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-01-04 : 13:18:52
|
| Here is a solution. You may need to twist the formating of the date.create table testOrders ([ID_Num] [int] IDENTITY(1,1) NOT NULL,[Date] [smalldatetime] NOT NULL,[Quantity] [int] NOT NULL,[Ord_Num] [char](15)) -- to be created via ???Insert INTO testOrders([Date], Quantity,Ord_Num ) Values ( getdate(), 1, 'test')select * from testOrdersCreate Trigger tr_InsertOrd_numON testOrdersAfter InsertASDeclare @Ord_Num as char(15)Set @Ord_Num = rtrim((Select convert(char(10), [DATE], 112) from Inserted)) + (Select CAST(ID_Num as varchar) from Inserted)Update testOrders set Ord_Num = @Ord_NumWhere ID_Num = (Select ID_Num from Inserted)Insert INTO testOrders([Date], Quantity ) Values ( getdate(), 2)select * from testOrders |
|
|
|
CSDevMan
Starting Member
5 Posts |
Posted - 2008-01-04 : 15:05:27
|
| Wow!! Thanks to everyone for their time and talent!!! I really appreciate it!I can't wait to get home and give these a try. I will post back after I have tried these.Thanks again,Kurt |
|
|
|
|
Help!! Combining two columns.