August 19, 2016 at 3:54 pm
Trying to figure out how to do this in my CASE WHEN --
I am unsure of how to write this in my statement - what I am after, I need to take the Agent's [staffedtime] less their [idletime] then Subtract the sum of their total time on calls then divide / that it into the sum of their (Staffed minus their idletime) - this gives me a % of unproductive time
If an agent's logged in time is [TotalStaffedTime] = 8.28
less their idle time of [IdleTime] = 1.52 (8.28-1.52) = 6.75
then, their total working time is [ContactTalkTime]+[PostCallProcessingTime]+[DNOutExtTalkTime]=3.43
=(6.75-3.43)/6.75 = 49.20%
Hoping I can get some help, I tried this and I am getting a divide by zero error...
'=([TotalStaffedTime] - [IdleTime]) - ([ContactTalkTime]+[PostCallProcessingTime]+[DNOutExtTalkTime]) / [TotalStaffedTime] - [IdleTime]
USE LDW_Avaya
GO
WITH A AS (
SELECT
[TimeStamp]
,[AgentLogin]
,SUM([DNOutExtCalls]) as [DNOutExtCalls]
,SUM([DNOutExtCallsTalkTime]) AS [DNOutExtCallsTalkTime]
FROM [LDW_Avaya].[dbo].[dAgentPerformanceStat]
GROUP by [AgentLogin],[TimeStamp]
)
SELECT distinct
CONVERT (varchar,[dAgentBySkillsetStat].TimeStamp,1) AS 'DATE'
,CAST([dAgentBySkillsetStat].[AgentLogin] as Int) as EXT
,[Skillset]
,[dAgentBySkillsetStat].[AgentSurname] + ' ' + [dAgentBySkillsetStat].[AgentGivenName] + ' - ' + [dAgentBySkillsetStat].[AgentLogin] as '[Name & ext]'
,ISNULL(SUM([CallsOffered]),0) AS 'CallOffered'
,ISNULL(SUM([CallsAnswered]),0) as 'CallsAnswered'
,ISNULL(SUM([PostCallProcessingTime]),0) as 'ACW'
,ISNULL(SUM([ContactTalkTime]),0) as 'ContactTime'
,a.[DNOutExtCalls]
,a.[DNOutExtCallsTalkTime]
,[NotReadyTime]
,[TotalStaffedTime]
,[IdleTime]
,CASE WHEN [TotalStaffedTime] = 0 THEN 0 ELSE [TotalStaffedTime]-[IdleTime] end as [Staffed Less Idle]
,CASE WHEN SUM([CallsAnswered]) = 0 THEN 0 ELSE SUM([ContactTalkTime]+[PostCallProcessingTime])/SUM([CallsAnswered]) END AS [AVG HANDLETIME]
,CASE WHEN SUM([TotalStaffedTime]) = 0 THEN 0 ELSE SUM([ContactTalkTime]+[PostCallProcessingTime]+[DNOutExtTalkTime]) END AS [TOTAL TIME ON PHONES]
,CASE WHEN [TotalStaffedTime] = 0 THEN 0 ELSE [IdleTime] / cast([TotalStaffedTime] as money) end as [(%) Of time in Idle]
FROM [LDW_Avaya].[dbo].[dAgentBySkillsetStat]
LEFT JOIN A ON [dAgentBySkillsetStat].[AgentLogin] = A.[AgentLogin]
AND [dAgentBySkillsetStat].[TIMESTAMP] = A.[TIMESTAMP]
WHERE [AgentGivenName] is not null
GROUP BY
[dAgentBySkillsetStat].[TimeStamp]
,[AgentSurname]
,[AgentGivenName]
,[dAgentBySkillsetStat].[AgentLogin]
,[IdleTime]
,[TotalStaffedTime]
,[NotReadyTime]
,a.[DNOutExtCalls]
,a.[DNOutExtCallsTalkTime]
,[Skillset]
ORDER BY date
August 19, 2016 at 4:34 pm
=([TotalStaffedTime] - [IdleTime]) - ([ContactTalkTime]+[PostCallProcessingTime]+[DNOutExtTalkTime]) / [TotalStaffedTime] - [IdleTime]
You're dividing by TotalStaffedTime. Apparently you have cases where TotalStaffedTime = 0. What do you want to happen in those cases?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 19, 2016 at 7:06 pm
CASE WHEN [TotalStaffedTime] = 0 THEN 0 else ((do this calculation))
August 19, 2016 at 7:09 pm
bcrockett (8/19/2016)
CASE WHEN [TotalStaffedTime] = 0 THEN 0 else ((do this calculation))
And I tried this, and its not working, is my syntax wrong? Or what's the correct way to perform this?
August 20, 2016 at 4:23 am
bcrockett (8/19/2016)
bcrockett (8/19/2016)
CASE WHEN [TotalStaffedTime] = 0 THEN 0 else ((do this calculation))And I tried this, and its not working, is my syntax wrong?
Probably. What't the exact syntax you tried and what does it do/not do incorrectly?
If it's the code at the top, then you're checking if the sum is 0 and dividing by the individual values, which can still give you divide by zero errors. Try moving the SUM() outside of the CASE expression,
SUM(CASE WHEN [TotalStaffedTime] = 0 THEN ..... END ) AS ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2016 at 5:24 am
Can you please post the DDL (create table), some sample data as an insert statement and the expected results?
😎
What you have described so far isn't a complex task but it is quite elaborate having to set up the sample set in order to demonstrate a solution.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply