Tuesday, December 06, 2011 4:20 PMI 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?
Tuesday, December 13, 2011 1:28 AM
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.
Tuesday, December 20, 2011 1:39 PM
You can get the change tracking details using the below method. Please go through the article this will help you.http://www.sqltreeo.com/wp/tracking-table-changes-seamlessly-with-change-data-capture-cdc/