Split durations in a half hourly report

Question

Post reply

Split durations in a half hourly report

sreecareer

SSC Enthusiast

Points: 114
More actions
May 5, 2011 at 5:58 am

#389445

Hi,
I am developing an agent statistics half hourly report. The source table structure is as below..
AgentIDStateStartTime EndTime Duration
1234NotReady04-05-2011 08:00:0004-05-2011 08:00:055
1234Ready04-05-2011 08:00:0504-05-2011 08:01:0055
1234Busy04-05-2011 08:01:0004-05-2011 08:03:00120
1234ACW04-05-2011 08:03:0004-05-2011 08:03:1010
1234Ready04-05-2011 08:03:1004-05-2011 08:05:00110
1234NotReady04-05-2011 08:05:0004-05-2011 08:35:001800
1234Ready04-05-2011 08:35:0004-05-2011 08:40:00300
The report should look like below
AgentID Interval LoginTime ReadyTime NotReadyTime ACWTime
1234 08:00 1800 165 1505 10
1234 08:30 600 300 300 0
The login time is the total time irrespective of the state, as above, 8 to 8:30 the agent was logged in, so the time is 1800 seconds. Another thing, the not ready state starts at 8:05 and ends at 08:35 so 25min should come in first interval and 5 min should come in second interval.
Could anyone help me to write a query to get this output?
Thanks
Sree

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

here's the data in consumable format for other posters; I'm still trying to get my head around the requirements:

With MySampleData

(AgentID,State,StartTime,EndTime,Duration)

AS (

SELECT '1234','NotReady','04-05-2011 08:00:00','04-05-2011 08:00:05','5' UNION ALL

SELECT '1234','Ready','04-05-2011 08:00:05','04-05-2011 08:01:00','55' UNION ALL

SELECT '1234','Busy','04-05-2011 08:01:00','04-05-2011 08:03:00','120' UNION ALL

SELECT '1234','ACW','04-05-2011 08:03:00','04-05-2011 08:03:10','10' UNION ALL

SELECT '1234','Ready','04-05-2011 08:03:10','04-05-2011 08:05:00','110' UNION ALL

SELECT '1234','NotReady','04-05-2011 08:05:00','04-05-2011 08:35:00','1800' UNION ALL

SELECT '1234','Ready','04-05-2011 08:35:00','04-05-2011 08:40:00','300'

)

Select * from MySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

drew.allen SSC Guru Points: 77014 More actions · Answer 2

I used a calendar table fragment for this, because dynamically generating half-hour intervals would be a bit of a mess. Also the conditions under which two time intervals overlap is a bit counterintuitive, because people want to compare starts to starts and ends to ends, but the simplest actually compares the start of each to the end of the other. I then used a CASE statement to determine the start and end times to use in the one CTE and then used a CASE statement in the final query to create the crosstab.

With MySampleData

(AgentID,State,StartTime,EndTime,Duration)

AS (

SELECT '1234','NotReady',Cast('2011-04-05 08:00:00' AS datetime),Cast('2011-04-05 08:00:05' AS datetime),'5' UNION ALL

SELECT '1234','Ready','2011-04-05 08:00:05','2011-04-05 08:01:00','55' UNION ALL

SELECT '1234','Busy','2011-04-05 08:01:00','2011-04-05 08:03:00','120' UNION ALL

SELECT '1234','ACW','2011-04-05 08:03:00','2011-04-05 08:03:10','10' UNION ALL

SELECT '1234','Ready','2011-04-05 08:03:10','2011-04-05 08:05:00','110' UNION ALL

SELECT '1234','NotReady','2011-04-05 08:05:00','2011-04-05 08:35:00','1800' UNION ALL

SELECT '1234','Ready','2011-04-05 08:35:00','2011-04-05 08:40:00','300'

)

, Calendar( StartTime, EndTime ) AS (

SELECT Cast('2011-04-05 07:30' AS datetime), Cast('2011-04-05 08:00' AS datetime)

UNION ALL

SELECT Cast('2011-04-05 08:00' AS datetime), Cast('2011-04-05 08:30' AS datetime)

UNION ALL

SELECT Cast('2011-04-05 08:30' AS datetime), Cast('2011-04-05 09:00' AS datetime)

)

, Interval_Details AS (

SELECT d.AgentID, c.StartTime AS Interval

, DateDiff(ss

, CASE WHEN c.StartTime > d.StartTime THEN c.StartTime ELSE d.Starttime END

, CASE WHEN c.EndTime < d.EndTime THEN c.EndTime ELSE d.Endtime END

) AS IntervalLength

, [State]

FROM MySampleData AS d

INNER JOIN Calendar AS c

ON d.StartTime < c.EndTime

AND c.StartTime < d.EndTime

)

SELECT

AgentID

, Interval

, Sum(IntervalLength) AS LoginTime

, Sum(CASE [State] WHEN 'Ready' THEN IntervalLength ELSE 0 END) AS ReadyTime

, Sum(CASE [State] WHEN 'NotReady' THEN IntervalLength ELSE 0 END) AS NotReadyTime

, Sum(CASE [State] WHEN 'ACW' THEN IntervalLength ELSE 0 END) AS ACWTime

FROM Interval_Details

GROUP BY AgentID, Interval

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sreecareer SSC Enthusiast Points: 114 More actions · Answer 3

Just Fantastic!!! It worked like a charm.. Thanks Drew....

Another way, which I got from another forum.. thought of sharing with you..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=160277

Thanks

Sree