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

By:   |   Comments (4)   |   Related: > TSQL


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?


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,

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.

select all of the rows in the table

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.

Validate the results in SQL Server Management Studio
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:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, December 19, 2021 - 3:45:13 PM - pabeader Back To Top (89604)
You, sir, are a lifesaver. I've been trying to find a way to do this for a very long time. Much needed in SPC data analysis.

Wednesday, December 12, 2018 - 12:08:18 AM - Ankit Upadhyay Back To Top (78455)


How about this?

SELECT pkID, DataValue


FROM [dbo].[tblSensorData] s

WHERE s.DataValue > 5

) t1

WHERE neighbor_sum > 15

Wednesday, August 10, 2016 - 11:11:39 AM - JohnC Back To Top (43094)

Nice. The only thing I don't like about this technique is that it requires an unbroken chain of identity values. An improvement might be to substitute a ROW_NUMBER computed over a DateTime column. That'll guarantee sequential values.

Friday, March 18, 2016 - 9:45:02 AM - Henry Stinson Back To Top (40985)

 Very cool as an exercise in CTE and the windowing functions and new syntax.

Not sure this would ever be useful to me, unless I were writing code to monitor some system, such as a medical monitoring system or temperature control system, but I CAN see where this could be useful for detecting certain kinds of financial transactions that might be involved in money laundering (for example).


get free sql tips
agree to terms