The following query will give you the gaps exactly :
Insert one more record...
INSERT INTO sensor_data VALUES ('UNIT1',10,1.235,SYSDATE);
COMMIT;
SELECT measurement_id - gap gap_from, measurement_id - 1 gap_to
FROM ( SELECT measurement_id, CASE measurement_id - NVL((LAG(measurement_id) OVER (ORDER BY measurement_id)),0)
WHEN 1
THEN 0
ELSE measurement_id - (LAG(measurement_id)
OVER (ORDER BY measurement_id)) -1
END gap
FROM sensor_data )
WHERE gap > 0;
Output will look like:
-GAP_FROM GAP_TO
3 3
6 6
8 9
Hope you find this useful.
Wednesday, April 30, 2008
Subscribe to:
Post Comments (Atom)

No comments:
Post a Comment