CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ray
Ray

Reputation: 103

SUM of time spent in a State

Please consider the table below for call center agent states. What I need is to calculate the sum of time Bryan spent in "Break" for the whole day.

Break_Sum

This is what I'm trying to execute but it returns some inaccurate values:

    select sum (CASE   
    WHEN State = 'Not Working' and Reason = 'Break'
    THEN Datediff(SECOND, [Time_Stamp], CURRENT_TIMESTAMP)
    else '' END) as Break_Overall
    from MyTable
    where Agent = 'Bryan'

Upvotes: 1

Views: 605

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270663

Use lead():

select agent,
       sum(datediff(second, timestamp, next_timestamp) 
from (select t.*,
             lead(timestamp) over (partition by agent order by time_stamp) as next_timestamp
      from mytable t
     ) t
where state = 'Not Working' and reason = 'Break'
group by agent;

If the agent can currently be on break, you might want a default value:

select agent,
       sum(datediff(second, timestamp, next_timestamp) 
from (select t.*,
             lead(timestamp, 1, current_timestamp) over (partition by agent 
                                                         order by time_stamp) as next_timestamp
      from mytable t
     ) t
where state = 'Not Working' and reason = 'Break'
group by agent;

I'm a little uncomfortable with this logic, because current_timestamp has a date component, but your times don't.

EDIT:

In SQL Server 2008, you can do:

select agent,
       sum(datediff(second, timestamp, coalesce(next_timestamp, current_timestamp)) 
from (select t.*, t2.timestamp as next_timestamp
      from mytable t outer apply
           (select top 1 t2.*
            from mytable t2
            where t2.agent = t.agent and t2.time_stamp > t.time_stamp
            order by t.time_stamp
           ) t2
     ) t
where state = 'Not Working' and reason = 'Break'
group by agent;

Upvotes: 1

autophage
autophage

Reputation: 196

As it is, you're getting the difference between the record's Time_Stamp and CURRENT_TIMESTAMP. That's probably not correct - you probably want to get the difference between the record's Time_Stamp and the next Time_Stamp for the same "Agent".

(Note that "Agent" will also present problems if you have multiple Agents with the same name; you probably want to store Agents in a different table and use a unique identifier as a foreign key.)

So, for Bryan, you'd get the sum of both the "total time" for the 8:30:21 record AND the 11:34:58 record, which is right - except that you're calculating "total time" incorrectly, so instead you'd get the sum of the time since 8:30:21 and 11:34:58.

Upvotes: 0

Related Questions