Getting percentage

#1

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

#2

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

#3

On Dec 15, 2:45*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:

Quote:

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 Ratchevhttp://www.SQLStudio.com

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

#4

On Dec 15, 2:45*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:

Quote:

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 Ratchevhttp://www.SQLStudio.com

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

#5

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)) /
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

#6

On Dec 15, 2:55*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:

Quote:

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)) /
* * * * 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 Ratchevhttp://www.SQLStudio.com

okay thanks I will try it in the morning.

#7

On Dec 15, 4:00*pm, JJ297 <nc... (AT) yahoo (DOT) com> wrote:

Quote:

On Dec 15, 2:55*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:

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)) /
* * * * 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 Ratchevhttp://www.SQLStudio.com

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

- Show quoted text -

Thanks so much Plamen that worked!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Getting percentage

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Getting percentage - 12-15-2009 , 01:40 PM

Re: Getting percentage - 12-15-2009 , 01:45 PM

Re: Getting percentage - 12-15-2009 , 01:48 PM

Re: Getting percentage - 12-15-2009 , 01:52 PM

Re: Getting percentage - 12-15-2009 , 01:55 PM

Re: Getting percentage - 12-15-2009 , 03:00 PM

Re: Getting percentage - 12-16-2009 , 07:36 AM