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


By:   |   Updated: 2016-03-16   |   Comments (3)   |   Related: More > T-SQL


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.

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:



Last Updated: 2016-03-16


get scripts

next tip button



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.

View all my tips





Comments For This Article




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

 

How about this?

SELECT pkID, DataValue

FROM
(SELECT pkID, DataValue, SUM(DataValue) OVER(ORDER BY pkID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) neighbor_sum

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).

 



download





Recommended Reading

Cursor in SQL Server

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

How to use @@ROWCOUNT in SQL Server

SQL Server Loop through Table Rows without Cursor








get free sql tips
agree to terms


Learn more about SQL Server tools