StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>But, that's not what you asked. This is the solution to your problem:</p> <pre><code>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 </code></pre> <p>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 <strong>does not perform well</strong> with queries like this. Here are the test results:</p> <pre><code>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 </code></pre>

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