Help working out averages - SQL Server Forums

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)

Help working out averages

Author

Topic

Manx
Starting Member

4 Posts

Posted - 2004-10-20 : 06:16:43

I'm new to SQL. I've setup a stored procedure to calculate a monthly average score for the previous month as follows:

CREATE PROCEDURE [dbo]. [sp_monthly_average] (@staffID int, @Qtbl [varchar](10))

AS

declare @average int

--calculate average fo question requested

if @Qtbl = 'Q1'
begin
SELECT @average = AVG (c_Q1) FROM tblPerformanceScores where DATEDIFF ( month, c_FriDate, getdate())=1 and c_LMSid=@staffID
end
if @Qtbl = 'Q2'
begin
SELECT @average = AVG (c_Q2) FROM tblPerformanceScores where DATEDIFF ( month, c_FriDate, getdate())=1 and c_LMSid=@staffID
end
if @Qtbl = 'Q3'
begin
SELECT @average = AVG (c_Q3) FROM tblPerformanceScores where DATEDIFF ( month, c_FriDate, getdate())=1 and c_LMSid=@staffID
end

--return average to ASP
SELECT @average AS 'average'
GO

This works fine but I'm struggling to create procedures to do the same thing but for a quarterly average (Jan-Mar, Apr-Jun, July-Sept, Oct-Dec) and yearly average (starting in Jan 1st).

Can anyone help me out?
Thanks in advance

a_shyam41
Starting Member

9 Posts

Posted - 2004-10-20 : 06:28:54

For quarterly average, use
"SELECT @average = AVG (c_Q1) FROM tblPerformanceScores where MONTH(c_FriDate) IN (1,2,3) and c_LMSid=@staffID" for 1st quarter and similarly for other quarters by changing the months checked

For yearly average, use
"SELECT @average = AVG (c_Q1) FROM tblPerformanceScores where YEAR(c_FriDate) = 2004 and c_LMSid=@staffID" and similalry for other years

Hope this helps

Manx
Starting Member

4 Posts

Posted - 2004-10-20 : 06:36:46

Ah that makes sense. I'll give it a whirl.

Cheers buddy

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-10-20 : 06:51:23

Also see the DATEPART function in Books Online. It lets you retrive the quarter of a given date, so your expression can be: WHERE DATEPART(qq, MyDate) = 1 AND DATEPART(yy, MyDate) = 2004, to retrive all data for the first quarter of 2004.

OS

Manx
Starting Member

4 Posts

Posted - 2004-10-21 : 06:34:58

Cheers guys.

I've got everything working but the results always round down:
ie. If the result = 14.7, the score of 14 is displayed.

I've declared the average as a float and decimal but I'm having no joy. I could work the round in asp but I'd rather be able to do it in sql.

Any idea's?

Manx
Starting Member

4 Posts

Posted - 2004-10-21 : 07:46:47

aha. I've cracked it. thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources