Suggestions needed: Daily calculations

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Suggestions needed: Daily calculations

Author

Topic

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-05-28 : 14:22:04

Hello. I need a suggestion for the best way to attack this. I'm most concerned with doing this in a way that is most reliable and requires as little server overhead as possible.

I need to schedule a job that will run nightly that will grab many thousands of records from a table and calculate totals and averages for each fields in the table. The results calculations will be saved to another table as a single record.

The second job I need to run nightly uses the same table with the thousands of records. Each record is associated with a user in another table. I need for the second job to run its calculations for each user found in the large table. After this job is run, each user that has a record in the large table should have one record with their calculations in another table. For example, if there are 100 records in the large table and those records account for 13 users, there should be 13 records in the calcuation table.

I can write this in ASP and call it with a job, but I think that would be inefficent since IIS isn't needed to do this. Most importantly, I think it would time out due to the large number of records.

Let me know what you think and I appreciate your help. Thanks.

Huligan

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-28 : 14:32:33

Sounds Like a Job for a DTS Package

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-28 : 14:36:08

I'm thinking a scheduled stored procedure.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-28 : 14:41:37

Two stored procedures. One job with two steps. I agree with Tara.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

stephe40
Posting Yak Master

218 Posts

Posted - 2004-05-28 : 15:10:08

I also agree, this would be pretty easy as a stored proc that is scheduled to run every night.

- Eric

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-06-01 : 13:24:54

Can anyone point me to some example code of a stored procedure that grabs some records, does some calculations on them, and saves the results in another table? Thanks for the suggestions.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-01 : 13:28:17

select avg(dbid), avg(mode), sum(version)
into #temp
from master.dbo.sysdatabases

If you provided more detail such as table structure and what needs to be done, we could help you out better.

Tara

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-06-01 : 19:12:36

OK, I can do that. Here's the database structure.

tblMilk
-------
MilkID - int
MilkCost - money
MilkQuantity - float
UserID - int

tblLogAll
---------
LogAllID - int
LogAllCostTotal - money
LogAllCostAvg - money
LogAllQuantityTotal - float
LogAllQuantityAvg - float
LogAllDate - datetime

tblLogSingle
---------
LogSingleID - int
LogSingleCostTotal - money
LogSingleCostAvg - money
LogSingleQuantityTotal - float
LogSingleQuantityAvg - float
LogSingleDate - datetime
UserID - int

tblUser
-------
UserID - int

With this structure, I need for one stored procedure to take all the records from tblMilk (thousands of them) and run totals and averages for MilkCost and MilkQuantity. Those results would be saved into tblLogAll on one record in 4 fields (LogAllCostTotal, LogAllCostAvg, LogAllQuantityTotal, LogAllQuantityAvg). I also need to record the date and time it was run in the LogAllDate field.

I need another stored procedure that will run the same calculations except there should be a result record in tblLogSingle for each user that has a record in tblMilk. So it will take all records from tblMilk and run totals and averages for each user represented in those records (UserID).

Thanks again for the help. I appreciate it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-01 : 19:22:16

This should get you started:



CREATE PROC Proc1
AS

INSERT INTO tblLogAll (LogAllCostTotal, LogAllCostAvg, LogAllQuantityTotal, LogAllQuantityAvg, LogAllDate)
SELECT SUM(MilkCost), AVG(MilkCost), SUM(MilkQuantity), AVG(MilkQuantity), GETDATE()
FROM tblMilk

RETURN 0
GO

Tara

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-06-08 : 07:52:35

Thanks tduggan. That was exactly what I needed to get started. My final stored procedure ended up much different and larger, but your example code got me started down the right road. Thanks again.

Huligan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources