Select min date after selected date

Question

I have a schema that looks like this:

------------------------------------
ID | Time | Type | Description | obj
------------------------------------

And some data will be like

1 | 01/01/1900 01:01:01 AM | 1 | Start | O1
2 | 01/01/1900 01:01:02 AM | 1 | Start | O2
3 | 01/01/1900 01:01:03 AM | 2 | Stop  | O1
4 | 01/01/1900 01:01:04 AM | 2 | Stop  | O2

Notes:

• The O1, O2 etc. is the ID of the object that the process operated on. It will be consistent between the start/stop times, but there will be multiple start/stop times for each object (the process will start and finish operating on a specific object multiple times, and my query will need to select records for each time the process processed that object)
• The description says Start/Stop for the benefit of this question's clarity. In practice, it has all kinds of data that is parsed out.

So, what I need are the pairs of start times and stop times that are closest to each other. Stated another way: for every start time, I need the next closest stop time. So the result of a select statement for the sample data above (that only selected ids) would return:

(1, 3)

(2, 4)

What I've tried:

SELECT  obj, 
        [Time] AS StartTime, 
        (SELECT MIN([TIME]) AS t
         FROM   TheTable
         WHERE  [Type] = 2
         HAVING MIN([Time]) > StartTime) AS StopTime
FROM     TheTable
WHERE    [Type] = 1;

This obviously doesn't work as StartTime is unknown to the inner select. Without the Having clause in the inner select, it runs but I get the same StopTime for all entries, as you would expect. Which is, of course, not what I need.

Is there any way that I can solve this?

Answer 1

I am not sure why it doesn't work.

You can always use outer column reference in SubQ, you are missing a 'comma' and 'group by' BTW

SELECT  obj, 
       [Time] AS StartTime,
       (SELECT MIN([TIME]) AS t
         FROM   TheTable
         WHERE  [Type] = 2
         and t1.obj = obj
         HAVING t > StartTime) AS StopTime
FROM     TheTable t1
WHERE    [Type] = 1
group by obj,TIME;

EDIT:

I am not expert in SQL server and have no idea why the column alias is not working. This query works in other Dbs like Teradata which I was using. Anyhow, you can use table alias to workaround this.

SELECT  obj, 
   [TIME] AS StartTime,
   (SELECT MIN([TIME]) As [tt]
     FROM   TheTable
     WHERE  [Type] = 2
    and t1.obj = obj
     HAVING MIN([TIME]) > t1.TIME
      ) AS StopTime
FROM     TheTable t1
WHERE    [Type] = 1
group by obj,TIME;

SQLFiddle:

http://sqlfiddle.com/#!6/3a745/14

Now, it seems that column alias is not allowed in Having clause in SQL server:

SELECT  obj, 
   min([tdate]) AS StartTime from thetable group by obj having starttime>5 ;

Invalid column name 'starttime'.: SELECT obj, min([tdate]) AS StartTime from thetable group by obj having starttime>5

Answer 2

SELECT t1.obj, t1.Time as Start, min(t2.Time) as Stop
FROM TheTable t1
LEFT OUTER JOIN TheTable t2
ON t1.obj = t2.obj and t2.Description = 'Stop' and t2.Time > t1.time
WHERE t1.Description = 'Start' 
GROUP BY (t1.obj, t1.Time, t1.Description, t2.Description)

left outer join because there might be a start time and not yet a stop time