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 |
|
sj1187534
Starting Member
5 Posts |
 Posted - 2004-04-22 : 13:41:13
|
| Hi..I have table that has the following fields:ID, S, M, T, W, Th, F, Sa, LW, LM, Tmp, TotI think you can guess what the first 8 fields stand for. The others:LW - Last WeekLM - Last MonthTmp - TempTotalTot - TotalWhat I want to achieve :1) Once every week, I want to sum up the values for all days of week and store them in LW. And I want to store the sum of values for that month in the Tmp field.2) Once every month, I want to update 'total' from 'tmp'.What do you think is the best way to do it. I am not so convinced with the way I designed the table. If the design is OK, how can we perform the the required task periodically, like once every week and every month. I have an idea of using some sort of global variables but I have no idea of how to store a global variable in SQL server.Thanks,SJ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-22 : 13:44:07
|
| No, the design is not ok. What information is being stored in those columns? Show us some data. Your structure should be something like this:TableID, Info, CreateDatePeriod tasks can be accomplished through scheduling jobs in SQL Server.Tara |
 |
|
|
sj1187534
Starting Member
5 Posts |
Posted - 2004-04-22 : 13:53:44
|
| The data I am storing is the traffic for each ID. I dont have any data as yet in the table. Thats one good thing as I may have to change the design. The values in the "Days" field are incremented by one for every request that is made for a specific "ID".SJ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-22 : 14:00:29
|
| You should consider a normalized approach. What you have proposed is denormalized.Tara |
|
|
|
sj1187534
Starting Member
5 Posts |
Posted - 2004-04-22 : 14:04:24
|
| Okay...Lets say that I designed a normalised table. What about the global variables? How do we maintain them in SQL Server?SJ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-22 : 14:11:01
|
| There is no such thing in SQL Server. You would just calculate the information when you need to pull it up or store the calculated value in a table.Tara |
|
|
|
sj1187534
Starting Member
5 Posts |
Posted - 2004-04-22 : 14:14:30
|
| Ok...I guess I need to change the design then.Can you explain more about this:"Period tasks can be accomplished through scheduling jobs in SQL Server"SJ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-22 : 14:18:27
|
| Jobs can be found in EM under Management, SQL Server Agent, jobs. I would just calculate the information when I needed it. Have a look at aggregate functions in SQL Server Books Online.Tara |
|
|
|
|
|
|
|
|
Periodical execution and global variables