Getting percentage

[JJ297]

How do I add the icode to this query to get the percentage of calls
received / calls offered?

select sum(isnull([calls offered],0)) as CO,
sum(isnull([calls received],0)) as CR,
sum(isnull(Answered,0)) as Ans,
sum(isnull(Overflow,0)) as OverFlow,
sum(isnull(Abandoned,0)) as Aband,
sum(isnull(Busy,0)) as Busy, wkdate, dowrdate
from TSRPReport
WHERE Date BETWEEN DATEADD(day, - 4, wkdate) AND wkdate
group by wkdate,dowrdate

[Plamen Ratchev]

Try this:

SELECT SUM(COALESCE([calls offered], 0)) AS CO,
SUM(COALESCE([calls received], 0)) AS CR,
SUM(COALESCE(Answered, 0)) AS Ans,
SUM(COALESCE(Overflow, 0)) AS OverFlow,
SUM(COALESCE(Abandoned, 0)) AS Aband,
SUM(COALESCE(Busy, 0)) AS Busy,
SUM(COALESCE([calls received], 0)) /
NULLIF(SUM(COALESCE([calls offered], 0)), 0) AS perc,
wkdate,
dowrdate
FROM TSRPReport
WHERE [date] BETWEEN DATEADD(day, - 4, wkdate) AND wkdate
GROUP BY wkdate, dowrdate;

--
Plamen Ratchev
http://www.SQLStudio.com

[JJ297]

No that didn't work it came out as whole numbers and I don't have a
perc column (should I)?

[JJ297]

On Dec 15, 2:45 pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:

Sorry I do see the Perc column but it has all 0's in them.

[Plamen Ratchev]

You can avoid integer division by casting to decimal with correct precision or simply by multiplying by 1.0:

SELECT SUM(COALESCE([calls offered], 0)) AS CO,
SUM(COALESCE([calls received], 0)) AS CR,
SUM(COALESCE(Answered, 0)) AS Ans,
SUM(COALESCE(Overflow, 0)) AS OverFlow,
SUM(COALESCE(Abandoned, 0)) AS Aband,
SUM(COALESCE(Busy, 0)) AS Busy,

1.0 * SUM(COALESCE([calls received], 0)) /

[JJ297]

okay thanks I will try it in the morning.

[JJ297]

> okay thanks I will try it in the morning.- Hide quoted text -
>
> - Show quoted text -

Thanks so much Plamen that worked!