Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
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. –  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
show 3 more comments

1 Answer

up vote 8 down vote accepted
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;

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 ...
share|improve this answer
    
This is absolutely briliant :) thank you very much :) You helped me greatly. Really very. :) –  nørdic 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. –  nørdic 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
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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