Clever Geek Handbook

How would I calculate the time frame from the check and validate list?

I am writing a simple time tracking program to manage my own projects. I'm a big fan of storing reporting code in a database, so I tried to create a few sprocs that generate invoices and timetables, etc.

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

I can select from this table to get the clock for a specific time / project / and user, but I want to concatenate it so that each clock input and output converts from two rows to one row containing the total time.

For example, here's some 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)

If ActionType 1 is "ClockIn" and ActionType 2 is "ClockOut". I've also abbreviated 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 suppose this is going to be pretty simple, I'm just not really sure which way it fits.

EDIT: The user will be able to work on multiple projects at the same time, although narrowing the result set to one project first, this should have nothing to do with the logic here.

0


source to share


3 answers


I agree that the design is not the biggest - an event based structure with one line to start with will probably save you a lot of time. In this case, you can create an entry with a zero end date when someone starts syncing. Then enter the end date when they disconnected.

But that is not what you asked for. 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


Please note that I am using a table variable which works with MS SQL Server for testing purposes only. Change if necessary. Also note that SQL Server 2000 does not work well with such queries. 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
+1


source


I think your original storage scheme is wrong. You are looking at it in terms of a clock, hence ClockAction. Why not from a project perspective?

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



Then a simple GROUP BY command should do the trick.

+1


source


Have you ever thought about things like:

  • someone forgets to get out of order, and hence it seems like they have synced twice?
  • someone forgets about the clock, and therefore it seems like it went out of order twice?

In the first case, is it right to just ignore the second timeout? The accepted answer will associate both timers with the same timeout. It is right?

In the second case, the second timeout will be ignored. It is right?

0


source






More articles:


All Articles