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 |
|
Maelstrom3k
Starting Member
2 Posts |
 Posted - 2007-11-07 : 01:31:15
|
| Greetings friends:I am a student at DePaul University in Chicago, IL. We have a big db project tomorrow and I'm proud with the work I've done so far, I've designed my own db and tested it using queries and such. Pretty good for being introduced to SQL just a couple short months ago. Anyway, I've sort of run into a wall here. I need to have an attribute of a table be computed from others.I'm working with 2005 Server Management Studio and I have found the computed column specification under the column properties tab. I know that I have to enter a formula, but I'm just not sure on what to do. I have a CHG_HOUR attribute in a table called "EXPERTISE" which signifies how much a consultant charges per hour, based on what he exactly does. I also have a JOB_HOURS attribute in a JOB table (that links my CONTRACT table to CONSULTANT). I want to basically give a formula that multiplies the CHG_HOUR from the EXPERTISE table by the JOB_HOURS in the JOB table. Any suggestions on how I might do this? Thank you in advance for your response, and hopefully with more practice and courses, I can be the one helping people like me on these boards in a few years. |
|
|
Maelstrom3k
Starting Member
2 Posts |
Posted - 2007-11-07 : 01:35:47
|
| By the way, I want my new column to be called JOB_COST. Would you guys place it in the JOB or CONTRACT table? I already have an "ESTIMATED_COST" column in the contract table. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-07 : 01:50:40
|
| "Any suggestions on how I might do this?"What happens if the CHG_HOUR from the EXPERTISE table changes in the future? Retrospectively all completed jobs will be increased in price ...I think you need a REAL column here, not a calculated one. Possibly using a Trigger on the table to ensure that the calculated field is up to date whenever the JOB_HOURS changes - in which case I would put the new column in the JOB table. You would then need some sort of Job you could run if the CHG_HOUR in the EXPERTISE table ever changed, which would decide WHICH rows in JOB_HOURS actually needed to be re-costed.If you only need a "virtual" column then I would use a VIEW and keep the calculation out of the table; I am not very keen on computed columns because of knock-on effects of their requirements which tend to catch you out much later on.Kristen |
|
|
|
|
|
|
Hello--First Post & Already Need Help!