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)
 Stored procedure: Dynamic loop? Loop with sort?

Author  Topic 

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-06-08 : 08:00:09
Hello. I need to create a stored procedure that (I think) needs a dynamic loop or a loop on all records with a sort. Here are the tables involved.

tblMilk
-------
MilkID - int
MilkCost - money
UserID - int

tblLogMilk
---------
LogMilkID - int
LogMilkCostTotal - money
LogMilkCostAvg - money
LogMilkDate - datetime
UserID - int

tblUser
-------
UserID - int

I need a stored procedure to grab all records (thousands) from tblMilk. I need to group together the records by UserID. For each group of records, I need to calculate a sum and average for MilkCost and save those values to tblLogMilk with the UserID.

I know how to do this in ASP, but I'm struggling with finding the syntax I need. In ASP, I would sort the records by UserID and loop through the records. Everytime the UserID changes, I would perform my calculations, save them to tblLogMilk and reset the variables used to total the values from tblMilk.

Is this the way it should be done in a stored procedure or is there a better way? Either way, I would appreciate some syntax suggestions. Thanks.

Huligan

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-08 : 08:21:30
Do you have Books Online (the SQL Server User Manual)? Look up GROUP BY.

Your query would be similar to:

SELECT UserID, SUM(MilkCost), AVG(MilkCost) FROM tblMilk GROUP BY UserID

OS
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-08 : 08:23:21
No Loops Please

Delete
From dbo.tblLogMilk

Insert into dbo.tblLogMilk
(logUserid,logMilkID,logMilkCostTotal,logMilkCostAvg)

Select UserID as logUserid, milkid as logMilkID, Sum(Milkcost) As logMilkCostTotal, Avg(Milkcost) AS logMilkCostAvg
From dbo.tblmilk
Group By UserID, MilkID

Jim
Users <> Logic
Go to Top of Page

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-06-13 : 09:08:47
Thanks for the help Jim. The only problem I had was the INSERT statement kept creating errors. Then I saw you edited your post and the correction took care of my error. Thanks again.
Go to Top of Page
   

- Advertisement -