asked
How many minutes agent is logged in per interval
I have a table of agents. What I would like to do is have a column for each interval of 30 minutes (for now but that could be changed) showing how many minutes of that 30 they were logged in for. This could later change include how long they were ready for calls etc but I am keeping it simple for now. The fields I have are LoginDateTime (Datetime the agent logged in to start their shift), LogoutDateTime (Datetime the agent logged out to end their shift), LogoutLunchDateTime (Datetime the agent logged out for lunch) and LoginLunchDateTime (Datetime the agent logged in after lunch). I would like it to then return a table similar to the one below: Agent | 07:00 - 07:30 | 07:30 - 08:00 | 08:00 - 08:30 ----------------------------------------------------- 1 | 26 | 30 | 30 2 | 0 | 9 | 30 If you need more then give me a shout. Cheers.
@WilliamD - cripes, I've only just finished mocking up some datas ! ``
I hadn't even thought about an approach to this, beyond thinking "dynamic PIVOT"
Just so people know, my comment "Almost there, just putting the polish on the pivot" was turned into an answer - it evidently had 2 upvotes at that point, then got 2 more when it was turned into an answer. I have only got 2 upvote points for the answer. This will need logging with OSQA. I saw similar behaviour earlier today with a comment that was converted. I had voted on the comment and voted the answer again. The total votes didn't change, but the karma for that person went up.
Almost there, just putting the polish on the pivot. EDIT2 ----- I haven't got a reference to where it is from, I think I wrote it myself after looking at how other people had done similar work, but here is my Nums table valued function: CREATE FUNCTION dbo.Nums (@m AS bigint) RETURNS TABLE AS RETURN WITH t0 AS (SELECT n = 1 UNION ALL SELECT n = 1), t1 AS (SELECT n = 1 FROM t0 AS a, t0 AS b), t2 AS (SELECT n = 1 FROM t1 AS a, t1 AS b), t3 AS (SELECT n = 1 FROM t2 AS a, t2 AS b), t4 AS (SELECT n = 1 FROM t3 AS a, t3 AS b), t5 AS (SELECT n = 1 FROM t4 AS a, t4 AS b), result AS (SELECT ROW_NUMBER() OVER (ORDER BY n) AS n FROM t5) SELECT n FROM result WHERE n
Show more comments
Right. I had hardcoded the dates for the comparisons. At the beginning of my code I declare and sset startofday and endofday. It was set to get the start of today, and the example data is for the 18th of March. I have changed that, so now it gives you some results. You need to pay attention to the time frame declared at the beginning. You set the start and end of the timeframe you want to analyse, then the minutegroupings (currently 30 minutes as per your original question). These can all be changed, so you could do an analysis of multiple days and different timegroups (you stated that this may change in the future). Give it a go now and let me know if it worked.
So are these fields in a table something like AgentID LoginDateTime LogoutLunchDateTime LoginLunchDateTime LogoutDateTime 1 2011-01-01 09:15 2011-01-01 12:45 2011-01-01 13:15 2011-01-01 17:23 1 2011-01-02 09:15 ... or some other form?
Ian, where is this data going? If its in a report than fire the raw data to the report and use the matrix to do the cross tab/pivot work. I'd use a tally table to provide the time period boundaries still but SQL for this will never be pretty and will be extra work the server neednt do
