how to get maintained values for a certain period

[Huisheng Chen]

there are a series of time based data, every 15 mins 1 record： Time Value 00:15 10 00:30 11 00:45 9 01:00 21 01:15 18 01:30 20 01:45 19 02:00 15 02:15 13 02:30 15 02:45 14 03:00 12 03:15 22 03:30 20 03:45 21 04:00 19 ... ... ... how to get the records that maintains over 18 for over 30 mins? for example, for 01:00 it starts to be over 18, and maintains for over 30 mins, so 01:00 should be chosen, but 01:15,01:30 should not, because the time difference between them and 01:00 is less than 30 mins，and even 01:45 should not be chosen, because no values are over 18 and maintains for 30 mins.

Regards, unruledboy_at_gmail_dot_com http://www.xnlab.com

[Luc Pattyn]

here is an idea: join the table to itself, and set appropriate conditions. in pseudo-SQL-code:

SELECT a.time,a.value,b.value,c.value FROM table as a
JOIN table as b ON b.time=a.time+15minutes
JOIN table as c ON c.time=b.time+15minutes
WHERE a.value>=18 AND b.value>=18 AND c.value>=18

You'll have to work out the details yourself. :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

[RyanEK]

With this approach you could use any over(18) and duration(30) values.

select time, value from (
select *,
(select count(time) from @temp t2 where t2.time > t1.time and t2.time <= dateadd(mi, 30, t1.time)) as reccount,
(select count(time) from @temp t2 where t2.value >= 18 and t2.time > t1.time and t2.time <= dateadd(mi, 30, t1.time)) as okcount
from @temp t1
where value > 18
) t
where t.reccount = t.okcount