Reputation: 95
My data look like this:
ADMINDATE ............ NUMVALUE
14.01.2014 06:08:00 ... 108
14.01.2014 06:58:00 ... 119
14.01.2014 07:58:59 ... 116
14.01.2014 09:00:00 ... 118
14.01.2014 15:52:00 ... 127
14.01.2014 17:00:00 ... 123
*14.01.2014 18:02:59 ... 131
14.01.2014 18:59:59 ... 125
14.01.2014 19:59:59 ... 120
14.01.2014 20:57:59 ... 123
*14.01.2014 21:59:59 ... 138
14.01.2014 22:52:59 ... 118
14.01.2014 23:58:00 ... 117
15.01.2014 00:33:59 ... 121
*15.01.2014 01:00:00 ... 140
15.01.2014 01:25:59 ... 128
*15.01.2014 02:01:00 ... 143
15.01.2014 03:00:00 ... 143
15.01.2014 04:00:00 ... 141
15.01.2014 08:33:59 ... 153
What I want to do is to extract all contiguous time intervals, in which the numvalue is for example less than 130:
14.01.2014 06:08:00 ... 14.01.2014 17:00:00
14.01.2014 18:59:59 ... 14.01.2014 20:57:59
14.01.2014 22:52:59 ... 15.01.2014 00:33:59
15.01.2014 01:25:59 ... 15.01.2014 01:25:59
Is there any way to achieve that in SQL. I tried analytic functions, but couldn't solve it.
Upvotes: 4
Views: 3086
Reputation: 1271241
The key is that values of 130 or more start a new group. You can find these, and then use a cumulative sum to group these together:
select min(AdminDate), max(AdminDate)
from (select t.*, sum(NewGroupStart) over (order by AdminDate) as NewGroupId
from (select t.*, (case when NumValue >= 130 then 1 else 0 end) as NewGroupStart
from t
) t
) t
group by NewGroupId;
Actually, you don't need the nested subquery:
select min(AdminDate), max(AdminDate)
from (select t.*,
sum(case when NumValue >= 130 then 1 else 0 end) over (order by AdminDate) as NewGroupId
from t
) t
group by NewGroupId;
The idea is that it counts the number of rows that occur before any given row where the AdminDate is 130 or more. This serves to identify a group of related rows, which are then aggregated together.
EDIT:
If you are excluding values of 130+, then just filter on the outermost level:
select min(AdminDate), max(AdminDate)
from (select t.*,
sum(case when NumValue >= 130 then 1 else 0 end) over (order by AdminDate) as NewGroupId
from t
) t
where NumValue < 130
group by NewGroupId;
Upvotes: 2