CODEWITHSUNDEEP
sqlsql-servergroup-bycountgaps-and-islands
Dog
Dog

Reputation: 115

How to select values when there is an specific gap between that and the last record in SQL Server

I am creating a query that would let me select values when there is a gap of 10 between it and the last record in SQL Server.

Let's say there is a sensor in a freezer, the sensor uploads the freezer temperature every 5 minutes, I am trying to create a query that would let me get all records when temperatura dropped 2°F or more after the last record.

Here is an example of the table:

Value | Timestamp
------+------------------------
  10   2018-04-25 17:11:00.000 
  11   2018-04-25 17:16:00.000
  12   2018-04-25 17:21:00.000
  11   2018-04-25 17:26:00.000
  9    2018-04-25 17:31:00.000 <<<<<<<<<<<< Dropped 2°F
  10   2018-04-25 17:36:00.000 
  12   2018-04-25 17:41:00.000 
  9    2018-04-25 17:46:00.000 <<<<<<<<<<<< Dropped more than 2°F

Then my desired result of the query if I want it to return values from 2018-04-25 would be:

Times_Dropped_2
     2

Upvotes: 0

Views: 153

Answers (3)

Dog
Dog

Reputation: 115

WHERE value_diff >= Temp_Value + 10

This was the missing line, thanks

Upvotes: 0

Daniel Marcus
Daniel Marcus

Reputation: 2686

Adding to previous answer 

 select count(*) from (SELECT value,
           Timestamp,
           value- LAG(value, 1, 0) OVER (ORDER BY TimeStamp) AS value_diff
    FROM   table)a
    where value_diff>=2

Upvotes: 1

kiran
kiran

Reputation: 445

SELECT value,
       Timestamp,
       value- LAG(value, 1, 0) OVER (ORDER BY TimeStamp) AS value_diff
FROM   table;

This lists the diff from previous entry , u can fetch ur req detail from this

Upvotes: 2

Related Questions