What's wrong with this query - 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

General SQL Server Forums

New to SQL Server Programming

What's wrong with this query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-15 : 12:09:32

This query is working fine:

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

But when I tried to get the % of a column it's gives me too many rows back. What am I doing wrong here?

select sum(isnull([calls offered],0)) as CO,
sum(isnull([calls received],0)) as CR,
Case
when sum ([calls offered]) = 0 then '0'
when sum ([calls received]) = 0 then '0'
else convert (varchar, ([calls received] * 100/[calls offered] * 100/100)
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

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-15 : 12:26:49

Usually the first answer is, "The developer"

In any case, that query won't even run...I don't see the END for the CASE statement

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-15 : 13:08:04

Oops...here it is again with the end statement but it still doesn't work ugh...

select sum(isnull([calls offered],0)) as CO,
sum(isnull([calls received],0)) as CR,
Case
when 0 then '0'
else convert(varchar,([calls received] * 100/[calls offered] * 100)/100)
end + '%' as [Perc Received],
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, [calls received], [calls offered]

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-15 : 13:12:14

You're gonna have to post the DDL

And perhaps some sample Data and what the expected reulsta re suppose to be

In any Case, what's with the % in the select?

Do expect it to do anything?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-15 : 13:58:31

This Query:

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

Produces this:


CO        CR          Ans     overflow  Abandoned   Busy    Wkdate
186431    105994      66191    16364	  23439	    80430  2009-11-06	
172057    101377      62127    16747	  22503	    70677  2009-10-09	
163377     96376      62847    14591      18938	    66987  2009-10-02	
173961    106283      64722    15482	  26079	    67674  2009-10-23	
178409     96465      51002    17125	  28338	    81939  2009-10-16	
161044    88368       54536    18360	  15472	    72671  2009-11-13	
18192	   9186        6977	1059	   1150	     9006  2009-11-20	 
156773    96289       64967    15178	  16144	    60478  2009-10-30

Now I'm trying to get the Percent Receved by dividing

CO(calls received) / CR(calls offered)

So I added this to the above query:

Case
when sum ([calls received]) = 0 then '0'
when sum ([calls offered]) = 0 then '0'
else convert (varchar,([calls received] * 100/[calls offered]*100)/100) end + '%' as [Perc Received],

Here it is all together:

select sum(isnull([calls offered],0)) as CO,
sum(isnull([calls received],0)) as CR,
Case
when sum ([calls received]) = 0 then '0'
when sum ([calls offered]) = 0 then '0'
else convert (varchar,([calls received] * 100/[calls offered]*100)/100) end + '%' as [Perc Received],
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

It returns this:


CO       CR     Per Rec  Ans   OF     Aband   Busy    Wkdate
15	 11       73%	   5	5	4	4   2009-10-02 
16	 11	  68%	   9	1	1	5   2009-10-02	
34       22       64%     12	0      10	12  2009-10-02	
54       33       61%	  14   11	8	21  2009-10-02	
22       11       50%	   6	1	4	11  2009-10-02

This is what I want but it returns too many rows like the calculation is off.

By the way This...

+ '%' as [Perc Received]

places the % symbol in the Perc Received

Any ideas?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources