Avg by Month - 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)

Avg by Month

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2009-02-14 : 22:25:13

I have data like this ...

Date OEE WorkGroupName
1/1/09 1.2 Press Room
1/2/09 5.2 Press Room

2/1 4.3 Press Room
2/2 1.2 Press Room

I'm trying to get an average by Month - so the output would be

WorkGroupName Jan Feb
PressRoom AVG for Jan AVG for Feb

While I get the correct formatted output - the average is wrong. Here's the query . .

SELECT wc.WorkGroupName,
Convert(decimal(9,1), Avg(Case when Month(r.RunDate) = 1 Then r.OEE * 100 Else 0 End)) as Jan,
Convert(decimal(9,1), Avg(Case when Month(r.RunDate) = 2 Then r.OEE * 100 Else 0 End)) as Feb
FROM dbo.TAM_DP_QAD_Reporting AS r INNER JOIN
dbo.QAD_WC AS wc ON r.WC = wc.WCID
GROUP BY wc.WorkGroupName, r.Plant, wc.Status
HAVING (r.Plant = 3047) AND (wc.Status = 1)
ORDER BY wc.WorkGroupName

Any help would be sooooo appreciated!