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
  • 3
    Can you show your actual table structure and some sample data? – JNK Jul 9 '12 at 16:04
  • 2
    Can you also show the desired output? – Aaron Bertrand Jul 9 '12 at 16:59

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.


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;

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 
  • @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');
  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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