SQL Server T-SQL to Return Consecutive Values Over a Certain Value

Problem

I need to know when the value of my sensor data stored in a SQL Server column exceeds a specified value for more than 3 consecutive rows. How can I do this in T-SQL?

Solution

Often times with time-series data, we are only interested when data exceeds a particular value for a specified window of time. In this tip, we will use T-SQL to return the rows where our sensor data exceeds a value of 5 for more than 3 rows.

Let’s start by creating a table and loading some data using the T-SQL below.

CREATE TABLE [dbo].[tblSensorData](
[pkID] [int] identity(1,1) Primary key,
[DataValue] [numeric](5, 2) NULL,
) ON [PRIMARY]
GO
insert into [dbo].[tblSensorData] values (1.0)
insert into [dbo].[tblSensorData] values (7.1)
insert into [dbo].[tblSensorData] values (2.2)
insert into [dbo].[tblSensorData] values (3.3)
insert into [dbo].[tblSensorData] values (4.4)
insert into [dbo].[tblSensorData] values (5.5)
insert into [dbo].[tblSensorData] values (6.6)
insert into [dbo].[tblSensorData] values (7.7)
insert into [dbo].[tblSensorData] values (1.8)
insert into [dbo].[tblSensorData] values (1.9)
select * from [dbo].[tblSensorData]

We have 10 data points in this example as shown below.

After loading the data, we will execute the following T-SQL code to select all of the rows where the DataValue column exceeds a value of 5 for 3 or more consecutive rows. The T-SQL code below uses a Common Table Expression (CTE) to temporarily store the rows where the DataValue exceeds 5 and a count of the number of consecutive rows. The bottom SELECT query returns those rows where the consecutiveValueCount is greater than or equal to 3. In this example only rows 6, 7 and 8 will be returned.

;WITH consecutiveValues AS (
  SELECT *, pkID - ROW_NUMBER() OVER (ORDER BY pkID) AS dataGrouping
  FROM  [dbo].[tblSensorData]
  WHERE [DataValue] > 5
),
valuesCounted AS (
  SELECT *, COUNT(*) OVER (PARTITION BY dataGrouping) AS consecutiveValueCount
  FROM consecutiveValues
)
SELECT pkID, [DataValue]
FROM valuesCounted
WHERE consecutiveValueCount >= 3

The image below shows the results of executing the queries. We can see that row 2 was not selected because even though the value in row 2 exceeds 5, there are not 3 or more consecutive values where the value exceeds 5.

Next Steps

Adjust the thresholds for the DataValue and consecutiveValueCount in the WHERE clauses of the above queries to see how the returned rows change. Also, be sure to check out these other tips on T-SQL from MSSQLTips.com:

• How to use SQL Server CTEs to make your T-SQL code readable by humans
• Create Your Own RANDBETWEEN Function in T-SQL
• Generating Random Numbers in SQL Server Without Collisions
• SQL Server Random Sorted Result Set
• Our complete tutorial list

.

Dallas Snider

Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida. He received his Ph.D. in Integrated Computing and M.S. in Instrumental Sciences from the University of Arkansas at Little Rock. He received a B.A. in Physics from Hendrix College. Before joining UWF, he worked as a data warehouse developer for Northrop Grumman Information Systems and prior to that as a database application developer for Acxiom and Euronet. Dr. Snider’s teaching and research interests include data mining, data warehousing, information visualization, and software development.