December 27, 2017 at 5:57 pm
I am having some issues with this.
There must be something I am formatting incorrectly.
I tried summing it in the outer query and the inner, the results are line by line (not summed).
Any ideas?
SELECT s.AgentName
, s.ReasonCode
, Sum(s.StateDuration) AS 'StateDuration'
FROM (
SELECT [Agent Name] AS 'AgentName'
, [Reason Code] AS 'ReasonCode'
, Cast(Datepart(Second, [Duration]) AS INT) + Cast(60 * Datepart(Minute, [Duration]) AS INT) + Cast(3600 * Datepart(Hour, [Duration]) AS INT) AS 'StateDuration'
FROM State_Details
GROUP BY [Agent Name]
, [Reason Code]
, [Duration]
) s
GROUP BY s.AgentName
, s.ReasonCode
, s.StateDuration
December 27, 2017 at 6:33 pm
Remove the s.StateDuration from the GROUP BY. You're SUMMING that... Your query should look like this:SELECT s.AgentName
, s.ReasonCode
, Sum(s.StateDuration) AS 'StateDuration'
FROM (
SELECT [Agent Name] AS 'AgentName'
, [Reason Code] AS 'ReasonCode'
, Cast(Datepart(Second, [Duration]) AS INT) + Cast(60 * Datepart(Minute, [Duration]) AS INT) + Cast(3600 * Datepart(Hour, [Duration]) AS INT) AS 'StateDuration'
FROM State_Details
GROUP BY [Agent Name]
, [Reason Code]
) s
GROUP BY s.AgentName
, s.ReasonCode
December 28, 2017 at 6:58 am
So simple. I was so close.
Thanks so much! I really appreciate it.
December 28, 2017 at 7:33 am
There's no reason to have a subquery there.
SELECT [Agent Name] AS AgentName
, [Reason Code] AS ReasonCode
, SUM(Cast(Datepart(Second, [Duration]) AS INT) + Cast(60 * Datepart(Minute, [Duration]) AS INT) + Cast(3600 * Datepart(Hour, [Duration]) AS INT)) AS StateDuration
FROM State_Details
GROUP BY [Agent Name]
, [Reason Code]
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply