question

Ian Roke avatar image
Ian Roke 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.
sql-server-2005t-sql
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@WilliamD - cripes, I've only just finished mocking up some datas ! ``
1 Like 1 ·
That is right yes they are all entered as SQL Server datetime fields in the format yyyy-MM-dd hh:mm:ss.
0 Likes 0 ·
I hadn't even thought about an approach to this, beyond thinking "dynamic PIVOT"
0 Likes 0 ·
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.
0 Likes 0 ·
@Ian Roke - awesome friday question. Released some frustration for me one this, cheers! (+1)
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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
15 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
So it must have worked then. Wonderful. Like I wrote, the dynamic Sql part may prove difficult - but you have the start to get the data in the right shape.
1 Like 1 ·
I'm glad you are because I am totally stumped!
0 Likes 0 ·
I have tried to explaing my madness in the code, but found it a little difficult. Hope it is clear enough, otherwise, ask!
0 Likes 0 ·
Thanks mate I get the following error though: (2 row(s) affected) Msg 208, Level 16, State 1, Line 45 Invalid object name 'dbo.Nums'.
0 Likes 0 ·
Show more comments
ThomasRushton avatar image
ThomasRushton answered
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?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It's going on to an ASP page. I would prefer all the work to be done by a dedicated server rather than in ASP if I am honest.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.