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.
| Author |
Topic |
|
pankajsriv1
Starting Member
1 Post |
 Posted - 2014-09-12 : 06:45:32
|
| Hi,I am developing an agent statistics half hourly report. The source table structure is as below..AgentID State StartTime EndTime 1234 NotReady 04-05-2011 08:00:00 04-05-2011 08:00:051234 Ready 04-05-2011 08:00:05 04-05-2011 08:01:001234 Busy 04-05-2011 08:01:00 04-05-2011 08:03:001234 ACW 04-05-2011 08:03:00 04-05-2011 08:03:101234 Ready 04-05-2011 08:03:10 04-05-2011 08:05:001234 NotReady 04-05-2011 08:05:00 04-05-2011 08:35:001234 Ready 04-05-2011 08:35:00 04-05-2011 08:40:001234 Ready 04-05-2011 08:47:00 04-05-2011 10:40:00The report should look like belowAgentID Interval LoginTime ReadyTime NotReadyTime ACWTime1234 08:00 1800 165 1505 101234 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 and 8:30 to 9:00 and 9:00 to 9:30 and 9:30 to 10:00 are 1800 and 10:30 to 11:00 should be 600 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?Regards,Pankaj |
|
|
|
|
|
Half Hourly Interval Calculation