2

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 – msavara Mar 7 '13 at 19:52
  • 1
    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. – msavara 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
9
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 ...
  • This is absolutely briliant :) thank you very much :) You helped me greatly. Really very. :) – msavara Mar 7 '13 at 20:20
  • 3
    No problem. Note that if you are missing a row in a pair, everything is going to be messed up... you really should be storing start and end times in separate columns. Much easier to deal with missing data that way (and this query would be a lot simpler, too). – Aaron Bertrand Mar 7 '13 at 20:24
  • What would you think is the best db schema for attendance? for different number of stard and end work. (lunch, dinner etc) ? :) I mean just attendance. not the user, project, and other in this solution. – msavara Mar 7 '13 at 20:30
  • UserID, SignIn, SignOut – Stu Mar 7 '13 at 20:57
  • 1
    @nørdic I have absolutely no idea. I don't know your business / application requirements and what else you want to track. This will track clocking in and clocking out. If you have other business requirements, and are looking for schema design help, please create a question explicitly for that. – Aaron Bertrand Mar 7 '13 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.