SQL list periodes where value is more then a spesific value

Question

I have a table like this Id, DateTime, Device, Value. I need to find all periods where Value >=5, and Return a From and To dateTime for this periods. The Device can be a temp sensor, and I need a list of From/To where the temp has been over 5.

If we think it is a temp sensor and it logs every 5min. the table will be like this:

Id DateTime         Device Value
--------------------------------
1  2015.09.01 09:10  T1     3,2 
2  2015.09.01 09:15  T1     5,2 
3  2015.09.01 09:20  T1     6,2 
4  2015.09.01 09:25  T1     5,8 
5  2015.09.01 09:30  T1     3,2 
6  2015.09.01 09:35  T1     1,2 
7  2015.09.01 09:40  T1     5,6 
8  2015.09.01 09:45  T1     6,1 
9  2015.09.01 09:50  T1     5,0 
10 2015.09.01 09:55  T1     2,0

The periods I try to find is then from 09:15 to 09:25 and from 09:40 to 09:50.

Is there a smart way of doing this in SQL Server 2014?

It would be helpful if you could provide some sample data along with required result set. — Giorgos Betsos, Sep 01 '15 at 15:55
I am not sure where to start. I think I have to find all records where value>=5 and then I have to find the last record where the value is >=5 but where ther has not been any records With lover value. — Svein Jarle, Sep 01 '15 at 15:59
If we think it is a temp sensor and it logs every 5min. the tabel will be like this. 1. 2015.09.01 09:10 T1 3,2 2. 2015.09.01 09:15 T1 5,2 3. 2015.09.01 09:20 T1 6,2 4. 2015.09.01 09:25 T1 5,8 5. 2015.09.01 09:30 T1 3,2 6. 2015.09.01 09:35 T1 1,2 7. 2015.09.01 09:40 T1 5,6 8. 2015.09.01 09:45 T1 6,1 9. 2015.09.01 09:50 T1 5,0 10. 2015.09.01 09:55 T1 2,0 the to periodes I try to find is then from 09:15 to 09:25 and from 09:40 to 09:50. — Svein Jarle, Sep 01 '15 at 16:08
If I understand the question correctly you have a table with frequently recorded temperatures, and you want to get a resultset with periods where the temperature did not drop below 5 degrees. To do this, you would need to order the table by the date, group by records having temperatures above 5 degrees, and select the MIN and MAX date for each group. A nice problem indeed! — Karel-Jan Misseghers, Sep 01 '15 at 16:13
Yes, I think you start to now what I need :), I like Your ide, but then I think I will only get 1 result in return? There can be 5 times in a day where the temp has been over 5 degrees and I need to find all of this periodes. The result set is 5 records with start and end time of every time the temp has gone over 5 degrees. (sorry my English writing is not that good) — Svein Jarle, Sep 01 '15 at 16:25

score 2 · Accepted Answer · answered Sep 01 '15 at 17:03

This is a way of getting the required result:

SELECT MIN([DateTime]) AS StartPeriod, MAX([DateTime]) AS EndPeriod,
       Device
FROM (       
SELECT Id, [DateTime], Device, Value,
       ROW_NUMBER() OVER (PARTITION BY Device ORDER BY Id) -
       COUNT(CASE WHEN Value >= 5 THEN 1 END) 
       OVER (PARTITION BY Device ORDER BY Id) AS grp
FROM mytable ) AS t
WHERE t.Value >= 5
GROUP BY Device, grp

You may need to PARTITION BY Device, [DateTime] if you want to get Start - End periods on a daily basis.

Demo here

Thanks, Giorgos Betsos :) – Svein Jarle Sep 01 '15 at 18:36 — Svein Jarle, Sep 01 '15 at 18:36

score 0 · Answer 2 · answered Sep 01 '15 at 16:44

Yes there is a smart way of doing this.

Take a look at 'PARTITION BY' and the 'OVER' clause.

Using these functionalities I believe you should be able to partition (group) the data in periods and get the MIN and MAX values for the DateTime column of each group.

score 0 · Answer 3 · edited May 23 '17 at 12:16

