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 |
|
mdhingra01
Posting Yak Master
179 Posts |
 Posted - 2004-09-16 : 09:58:47
|
| I know I can have a computed field in a table. I am currently setting a column to equal the the following columns in the same table (Day1+day2+day3+day4....Day+31). Is it possible to use a sum (Day1:Day31) or something like that rather than typing all the column names into the formula field in Table design.Thanks? |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2004-09-16 : 10:03:51
|
No, there isn't!I know it's nothing to do with me or anything but what is the wisdom behind storing the sum of your other fields?! Waste of space if you ask me, I mean you can always do this at run time surely.------------->>> BREAKING NEWS!!! <<<------------- Saddam Hussien has weapons of mass destrcution |
 |
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-09-16 : 10:06:45
|
| I didn't think it wasted space as it wasn't storing any data. I thought it was only performing a calc at the time the column was queried. Is my theory wrong?Thanks |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2004-09-16 : 10:15:03
|
quote:
I am currently setting a column to equal the the following columns in the same table (Day1+day2+day3+day4....Day+31)
Confused me a little actually. Anyway, you could create a function for this, I mean how often will you be repeating this calculation?------------->>> BREAKING NEWS!!! <<<------------- Saddam Hussien has weapons of mass destrcution |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-09-16 : 10:56:54
|
| I only need to do this to about 5 tables, but am lazy and want an easy way to do this. No the column names are not the same for the 5 tables.Thanks |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-16 : 12:33:58
|
| You should normalize your database design and not store days as columns in your table. then, of course, you won't have this problem. YOu can always cross-tab your table in a VIEW to make it appear like you have 1 day stored per column. but logically and physically, you are better off storing the days as rows.i.e., if you have:SOmeID, Month, Day1, Day2, ... Day31ABC,1,10,20,...30What does Day1-Day31 represent? your data doesn't tell you. it is hours, cost, or something else? what do you in in months with < 31 days? What if you need to store 2 pieces of information per day in that table -- how do you do that with this design?However, if you have:SomeID, Month, Day, HoursABC,1,1,10ABC,1,2,20...ABC,1,31,30Then your design is more flexible and makes more sense. you can handle any number of days you need to, and you can add other columns for other measures as necessary.And, of course, the whole point of this is now your problem is solved. All you need to do to get the results you asked for in your question is:Select SomeID, SUM(Value) as TotalFrom YourTableI hope this makes some some; read up on normalization for more info. Trust me -- it is worth it to understand this stuff, it'll make your life much easier in the long run.- Jeff |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-09-16 : 13:08:49
|
| Thanks that is a great help. I will look into this. |
|
|
|
|
|
|
|
|
Calculated field