Multiple select in same query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Multiple select in same query

Multiple select in same query

Rate Topic

Display Mode

Topic Options

Author

Message

tony 9186

Posted Thursday, February 14, 2013 1:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 11:46 AM
Points: 4, Visits: 29

I have a sql storeprocedure where i want to count calls to a helpdesk..

The rows in my query for example are like
id caller Agent Missed
1 anon null true
1 anon null true
1 anon Me false
1 anon null true
2 som1 Me false
3 anon null true
3 anon null true
4 CIO Me false
4 Cio som1 true

Im going to produce a report of this but how my problem is that the incomming calls is calling on evry Agent that is avalible so the same ID is displayed multiple times.
And i want to know how many calls are Missed and how many calls are answered therefor only counting the id:s once, In this case it will be

Total Calls Answerd Missed
4 3 1

How can i accomplish this in my procedure?
I cannot do this by grouping on ID cuz the same ID can be Answered and not answered?

Somebody has any idea? sorry if its not written i typing on my PAD right now :/

Post #1420283

Erin Ramsay

Posted Thursday, February 14, 2013 1:57 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 420, Visits: 774

Perhaps something like this would put you on ther right track?

select sum(calls) calls, sum(answered) answered, sum(missed) missed
from
(
select case when call = 'anon' then 0 else 1 end as calls
	, case when Agent is null then 0 else 1 end as answered
	, case when missed = 'false' then 0 else 1 end as missed
from yourtable
) source

Post #1420289

tony 9186

Posted Sunday, February 17, 2013 3:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 11:46 AM
Points: 4, Visits: 29

Hi Erin thanks for your reply.. But my problem remains that it counts every row.

CREATE TABLE Calls
(
ID int NULL,
caller nvarchar(50) NULL,
Agent nvarchar(50) NULL,
Missed bit NULL
) ON [PRIMARY]
GO
insert into Calls
values
(1, 'anon', null, 1),
(1, 'anon', null, 1),
(1, 'anon', 'me', 0),
(1, 'anon', null, 1),
(2, 'som1', 'me', 1),
(3, 'another', null, 1),
(3, 'another', null, 1),
(4, 'CIo', 'me', 0),
(4, 'CIo', null, 0)

If i run your code.

select sum(calls) calls, sum(answered) answered, sum(missed) missed
from
(
select case when caller = 'anon' then 0 else 1 end as calls
, case when Agent is null then 0 else 1 end as answered
, case when missed = 'false' then 0 else 1 end as missed
from calls
) source

The output is

calls answered missed
5 3 6

But there is ony four distinct calls, so i need to rule out the otherones when deterimin if it is answed or missed so the correct output would be.

calls answered missed
4 3 1

Post #1420953

Steve JP

Posted Sunday, February 17, 2013 5:33 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:40 AM
Points: 50, Visits: 230

Tony, you may want to take the bugs out of the application first as in the long run it will cause less complex sql.

I think the answer you are looking for:

SET ANSI_NULLS ON

select	[# Calls]  = COUNT(distinct  [id] )
  ,	[Answered] = COUNT( nullif( Missed , 1 ) )
  ,	[Missed] = /* [# Calls] - [Answered]  =  */ COUNT(distinct  [id] ) -  COUNT( nullif( Missed , 1 ) )
from Calls

Post #1420964

tony 9186

Posted Sunday, February 17, 2013 9:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 11:46 AM
Points: 4, Visits: 29

Thank you Steve that looks like it could work. :) now i just need to implement it.

Actually i cannot change the table schema at all, the sample i provided are just a simple sample, but the problem are the same.

Post #1420993

Steven Willis

Posted Sunday, February 17, 2013 5:12 PM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:23 AM
Points: 283, Visits: 1,237

I went into more detail to break down the call status. I added a category 'Abandoned' to apply to callers who were never answered. I wasn't sure how to handle your item #5 which shows an agent name but flags the call as missed. I counted it as a miss, so I get two abandoned calls.


DistinctCallers	TotalCalls	TotalAnswered	TotalMissed	Abandoned
4		9		3		6		2


SELECT
     MAX(RowNum) AS DistinctCallers    
    ,SUM(CallerCalls) AS TotalCalls
    ,SUM(CallerAnswered) AS TotalAnswered
    ,SUM(CallerMissed) AS TotalMissed
    ,SUM(Abandoned) AS Abandoned
FROM
    (
    SELECT
         ROW_NUMBER() OVER (ORDER BY Caller) AS RowNum
        ,Caller
        ,CallerCalls
        ,CallerAnswered
        ,(SELECT COUNT(ISNULL(Missed,1)) FROM #Calls where Caller = c2.Caller and Missed = 1) AS CallerMissed
        ,(CASE
            WHEN NULLIF(CallerAnswered,0) IS NULL THEN 1
            ELSE 0
          END) AS Abandoned
    FROM
        (
         SELECT
           Caller
          ,COUNT(Caller) AS CallerCalls
          ,(SELECT COUNT(ISNULL(Missed,0)) FROM #Calls where Caller = c1.Caller and Missed = 0) AS CallerAnswered
        FROM #Calls as c1
        GROUP BY Caller 
        ) c2
    ) c3

Post #1421014

« Prev Topic | Next Topic »

Permissions