How would I calculate timespans from a list of check in and check outs?

Question

I'm writing a simple time tracking program to manage my own projects. I'm a big fan of keeping reporting code in the database, so I've been attempting to create a few sprocs that generate the invoices and timesheets etc.

I have a table that contains Clock Actions, IE "Punch In", and "Punch Out". It also contains the user that did this action, the project associated with the action, and the current date/time.

I can select from this table to get clock in's for a specific time/project/and user, but I want to aggregate it down so that each clock in and out is converted from 2 rows to a single row containing total time.

For example, here is a sample output:

ClockActionID        ActionType DateTime
-------------------- ---------- -----------------------
17                   1          2008-11-08 18:33:56.000
18                   2          2008-11-08 18:33:59.587
19                   1          2008-11-08 18:34:01.023
20                   2          2008-11-08 18:34:02.037
21                   1          2008-11-08 18:45:06.317
22                   2          2008-11-08 18:46:14.597
23                   1          2008-11-08 18:46:16.283
24                   2          2008-11-08 18:46:17.173
25                   1          2008-11-08 18:50:37.830
26                   2          2008-11-08 18:50:39.737
27                   1          2008-11-08 18:50:40.547

(11 row(s) affected)

Where ActionType 1 is "ClockIn" and ActionType 2 is "ClockOut". I also pruned out the User, Project, and Description columns for brevity.

I need to generate, in pure SQL, a result set like:

Description   |    Total Time

For each ClockIn / ClockOut Pair.

I figure this will actually be fairly simple, I'm just not quite sure which way to approach it.

EDIT: The user will be able to clock into multiple projects simultaneously, though by first narrowing down the result set to a single project, this shouldn't make any difference to the logic here.

Answer 1

I think your original storage schema is flawed. You're looking at it from the perspective of the clock, hence ClockAction. Why not from the Project's perspective?

TABLE ProjectAction (Projectid, Userid, Type, Start, End)

Then a simple GROUP BY should do the trick.

Answer 2

I agree that the design isn't the greatest--an event based structure with a single row for start-end will probably save you a lot of time. In that case, you could create a record with a null end-date when someone clocks in. Then, fill in the end date when they clock out.

But, that's not what you asked. This is the solution to your problem:

DECLARE @clock TABLE (ClockActionID INT PRIMARY KEY IDENTITY, ActionType INT, ActionDateTime DATETIME)

INSERT INTO @clock (ActionType, ActionDateTime) VALUES (1,'20080101 00:00:00')
INSERT INTO @clock (ActionType, ActionDateTime) VALUES (2,'20080101 00:01:00')
INSERT INTO @clock (ActionType, ActionDateTime) VALUES (1,'20080101 00:02:00')
INSERT INTO @clock (ActionType, ActionDateTime) VALUES (2,'20080101 00:03:00')
INSERT INTO @clock (ActionType, ActionDateTime) VALUES (1,'20080101 00:04:00')
INSERT INTO @clock (ActionType, ActionDateTime) VALUES (2,'20080101 00:05:00')
INSERT INTO @clock (ActionType, ActionDateTime) VALUES (1,'20080101 00:06:00')
INSERT INTO @clock (ActionType, ActionDateTime) VALUES (2,'20080101 00:07:00')
INSERT INTO @clock (ActionType, ActionDateTime) VALUES (1,'20080101 00:08:12')
INSERT INTO @clock (ActionType, ActionDateTime) VALUES (2,'20080101 00:09:00')

-- Get the range
SELECT ActionDateTime CheckIn, 
  (SELECT TOP 1 ActionDateTime 
   FROM @clock C2 
   WHERE C2.ActionDateTime > C.ActionDateTime) CheckOut   
FROM @clock C
WHERE ActionType = 1

-- Get the duration
SELECT DATEDIFF(second, ActionDateTime, 
  (SELECT TOP 1 ActionDateTime 
   FROM @clock C2 
   WHERE C2.ActionDateTime > C.ActionDateTime)
  ) / 60.0 Duration_Minutes
FROM @clock C
WHERE ActionType = 1

Note that I'm using a table variable which works with MS SQL Server just for testing. Change as needed. Also note that SQL Server 2000 does not perform well with queries like this. Here are the test results:

CheckIn                 CheckOut
2008-01-01 00:00:00.000 2008-01-01 00:01:00.000
2008-01-01 00:02:00.000 2008-01-01 00:03:00.000
2008-01-01 00:04:00.000 2008-01-01 00:05:00.000
2008-01-01 00:06:00.000 2008-01-01 00:07:00.000
2008-01-01 00:08:12.000 2008-01-01 00:09:00.000

Duration_Minutes
1.000000
1.000000
1.000000
1.000000
0.800000

Answer 3

Have you thought about things like:

• someone forgets to clock out, and thus it appears they clocked in twice?
• someone forgets to clock in, and thus it appears they clocked out twice?

In the first case, is it correct to simply ignore the second clock-in? The accepted answer will bind both clock-ins to the same clock-out. Is that correct?

In the second case, the second clock-out will be ignored. Is that correct?