title: SQL Query to group items by time, but only if near each other?
body: <p>I am trying to craft a SQL statement to pull sample values from a DB. The table contains values that pertain to tool changes in a CNC machine. The current statement I have pulls values properly, but only if there is one occurrence of the tool in for a given program. If the tool appears multiple times, the time values correspond from the first load, to the last load. There is only one TIME column, and by finding the first and last occurrence of it, I can determine a tools in/out time.</p>
  
  <p>Basic example:    </p>
  
  <pre><code>Raw Data:
  Tool_Number    TIME    
  100            12:00
  100            12:01
  100            12:02
  100            12:03
  
  Current Query Returns: 
  Tool_Number    TIME_IN     TIME_OUT    
  100            12:00       12:03
  </code></pre>
  
  <p>Things get hairy when the tool appears multiple times though, since I can no longer utilize TOP and DISTINCT rules.</p>
  
  <pre><code>Raw Data:
  Tool_Number    TIME    
  100            12:00
  100            12:01
  100            12:02
  100            12:03
  200            12:04
  200            12:05
  100            12:06
  100            12:07
  
  Current Query Returns: 
  Tool_Number    TIME_IN     TIME_OUT    
  100            12:00       12:07
  200            12:04       12:05
  
  Ideal Query Returns:
  Tool_Number    TIME_IN     TIME_OUT    
  100            12:00       12:03
  200            12:04       12:05
  100            12:06       12:07
  </code></pre>
  
  <p>We are doing time analysis, and of course this seriously is messing with the total time values.
  Current query is:</p>
  
  <pre><code>SELECT * FROM (SELECT DISTINCT SPINDLE_POT FROM TBL_SPINDLE_DATA_M1 
  WHERE TIME BETWEEN '4/3/20131:24:13 PM' AND '4/3/2013 3:07:33 PM') AS A 
  
  CROSS APPLY
  
  ((SELECT TOP 1 TIME FROM TBL_SPINDLE_DATA_M1 B WHERE B.SPINDLE_POT = A.SPINDLE_POT AND
  TIME BETWEEN '4/3/2013 1:24:13 PM' AND '4/3/2013 3:07:33 PM') AS NEWTABLE1
  
  JOIN
  
  (SELECT TOP 1 TIME FROM TBL_SPINDLE_DATA_M1 B WHERE B.SPINDLE_POT = A.SPINDLE_POT 
  AND TIME BETWEEN '4/3/2013 1:24:13 PM' AND '4/3/2013 3:07:33 PM' ORDER BY TIME DESC) 
  AS NEWTABLE2 ON (0=0))
  </code></pre>
  
  <p>I am by no means any kind of SQL Query expert! The above query may be horribly wrong, but it does in fact return what I need. Is there anyway to group similar items, but be judicial enough to not group them if their indexes do not touch each other?</p>