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.
| 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! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-15 : 03:35:22
|
then r.oee * 100 else null end
E 12°55'05.63"
N 56°04'39.26" |
 |
|
|
Girlnet
Yak Posting Veteran
77 Posts |
Posted - 2009-02-15 : 08:47:39
|
| Wow . . . how simple was that? It totally worked. Thanks much! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-15 : 11:12:03
|
That's the way COUNT and AVG works with NULL values.
Good luck.
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
|
|
|
Avg by Month