How to find consecutive records below a certain value?

Question

I have a table with hundreds of millions of rows in the following structure: case_id (int), channel_index (int), start_time (datetime), value (float), duration (smallint) I need to create a query that will return the case_ids where a channel_index has a value < a specific amount for 3 consecutive minutes. We have the duration column which contains the calculated number of seconds between that record and the next record to help speed up the query but I cannot seem to find a non brute force way to accomplish this. For example: I thought there might be a way to have a running sum over value < 60 or something like that but it needs to reset when the value is > 60. Any suggestions?

Answer 1

Any luck here?  I'm looking for something similar..

I'm polling (every 10 minutes or so) and  recording state changes in a 'service' with a reason for the change.  I want to report on the instances and duration a service is out or in a specific state.  

 

Answer 2

You can get the change tracking details using the below method. Please go through the article this will help you.

http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/

http://www.sqltreeo.com/wp/tracking-table-changes-seamlessly-with-change-data-capture-cdc/

http://msdn.microsoft.com/en-us/library/bb964713.aspx