1. Registered User
Join Date
Sep 2011
Posts
44

Hi guys,

Any idea why the following code isnt giving the correct average. It should be : 22.66

declare @@empref as varchar(10)
declare @@Date as datetime
Declare @@dayofweek float
set @@empref='50025192'
set @@date= '04/09/2012'

set @@dayofweek=(select datepart (dw, @@date) as dayOfTheWeek)
if @@dayofweek=1 begin
set @@dayofweek=8
end
select * from tmsuser.tmswrhrs where empref=@@empref and hrscode='REG' and procdate>=@@date-5-@@dayofweek and procdate <@@date-@@dayofweek+2

select avg(payrate/cast (hours as float)*60) from tmsuser.tmswrhrs where empref=@@empref and hrscode='REG' and procdate>=@@date-5-@@dayofweek and procdate <@@date-@@dayofweek+2

Hours PayRate

720 285
720 285
720 234.36
702 277.875
18 5.859

Avg
22.062

Thanks!

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Why are using float, and what version of SQL Server are you using?

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Hmmm...
Try casting as decimal(10, 2) instead.

Or simply throw some precision onto your constant:

select avg(payrate/(hours*60.0))

4. Registered User
Join Date
Sep 2011
Posts
44
Thanks for the replies

As decimal gave same result.

without cast give 0.00612833333333333

I am using sql server 2008

Any other ideas

5. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Because 22.062 is the correct answer. That is the average pay rate. The average of all the individually calculated pay rates.
What you are calculating to come up with 22.66 is the WEIGHTED average pay rate. It is simply the Total Rate over the Total Hours.

6. Registered User
Join Date
Nov 2004
Posts
1,428
I have entered your data in an Excel worksheet and calculated the average. It gives 22,062.

What makes you think it should be 22.66 ?

7. Registered User
Join Date
Sep 2011
Posts
44
Thanks Blindman,

I don't know what the difference means but do you know how I get the weighted avg to print out? Is there a different function?

thanks again

8. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Weighted average should be:
sum(payrate)/sum(cast (hours as float)*60)

9. Registered User
Join Date
Nov 2004
Posts
1,428
Originally Posted by blindman
Weighted average should be:
sum(payrate)/sum(cast (hours as float)*60)
I think it's
sum(payrate * cast(hours as float))/sum(cast(hours as float))

10. Registered User
Join Date
Sep 2011
Posts
44

## Resolved

Thanks guys,

I used

select (sum(payrate)/sum(hours)*60.0)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•