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.
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
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