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

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.

-
Really difficult to tell what is being asked here. Could you show the desired results? – Aaron Bertrand Mar 7 '13 at 19:49
The best result would be something like : 22. 2. 2013 - 5:12 (hours:minutes) - 25. 2. 2013 - 8:18 (hours:minutes) etc – nørdic Mar 7 '13 at 19:52
These answers do not correspond to the data in the question, correct? – Stu Mar 7 '13 at 19:56
I wrote it wrong :( comment is regular result for question. – nørdic Mar 7 '13 at 20:01
@Stu yes, they are the datediff between each pair of rows. – Aaron Bertrand Mar 7 '13 at 20:01

``````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
);
``````

``````SELECT
EmployeeID,
ClockIn,
ClockOut,