September 26, 2012 at 2:33 pm
I'm trying to setting some queries for our call center. I need to capture the times an agent is in a status other than ready.
idsession_idtimeactionreason
8335448621572012-09-17 17:41:53agent_readyNULL
8339022621572012-09-17 18:11:48agent_awayLunch Break
8339372621572012-09-17 18:22:46agent_readyNULL
8340198621572012-09-17 18:45:46agent_awayWrap Up
8340526621572012-09-17 18:57:11agent_readyNULL
8341560621572012-09-17 19:37:13agent_awayPersonal
8341701621572012-09-17 19:43:41agent_readyNULL
8341970621572012-09-17 19:56:43logout force_logout
Is there a way I can sum of the times, like with using DATEDIFF, from the time values. Ideally, I'd like to get something like ...
reasontime (in mins)
Lunch Break50
Wrap Up12
Personal8
I've been struggling with this for awhile, and I can't figure out a way to use DATEDIFF across different row values and group by the reason. Maybe another approach is needed? Any help is greatly appreciated.
September 26, 2012 at 4:04 pm
I see you are new around here. It is generally considered best to post ddl and sample data in a consumable format. I did this for you as an example for future posts.
I have to say I don't understand the numbers you came up with. But I think you want something somewhat like this.
create table #SomeData
(
ActionTime datetime,
ActionDesc varchar(25),
Reason varchar(25)
)
insert #SomeData
select '2012-09-17 17:41:53', 'agent_ready', NULL union all
select '2012-09-17 18:11:48', 'agent_away', 'Lunch Break' union all
select '2012-09-17 18:22:46', 'agent_ready', NULL union all
select '2012-09-17 18:45:46', 'agent_away', 'Wrap Up' union all
select '2012-09-17 18:57:11', 'agent_ready', NULL union all
select '2012-09-17 19:37:13', 'agent_away', 'Personal' union all
select '2012-09-17 19:43:41', 'agent_ready', NULL union all
select '2012-09-17 19:56:43', 'logout', 'force_logout'
;with cte as
(
select *, ROW_NUMBER()over(order by ActionTime) as RowNum
from #SomeData
)
select c1.*, c1.ActionTime as BeginTime, c2.ActionTime as EndTime, DATEDIFF(n, c1.ActionTime, c2.ActionTime) as TotalTime
from cte c1
left join cte c2 on c1.RowNum = c2.RowNum - 1
drop table #SomeData
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2012 at 2:15 pm
Thanks Sean, that worked and got me the results I need.
Those number were just examples; not taken from the data. I probably should have clarified that.
I'll remember to following the posting guidelines when I make future posts.
September 28, 2012 at 2:17 pm
jhtein (9/28/2012)
Thanks Sean, that worked and got me the results I need.Those number were just examples; not taken from the data. I probably should have clarified that.
I'll remember to following the posting guidelines when I make future posts.
You are quite welcome. Glad that worked for you and thanks for letting me know. Have a great weekend!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply