February 14, 2013 at 1:49 pm
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 :/
February 14, 2013 at 1:57 pm
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
February 17, 2013 at 3:10 am
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
February 17, 2013 at 5:33 am
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
February 17, 2013 at 9:53 am
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.
February 17, 2013 at 5:12 pm
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.
DistinctCallersTotalCallsTotalAnsweredTotalMissedAbandoned
49362
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply