March 11, 2009 at 10:17 am
Here is the statement.
SELECT AVG(FCR) AS Expr2
FROM [Wave Data - Weighted]
GROUP BY Center
HAVING (Center = N'EL PASO - NC') AND (Date BETWEEN CONVERT(DATETIME, '2008-12-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-02-28 00:00:00',102))
There are three records 0.63, 0.58, 0.63 Doing some quick math the average should be 61%, however the statement averages them and produces a 62%.
Can anyone assist me with why i am receiving the wrong info?
also here is the original record set
Center FCR Date
El Paso - NC 0.63 12/31/08
El Paso - NC 0.58 1/31/09
El Paso - NC 0.63 2/28/09
March 11, 2009 at 10:25 am
I have this figured out, it had to do with my groupings outside of the code i pasted below.
SELECT Survey, Center, AVG(FCR) AS FCR, ROUND(AVG([Total Resolved]), 2) AS [Total Resolved], ROUND(AVG([Called Once]), 2) AS [Called Once],
ROUND(AVG([OS T3]), 2) AS [OS T3], ROUND(AVG([OS B3]), 2) AS [OS B3], ROUND(AVG([WTR T3]), 2) AS [WTR T3], ROUND(AVG([WTR B3]), 2)
AS [WTR B3], ROUND(AVG(RIT3), 2) AS RIT3, ROUND(AVG(RIB3), 3) AS RIB3, ROUND(AVG([Respect T3]), 2) AS [Respect T3],
ROUND(AVG([Respect Bottom 3]), 2) AS [Respect Bottom 3], CASE WHEN [Survey] = 'Care' AND [Center] IN ('MONTERREY - SC', 'MONTERREY - W',
'MONTERREY - SE', 'Puerto Rico', 'MONTERREY - Total') THEN 'Spanish' WHEN [Survey] = 'Care' AND [Center] IN ('Northeast Total',
'Central South Total', 'Central North Total', 'West Total', 'Southeast Total') THEN 'Regional' WHEN [Survey] = 'Care' AND
IN ('Lafayette SC',
'Miami', 'Schaumburg', 'Mililani') THEN 'AM Led' WHEN [Survey] = 'Care' AND
IN ('LUBBOCK-Grace', 'LUBBOCK-Ratliff', 'LUBBOCK-Davidson')
THEN 'Lubock' WHEN
IN ('Care Total', 'BEUC Total')
THEN 'Totals' ELSE (CASE WHEN [Survey] = 'Care' THEN 'Care' WHEN [Survey] = 'BEUC' THEN 'BEUC' ELSE 'N/A' END) END AS [Group]
FROM dbo.[Wave Data - Weighted]
WHERE (Date BETWEEN CONVERT(DATETIME, '2008-12-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-02-28 00:00:00', 102))
GROUP BY Center, Survey
March 12, 2009 at 12:23 am
Hi,
What you written is correct, but do the round off for the AVERAGE
Like
declare @abc table (
Center varchar(15),
FCR numeric(8,3),
DATE1 datetime)
insert into @abc values('El Paso - NC',0.63,'2008-12-31')
insert into @abc values('El Paso - NC',0.58,'2009-01-31')
insert into @abc values('El Paso - NC',0.63,'2009-02-28')
--select * from @abc
select round(AVG(FCR),2)* 100 from @abc
group by Center
HAVING
(Center = N'EL PASO - NC')
result
61.000000
ARUN SAS
March 12, 2009 at 8:24 am
arun.sas (3/12/2009)
Hi,What you written is correct, but do the round off for the AVERAGE
Like
declare @abc table (
Center varchar(15),
FCR numeric(8,3),
DATE1 datetime)
insert into @abc values('El Paso - NC',0.63,'2008-12-31')
insert into @abc values('El Paso - NC',0.58,'2009-01-31')
insert into @abc values('El Paso - NC',0.63,'2009-02-28')
--select * from @abc
select round(AVG(FCR),2)* 100 from @abc
group by Center
HAVING
(Center = N'EL PASO - NC')
result
61.000000
ARUN SAS
Why? If you plan on reusing the average - you've now introduced a fairly sizeable margin of error by rounding. If it's for display - then let whatever is displaying it format the output.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply