Need query help for consecutive records...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need query help for consecutive records...

Author

Topic

mdutra
Starting Member

2 Posts

Posted - 2011-12-06 : 16:26:02

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-06 : 16:30:12

You'll need to illustrate this with sample data, please.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 23:55:31

are you looking for something like this? sounds like below from your explanation

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mdutra
Starting Member

2 Posts

Posted - 2011-12-07 : 10:07:02

Here is some sample data:

SET NOCOUNT ON;
GO
USE tempdb;
GO
CREATE TABLE [continuous_data](
[case_id] [int] NOT NULL,
[channel_index] [smallint] NOT NULL,
[start_time] [datetime] NOT NULL,
[dms_type] [char](1) NOT NULL,
[dms_value] [float] NOT NULL,
[value_duration] [smallint] NOT NULL
);
GO
Insert into continuous_data(case_id, channel_index, start_time, dms_type, dms_value, value_duration)
select 2674,0,'2011-09-30 15:32:46.000','0',70.11,1 union all
select 2674,1,'2011-09-30 15:32:46.000','0',15,1 union all
select 2674,2,'2011-09-30 15:32:46.000','0',96.1,1 union all
select 2674,3,'2011-09-30 15:32:46.000','0',3.5,1 union all
select 2674,0,'2011-09-30 15:32:47.000','0',70.2,1 union all
select 2674,1,'2011-09-30 15:32:47.000','0',15.2,1 union all
select 2674,2,'2011-09-30 15:32:47.000','0',96,1 union all
select 2674,3,'2011-09-30 15:32:47.000','0',3.5,1 union all
select 2674,0,'2011-09-30 15:32:48.000','0',65.4,1 union all
select 2674,1,'2011-09-30 15:32:48.000','0',15.2,1 union all
select 2674,2,'2011-09-30 15:32:48.000','0',95.8,1 union all
select 2674,3,'2011-09-30 15:32:48.000','0',3.5,1 union all
select 2674,0,'2011-09-30 15:32:49.000','0',60,1 union all
select 2674,1,'2011-09-30 15:32:49.000','0',15.1,1 union all
select 2674,2,'2011-09-30 15:32:49.000','0',94.5,1 union all
select 2674,3,'2011-09-30 15:32:49.000','0',3.5,1
go

This is 4 seconds of data for 4 channels for a single case. In a real case we will have 2+ hours worth for 10+ channels and there will be thousands of cases in the database. I didn't want to make the sql I included here too huge but if more data is needed I can provide it. The time between each reading is not always 1 second either so that I cannot make any assumptions on the duration. The duration column is added by the device entering in the data to improve performance of subsequent data queries so it can be omitted if it provides no value.

An example query is what I had in the first post but the repeat:
Find all cases where channel 0 is below 65 for more than 3 consecutive minutes.

Our brute force method uses cursors and simply loops through all the records case by case and adding up duration when dms_value is below 65 and adding the case if the sum reaches 180 and if the dms_value goes back over 65 clearing it out. Seems to me that there has to be a better way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources