Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
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

3 Answers 3

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.

http://sqlfiddle.com/#!3/6b1f5/9

CREATE TABLE event (ID int, EventTime datetime, Action varchar(10));
INSERT INTO event VALUES
(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 (
SELECT *
FROM event
WHERE Action = 'Start'
)
,stops AS (
SELECT *
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
(
    SELECT
        e.ID,
        e.Action,
        (
            CASE e.Action
                WHEN 'Start' THEN MIN(e.EventTime)
                WHEN 'Stop' THEN MAX(e.EventTime)
            END
        ) AS ActionTime
        FROM [dbo].[event] e
        GROUP BY e.ID, e.Action
)
SELECT
    ea1.ID,
    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
    WHERE
        (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

 
discard

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.