Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have 2 records, one details when the stop clock was stopped and the other started. I only have the one date value in each record. I'm trying to work out the difference between the stop and start dates. I'm looking for pointers. I thought by doing something like:

SELECT e1.ID, MIN(e1.EventTime) AS [StopTotalMinutes]
          ,MAX(e2.EventTime) AS [StartTotalMinutes]
  FROM event AS e1
  JOIN event AS e2
    ON e1.ID = e2.ID

I would have been able to work something out, but I'm failing.

So, Table and some sample data is:

ID    |    EventTime     |   Action
1       2012-01-01 10:00      Stop
1       2012-01-01 13:31     Start
1       2012-01-03 09:45      Stop
2       2012-01-01 11:12      Stop
2       2012-01-10 15:35     Start
2       2012-01-11 08:39      Stop
share|improve this question
Can you show your actual table structure and some sample data? – JNK Jul 9 '12 at 16:04
Can you also show the desired output? – Aaron Bertrand Jul 9 '12 at 16:59
up vote 4 down vote accepted

With your sample data, there are some unmatched pairs - this solution only gives the interval for the matched pairs, and more than two entries for each event, which can cause an cross join between the start and two stops.!3/6b1f5/9

CREATE TABLE event (ID int, EventTime datetime, Action varchar(10));
(1,       '2012-01-01 10:00',      'Stop')
,(1,       '2012-01-01 13:31',     'Start')
,(1,       '2012-01-03 09:45',      'Stop')
,(2,       '2012-01-01 11:12',      'Stop')
,(2,       '2012-01-10 15:35',     'Start')
,(2,       '2012-01-11 08:39',      'Stop');

WITH starts AS (
FROM event
WHERE Action = 'Start'
,stops AS (
FROM event
WHERE Action = 'Stop'
,intervals AS (
SELECT Starts.ID, Starts.EventTime AS StartTime
       ,(SELECT MIN(Stops.EventTime)
         FROM Stops
         WHERE Stops.EventTime > Starts.EventTime
         AND Stops.ID = Starts.ID) AS StopTime
FROM Starts
SELECT *, DATEDIFF(MINUTE, StartTime, StopTime) AS minutes FROM intervals;
share|improve this answer

This should do it:

SELECT e1.ID, DATEDIFF(MINUTE,e1.EventTime,e2.EventTime) [Minutes]
FROM (SELECT * FROM event WHERE Action = 'Start') AS e1
LEFT JOIN (SELECT * FROM event WHERE Action = 'Stop') AS e2
ON e1.ID = e2.ID 
share|improve this answer
@NeilKnight - sorry, but looking at your sample, where you can have more than 2 records for one idea, then this solution won't work – Lamak Jul 9 '12 at 16:29

I used Cade Roux's data setup.

WITH eventActions AS
            CASE e.Action
                WHEN 'Start' THEN MIN(e.EventTime)
                WHEN 'Stop' THEN MAX(e.EventTime)
        ) AS ActionTime
        FROM [dbo].[event] e
        GROUP BY e.ID, e.Action
    ea1.ActionTime AS StartTime,
    ea2.ActionTime AS StopTime,
    DATEDIFF(MINUTE, ea1.ActionTime, ea2.ActionTime) AS Interval
    FROM eventActions ea1
    LEFT OUTER JOIN eventActions ea2 ON ea2.ID = ea1.ID
        (ea1.Action = 'Start') AND
        (ea2.Action = 'Stop');
share|improve this answer
Note: this accounts for all scenarios except for dangling stop times (i.e., stop times with no start times for the same ID value). – Jon Seigel Jul 9 '12 at 17:49
Thanks for this, but I'd need to work out the dangling Stop time up to GetDate(). – Neil Knight Jul 10 '12 at 8:02
@Neil: What? To include the dangling stop times, use a FULL OUTER JOIN. – Jon Seigel Jul 10 '12 at 13:06

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.