This has basically been answered before and the trick is that you have continuously and contiguously ordered id column already. If that did not exist you'd need to do a little more up front work to get the subtraction to work appropriately.

select * into #tempTable from (
select Id = 1,[DateTime] = cast('2015.09.01 09:10' as datetime), device = 'T1', value = 3.2
union all select 2,cast('2015.09.01 09:15' as datetime),'T1',5.2
union all select 3,cast('2015.09.01 09:20' as datetime),'T1',6.2
union all select 4,cast('2015.09.01 09:25' as datetime),'T1',5.8
union all select 5,cast('2015.09.01 09:30' as datetime),'T1',3.2
union all select 6,cast('2015.09.01 09:35' as datetime),'T1',1.2
union all select 7,cast('2015.09.01 09:40' as datetime),'T1',5.6
union all select 8,cast('2015.09.01 09:45' as datetime),'T1',6.1
union all select 9,cast('2015.09.01 09:50' as datetime),'T1',5
union all select 10,cast('2015.09.01 09:55' as datetime),'T1',2) A


with T as (select *, row_number() over (order by id) - id as grp
    from #tempTable
    where value >=5
    )
select [to] = min(T.Datetime), [from] =  max(T.DateTime) 
from T
group by T.grp
order by T.grp

with the output being

to                        from
2015-09-01 09:40:00.000   2015-09-01 09:50:00.000
2015-09-01 09:15:00.000   2015-09-01 09:25:00.000

(I had to do some casting to make your number/date formats work for me)

user2955677 · Answer 4 · 2015-09-01T17:38:51.453

I've been using a table User Defined Function that takes the requested day in argument, and a cursor inside the function allowing to populate the returned table, as follows :

CREATE TABLE sensor (id int not null identity(1,1) primary key, 
    measureDate datetime, sensor nvarchar(10), measure float)

INSERT sensor (measureDate, sensor, measure) VALUES
('2015-09-01 09:10', 'T1', '3.2'), ('2015-09-01 09:15', 'T1', '5.2'),
('2015-09-01 09:20', 'T1', '6.2'), ('2015-09-01 09:25', 'T1', '5.8'),
('2015-09-01 09:30', 'T1', '3.2'), ('2015-09-01 09:35', 'T1', '1.2'),
('2015-09-01 09:40', 'T1', '5.6'), ('2015-09-01 09:45', 'T1', '6.1'),
('2015-09-01 09:50', 'T1', '5.0'), ('2015-09-01 09:55', 'T1', '2.0')
GO
CREATE FUNCTION [dbo].[getTimeSpansBelowMaxTemp] (@measureDate date) 
    RETURNS @timeSpans TABLE (fromTime time, toTime time) AS BEGIN

    DECLARE @measure float, @currentMeasure float = NULL
    DECLARE @measureTime time, @fromMeasureTime time, @toMeasureTime time
    DECLARE yourCursor CURSOR FOR
    SELECT CAST(measureDate AS time), measure 
        FROM sensor
            WHERE CAST(measureDate as date) = @measureDate
    OPEN yourCursor
    FETCH NEXT FROM yourCursor INTO @measureTime, @measure
    WHILE (@@FETCH_STATUS = 0) BEGIN -- Loops on all the measures of the given day
        IF @measure >= 5.0 BEGIN
                IF @currentMeasure IS NULL BEGIN -- Start of a period
                    SET @currentMeasure = @measure
                    SET @fromMeasureTime = @measureTime
                END
                SET @toMeasureTime = @measureTime
        END
        ELSE BEGIN
                IF @currentMeasure IS NOT NULL BEGIN -- End of a period
                    INSERT INTO @timeSpans VALUES (@fromMeasureTime, @toMeasureTime)
                    SET @currentMeasure = NULL
                END
        END 
        FETCH NEXT FROM yourCursor INTO @measureTime, @measure
    END

    CLOSE yourCursor
    DEALLOCATE yourCursor

    RETURN
END
GO

select * from dbo.[getTimeSpansBelowMaxTemp]('2015-09-01')

SQL list periodes where value is more then a spesific value

4 Answers4