SQL Query to group items by time, but only if near each other?

gaps-and-islands, sql, sql-server, sql-server-2012

Solution

Here's another approach using `LAG/LEAD`:

DECLARE @rawdata TABLE(Tool_Number INT, [Time] TIME(0));

INSERT @rawdata VALUES
(100,'12:00'), (100,'12:01'), (100,'12:02'), (100,'12:03'),
(200,'12:04'), (200,'12:05'),
(100,'12:06'), (100,'12:07');

;WITH x AS
(
  SELECT Tool_Number, [Time], 
    s = CASE Tool_number WHEN LAG(Tool_number,1) OVER (ORDER BY [Time]) 
        THEN 0 ELSE 1 END,
    e = CASE Tool_number WHEN LEAD(Tool_number,1) OVER (ORDER BY [Time]) 
        THEN 0 ELSE 1 END
  FROM @rawdata
),
y AS 
(
  SELECT Tool_Number, s, [Time], e = LEAD([Time],1) OVER (ORDER BY [Time]) 
  FROM x WHERE 1 IN (s,e)
)
SELECT Tool_number, TIME_IN = [Time], TIME_OUT = e 
FROM y 
WHERE s = 1
ORDER BY TIME_IN;

Results:

Tool_number  TIME_IN   TIME_OUT
-----------  --------  --------
100          12:00:00  12:03:00
200          12:04:00  12:05:00
100          12:06:00  12:07:00

Problem

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. Basic example: ``` 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 ``` Things get hairy when the tool appears multiple times though, since I can no longer utilize TOP and DISTINCT rules. ``` 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 ``` We are doing time analysis, and of course this seriously is messing with the total time values. Current query is: ``` 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)) ``` 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?

Original source