How can I calculate time spent at work in a single column?

Question

I would like to calculate the amount of time spent working from rows of data that contain a single date/time field.

The first occurrence on a given day can be considered the start time, with the next occurrence being the end time, alternating back and forth on any given day. So if I start at 9:00am and go on lunch at 12:00pm, return from lunch at 12:30pm, then leave for the day at 5:00pm, my data would look like:

2013-03-07 09:00:00
2013-03-07 12:00:00
2013-03-07 12:30:00
2013-03-07 17:00:00

Here is a further sample (in my locale we use . as the date separator):

22. 2. 2013 9:09:00
22. 2. 2013 13:28:09
25. 2. 2013 9:43:16
25. 2. 2013 15:15:19
25. 2. 2013 19:30:00
25. 2. 2013 21:30:00
26. 2. 2013 8:27:54
26. 2. 2013 16:31:51
27. 2. 2013 8:52:59
27. 2. 2013 15:08:35
28. 2. 2013 8:37:54
28. 2. 2013 16:37:08
28. 2. 2013 18:30:00
28. 2. 2013 20:30:00

I would like to know the total time spent at work for any given day, and would also like to be able to tally the time spent over, for instance, any given week or month.

Answer 1

DECLARE @d TABLE(dt DATETIME);

INSERT @d VALUES
('2013-02-22 09:09:00'),
('2013-02-22 13:28:09'),
('2013-02-25 09:43:16'),
('2013-02-25 15:15:19'),
('2013-02-25 19:30:00'),
('2013-02-25 21:30:00'),
('2013-02-26 08:27:54'),
('2013-02-26 16:31:51'),
('2013-02-27 08:52:59'),
('2013-02-27 15:08:35'),
('2013-02-28 08:37:54'),
('2013-02-28 16:37:08'),
('2013-02-28 18:30:00'),
('2013-02-28 20:30:00');

;WITH x AS
(
  SELECT 
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt),
   p = LAG(dt, 1) OVER (ORDER BY dt),
   m = DATEDIFF(MINUTE, LAG(dt, 1) OVER (ORDER BY dt), dt)
 FROM @d
)
SELECT 
  start = p, 
  [end] = e, 
  [minutes_elapsed] = m, 
  [time] = CONVERT(TIME(0), DATEADD(MINUTE, m, 0))
 FROM x
 WHERE r % 2 = 0
 ORDER BY e;

If you need to do this on older versions (2005 -> 2008 R2):

;WITH cte1 AS
(
  SELECT 
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt)
  FROM @d
),
cte2 AS
(
  SELECT 
    x.r,
    [start] = x.e, 
    [end] = y.e, 
    m = DATEDIFF(MINUTE, x.e, y.e)
  FROM cte1 AS x INNER JOIN cte1 AS y
  ON x.r = y.r - 1
  WHERE y.r % 2 = 0
)
SELECT
  [start],
  [end],
  minutes_elapsed = m,
  [time] = CONVERT(CHAR(8), DATEADD(MINUTE, m, 0), 108)
 FROM cte2
 ORDER BY [start];

Suggested schema for recording time in/time out is pretty simple:

CREATE TABLE dbo.Shifts
(
  EmployeeID INT NOT NULL, -- probably Foreign Key, right?
  ClockIn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ClockOut DATETIME NULL
);

Now your query is:

SELECT 
  EmployeeID, 
  ClockIn, 
  ClockOut, 
  ShiftLength = CONVERT(TIME(0), DATEADD(MINUTE, 
    DATEDIFF(MINUTE, ClockIn,  
      COALESCE(ClockOut, CURRENT_TIMESTAMP) -- to capture shift in progress
    ), 0))
FROM dbo.Shifts
WHERE ...