Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (2)   |   Related Tips: More > T-SQL


SQL Server Conference Giveaway - click to learn more


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 Update:


signup button

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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

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

 


Learn more about SQL Server tools