How to sum agent state time seconds using another table as a filter

Question

My agents need to log in to their console under a specific skill, and from there they can select their state code. For example, Ready, Not Ready. If they select a Not Ready state, then they need to select a Reason Code. Restroom, Meeting, etc.

I'm looking to make a list of each agent logged into a specific skill, and count the total number of seconds they were in that skill but EXCLUDE specific reason codes. So for example if they logged out for 1000 seconds of Restroom then I want that subtracted from the results. For some reason I keep getting a much smaller than expected number of seconds.

Here's a demonstration of how my code appears:

DECLARE @startDate DATE = '2021-08-19'  

SELECT    
    B.Agent,   
    A.Date,   
    A.StartTime,   
    A.EndTime,   
    A.SkillTimeSecs,   
    A.Skill
    
FROM    
    Database.AgentSkill AS A  
    
JOIN   
    Database.AgentState AS B   
    ON A.SessionID=B.SessionID  
    
WHERE    
    A.Date = @startDate 
    AND B.Date = @startDate  
    AND B.Interval_30Minute BETWEEN '06:00:00' AND '17:00:00'   
    AND A.Skill = 'Agent'
    AND B.ReasonCode IN ('Follow-Up Work','Task Completion','Out calls')

GROUP BY   
     
    B.Agent,   
    A.Date,   
    A.StartTime,   
    A.EndTime,
    A.SkillTimeSecs,   
    A.Skill

Any idea why the seconds are so low?

Answer 1

You don't have any aggregate functions in your SELECT statement, so your GROUP BY is in effect a DISTINCT. If you want the total of SkillTimeSecs then you need to remove it from the GROUP BY clause and use the SUM function:

SELECT    
    B.Agent,   
    A.Date,   
    A.StartTime,   
    A.EndTime,   
    SUM(A.SkillTimeSecs) TotalSkillTimeSecs,   
    A.Skill
    
FROM    
    Database.AgentSkill AS A  
    
JOIN   
    Database.AgentState AS B   
    ON A.SessionID=B.SessionID  
    
WHERE    
    A.Date = @startDate 
    AND B.Date = @startDate  
    AND B.Interval_30Minute BETWEEN '06:00:00' AND '17:00:00'   
    AND A.Skill = 'Agent'
    AND B.ReasonCode IN ('Follow-Up Work','Task Completion','Out calls')

GROUP BY   
     
    B.Agent,   
    A.Date,   
    A.StartTime,   
    A.EndTime,
    A.Skill