I am unable to solve this query problem for fetching common duration of two different IP addresses in which both are in OFF status.
Cases in the below sample data
1 (Simple case) - IP address "10.0.1.2" is remains OFF for 00:10:10 to 00:20:00 and "10.0.1.3" is also OFF in this duration, so the common duration of OFF for both is 00:10:10 to 00:20:00.
2 (Problematic)- IP address "10.0.1.2" is OFF for 13:00:00 to 13:25:00 and if we check it with other IP address, it is OFF for 12:55:00 to 13:20:00. So, the common duration in both are 13:00:00 to 13:20:00.
Sample data:
ID IP address Status Time
----------------------------------
1 10.0.1.2 OFF 00:10:00
1 10.0.1.2 ON 00:20:00
1 10.0.1.2 OFF 11:00:00
1 10.0.1.2 ON 11:20:00
1 10.0.1.2 OFF 13:00:00
1 10.0.1.2 ON 13:25:00
1 10.0.1.2 OFF 14:05:00
1 10.0.1.2 ON 14:10:00
1 10.0.1.2 OFF 15:35:00
1 10.0.1.2 ON 15:45:00
1 10.0.1.3 OFF 00:10:00
1 10.0.1.3 ON 00:20:00
1 10.0.1.3 OFF 11:05:00
1 10.0.1.3 ON 11:25:00
1 10.0.1.3 OFF 12:55:00
1 10.0.1.3 ON 13:20:00
1 10.0.1.3 OFF 17:10:00
1 10.0.1.3 ON 17:15:00
1 10.0.1.3 OFF 15:00:00
1 10.0.1.3 ON 16:45:00
Output:
ID IP addresses Status Time
-----------------------------------------
1 10.0.1.3,10.0.1.2 OFF 00:10:00
1 10.0.1.3,10.0.1.2 ON 00:20:00
1 10.0.1.3,10.0.1.2 OFF 11:05:00
1 10.0.1.3,10.0.1.2 ON 11:20:00
1 10.0.1.3,10.0.1.2 OFF 13:00:00
1 10.0.1.3,10.0.1.2 ON 13:20:00
1 10.0.1.3,10.0.1.2 OFF 15:35:00
1 10.0.1.3,10.0.1.2 ON 15:45:00