On the 18th March I asked the question How many minutes agent is logged in per interval and [WilliamD] was able to provide me with a fantastic answer.
As you would expect that was great but the requirements have changed (Who would have thought!) and now the customer (The person I am doing the work for - this isn't paid work) would like to see how long a particular agent is ready to receive calls by interval.
We use the Cisco ICM/IPCC dialer so I am able to look at the Agent Event table and produce the following data for one agent:
An EventID of 1 is a login, 2 is logout and 3 is not ready. However the not readies are logged at the end of the event and the duration - datetime gives you the start time so I did a script that produces an example set of data for one agent.
This gives a table that looks like so:
What I would like is one line per agent with intervals along the top split by 30 minutes (or another value like the other question but only to show if an agent is ready to receive calls. I'm a bit stumped by this can anybody help?
Edit 1: Adding an example output table
: h ttp://ask.sqlservercentral.com/users/256/williamd/
the way you are explaining the problem is very similar to your last question. You only need to change the table structure of my answer to fit your new requirements. Unfortunately, your sample data and expected results do not match. The sample data will return SkillTargetID 12345 as being ready for the whole day from 10am onwards.
The CASE statement inside the AgentTimings CTE needs restating to take into account that you have differing statuses deriving different calculations. The join to the AgentEventLog also needs a similar join condition to the CASE statement.
Other than these two changes, everything else should work. Here is my solution:
Notice that the only change is the test table definition and the definition of the AgentTimings CTE, everything else remains the same.You will still need the PIVOT, but you have stated that is not a problem. Give this a try and let me know.
It looks to me like you're after the time spans that are implicit between your result rows. This gets those spans (in seconds rather than minutes):
This query returns the following results:
Here is the table setup
answered Mar 21 '11 at 12:54 PM
With luck, this will hopefully be closer to what you were expecting. The code is pretty sloppy, I'm afraid, so I'm open to suggestions on how to improve it. It should, however, work if the jobs start and end in the same day. If you have some overnight calls (start at 11 PM and finish at 1 AM, for example) or a call which lasts more than 24 hours, there would be some fidgeting with the comparisons and possibly the addition of another set of Time records for each additional day that things can run. Your examples didn't include this, though, so I coded lazily optimistically.
You can adjust the minute range (you said 30 minutes, but it sounded like that was a flexible requirement) and the begin and end times for display ranges, to keep from having too many records shown. Also, I left ReadyMinutes as an int; changing it to a decimal should be pretty easy if you do want the fractional minutes broken out as well.