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.
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