Work out date difference when there is only one date

Question

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

Answer 1

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;

Answer 2

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 

Answer 3

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');