Split durations in half hourly report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Split durations in half hourly report

Author

Topic

sreecareer
Starting Member

2 Posts

Posted - 2011-05-05 : 07:48:03

Hi,
I am developing an agent statistics half hourly report. The source table structure is as below..


AgentID		State		StartTime		        EndTime		                Duration
1234		NotReady	04-05-2011 08:00:00		04-05-2011 08:00:05		5
1234		Ready		04-05-2011 08:00:05		04-05-2011 08:01:00		55
1234		Busy		04-05-2011 08:01:00		04-05-2011 08:03:00		120
1234		ACW		04-05-2011 08:03:00		04-05-2011 08:03:10		10
1234		Ready		04-05-2011 08:03:10		04-05-2011 08:05:00		110
1234		NotReady	04-05-2011 08:05:00		04-05-2011 08:35:00		1800
1234		Ready		04-05-2011 08:35:00		04-05-2011 08:40:00		300

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-05 : 09:11:46

I'm sooo happy I'm not an agent

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-05 : 12:15:32

There is a smart way to do this, unfortunately, I'm not that smart.
Jim


DECLARE @Table Table (AgentID int	,	[State] varchar(10),StartTime datetime 
                      ,  EndTime datetime,  Duration int)

INSERT INTO @Table
SELECT 1234,'NotReady','04-05-2011 08:00:00','04-05-2011 08:00:05',		5  UNION 
SELECT 1234,'Ready','04-05-2011 08:00:05','04-05-2011 08:01:00',		55  UNION 
SELECT 1234,'Busy','04-05-2011 08:01:00','04-05-2011 08:03:00',		120  UNION 
SELECT 1234,'ACW','04-05-2011 08:03:00','04-05-2011 08:03:10',		10  UNION 
SELECT 1234,'Ready','04-05-2011 08:03:10','04-05-2011 08:05:00',		110  UNION 
SELECT 1234,'NotReady','04-05-2011 08:05:00','4-05-2011 08:35:00',		1800  UNION 
SELECT 1234,'NotReady','04-05-2011 08:40:00','4-05-2011 09:10:00',		1800  UNION 
SELECT 1234,'Ready','04-05-2011 08:35:00','04-05-2011 08:40:00',		300


;with cte
as
(
select * 
,[NewDuration] =
		CASE WHEN datepart(minute,StartTime)/30 = datepart(minute,EndTime)/30
			 THEN Datediff(second,starttime,endtime)
			 ELSE
               CASE WHEN datepart(minute,StartTime)/30 < datepart(minute,EndTime)/30
                    THEN  Datediff(second,starttime,DATEADD(minute,30,DATEADD(hour,datediff(hour,0,starttime),0)))
                    ELSE  Datediff(second,starttime,DATEADD(hour,1,DATEADD(hour,datediff(hour,0,starttime),0)))
               END
		END
 , [InterVal] =DATEADD(minute,((datediff(minute,0,StartTime )) /30       )*30       ,0) 
from @table
)
,cte2 as
(
select agentid,[state],starttime,endtime,duration,[newduration] 
,[interval] = convert(varchar(10),interval,108)
from cte
union

select agentid,[state],starttime,endtime,duration
      ,[duration] - [newduration]
      ,convert(varchar(10),dateadd(minute,30,interval),108)
from cte
where [duration] <> [newduration]
)

SELECT agentID
     ,Interval
     ,isnull(Ready,0) as ReadyTime
     ,isnull(NotReady,0) as NotReadyTime
     ,isnull(ACW,0) as ACWTime
     ,isnull(Busy,0) as BusyTime
FROM 

  ( SELECT agentID,Interval,[state],NewDuration 
    FROM cte2
  ) as t1

PIVOT ( sum(NewDuration) For [State] in ([ACW],[Busy],[NotReady],[Ready])

      ) pvt

Everyday I learn something that somebody else already knew

sreecareer
Starting Member

2 Posts

Posted - 2011-05-05 : 13:19:31

Thats fantastic!!! Thanks Jim...

Another way which I got from another forum...

http://www.sqlservercentral.com/Forums/Topic1103770-338-1.aspx

Thanks
Sree

Thanks
Sree

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-05 : 13:27:27

Somewhere between the 2 is probably a better solution. With mine you don't have to hard code all the intervals or change anything when the interval is from say 8:40 to 9:10

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources