Databases
StackOverflow2013
Postsdbo
POHow would I calculate timespans from a list of check in and check outs?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow would I calculate timespans from a list of check in and check outs?
    text
    copied!<p>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.</p> <p>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.</p> <p>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.</p> <p>For example, here is a sample output:</p> <pre><code>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) </code></pre> <p>Where ActionType 1 is "ClockIn" and ActionType 2 is "ClockOut". I also pruned out the User, Project, and Description columns for brevity.</p> <p>I need to generate, in pure SQL, a result set like:</p> <pre><code>Description | Total Time </code></pre> <p>For each ClockIn / ClockOut Pair.</p> <p>I figure this will actually be fairly simple, I'm just not quite sure which way to approach it.</p> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload