CODEWITHSUNDEEP
sqlsql-servertimegroupinggaps-and-islands
tbru
tbru

Reputation: 5

How to find start and end times where a particular condition is continuously true in SQL Server

I've run into an interesting problem, I'm attempting to tie energy usage logs back to particular events within a production environment. Our production data has been logging for about 6 months, the logged data inserts a record every 30 secs or on change. within these logs 'factory mode' is also logged with the following structure:

t_stamp             tag     value
----------------------------------
11/12/17 21:00:00   mode    10
11/12/17 21:00:30   mode    10
11/12/17 21:01:00   mode    80
11/12/17 21:01:30   mode    80
11/12/17 21:02:00   mode    80
11/12/17 21:02:30   mode    80
….                  mode    80
12/12/17 03:03:30   mode    80
12/12/17 03:04:00   mode    80
12/12/17 03:04:30   mode    80
12/12/17 03:05:00   mode    10
12/12/17 03:05:30   mode    10
12/12/17 03:06:00   mode    10
12/12/17 03:06:30   mode    10

I'd like a query to return a result like:

row    mode    start_t_stamp        end_time_stamp
------------------------------------------------------
1      10      11/12/17 21:00:00    11/12/17 21:00:30
2      80      11/12/17 21:01:00    12/12/17 03:04:30
3      10      12/12/17 03:05:00    12/12/17 03:06:30

once I have the start and end timestamps i'll be able to extract the energy usage data for each of the mode of the factory eg, idle, warmup, ready_standby, production etc and form an average usage for each mode, but I'm stuck at the every first step, I've looked at partition by, read articles on islands and gaps but I can't find an example where to data is logged in this way. any suggestions would be awesome

Upvotes: 0

Views: 51

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

It is a gaps and islands problem. If you have window functions available then you can use the following

select value, 
       min(t_stamp) start_t_stamp , 
       max(t_stamp) end_time_stamp 
from
(
    select *, row_number() over (partition by value order by t_stamp) - 
              row_number() over (order by t_stamp) grn
    from data
) t
group by value, grn

The basic idea is that the consequent rows with the same value will have the same grn. When you have this you can group them easily.

Upvotes: 1

Related Questions